如何将yyyy-mm-ddThh:mm:ssZ格式日期字符串转换为timestamp并解决导入报错

根因分析 #

1. MySQL STR_TO_DATE 函数不支持Java风格的yyyy/MM格式占位符，需要使用MySQL专属的日期格式标识
2. 你使用的转换格式包含.SSS毫秒匹配规则，但你的示例时间2018-04-04T00:03:04Z不含毫秒值，匹配失败直接返回NULL
3. Table Data Import Wizard默认会直接将CSV中的字符串写入TIMESTAMP字段，MySQL原生不识别带T、Z后缀的ISO8601格式时间字符串，因此抛出> Incorrect datetime value错误终止导入

解决步骤 #

方案1：适配导入向导的操作（无需手动写LOAD语句） #

1. 重新走导入流程，在「选择目标表」步骤，临时将时间字段的类型设置为VARCHAR(50)，先完成全量数据导入
2. 新增TIMESTAMP类型的目标字段：

ALTER TABLE 你的表名 ADD COLUMN date_sale_ts TIMESTAMP NULL;

3. 用正确的格式规则转换时间并赋值：

-- 无毫秒的时间用这个
UPDATE 你的表名 
SET date_sale_ts = STR_TO_DATE(date_sale, '%Y-%m-%dT%H:%i:%sZ');

-- 有毫秒的时间用这个兼容
-- UPDATE 你的表名 
-- SET date_sale_ts = STR_TO_DATE(date_sale, '%Y-%m-%dT%H:%i:%s.%fZ');

4. 验证转换后无NULL值，即可替换原有字段：

ALTER TABLE 你的表名 DROP COLUMN date_sale;
ALTER TABLE 你的表名 CHANGE COLUMN date_sale_ts date_sale TIMESTAMP NOT NULL;

方案2：时区适配（可选） #

如果你的时间戳是UTC时区，需要适配数据库本地时区，可在转换时增加时区转换逻辑：

UPDATE 你的表名 
SET date_sale_ts = CONVERT_TZ(STR_TO_DATE(date_sale, '%Y-%m-%dT%H:%i:%sZ'), '+00:00', @@system_time_zone);

内容的提问来源于stack exchange，提问作者Gabriela Baldivia Soncini