我想这样做,但似乎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
发布评论