为JSON传递的时间戳创建索引时出现日期时间值错误的解决方法

你遇到的核心问题是：JSON里的ISO 8601格式时间（比如2021-12-26T14:00:00.007294Z）无法被MariaDB的TIMESTAMP类型直接识别。之前的虚拟列定义只是单纯提取字符串，没有做格式转换，导致创建索引时触发数据校验错误。

解决方案：显式转换时间格式 #

你需要在虚拟列的定义中，用STR_TO_DATE函数把JSON中的时间字符串转换成MariaDB支持的TIMESTAMP格式，同时确保虚拟列是可存储类型（因为索引需要实际存储的值）。

1. 修改现有表的虚拟列定义

如果表已经创建，执行以下语句修改time列：

ALTER TABLE some_table
MODIFY COLUMN time TIMESTAMP AS (STR_TO_DATE(JSON_VALUE(my_json, '$.time'), '%Y-%m-%dT%H:%i:%s.%fZ')) STORED;

2. （可选）重新创建表的正确写法

如果是从头建表，直接用这个语句：

CREATE TABLE some_table (
    my_json JSON NOT NULL,
    time TIMESTAMP AS (STR_TO_DATE(JSON_VALUE(my_json, '$.time'), '%Y-%m-%dT%H:%i:%s.%fZ')) STORED,
    some_measure DOUBLE AS (JSON_VALUE(my_json, '$.some_measure')) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

这里的格式符%Y-%m-%dT%H:%i:%s.%fZ完全匹配你的时间字符串：

• %Y-%m-%d 匹配年-月-日部分
• T 对应字符串里的时间分隔符
• %H:%i:%s 匹配时:分:秒
• %f 解析微秒数值
• Z 匹配末尾的时区标识

3. 创建索引

修改完虚拟列后，再执行创建索引的语句就可以成功了：

CREATE INDEX some_index ON some_table (time);

额外说明 #

• 为什么要加STORED？MariaDB的虚拟列分两种：VIRTUAL（默认，查询时动态计算）和STORED（存储在磁盘上）。只有STORED类型的虚拟列才能创建索引，因为索引需要依赖实际存储的数值。
• 先验证转换是否正确：可以单独测试转换函数，确保格式匹配：

SELECT STR_TO_DATE('2021-12-26T14:00:00.007294Z', '%Y-%m-%dT%H:%i:%s.%fZ');

执行后会返回合法的TIMESTAMP值：2021-12-26 14:00:00.007294

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