MySQL/MariaDB GENERATED ALWAYS列中from_unixtime替代方案

FROM_UNIXTIME() 属于非确定性函数：返回结果受当前会话时区设置影响，相同Unix时间戳在不同时区配置下会返回不同日期时间值。MySQL/MariaDB 要求 STORED 类型的生成列必须使用确定性函数作为计算表达式（保证列值写入存储后，不会因环境变量变化出现逻辑不一致），因此原SQL无法执行。

补充：原SQL中FROM_UNIXTIME的格式字符串'%Y-%M-%D'写法有误：%M返回英文月份全称、%D返回带英文序数后缀的日期（如1st、2nd），返回结果无法被正确解析为DATE类型。要获取标准日期值，不需要传入自定义格式参数，直接在外层套DATE()函数即可。

方案1：改写为确定性表达式，保留STORED生成列 #

只要在时间转换时固定时区偏移，不依赖会话级时区变量，整个表达式就满足确定性要求，可以正常创建STORED生成列。以业务使用东八区（UTC+8）为例，语句如下：

ALTER TABLE messages 
ADD COLUMN only_date DATE 
GENERATED ALWAYS AS (
  DATE('1970-01-01 00:00:00' + INTERVAL timestamp_column SECOND + INTERVAL 8 HOUR)
) STORED;

如果使用UTC时区，去掉末尾的+ INTERVAL 8 HOUR即可；其他时区对应调整偏移小时数即可。该写法完全不依赖会话配置，相同时间戳输入永远返回固定日期值，符合STORED生成列的所有要求。

方案2：使用VIRTUAL类型生成列（无额外存储开销） #

如果不需要把日期值实际持久化到磁盘（查询时动态计算，不占用额外存储空间），VIRTUAL类型的生成列不要求表达式必须为确定性函数，可以直接使用FROM_UNIXTIME，写法如下：

ALTER TABLE messages 
ADD COLUMN only_date DATE 
GENERATED ALWAYS AS (DATE(FROM_UNIXTIME(timestamp_column))) VIRTUAL;

该类型的生成列支持创建索引，性能与普通列差异极小，适合写多读少、对存储空间敏感的场景。

方案3：触发器自动填充（全版本兼容） #

如果需要动态跟随会话时区计算日期、同时要求值持久化存储，可以放弃生成列，改用普通DATE列+BEFORE INSERT/BEFORE UPDATE触发器实现自动赋值，效果和STORED生成列完全一致。

1. 先添加普通日期列

   ALTER TABLE messages ADD COLUMN only_date DATE NULL;
   

2. 创建插入自动赋值触发器

   DELIMITER //
   CREATE TRIGGER trg_messages_only_date_insert
   BEFORE INSERT ON messages
   FOR EACH ROW
   BEGIN
       SET NEW.only_date = DATE(FROM_UNIXTIME(NEW.timestamp_column));
   END //
   DELIMITER ;
   

3. 创建更新自动赋值触发器

   DELIMITER //
   CREATE TRIGGER trg_messages_only_date_update
   BEFORE UPDATE ON messages
   FOR EACH ROW
   BEGIN
       IF NEW.timestamp_column <> OLD.timestamp_column THEN
           SET NEW.only_date = DATE(FROM_UNIXTIME(NEW.timestamp_column));
       END IF;
   END //
   DELIMITER ;
   

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