是否可以创建一个在MySQL中使用UNIX_TIMESTAMP默认值的列？ [英] Is it possible to create a column with a UNIX_TIMESTAMP default in MySQL?

问题描述

我想这样做，但似乎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.

推荐答案

TIMESTAMP 数据类型，它实际上是将时期时间存储在数据库中。因此，您只需使用默认值为 CURRENT_TIMESTAMP 的 TIMESTAMP 列，并应用 UNIX_TIMESTAMP ）如果你想显示为一个int：

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)

这篇关于是否可以创建一个在MySQL中使用UNIX_TIMESTAMP默认值的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！