MySQL LOAD XML 怎样把 XML 字段映射到不同列

MySQL 的 LOAD XML 默认会尝试按 XML 字段名寻找同名的目标列。当 XML 使用 节点，而表里使用 individual_id、name1 这类不同名称时，直接导入就容易出现列没有得到值、非空列报错或字段落错位置。

解决办法是把 XML 字段先读入用户变量，再通过 SET 明确写入目标列。也就是说，导入链路不是“节点名直接等于列名”，而是“识别行节点 → 变量暂存 → SET 映射/转换 → 目标表”。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/load-xml.html

先看懂 LOAD XML 的行识别规则

LOAD XML 需要知道 XML 中哪个元素代表一行记录。默认情况下， 元素会被当作数据行；如果实际文件使用的是 、 等节点，就应使用 ROWS IDENTIFIED BY 指定它。

这个文件里真正重复出现的是 person，所以导入时的行选择器应写成 ROWS IDENTIFIED BY ''。如果省略它，MySQL 会按默认的 规则解释，得到的行边界就可能不是预期结果。

目标列名称不一致时，先用用户变量接住字段

假设目标表不是用 XML 中的字段名，而是下面这些列名：

CREATE TABLE individual (
  individual_id INT NOT NULL PRIMARY KEY,
  name1 VARCHAR(80) NOT NULL,
  name2 VARCHAR(80) NOT NULL,
  made DATETIME NOT NULL
);

-- 这条查询只用于说明目标列与 XML 字段名并不相同
SELECT individual_id, name1, name2, made FROM individual;

此时不要把 XML 字段名直接写在目标列列表里期待 MySQL 自动猜出关系。把字段写成用户变量后，变量就像一层输入暂存区：@person_id 接收 person_id，@fname 接收 fname，然后再由 SET 决定真正落到哪一列。

用 SET 把 XML 字段映射到不同列

完整的映射语句可以这样写：

LOAD XML LOCAL INFILE '/data/people.xml'
  INTO TABLE individual
  -- 明确指定每个 person 元素是一行
  ROWS IDENTIFIED BY ''
  -- 先把 XML 字段放入用户变量，绕开同名列匹配
  (@person_id, @fname, @lname, @created)
  -- SET 负责重命名，也可以在这里做表达式转换
  SET individual_id = @person_id,
      name1 = @fname,
      name2 = @lname,
      made = @created;

这里的列列表不是直接写目标表列名，而是写用户变量。之后的 SET 明确给出映射关系：

• @person_id → individual_id：XML 的业务字段名换成表的主键列名。
• @fname → name1、@lname → name2：输入字段和存储字段分别命名。
• @created → made：把时间字段写到目标列。

如果 XML 字段与目标列的类型不完全一致，也应把转换写在 SET 中，让映射规则集中在一处。例如可以用 NULLIF 把空字符串视为 NULL，或用日期函数把文本转换为目标时间类型；但目标列是 NOT NULL 时，必须先决定缺失值的业务默认值。

LOAD XML LOCAL INFILE '/data/people.xml'
  INTO TABLE individual
  -- 行节点仍然按 person 识别
  ROWS IDENTIFIED BY ''
  (@person_id, @fname, @lname, @created)
  -- 只在这里处理空值和类型转换，避免映射散落到应用代码
  SET individual_id = CAST(@person_id AS UNSIGNED),
      name1 = NULLIF(@fname, ''),
      name2 = NULLIF(@lname, ''),
      made = STR_TO_DATE(@created, '%Y-%m-%d %H:%i:%s');

转换表达式要和列约束一起考虑。比如 name1 是 NOT NULL，那么 NULLIF(@fname, '') 可能导致这一行无法插入；此时可以提供明确默认值，或者先把目标列设计为允许空值。

XML 使用子标签时，先确认字段形态

LOAD XML 支持属性形式，也支持子标签形式。下面的 XML 没有把值放在属性里，而是放在 person_id、fname 等子元素中：

101LinMing2026-11-11 09:30:00

导入前要先确认文件到底是“属性值”还是“子标签内容”，并确认一行节点的名称。不要只根据文件扩展名推断结构；同样是 XML，字段承载方式不同，映射结果也会不同。

LOCAL、重复键和字段缺失怎么处理

LOCAL 表示文件从客户端一侧提供；不带 LOCAL 时，文件位置和读取权限按服务端配置处理。实际部署中，除了 SQL 语句本身，还要确认客户端是否允许本地文件、服务端是否允许对应的导入方式，以及文件路径是否真的可读。

-- 文件由发起 mysql 客户端的机器提供
LOAD XML LOCAL INFILE '/data/people.xml'
  INTO TABLE individual
  ROWS IDENTIFIED BY ''
  (@person_id, @fname, @lname, @created)
  SET individual_id = @person_id,
      name1 = @fname,
      name2 = @lname,
      made = @created;

-- 需要忽略重复键时，明确表达这个策略；不要把 IGNORE 当成字段映射
LOAD XML LOCAL INFILE '/data/people.xml'
  IGNORE INTO TABLE individual
  ROWS IDENTIFIED BY ''
  (@person_id, @fname, @lname, @created)
  SET individual_id = @person_id,
      name1 = @fname,
      name2 = @lname,
      made = @created;

IGNORE 或 REPLACE 解决的是重复键等写入策略，不会替代 SET 的字段映射。如果 XML 中缺少非空字段，应该先决定跳过、补默认值还是拒绝该行，不要靠导入器的隐式默认值猜测业务含义。

导入后怎样快速判断映射是否正确

正式导入前，建议用一小份 XML 样本覆盖三种情况：正常值、字段名需要重命名、可选字段为空。导入后先看受影响行数，再按主键抽取样本，检查每个变量是否落到了预期列。

-- 先抽取刚导入的少量样本，核对每个映射目标
SELECT individual_id, name1, name2, made
FROM individual
WHERE individual_id IN (101, 102)
ORDER BY individual_id;

-- 检查主键是否出现重复，避免把写入策略误当成映射成功
SELECT individual_id, COUNT(*) AS row_count
FROM individual
GROUP BY individual_id
HAVING COUNT(*) > 1;

如果主键值正确但姓名或时间列为空，优先回看用户变量顺序、XML 属性/子标签形态和 SET 表达式；如果整批行数为零，优先回看 ROWS IDENTIFIED BY 是否写成了实际节点名称。

一句话总结

MySQL LOAD XML 的跨名称映射可以固定成四步：用 ROWS IDENTIFIED BY 找到一行、用用户变量接收字段、用 SET 映射或转换、最后按目标列约束处理缺失值和重复键。这样 XML 的外部命名和表的内部命名可以解耦，导入规则也更容易审查和复用。

常见问题

为什么直接 LOAD XML 后目标列是空的？

常见原因是 XML 字段名与目标列名不同，或者实际行节点不是默认的 。先确认节点结构，再改用用户变量和 SET 显式映射。

ROWS IDENTIFIED BY 必须写尖括号吗？

需要按 MySQL 语法把节点名写成带尖括号的字面量，例如 ROWS IDENTIFIED BY ''。它描述的是 XML 元素，而不是普通字符串标签。

SET 能不能做类型转换？

可以。SET 右侧是表达式位置，可以集中放置重命名、空值处理和日期或数值转换，但转换结果仍必须满足目标列的数据类型与约束。