使用LOAD DATA LOCAL INFILE导入MySQL时如何处理带时区的日期字段

核心问题原因 #

你之前导入失败是两个问题共同导致的：

1. 日期格式符使用错误：%A是匹配星期全称的占位符，你日期中的AM/PM标识需要用%p匹配，同时如果你给出的示例日期无秒数，格式符中多余的%s也会导致匹配失败。
2. 字符串截取索引错误：MySQL的字符串操作函数起始索引从1开始计数，你之前写的SUBSTR(@revision_date, 0, 19)会丢失第一个字符，无法匹配日期格式。

可行解决方案 #

方案1：MySQL 8.0+ 通用方案（适配不同长度的时区后缀）

使用正则提取前面的日期时间部分，不受时区后缀长度变化影响：

SET revision_date = STR_TO_DATE(
    REGEXP_SUBSTR(@revision_date, '^[0-9]{2}/[0-9]{2}/[0-9]{4} [0-9]{1,2}:[0-9]{2}(:[0-9]{2})? [AP]M'),
    '%m/%d/%Y %h:%i %p'
)

如果你的实际数据包含秒数，把格式符调整为%m/%d/%Y %h:%i:%s %p即可。

方案2：MySQL 5.x 兼容方案（固定长度截取）

如果是不支持正则的低版本MySQL，修正截取的起始索引和格式符即可：

-- 写法1：用LEFT直接取前19位
SET revision_date = STR_TO_DATE(
    LEFT(@revision_date, 19),
    '%m/%d/%Y %h:%i %p'
)

-- 写法2：用SUBSTR，注意起始索引为1
SET revision_date = STR_TO_DATE(
    SUBSTR(@revision_date, 1, 19),
    '%m/%d/%Y %h:%i %p'
)

可选：保留时区转换逻辑 #

如果你需要将导入的时间统一转换为MySQL服务器的指定时区，可使用CONVERT_TZ函数（需要提前导入MySQL时区表）：

SET revision_date = CONVERT_TZ(
    STR_TO_DATE(LEFT(@revision_date, 19), '%m/%d/%Y %h:%i %p'),
    TRIM(SUBSTRING(@revision_date, 20)), -- 提取时区部分
    'Asia/Shanghai' -- 替换为你需要转换到的目标时区
)

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