MySQL LOAD XML INFILE加载多为NULL值，如何拆分<tags>为单行？

我之前也踩过这个坑——LOAD XML INFILE明明没报错，但查stage表全是没用的NULL值，尤其是处理嵌套的<tags>列表这种结构时，MySQL的默认XML加载逻辑很容易掉进去。结合你要把每个标签转成单行、适配posts-tags N-N关系的需求，给你梳理下问题根源和解决步骤：

一、先搞懂MySQL LOAD XML的默认坑点 #

MySQL的LOAD XML INFILE默认是按顶级XML元素生成行的，而且只会读取元素的直接子节点文本内容。举个例子，如果你的XML结构是这样：

<post>
  <id>1</id>
  <title>XML导入踩坑记录</title>
  <tags>
    <tag>MySQL</tag>
    <tag>XML</tag>
    <tag>数据库</tag>
  </tags>
</post>

直接跑LOAD XML INFILE 'your_file.xml' INTO TABLE stage;的话，MySQL会把整个<post>当作一行，而<tags>是嵌套节点，它不会自动解析里面的<tag>子元素，只会把<tags>当成一个字段（大概率存NULL，因为它不是纯文本节点）——这就是你看到大量NULL的核心原因。

二、分阶段实现需求（把标签拆成单行） #

1. 先导入基础数据+提取标签列表到临时表 #

首先创建一个临时表，用来存post的基础信息和提取后的标签字符串：

CREATE TEMPORARY TABLE temp_posts (
  post_id INT,
  post_title VARCHAR(255),
  tags TEXT
);

然后用LOAD XML指定顶级元素，同时用ExtractValue函数把嵌套的<tag>提取成拼接字符串：

LOAD XML INFILE '/path/to/your/posts.xml'
INTO TABLE temp_posts
ROWS IDENTIFIED BY '<post>'  -- 指定以<post>作为每行的根元素
(post_id, post_title, @raw_tags)  -- @raw_tags接收XML里的<tags>节点
SET tags = ExtractValue(@raw_tags, '//tag');  -- 提取所有<tag>内容，默认空格分隔

如果你的标签本身带空格，可以改成用特殊分隔符，比如ExtractValue(@raw_tags, '//tag', '|')，避免拆分时出错。

2. 把拼接的标签拆成单独行 #

接下来用递归CTE把拼接的标签拆成每行一个标签，这样就能得到post和标签的关联数据：

WITH RECURSIVE tag_split AS (
  SELECT
    post_id,
    post_title,
    SUBSTRING_INDEX(tags, ' ', 1) AS tag,  -- 取第一个标签
    SUBSTRING(tags, LENGTH(SUBSTRING_INDEX(tags, ' ', 1)) + 2) AS remaining_tags  -- 剩下的标签
  FROM temp_posts
  WHERE tags IS NOT NULL AND tags != ''
  UNION ALL
  SELECT
    post_id,
    post_title,
    SUBSTRING_INDEX(remaining_tags, ' ', 1) AS tag,
    SUBSTRING(remaining_tags, LENGTH(SUBSTRING_INDEX(remaining_tags, ' ', 1)) + 2) AS remaining_tags
  FROM tag_split
  WHERE remaining_tags IS NOT NULL AND remaining_tags != ''
)
SELECT post_id, post_title, tag FROM tag_split;

执行这段SQL后，你就能得到每个post对应每个标签的单行数据了。

3. 插入到最终的三张表（保证标签唯一） #

最后把数据导入到你的正式表，注意用INSERT IGNORE保证标签唯一性：

-- 插入posts表（去重，避免重复导入）
INSERT IGNORE INTO posts (id, title)
SELECT DISTINCT post_id, post_title FROM tag_split;

-- 插入tags表（确保标签唯一）
INSERT IGNORE INTO tags (name)
SELECT DISTINCT tag FROM tag_split;

-- 插入post_tags关联表
INSERT INTO post_tags (post_id, tag_id)
SELECT
  ts.post_id,
  t.id
FROM tag_split ts
JOIN tags t ON ts.tag = t.name;

三、额外注意事项 #

• 确保MySQL的local_infile参数已开启：SET GLOBAL local_infile = 1;，否则可能无法读取本地XML文件。
• 如果XML标签名和表字段名不匹配，要在LOAD XML时明确指定映射关系，比如(post_id, post_title, @raw_tags)对应XML里的<id>、<title>、<tags>。
• 若XML包含命名空间，需要在ExtractValue里加上命名空间前缀，比如ExtractValue(@raw_tags, '//ns:tag', '|')（前提是先声明命名空间）。

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