如何为 MySQL 日期时间列设置默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/168736/
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
How do you set a default value for a MySQL Datetime column?
提问by Brian Boatright
How do you set a default value for a MySQL Datetime column?
如何为 MySQL 日期时间列设置默认值？
In SQL Server it's getdate(). What is the equivalant for MySQL? I'm using MySQL 5.x if that is a factor.
在 SQL Server 中，它是getdate(). MySQL 的等价物是什么？如果这是一个因素，我正在使用 MySQL 5.x。
采纳答案by sebthebert
IMPORTANT EDIT:It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other postbelow...
重要编辑：从MySQL 5.6.5 开始，现在可以使用 DATETIME 字段来实现这一点，请查看下面的其他帖子...
Previous versions can't do that with DATETIME...
以前的版本不能用 DATETIME 做到这一点......
But you can do it with TIMESTAMP:
但是你可以用 TIMESTAMP 来做到：
mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)
mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str | varchar(32) | YES | | NULL | |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)
mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+---------------------+
| str | ts |
+------+---------------------+
| demo | 2008-10-03 22:59:52 |
+------+---------------------+
1 row in set (0.00 sec)
mysql>
**CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I hope this helps. I am using 5.5.56-MariaDB **
**CAVEAT：如果您定义一个默认为 CURRENT_TIMESTAMP ON 的列，您将需要始终为此列指定一个值，否则该值将在更新时自动重置为“now()”。这意味着如果您不想更改该值，则您的 UPDATE 语句必须包含“[您的列名] = [您的列名]”（或某些其他值），否则该值将变为“now()”。很奇怪，但确实如此。我希望这有帮助。我正在使用 5.5.56-MariaDB **
回答by Gustav Bertram
In version 5.6.5, it is possible to set a default value on a datetime column, and even make a column that will update when the row is updated. The type definition:
在 5.6.5 版本中，可以在日期时间列上设置默认值，甚至可以创建一个在行更新时更新的列。类型定义：
CREATE TABLE foo (
`creation_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)
Reference: http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html
参考：http: //optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html
回答by Stephan Unrau
MySQL (before version 5.6.5) does not allow functions to be used for default DateTime values. TIMESTAMP is not suitable due to its odd behavior and is not recommended for use as input data. (See MySQL Data Type Defaults.)
MySQL（版本 5.6.5 之前）不允许将函数用于默认 DateTime 值。TIMESTAMP 由于其奇怪的行为而不适合，不建议用作输入数据。（请参阅MySQL 数据类型默认值。）
That said, you can accomplish this by creating a Trigger.
也就是说，您可以通过创建 Trigger来完成此操作。
I have a table with a DateCreated field of type DateTime. I created a trigger on that table "Before Insert" and "SET NEW.DateCreated=NOW()" and it works great.
我有一个带有 DateTime 类型的 DateCreated 字段的表。我在该表“插入前”和“ SET NEW.DateCreated=NOW()”上创建了一个触发器，效果很好。
I hope this helps somebody.
我希望这对某人有帮助。
回答by John Larson
For me the trigger approach has worked the best, but I found a snag with the approach. Consider the basic trigger to set a date field to the current time on insert:
对我来说，触发方法效果最好，但我发现这种方法有一个障碍。考虑在插入时将日期字段设置为当前时间的基本触发器：
CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
FOR EACH ROW SET NEW.dateAdded = NOW();
This is usually great, but say you want to set the field manually via INSERT statement, like so:
这通常很好，但假设您想通过 INSERT 语句手动设置字段，如下所示：
INSERT INTO tblMyTable(name, dateAdded) VALUES('Alice', '2010-01-03 04:30:43');
What happens is that the trigger immediately overwrites your provided value for the field, and so the only way to set a non-current time is a follow up UPDATE statement--yuck! To override this behavior when a value is provided, try this slightly modified trigger with the IFNULL operator:
发生的情况是触发器立即覆盖您为该字段提供的值，因此设置非当前时间的唯一方法是跟进 UPDATE 语句 - 哎呀！要在提供值时覆盖此行为，请尝试使用 IFNULL 运算符稍微修改此触发器：
CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
FOR EACH ROW SET NEW.dateAdded = IFNULL(NEW.dateAdded, NOW());
This gives the best of both worlds: you can provide a value for your date column and it will take, and otherwise it'll default to the current time. It's still ghetto relative to something clean like DEFAULT GETDATE() in the table definition, but we're getting closer!
这提供了两全其美的好处：您可以为日期列提供一个值，它将需要，否则它将默认为当前时间。相对于表定义中的 DEFAULT GETDATE() 等干净的东西，它仍然是贫民窟，但我们越来越近了！
回答by John Larson
I was able to solve this using this alter statement on my table that had two datetime fields.
我能够在有两个日期时间字段的表上使用这个 alter 语句来解决这个问题。
ALTER TABLE `test_table`
CHANGE COLUMN `created_dt` `created_dt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
CHANGE COLUMN `updated_dt` `updated_dt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
This works as you would expect the now() function to work. Inserting nulls or ignoring the created_dt and updated_dt fields results in a perfect timestamp value in both fields. Any update to the row changes the updated_dt. If you insert records via the MySQL query browser you needed one more step, a trigger to handle the created_dt with a new timestamp.
这就像您期望 now() 函数工作一样工作。插入空值或忽略 created_dt 和 updated_dt 字段会在两个字段中产生完美的时间戳值。对该行的任何更新都会更改 updated_dt。如果您通过 MySQL 查询浏览器插入记录，您还需要一个步骤，即使用新时间戳处理 created_dt 的触发器。
CREATE TRIGGER trig_test_table_insert BEFORE INSERT ON `test_table`
FOR EACH ROW SET NEW.created_dt = NOW();
The trigger can be whatever you want I just like the naming convention [trig]_[my_table_name]_[insert]
触发器可以是任何你想要的我就像命名约定 [trig]_[my_table_name]_[insert]
回答by Donald
You can use triggers to do this type of stuff.
您可以使用触发器来执行此类操作。
CREATE TABLE `MyTable` (
`MyTable_ID` int UNSIGNED NOT NULL AUTO_INCREMENT ,
`MyData` varchar(10) NOT NULL ,
`CreationDate` datetime NULL ,
`UpdateDate` datetime NULL ,
PRIMARY KEY (`MyTable_ID`)
)
;
CREATE TRIGGER `MyTable_INSERT` BEFORE INSERT ON `MyTable`
FOR EACH ROW BEGIN
-- Set the creation date
SET new.CreationDate = now();
-- Set the udpate date
Set new.UpdateDate = now();
END;
CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON `MyTable`
FOR EACH ROW BEGIN
-- Set the udpate date
Set new.UpdateDate = now();
END;
回答by Augiwan
For all those who lost heart trying to set a default DATETIMEvalue in MySQL, I know exactly how you feel/felt. So here is is:
对于所有试图在MySQL 中设置默认DATETIME值而失去信心的人，我确切地知道您的感受/感受。所以这里是：
ALTER TABLE `table_name` CHANGE `column_name` DATETIME NOT NULL DEFAULT 0
Carefully observe that I haven't added single quotes/double quotesaround the 0
仔细观察我没有在0周围添加单引号/双引号
I'm literally jumping after solving this one :D
解决这个问题后，我真的要跳起来了：D
回答by Steven Lloyd
MySQL 5.6 has fixed this problem.
ALTER TABLE mytable CHANGE mydate datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP'
回答by Saveendra Ekanayake
If you have already created the table then you can use
如果您已经创建了表，那么您可以使用
To change default value to current date time
将默认值更改为当前日期时间
ALTER TABLE <TABLE_NAME>
CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
To change default value to '2015-05-11 13:01:01'
将默认值更改为“2015-05-11 13:01:01”
ALTER TABLE <TABLE_NAME>
CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> DATETIME NOT NULL DEFAULT '2015-05-11 13:01:01';
回答by Kinjal Dixit
this is indeed terrible news.here is a long pending bug/feature request for this. that discussion also talks about the limitations of timestamp data type.
这确实是个可怕的消息。这是一个长期未决的错误/功能请求。该讨论还讨论了时间戳数据类型的限制。
I am seriously wondering what is the issue with getting this thing implemented.
我很想知道实现这件事有什么问题。
