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

技术背景

在数据库设计中，经常需要为日期和时间列设置默认值。在SQL Server中，可以使用getdate()函数来为日期时间列设置当前时间作为默认值。而在MySQL里，不同版本对Datetime列设置默认值的支持有所不同。了解如何为MySQL的Datetime列设置默认值，对于数据库的设计和开发至关重要。

实现步骤

1. MySQL 5.6.5之前的版本

在MySQL 5.6.5之前，不能直接为Datetime列使用函数设置默认值，但可以使用TIMESTAMP类型来实现类似功能：

-- 创建表，包含一个TIMESTAMP列并设置默认值为当前时间
CREATE TABLE test (
    str varchar(32), 
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

或者使用触发器来为Datetime列设置默认值：

-- 创建表
CREATE TABLE mytable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date_created DATETIME
);

-- 创建触发器，在插入记录时设置date_created为当前时间
DELIMITER //
CREATE TRIGGER set_date_created BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    SET NEW.date_created = NOW();
END //
DELIMITER ;

2. MySQL 5.6.5及之后的版本

从MySQL 5.6.5开始，可以直接为Datetime列设置默认值，甚至可以设置列在记录更新时自动更新：

-- 创建表，设置creation_time列的默认值为当前时间，modification_time列在记录更新时自动更新
CREATE TABLE foo (
    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
);

3. 修改已存在表的列默认值

如果表已经创建，可以使用ALTER TABLE语句来修改列的默认值：

-- 将created_dt列的默认值设置为当前时间
ALTER TABLE `test_table`
CHANGE COLUMN `created_dt` `created_dt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- 将date_column列的默认值设置为指定时间
ALTER TABLE table_name
CHANGE COLUMN date_column date_column DATETIME NOT NULL DEFAULT '2015-05-11 13:01:01';

核心代码

使用TIMESTAMP类型设置默认值

CREATE TABLE test (
    str varchar(32), 
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

使用触发器为Datetime列设置默认值

DELIMITER //
CREATE TRIGGER set_date_created BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    SET NEW.date_created = NOW();
END //
DELIMITER ;

MySQL 5.6.5及之后版本直接为Datetime列设置默认值

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

最佳实践

• 选择合适的方法：如果使用的是MySQL 5.6.5及之后的版本，推荐直接为Datetime列设置默认值，这样代码更简洁。如果是旧版本，可以考虑使用TIMESTAMP类型或触发器。
• 考虑范围限制：TIMESTAMP类型的范围是’1970-01-01 00:00:01’ UTC到’2038-01-19 03:14:07’ UTC，如果需要处理超出这个范围的日期，应使用Datetime类型。
• 触发器的优化：使用触发器时，要注意避免在插入记录时覆盖用户手动设置的值，可以使用IFNULL函数来实现：

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

常见问题

1. 提示“Invalid default value”错误

这通常是因为使用的MySQL版本不支持为Datetime列设置函数作为默认值。请检查MySQL版本，如果是5.6.5之前的版本，需要使用TIMESTAMP类型或触发器。

2. TIMESTAMP列自动更新问题

如果定义了TIMESTAMP列的默认值为CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，在更新记录时，该列会自动更新为当前时间。如果不希望该列更新，需要在UPDATE语句中明确指定该列的值。

3. 触发器覆盖手动设置的值

使用触发器为Datetime列设置默认值时，如果用户手动设置了该列的值，触发器可能会覆盖这个值。可以使用IFNULL函数来避免这个问题。