包含多值属性的XML文件导入MySQL数据库的解决方案咨询

一、数据库表结构设计 #

先按照关系型数据库第三范式设计两张关联表：

-- 图书主表，存储所有单值属性
CREATE TABLE books (
    book_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    author VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    UNIQUE KEY uk_book_info (author, title) -- 避免重复导入相同图书
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 图书分类关联表，存储多值分类属性
CREATE TABLE book_categories (
    book_id INT UNSIGNED NOT NULL,
    category VARCHAR(100) NOT NULL,
    PRIMARY KEY (book_id, category), -- 联合主键避免重复分类
    FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

二、纯MySQL自动化导入方案（无需额外工具） #

不需要修改原始XML，全程可脚本化执行，适配大文件场景：

1. 创建临时中间表存储原始XML节点 #

CREATE TEMPORARY TABLE temp_book_raw (
    raw_xml TEXT -- 存储完整的<Book>节点原始内容
) ENGINE=InnoDB;

2. 导入原始XML到临时表 #

LOAD XML LOCAL INFILE 'file.xml'
INTO TABLE temp_book_raw
ROWS IDENTIFIED BY '<Book>'
(@raw_node)
SET raw_xml = @raw_node;

3. 批量导入单值属性到图书主表 #

INSERT IGNORE INTO books (author, title)
SELECT 
    TRIM(ExtractValue(raw_xml, '//Author/text()')),
    TRIM(ExtractValue(raw_xml, '//Title/text()'))
FROM temp_book_raw;

4. 拆分多值分类插入关联表 #

用递归CTE拆分多值分类，支持任意数量的Category标签：

WITH RECURSIVE category_split AS (
    SELECT
        b.book_id,
        TRIM(ExtractValue(t.raw_xml, '//Category/text()')) AS all_category,
        1 AS pos,
        TRIM(SUBSTRING_INDEX(TRIM(ExtractValue(t.raw_xml, '//Category/text()')), ' ', 1)) AS category,
        TRIM(SUBSTRING(TRIM(ExtractValue(t.raw_xml, '//Category/text()')), LENGTH(SUBSTRING_INDEX(TRIM(ExtractValue(t.raw_xml, '//Category/text()')), ' ', 1)) + 2)) AS remaining
    FROM temp_book_raw t
    JOIN books b ON TRIM(ExtractValue(t.raw_xml, '//Author/text()')) = b.author 
        AND TRIM(ExtractValue(t.raw_xml, '//Title/text()')) = b.title
    WHERE TRIM(ExtractValue(t.raw_xml, '//Category/text()')) != ''
    UNION ALL
    SELECT
        book_id,
        all_category,
        pos + 1,
        TRIM(SUBSTRING_INDEX(remaining, ' ', 1)),
        TRIM(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ' ', 1)) + 2))
    FROM category_split
    WHERE remaining != ''
)
INSERT IGNORE INTO book_categories (book_id, category)
SELECT book_id, category FROM category_split;

三、大文件优化建议 #

• 导入前执行SET FOREIGN_KEY_CHECKS=0; SET autocommit=0;关闭外键检查和自动提交，导入完成后执行COMMIT; SET FOREIGN_KEY_CHECKS=1;恢复配置，可提升导入速度数倍
• 调整MySQL参数max_allowed_packet到1G以上，避免大XML节点导入时报截断错误
• 若分类名称本身包含空格，可先对原始XML做简单正则替换，用|等特殊字符分隔多值分类，再对应修改拆分逻辑即可

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