MySQL 在插入时在 DATETIME 字段中设置当前日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1138928/
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
MySQL set current date in a DATETIME field on insert
提问by William
I have a 'created_date' DATETIME field which I would like to be populated with the current date upon insert. What syntax should I use for the trigger for this? This is what I have so far but it is incorrect.
我有一个“created_date”DATETIME 字段，我想在插入时用当前日期填充它。我应该为此触发器使用什么语法？这是我到目前为止所拥有的，但它是不正确的。
CREATE TRIGGER set_created_date
BEFORE INSERT ON product
FOR EACH ROW BEGIN
SET NEW.created_date = now();
采纳答案by dunedain289
Your best bet is to change that column to a timestamp. MySQL will automatically use the first timestamp in a row as a 'last modified' value and update it for you. This is configurable if you just want to save creation time.
最好的办法是将该列更改为时间戳。MySQL 将自动使用一行中的第一个时间戳作为“最后修改”值并为您更新它。如果您只想节省创建时间，这是可以配置的。
See doc http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
见文档http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
回答by pevik
DELIMITER ;;
CREATE TRIGGER `my_table_bi` BEFORE INSERT ON `my_table` FOR EACH ROW
BEGIN
SET NEW.created_date = NOW();
END;;
DELIMITER ;
回答by Coreus
Since MySQL 5.6.X you can do this:
从 MySQL 5.6.X 开始，你可以这样做：
ALTER TABLE `schema`.`users`
CHANGE COLUMN `created` `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;
That way your column will be updated with the current timestamp when a new row is inserted, or updated.
这样，当插入或更新新行时，您的列将使用当前时间戳更新。
If you're using MySQL Workbench, you can just put CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPin the DEFAULT value field, like so:
如果您使用的是 MySQL Workbench，则只需CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP输入 DEFAULT 值字段，如下所示：
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html
回答by Hafiz Shehbaz Ali
Using Now() is not a good idea. It only save the current time and date. It will not update the the current date and time, when you update your data. If you want to add the time once, The default value =Now() is best option. If you want to use timestamp. and want to update the this value, each time that row is updated. Then, trigger is best option to use.
使用 Now() 不是一个好主意。它只保存当前时间和日期。当您更新数据时，它不会更新当前日期和时间。如果你想添加一次时间，默认值 =Now() 是最好的选择。如果你想使用时间戳。并且想要在每次更新该行时更新此值。然后，触发器是最好的选择。
- http://www.mysqltutorial.org/sql-triggers.aspx
- http://www.tutorialspoint.com/plsql/plsql_triggers.htm
- http://www.mysqltutorial.org/sql-triggers.aspx
- http://www.tutorialspoint.com/plsql/plsql_triggers.htm
These two toturial will help to implement the trigger.
这两个 toturial 将有助于实现触发器。
