MySQL从JSON对象键批量插入行的存储过程实现求助

1. 先确保目标表存在 #

如果还未创建comments表，执行以下SQL语句：

CREATE TABLE IF NOT EXISTS comments (
    _id VARCHAR(50) PRIMARY KEY,
    type VARCHAR(50) NOT NULL,
    created INT NOT NULL,
    url VARCHAR(255) NOT NULL,
    content TEXT NOT NULL
);

2. 创建迁移存储过程 #

这个存储过程会遍历entities表中所有包含attributes的记录，拆分嵌套JSON结构并批量插入到comments表：

DELIMITER //

CREATE PROCEDURE MigrateEntitiesToComments()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE entity_content JSON;
    DECLARE attr_types JSON;
    DECLARE type_name VARCHAR(50);
    DECLARE type_index INT DEFAULT 0;
    DECLARE type_count INT;
    DECLARE comment_urls JSON;
    DECLARE comment_url VARCHAR(255);
    DECLARE url_index INT DEFAULT 0;
    DECLARE url_count INT;
    DECLARE comment_data JSON;

    -- 游标获取所有包含attributes字段的entities记录
    DECLARE entity_cursor CURSOR FOR
        SELECT JSON_UNQUOTE(contents)
        FROM entities
        WHERE JSON_CONTAINS_PATH(JSON_UNQUOTE(contents), 'all', '$.attributes');

    -- 游标结束触发的处理逻辑
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN entity_cursor;

    entity_loop: LOOP
        FETCH entity_cursor INTO entity_content;
        IF done = 1 THEN
            LEAVE entity_loop;
        END IF;

        -- 获取attributes下的所有类型节点（如typeA）
        SET attr_types = JSON_KEYS(entity_content, '$.attributes');
        SET type_count = JSON_LENGTH(attr_types);
        SET type_index = 0;

        type_loop: LOOP
            IF type_index >= type_count THEN
                LEAVE type_loop;
            END IF;

            -- 提取当前类型名称
            SET type_name = JSON_UNQUOTE(JSON_EXTRACT(attr_types, CONCAT('$[', type_index, ']')));
            -- 获取该类型下的所有URL键
            SET comment_urls = JSON_KEYS(entity_content, CONCAT('$.attributes.', type_name));
            SET url_count = JSON_LENGTH(comment_urls);
            SET url_index = 0;

            url_loop: LOOP
                IF url_index >= url_count THEN
                    LEAVE url_loop;
                END IF;

                -- 提取当前URL和对应的评论数据
                SET comment_url = JSON_UNQUOTE(JSON_EXTRACT(comment_urls, CONCAT('$[', url_index, ']')));
                SET comment_data = JSON_EXTRACT(entity_content, CONCAT('$.attributes.', type_name, '.', JSON_QUOTE(comment_url)));

                -- 插入或更新评论数据到目标表
                INSERT INTO comments (_id, type, created, url, content)
                VALUES (
                    JSON_UNQUOTE(JSON_EXTRACT(comment_data, '$._id')),
                    -- 将typeA格式转为Type A（不需要可直接替换为type_name）
                    CONCAT(UPPER(SUBSTRING(type_name, 1, 1)), SUBSTRING(type_name, 2)),
                    JSON_UNQUOTE(JSON_EXTRACT(comment_data, '$.created')),
                    comment_url,
                    JSON_UNQUOTE(JSON_EXTRACT(comment_data, '$.content'))
                )
                ON DUPLICATE KEY UPDATE
                    type = VALUES(type),
                    created = VALUES(created),
                    url = VALUES(url),
                    content = VALUES(content);

                SET url_index = url_index + 1;
            END LOOP url_loop;

            SET type_index = type_index + 1;
        END LOOP type_loop;

    END LOOP entity_loop;

    CLOSE entity_cursor;
END //

DELIMITER ;

3. 执行迁移操作 #

调用存储过程完成数据拆分与迁移：

CALL MigrateEntitiesToComments();

关键逻辑说明 #

• 用游标遍历所有符合条件的entities记录，确保不遗漏需处理的数据
• 嵌套循环分别处理attributes下的类型节点和每个类型下的评论节点
• 通过JSON_KEYS提取层级键名，JSON_EXTRACT获取具体字段值
• ON DUPLICATE KEY UPDATE处理_id重复的情况，避免插入冲突同时更新现有数据

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