是否可以在 MySQL 中创建一个具有 UNIX_TIMESTAMP 默认值的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5331026/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to create a column with a UNIX_TIMESTAMP default in MySQL?
提问by Kit Sunde
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?
我正在尝试这样做，但似乎 MySQL 不允许我这样做。是否有解决此问题的方法，还是希望我始终在 INSERT 查询中包含该函数？
CREATE TABLE foo(
created INT NOT NULL DEFAULT UNIX_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.
我知道接受 CURRENT_TIMESTAMP 默认值的 TIMESTAMP 类型，但我的客户坚持在数据库中使用纪元时间。
回答by Ike Walker
The way MySQL implements the TIMESTAMPdata type, it is actually storing the epoch time in the database. So you could just use a TIMESTAMPcolumn with a default of CURRENT_TIMESTAMPand apply the UNIX_TIMESTAMP()to it if you want to display it as an int:
MySQL实现TIMESTAMP数据类型的方式，其实就是在数据库中存储纪元时间。因此，如果要将其显示为 int，您可以只使用TIMESTAMP默认为 of的列CURRENT_TIMESTAMP并将其应用于UNIX_TIMESTAMP()它：
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)
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:
但是，如果您确实希望列的数据类型为INT，则可以使用 R. Bemrose 的建议并通过触发器进行设置：
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)
回答by Joe Stefanelli
From the documentation:
从文档：
With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.
除了一个例外，默认值必须是一个常量；它不能是函数或表达式。这意味着，例如，您不能将日期列的默认值设置为 NOW() 或 CURRENT_DATE 等函数的值。例外是您可以将 CURRENT_TIMESTAMP 指定为 TIMESTAMP 列的默认值。
回答by Arjun Raj
You can create triggers for this.
您可以为此创建触发器。
for insertion
用于插入
- query
- 询问
CREATE TRIGGER {trigger_name} BEFORE INSERT ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
CREATE TRIGGER {trigger_name} BEFORE INSERT ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
- in this case
- 在这种情况下
CREATE TRIGGER my_trigger_name_1 BEFORE INSERT ON foo FOR EACH ROW SET new.created = UNIX_TIMESTAMP(NOW());
CREATE TRIGGER my_trigger_name_1 BEFORE INSERT ON foo FOR EACH ROW SET new.created = UNIX_TIMESTAMP(NOW());
for update
更新
- query
- 询问
CREATE TRIGGER {trigger_name} BEFORE UPDATE ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
CREATE TRIGGER {trigger_name} BEFORE UPDATE ON {table_name} FOR EACH ROW SET new.{field_name} = UNIX_TIMESTAMP(NOW());
- in this case
- 在这种情况下
CREATE TRIGGER my_trigger_name_2 BEFORE UPDATE ON foo FOR EACH ROW SET new.created = UNIX_TIMESTAMP(NOW());
CREATE TRIGGER my_trigger_name_2 BEFORE UPDATE ON foo FOR EACH ROW SET new.created = UNIX_TIMESTAMP(NOW());
Note :I have no idea about the performance of MYSQL TRIGGER
Please go through these links
注意：我不知道MYSQL TRIGGER的性能
请通过这些链接
回答by Powerlord
Well, if MySQL won't let you do it directly, you can always do it using a BEFORE INSERT... FOR EACH ROWtrigger.
好吧，如果 MySQL 不允许您直接执行此操作，则始终可以使用BEFORE INSERT... FOR EACH ROWtrigger执行此操作。
