MySQL中INT/BIGINT字段实现ON UPDATE Unix时间戳的方案咨询

关于TIMESTAMP类型的存储方式 #

MySQL的TIMESTAMP类型确实是以Unix时间戳（32位整数，精确到秒）底层存储的，只是在查询时默认转换为YYYY-MM-DD HH:MM:SS的可读格式显示。它的取值范围受限于32位整数，对应UTC时间'1970-01-01 00:00:01'到'2038-01-19 03:14:07'。

自动更新Unix时间戳字段的替代方案 #

你提到的BIGINT NOT NULL DEFAULT UNIX_TIMESTAMP() ON UPDATE UNIX_TIMESTAMP()语法不可行，因为MySQL不支持给整数类型字段设置ON UPDATE自动更新规则，以下是两种可行方案：

方案1：用TIMESTAMP类型配合查询转换（推荐） #

直接使用TIMESTAMP类型实现自动更新，查询时再转换为Unix时间戳，兼顾自动更新需求和性能：

-- 创建字段
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- 查询时获取Unix时间戳
SELECT UNIX_TIMESTAMP(updated_at) AS updated_at_unix FROM your_table;

这种方案无需额外代码维护，MySQL内部处理转换的开销极低，完全能满足性能需求。

方案2：用触发器实现BIGINT字段的自动更新 #

如果必须存储BIGINT类型的时间戳，可以通过触发器实现插入和更新时的自动赋值：

插入触发器

DELIMITER //
CREATE TRIGGER trigger_table_insert_updated_at BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    SET NEW.updated_at = UNIX_TIMESTAMP();
END //
DELIMITER ;

更新触发器

DELIMITER //
CREATE TRIGGER trigger_table_update_updated_at BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
    SET NEW.updated_at = UNIX_TIMESTAMP();
END //
DELIMITER ;

注意替换your_table为实际表名，触发器会在每一行插入/更新前自动设置时间戳，但高并发场景下会有轻微性能开销，不如TIMESTAMP方案轻量。

额外的MySQL日期时间戳知识 #

• DATETIME类型：与TIMESTAMP不同，它以8字节存储字符串形式的日期时间，无时区转换，取值范围更大（'1000-01-01 00:00:00'到'9999-12-31 23:59:59'），但不支持原生ON UPDATE，需通过触发器实现自动更新。
• 毫秒级时间戳：MySQL 5.6+支持毫秒精度，若需要毫秒级Unix时间戳，可使用UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3))获取，对应字段可以用BIGINT存储（因为毫秒级时间戳需要64位整数）。
• 时区影响：TIMESTAMP会随MySQL时区设置转换存储和显示的时间；而BIGINT存储的Unix时间戳是UTC时间，不受时区影响，查询时无需转换时区。

内容的提问来源于stack exchange，提问作者LanternGaming