如何加载日期格式为%W %e, %Y的XML数据并正确转换为日期类型

问题根因 #

• 多SET语法错误：LOAD XML语句的SET子句仅需声明1次，多个列赋值直接用逗号分隔即可，重复写第二个SET关键字会直接触发语法报错，正确的赋值写法为SET date1= STR_TO_DATE(@ddate1,'%M %e, %Y'), date2= STR_TO_DATE(@date2,'%M %e, %Y')
• 单字段转换仍为NULL的原因：需同时满足两个条件：一是XML中的对应日期字段正确映射到了@ddate1、@date2两个用户变量；二是XML里的日期字符串格式完全匹配%M %e, %Y规则，该格式对应「月份英文全拼 + 空格 + 日期数字 + 逗号 + 空格 + 四位年份」，例如October 12, 2023，如果存在月份缩写、分隔符不符、日期顺序不对等情况，STR_TO_DATE都会返回NULL。

无需逐个列出全量字段的实现方案 #

只要你的表前23个非日期列和XML节点的字段顺序/名称完全匹配，仅需在语句末尾指定两个日期列映射为变量再转换即可，完整参考语句如下：

LOAD XML INFILE '/path/to/your/file.xml'
INTO TABLE 你的目标表名
ROWS IDENTIFIED BY '<你的行节点名称>'
-- 仅声明最后两列映射到变量，前23列自动匹配
(@ddate1, @date2)
SET date1 = STR_TO_DATE(@ddate1, '%M %e, %Y'),
    date2 = STR_TO_DATE(@date2, '%M %e, %Y');

如果XML和表字段是按名称映射而非按顺序映射，可补充配置COLUMNS IDENTIFIED BY '<列节点名称>'，同样不需要列出所有非日期字段。
如果转换后仍为NULL，可根据XML实际的日期格式调整STR_TO_DATE的格式符，例如月份缩写用%b、数字月份用%m、日期补零用%d等。

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