是否可以创建一个在MySQL中使用UNIX

本文介绍了是否可以创建一个在MySQL中使用UNIX_TIMESTAMP默认值的列？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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