是否可以在 MySQL 中创建一个具有 UNIX_TIMESTAMP 默认值的列?

2021-12-26 00:00:00 sql database database-schema mysql database-design

我正在尝试这样做，但 MySQL 似乎不允许我这样做.是否有解决此问题的方法，还是希望我始终在 INSERT 查询中包含该函数?

I'm trying to do this, but it seems like MySQL isn't allowing me. Is there a solution to this issue or am I expected to always include the function in my INSERT queries?

CREATE TABLE foo(
  created INT NOT NULL DEFAULT UNIX_TIMESTAMP()
)

我知道接受 CURRENT_TIMESTAMP 默认值的 TIMESTAMP 类型，但我的客户坚持在数据库中使用纪元时间.

I'm aware of the TIMESTAMP type that accepts a CURRENT_TIMESTAMP default, but my client insisted on using epoch time in the database.

推荐答案

MySQL 实现TIMESTAMP 数据类型的方式，实际上是在数据库中存储纪元时间.所以你可以只使用 TIMESTAMP 列的默认值 CURRENT_TIMESTAMP 并应用 UNIX_TIMESTAMP() 如果你想将它显示为内部:

The way MySQL implements the TIMESTAMP data type, it is actually storing the epoch time in the database. So you could just use a TIMESTAMP column with a default of CURRENT_TIMESTAMP and apply the UNIX_TIMESTAMP() to it if you want to display it as an int:

CREATE TABLE foo(
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

insert into foo values (current_Date()),(now());

select unix_timestamp(created) from foo;
+-------------------------+
| unix_timestamp(created) |
+-------------------------+
|              1300248000 |
|              1300306959 |
+-------------------------+
2 rows in set (0.00 sec)

但是，如果您确实希望列的数据类型为 INT，则可以使用 R. Bemrose 的建议并通过触发器进行设置:

However, if you really want the datatype of the column to be INT, you can use R. Bemrose's suggestion and set it via trigger:

CREATE TABLE foo(
  created INT NULL
);

delimiter $$

create trigger tr_b_ins_foo before insert on foo for each row
begin
  if (new.created is null)
  then
    set new.created = unix_timestamp();
  end if;
end $$

delimiter ;


insert into foo values (unix_timestamp(current_Date())), (null);

select created from foo;
+------------+
| created    |
+------------+
| 1300248000 |
| 1300306995 |
+------------+
2 rows in set (0.00 sec)

相关文章