mysql 自动添加datetime

基础概念

MySQL中的DATETIME是一种数据类型，用于存储日期和时间值。它可以表示的范围从1000-01-01 00:00:00到9999-12-31 23:59:59。DATETIME类型在数据库中占据8个字节。

自动添加DATETIME

在MySQL中，如果你希望某个字段自动记录数据的创建时间或更新时间，可以使用TIMESTAMP或DATETIME类型的字段，并设置其默认值或触发器。

1. 使用TIMESTAMP

TIMESTAMP类型与DATETIME类似，但它有一个特性：它的值会根据数据库服务器的时间自动更新。你可以设置一个TIMESTAMP字段的默认值为CURRENT_TIMESTAMP，这样每当插入新记录时，该字段都会自动设置为当前时间。

CREATE TABLE example (
    id INT PRIMARY KEY,
    data VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 使用DATETIME和触发器

如果你更喜欢使用DATETIME类型，并且希望在插入或更新记录时自动设置时间，你可以使用触发器。

CREATE TABLE example (
    id INT PRIMARY KEY,
    data VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME
);

DELIMITER //
CREATE TRIGGER before_insert_example
BEFORE INSERT ON example
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END;
//
CREATE TRIGGER before_update_example
BEFORE UPDATE ON example
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END;
//
DELIMITER ;

优势

• 自动化：自动记录时间戳可以减少手动输入错误，并确保数据的时效性。
• 审计跟踪：通过记录创建和更新时间，可以更容易地跟踪数据的变更历史。

类型

• TIMESTAMP：自动更新时间戳，范围较小（1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC）。
• DATETIME：不自动更新，范围较大（1000-01-01 00:00:00 到 9999-12-31 23:59:59）。

应用场景

• 日志记录：记录系统或应用的日志时间。
• 审计跟踪：跟踪数据的创建和修改时间。
• 会话管理：记录用户会话的开始和结束时间。

常见问题及解决方法

问题：为什么TIMESTAMP字段的值没有自动更新？

• 原因：可能是由于MySQL的explicit_defaults_for_timestamp系统变量的设置。当该变量设置为OFF时，TIMESTAMP字段的默认行为可能会被禁用。
• 解决方法：检查并设置explicit_defaults_for_timestamp为ON。

SET GLOBAL explicit_defaults_for_timestamp = ON;

问题：触发器没有按预期工作。

• 原因：可能是触发器的定义有误，或者触发器的执行顺序问题。
• 解决方法：检查触发器的定义，确保语法正确，并考虑使用BEFORE或AFTER触发器来满足需求。

参考链接

• MySQL官方文档 - DATETIME 和 TIMESTAMP
• MySQL官方文档 - 触发器