如何为 MySQL Datetime 列设置默认值？

Question

如何为MySQL Datetime列设置默认值？

在SQL Server中，使用getdate()设置默认值。那么在MySQL中该怎么做呢？我正在使用MySQL 5.x版本。

- brian

---

我在MySQL表中使用CURRENT_TIMESTAMP（而不是查询中），或许这也会有所帮助。 - Ruben

这个功能现在已经加入到MySQL 5.6.5中了。希望对某些人有所帮助。http://optimize-this.blogspot.co.uk/2012/04/datetime-default-now-finally-available.html - GhostInTheSecureShell

@GhostInTheSecureShell 安装它确实是一项艰巨的任务，至少在 Debian 上是这样，但绝对是一个很棒的功能。我认为最终所有这些“两个CURRENT_TIMESTAMP”的问题都将得到缓解！ - Marc DiMillo

如果不使用now()函数，您可以在列级别上使用默认设置。 - Anshul Sharma

Answer 1

重要编辑:自MySQL 5.6.5版本开始，使用DATETIME字段可以实现这一点，请查看下面的其他帖子...

早期版本无法使用DATETIME实现此功能...

但是您可以使用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>

注意：如果你定义一个默认为CURRENT_TIMESTAMP的列，那么你必须总是为这个列指定一个值，否则在更新时该列的值会自动重置为"now()"。这意味着，如果你不想改变这个值，在UPDATE语句中必须包含"[你的列名] = [你的列名]"（或其他某个值），否则该值将变为"now()"。虽然奇怪，但确实如此。我正在使用5.5.56-MariaDB。

- sebthebert

---

需要注意的是datetime的范围是1000年至9999年，但是时间戳(timestamp)的范围仅限于1970年至2038年。如果你的系统需要存储出生日期或者要处理一个30年抵押贷款的付款计划等，这可能会成为一个问题。参考链接：http://dev.mysql.com/doc/refman/5.0/en/datetime.html - Kip

注意时间戳，因为它会自动神奇地更新...请参阅下一个答案 https://dev59.com/gnVC5IYBdhLWcg3w1E7w#1483959。 - Cerber

从5.6.5版本开始就可以实现，参见下面Gustav的答案（我意识到你的回答是在MySQL还是5.0版本时发布的！） - Déjà vu

你好，@Kip，是否可以为 DATE 类型的列设置默认值？（不是 datetime 列） - Sajib Acharya

似乎无法将DATE列转换为DATETIME数据类型。您必须使用DATETIME数据类型。 - Fabio Napodano

这个警告对于mysql 5.6来说并不适用，除非你实际上指定了ON-UPDATE值，例如 mydate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。根据文档和我的尝试。- https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html - Curtis Yallop

Answer 2

在5.6.5版本中，可以在datetime列上设置默认值，并且甚至可以创建一个在行更新时也会更新的列。类型定义如下：

在5.6.5版本中，可以在datetime列上设置默认值，并且甚至可以创建一个在行更新时也会更新的列。类型定义：

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

- Gustav Bertram

---

'menu_creation_time' 的默认值无效。 - Fernando Trindade

@FernandoTrindade 您需要MySQL版本5.6.5或更高版本。您可以在此处查看其运行情况：http://sqlfiddle.com/#!9/dd2be - Gustav Bertram

@GustavBertram，我正在使用版本5.6.22，但仍然出现以下错误：CREATE TABLE tbl（ InsertDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP， UpdateDate TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP（6））; 错误代码：1294。'UpdateDate'列的ON UPDATE子句无效。 - Manish Sapkal

@ManishSapkal，你正在使用TIMESTAMP而不是DATETIME。此外，不要将其设置为NULL。 - Gustav Bertram

我认为“ON UPDATE”语法是错误的。根据https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html ，应该是 dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。 - Dan Bough

这个解决方案对我不起作用。创建表时出错 /* SQL错误（1067）：'creation_time'的默认值无效 */ - Jam

Answer 3

MySQL（5.6.5版本之前）不允许在默认DateTime值中使用函数。由于其奇怪的行为，TIMESTAMP不适合作为输入数据，并且不建议使用。（请参见MySQL数据类型默认值。）

话虽如此，您可以通过创建触发器来实现此目的。

我有一个类型为DateTime的DateCreated字段的表。我在该表上创建了一个触发器“Before Insert”和“SET NEW.DateCreated = NOW()”，效果非常好。

- Stephan Unrau

---

CREATE TRIGGER trigger_foo_SetCreatedAt BEFORE INSERT ON foo FOR EACH ROW SET NEW.created_at = UTC_TIMESTAMP();在foo表中插入新行之前，创建名为trigger_foo_SetCreatedAt的触发器。对于每一行，设置它的created_at字段为当前的UTC时间戳。 - kgriffs

