如何为 MySQL 日期时间列设置默认值

Question

如何为 MySQL 日期时间列设置默认值？

在SQL Server中，它是getdate()。MySQL 的等价物是什么？如果这是一个因素，我正在使用MySQL 5.x。

Answer 1

重要编辑：从 MySQL 5.6.5 开始，现在可以使用 DATETIME 字段来实现这一点，看看下面的另一篇文章......

以前的版本不能用日期时间做到这一点...

但是你可以用时间戳来做到这一点：

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 ON 的列，则需要始终为此列指定一个值，否则该值将在更新时自动重置为"now（）"。这意味着，如果您不希望更改值，则 UPDATE 语句必须包含"[您的列名] = [您的列名]"（或其他值），否则该值将变为"now（）"。奇怪，但真实。我正在使用 5.5.56-MariaDB

Answer 2

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

CREATE TABLE foo (
    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)

参考：http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

Answer 3

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

也就是说，您可以通过创建触发器来实现此目的。

我有一个表，其中包含日期时间类型的 DateCreated 字段。 我在该表上创建了一个触发器"插入之前"和"SET NEW.DateCreated=NOW()"，效果很好。

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（） 之类的干净内容，它仍然是贫民窟，但我们越来越近了！

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]_[插入]

Answer 6

您可以使用触发器来执行此类操作。

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;

Answer 7

对于那些试图在MySQL中设置默认DATETIME值的人，我确切地知道你的感受/感受。所以这里是：

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

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

解决完这个问题后，我真的跳了起来:D

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';

Answer 9

MySQL 5.6 已经解决了这个问题。

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

Answer 10

这确实是一个可怕的消息。这是一个长期悬而未决的错误/功能请求。 该讨论还讨论了时间戳数据类型的限制。

我真的想知道实施这件事有什么问题。

Answer 11

您可以使用 now（） 来设置日期时间列的值，但请记住，您不能将其用作默认值。

Answer 12

我正在运行MySql Server 5.7.11和这句话：

ALTER TABLE table_name CHANGE date_column datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

不工作。但以下几点：

ALTER TABLE table_name CHANGE date_column datetime NOT NULL DEFAULT '1000-01-01 00:00:00'

只是工作。

作为旁注，它在 mysql 文档中提到：

DATE

类型用于具有日期部分但没有时间部分的值。 MySQL 检索并以"YYYY-MM-DD"格式显示 DATE 值。支持的范围是"1000-01-01"到"9999-12-31"。

即使他们还说：

无效的日期、日期时间或时间戳值将转换为相应类型的"零"值（"0000-00-00

"或"0000-00-00 00：00：00"）。

Answer 13

使用 MySQL 8.x 工作正常

CREATE TABLE `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `dateCreated` datetime DEFAULT CURRENT_TIMESTAMP,
      `dateUpdated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `mobile_UNIQUE` (`mobile`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Answer 14

对于所有使用 TIMESTAMP 列作为解决方案的人，我想从手册中支持以下限制：

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

"TIMESTAMP 数据类型的范围是 '1970-01-01 00：00：01' UTC 到 '2038-01-19 03：14：07' UTC。它具有不同的属性，具体取决于MySQL版本和服务器运行的SQL模式。本节稍后将介绍这些属性。"

因此，这显然会在大约 28 年内破坏您的软件。

我相信数据库端的唯一解决方案是使用其他答案中提到的触发器。

Answer 15

在定义多行触发器时，必须更改分隔符，因为分号将被MySQL编译器作为触发器的结尾并生成错误。例如

DELIMITER //
CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON `MyTable`
FOR EACH ROW BEGIN
        -- Set the udpate date
    Set new.UpdateDate = now();
END//
DELIMITER ;

Answer 16

以下是在MySQL 5.1上执行此操作的方法：

ALTER TABLE `table_name` CHANGE `column_name` `column_name` 
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

我不知道为什么您必须输入两次列名。

Answer 17

虽然您无法在默认定义中使用 DATETIME 执行此操作，但您可以简单地在插入语句中合并一个 select 语句，如下所示：

INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', (select now()))

请注意，表格周围缺少引号。

对于 MySQL 5.5

Answer 18

如果您尝试将默认值设置为 NOW（），我认为 MySQL 不支持。在 MySQL 中，您不能使用函数或表达式作为任何类型的列的默认值，但 TIMESTAMP 数据类型列除外，您可以将CURRENT_TIMESTAMP指定为默认值。

Answer 19

如果设置ON UPDATE CURRENT_TIMESTAMP则表中的行数据更新将需要当前时间。

 CREATE TABLE bar(
        `create_time` TIMESTAMP CURRENT_TIMESTAMP,
        `update_time` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )

Answer 20

我认为在mysql中很简单，因为mysql是称为now（）的内置函数，它给出了当前时间（插入的时间）。

所以你的查询应该看起来像

CREATE TABLE defaultforTime(
    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modification_time` DATETIME default now()
);

谢谢。

Answer 21

CREATE TABLE `testtable` (
    `id` INT(10) NULL DEFAULT NULL,
    `colname` DATETIME NULL DEFAULT '1999-12-12 12:12:12'
)

在上面的查询中创建"testtable"，我使用"1999-12-12 12：12：12"作为DATETIME列colname的默认值

Answer 22

例如，

如果我有一个名为"site"的表，其中有一个created_at和一个update_at列，它们都是 DATETIME 并且需要默认值 now，我可以执行以下 sql 来实现这一点。

更改表"站点"更改"created_at"created_at"时间戳不为空默认CURRENT_TIMESTAMP;更改表"站点"更改"created_at"created_at"日期时间空默认值空;更改表"站点"更改"updated_at"updated_at"时间戳不为空默认值CURRENT_TIMESTAMP;更改表"站点"更改"updated_at"updated_at"日期时间空默认值空;

语句序列很重要，因为一个表不能有两列 TIMESTAMP 类型的列，默认值为 CUREENT TIMESTAMP

Answer 23

使用以下代码

DELIMITER $$
    CREATE TRIGGER bu_table1_each BEFORE UPDATE ON table1 FOR EACH ROW
    BEGIN
      SET new.datefield = NOW();
    END $$
    DELIMITER ;

Answer 24

如果您尝试将默认值设置为 NOW（），MySQL 支持您必须更改该列 TIMESTAMP 而不是 DATETIME 的类型。时间戳默认具有当前日期和时间。我认为它会解决你的问题。

Answer 25

这是我

的触发器示例：

/************ ROLE ************/
drop table if exists `role`;
create table `role` (
    `id_role` bigint(20) unsigned not null auto_increment,
    `date_created` datetime,
    `date_deleted` datetime,
    `name` varchar(35) not null,
    `description` text,
    primary key (`id_role`)
) comment='';
drop trigger if exists `role_date_created`;
create trigger `role_date_created` before insert
    on `role`
    for each row 
    set new.`date_created` = now();

Answer 26

您可以解析默认时间戳。首先考虑您使用的字符集，例如，如果您使用 utf8，则此字符集支持所有语言，如果您采用 laten1，则此字符集仅支持英语。下一步设置，如果您在任何项目下工作，您应该知道客户时区并选择您是客户区。此步骤是必需的。