MySQL刷新令牌表触发器致DATETIME值丢失问题排查

问题背景 #

搭建MySQL认证服务器时，使用以下表存储刷新令牌：

CREATE TABLE IF NOT EXISTS REFRESH_TOKEN (
    TOKEN VARCHAR(512) NOT NULL,
    EXPIRATION DATETIME
) ENGINE=INNODB;

为自动转换Unix时间戳为DATETIME格式，创建了触发器：

DROP TRIGGER IF EXISTS TG_RT_EXP;
DELIMITER //
CREATE TRIGGER TG_RT_EXP BEFORE INSERT ON REFRESH_TOKEN
FOR EACH ROW 
BEGIN
    SET NEW.EXPIRATION = FROM_UNIXTIME(NEW.EXPIRATION);
END;//
DELIMITER ;

执行以下插入语句时出现1265截断警告，EXPIRATION值为1969-12-31 16:00:00（预期为Unix时间戳1796239022对应的日期）：

INSERT INTO REFRESH_TOKEN
(TOKEN, EXPIRATION)
VALUES (
    'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c',
    1796239022
);

移除触发器后，EXPIRATION变为0000-00-00 00:00:00；但直接在插入语句中使用FROM_UNIXTIME(1796239022)却能正确存储。

根本原因 #

1. MySQL自动类型转换优先级高于触发器 #

向DATETIME列传入数字1796239022时，MySQL会在触发器执行前自动尝试将该数字转换为DATETIME类型。MySQL对数字转DATETIME的规则是把数字解析为YYYYMMDDHHMMSS格式的整数（例如20240520123000代表2024-05-20 12:30:00），而1796239022不符合这个格式（拆解后年份为17、月份为96，均为无效值），因此转换结果为0000-00-00 00:00:00。

此时触发器中NEW.EXPIRATION已经是0000-00-00 00:00:00，调用FROM_UNIXTIME()时会把这个无效日期对应的数值（0）转换为Unix时间戳起始点的本地时间（即1969-12-31 16:00:00，对应UTC-8时区）。

2. 直接使用FROM_UNIXTIME()的区别 #

插入语句中直接写FROM_UNIXTIME(1796239022)时，函数会先将数字Unix时间戳转换为合法的DATETIME字符串，再传入列中，MySQL不需要做自动类型转换，因此能正确存储。

解决办法 #

方法一：修改列类型存储原始Unix时间戳 #

将EXPIRATION列改为INT UNSIGNED类型，直接存储Unix时间戳，查询时再做转换：

-- 修改列类型
ALTER TABLE REFRESH_TOKEN MODIFY COLUMN EXPIRATION INT UNSIGNED;

-- 插入语句（无需触发器）
INSERT INTO REFRESH_TOKEN
(TOKEN, EXPIRATION)
VALUES (
    'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c',
    1796239022
);

-- 查询时转换为DATETIME
SELECT TOKEN, FROM_UNIXTIME(EXPIRATION) AS EXPIRATION FROM REFRESH_TOKEN;

方法二：调整触发器逻辑，接收字符串形式的时间戳 #

如果要保留DATETIME列，插入时将时间戳作为字符串传入，触发器中先转换为整数再处理：

-- 更新触发器
DROP TRIGGER IF EXISTS TG_RT_EXP;
DELIMITER //
CREATE TRIGGER TG_RT_EXP BEFORE INSERT ON REFRESH_TOKEN
FOR EACH ROW 
BEGIN
    SET NEW.EXPIRATION = FROM_UNIXTIME(CAST(NEW.EXPIRATION AS UNSIGNED));
END;//
DELIMITER ;

-- 插入时将时间戳用引号括为字符串
INSERT INTO REFRESH_TOKEN
(TOKEN, EXPIRATION)
VALUES (
    'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c',
    '1796239022'
);

方法三：插入时显式转换（最直接） #

直接在插入语句中使用FROM_UNIXTIME()转换时间戳，无需触发器：

INSERT INTO REFRESH_TOKEN
(TOKEN, EXPIRATION)
VALUES (
    'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c',
    FROM_UNIXTIME(1796239022)
);

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