使用时间戳比触发器更有利于未来的可维护性和健壮性。虽然触发器可以解决这个问题，但这太琐碎了，不值得使用触发器。 - getWeberForStackExchange

如果NEW.created_at不为空，你可能只想设置默认值。 - Petr Peller

Answer 4

对我来说，触发器方法效果最好，但是我发现了这种方法的一个问题。考虑基本触发器，在插入时将日期字段设置为当前时间：

CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
    FOR EACH ROW SET NEW.dateAdded = NOW();

通常情况下这很好，但是假设你想通过INSERT语句手动设置字段的值，像这样：

INSERT INTO tblMyTable(name, dateAdded) VALUES('Alice', '2010-01-03 04:30:43');

触发器会立即覆盖您提供的字段值，因此设置非当前时间的唯一方法是后续的UPDATE语句。为了在提供值时覆盖此行为，请尝试使用IFNULL运算符稍微修改过的触发器：

CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
    FOR EACH ROW SET NEW.dateAdded = IFNULL(NEW.dateAdded, NOW());

这样做可以兼顾两方面的需求：您可以为日期列提供一个值，否则它将默认为当前时间。与表定义中的DEFAULT GETDATE()之类的清晰方法相比，它仍然不够完美，但我们正在逐渐接近！

- John Larson

---

感谢您在插入时承认覆盖显式用户值的注意事项。+1。 - Kip

我个人认为这是最好的方式。TIMESTAMP确实有奇怪的行为，我永远不会编写具有2038年限制的代码。 - Eric

没关系，我解决了。忘记设置字段允许“NULL”了。现在没有警告了。 - Kaji

触发器很糟糕！只有在没有其他方法时才使用它们。最好升级到5.6.x，而不是使用触发器。 - ksymeon

在MySQL 5.5中，IFNULL函数无法作用于DATETIME类型数据；使用以上触发器会显示所有的“0000-00-00 00:00:00”。以下代码可解决此问题： FOR EACH ROW IF NEW.dateAdded = 0 THEN SET NEW.dateAdded = NOW(); END IF; - Kenney

Answer 5

我能够使用这个 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;

这个函数的工作方式与你对now()函数的期望相同。如果插入null值或忽略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();

触发器可以是任何你想要的，我只喜欢命名约定 [trig]_[my_table_name]_[insert]

- user188217

---

我的意思是，如果您通过MySQL查询浏览器手动通过网格插入记录而没有insert()语句，则需要触发器。如果您始终使用insert语句，则触发器完全不必要。 - user188217

糟糕！我的意思是触发器（名称）可以是任何你想要的，因为触发器名称并不影响功能。大多数人都知道这一点，但一些新手可能不清楚MySQL的情况... - user188217

抱歉，没有换行符可能有些混乱。请使用分号标记每行的结尾。 - user188217

Answer 6

对于那些在尝试设置默认的DATETIME值时感到沮丧的人，我知道你们的心情。这里是方法：

ALTER TABLE  `table_name` CHANGE `column_name` DATETIME NOT NULL DEFAULT 0

仔细观察，我没有在0周围添加单引号/双引号

我真的很高兴解决了这个问题:D

- Augiwan

---

它不会插入当前时间。它将插入“0000-00-00 00:00:00”。 - Pit Digger

我并没有说它设置当前时间。许多人一直试图设置默认的零值，但这根本行不通。我们需要消除引号。由于这个发现浪费了很多时间和精力，所以我想与社区分享。像你这样的人让用户失去了分享有用信息给其他人的兴趣。我真的看不出为什么要得到一个-1！无论怎样。 - Augiwan

DATETIME NOT NULL也能达到同样的效果。 - SineSwiper

在示例 SQL 命令中还有一个 ` 字符，我无法编辑掉它，因为只需要编辑一个字符。 - quapka

你的代码对我没用，这是有效的代码：ALTER TABLE zones MODIFY created datetime NOT NULL DEFAULT 0; - Smith

Answer 7

你可以使用触发器来完成这种操作。

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;

- Donald

Answer 8

如果您已经创建了表格，则可以使用将默认值更改为当前日期时间。

ALTER TABLE <TABLE_NAME> 
CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

将默认值更改为“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';

- Saveendra Ekanayake

Answer 9

MySQL 5.6已修复此问题。

ALTER TABLE mytable CHANGE mydate datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP'

- Steven Lloyd

Answer 10

这确实是个坏消息。这里是一个长期未解决的Bug / 功能请求讨论了时间戳数据类型的限制。

我真的很想知道为什么无法实现这个功能。

- kinjal

---

发生了：http://dev.mysql.com/doc/refman/5.6/zh-cn/timestamp-initialization.html - drewish