如何通过存储过程将列表型JSON数据批量插入MySQL数据库

完全可以通过存储过程处理这类JSON数组，而且优先推荐批量插入而非循环逐行插入，后者在数据量较大时性能会很差。以下是两种实现方式：

方式一：用JSON_TABLE批量插入（MySQL 8.0+ 推荐） #

MySQL 8.0及以上支持JSON_TABLE函数，可以直接将JSON数组解析为关系表结构，然后一次性批量插入，这是效率最高的方案：

DELIMITER //
CREATE PROCEDURE InsertPageTranslations(IN translations JSON)
BEGIN
    -- 解析JSON数组并批量插入到目标表
    INSERT INTO page_translations (page_name, language, item_id, item_text)
    SELECT 
        jt.page_name,
        jt.language,
        jt.item_id,
        jt.item_text
    FROM JSON_TABLE(
        translations,
        '$[*]' COLUMNS(
            page_name VARCHAR(50) PATH '$[0]', -- 取子数组第1个元素
            language VARCHAR(10) PATH '$[1]',  -- 取子数组第2个元素
            item_id INT PATH '$[2]',           -- 取子数组第3个元素
            item_text TEXT PATH '$[3]'         -- 取子数组第4个元素
        )
    ) AS jt;
END //
DELIMITER ;

调用存储过程 #

直接传入你的JSON数组即可：

CALL InsertPageTranslations('[["home","fr",1,"French text item 1"],["home","fr",2,"French text item 2"],["home","fr",3,"French text item 3"],["home","fr",4,"French text item 4"],["home","fr",5,"French text item 5"]]');

方式二：循环逐行插入（兼容MySQL 5.7及以下） #

如果你的MySQL版本低于8.0，不支持JSON_TABLE，可以用循环遍历JSON数组的方式：

DELIMITER //
CREATE PROCEDURE InsertPageTranslationsLegacy(IN translations JSON)
BEGIN
    DECLARE current_index INT DEFAULT 0;
    DECLARE total_items INT;
    DECLARE page_val VARCHAR(50);
    DECLARE lang_val VARCHAR(10);
    DECLARE item_id_val INT;
    DECLARE text_val TEXT;

    -- 获取JSON数组的长度
    SET total_items = JSON_LENGTH(translations);

    -- 循环遍历每个数组元素
    WHILE current_index < total_items DO
        -- 提取当前元素的各个字段值
        SET page_val = JSON_UNQUOTE(JSON_EXTRACT(translations, CONCAT('$[', current_index, '][0]')));
        SET lang_val = JSON_UNQUOTE(JSON_EXTRACT(translations, CONCAT('$[', current_index, '][1]')));
        SET item_id_val = JSON_EXTRACT(translations, CONCAT('$[', current_index, '][2]'));
        SET text_val = JSON_UNQUOTE(JSON_EXTRACT(translations, CONCAT('$[', current_index, '][3]')));

        -- 插入单条记录
        INSERT INTO page_translations (page_name, language, item_id, item_text)
        VALUES (page_val, lang_val, item_id_val, text_val);

        SET current_index = current_index + 1;
    END WHILE;
END //
DELIMITER ;

性能优化建议 #

• 批量插入（方式一）的性能远优于循环插入，数据量越大差距越明显，尽量优先使用
• 插入大量数据时，建议将操作包裹在事务中，减少事务提交的开销：

  START TRANSACTION;
  CALL InsertPageTranslations('你的JSON数据');
  COMMIT;
  

• 如果表上有非必要的索引，插入前可以先禁用，插入完成后再重建，能大幅提升插入速度

内容的提问来源于stack exchange，提问作者N.Widds