MySQL存储过程1064错误：提取变长JSON数据到现有表求助

首先说你的1064语法错误，90%的概率是没改语句结束符（DELIMITER）！MySQL默认用;作为语句结束标记，而存储过程内部有一堆;，MySQL会误以为你的存储过程定义到第一个;就结束了，自然会报语法错。解决这个的核心就是先把DELIMITER改成别的（比如//），写完存储过程再改回来。

一、先搞定语法错误：正确的存储过程写法 #

假设你的现有表叫target_table，有key_col（存JSON的键）和value_col（存对应值）两个字段，下面分两种JSON格式给你写示例：

情况1：输入的是JSON对象（比如{"name":"张三", "age":25, "job":"程序员"}） #

-- 先修改语句结束符为//
DELIMITER //

-- 删除已存在的存储过程（如果有）
DROP PROCEDURE IF EXISTS ExtractJSONToTable//

-- 定义存储过程
CREATE PROCEDURE ExtractJSONToTable(IN input_json JSON)
BEGIN
    -- 声明变量必须放在最开头
    DECLARE json_keys JSON;
    DECLARE total_keys INT;
    DECLARE current_idx INT DEFAULT 0;
    DECLARE current_key VARCHAR(60);
    DECLARE current_value TEXT;

    -- 获取JSON对象的所有键组成的数组
    SET json_keys = JSON_KEYS(input_json);
    -- 获取键的总数
    SET total_keys = JSON_LENGTH(json_keys);

    -- 遍历每个键值对
    WHILE current_idx < total_keys DO
        -- 取出当前索引的键
        SET current_key = JSON_UNQUOTE(JSON_EXTRACT(json_keys, CONCAT('$[', current_idx, ']')));
        -- 根据键取出对应的值
        SET current_value = JSON_UNQUOTE(JSON_EXTRACT(input_json, CONCAT('$.', current_key)));
        
        -- 插入到现有表（如果是更新现有记录，把INSERT改成UPDATE即可）
        INSERT INTO target_table (key_col, value_col) VALUES (current_key, current_value);
        
        -- 索引自增
        SET current_idx = current_idx + 1;
    END WHILE;
END//

-- 把语句结束符改回默认的;
DELIMITER ;

情况2：输入的是JSON数组（比如[{"key":"name","value":"张三"},{"key":"age","value":25}]） #

如果你的JSON是这种键值对数组格式，存储过程可以简化成：

DELIMITER //

DROP PROCEDURE IF EXISTS ExtractJSONArrayToTable//

CREATE PROCEDURE ExtractJSONArrayToTable(IN input_json JSON)
BEGIN
    DECLARE total_items INT;
    DECLARE current_idx INT DEFAULT 0;
    DECLARE current_key VARCHAR(60);
    DECLARE current_value TEXT;

    SET total_items = JSON_LENGTH(input_json);

    WHILE current_idx < total_items DO
        SET current_key = JSON_UNQUOTE(JSON_EXTRACT(input_json, CONCAT('$[', current_idx, '].key')));
        SET current_value = JSON_UNQUOTE(JSON_EXTRACT(input_json, CONCAT('$[', current_idx, '].value')));
        
        INSERT INTO target_table (key_col, value_col) VALUES (current_key, current_value);
        
        SET current_idx = current_idx + 1;
    END WHILE;
END//

DELIMITER ;

二、更简单的方案：用MySQL 8.0+的JSON_TABLE函数 #

如果你的MySQL版本是8.0及以上，完全不用写存储过程，用JSON_TABLE函数一行搞定，效率还更高！

比如处理JSON对象：

SET @your_json = '{"name":"张三", "age":25, "job":"程序员"}';

INSERT INTO target_table (key_col, value_col)
SELECT
    key_name,
    JSON_UNQUOTE(JSON_EXTRACT(@your_json, CONCAT('$.', key_name))) AS value
FROM
    JSON_TABLE(
        JSON_KEYS(@your_json),
        '$[*]' COLUMNS (key_name VARCHAR(60) PATH '$')
    ) AS key_list;

如果是JSON数组格式：

SET @your_json = '[{"key":"name","value":"张三"},{"key":"age","value":25}]';

INSERT INTO target_table (key_col, value_col)
SELECT
    j.key,
    j.value
FROM
    JSON_TABLE(
        @your_json,
        '$[*]' COLUMNS (
            key VARCHAR(60) PATH '$.key',
            value TEXT PATH '$.value'
        )
    ) AS j;

额外注意点 #

• 如果要更新现有表的记录而不是插入新数据，把INSERT语句改成UPDATE即可，比如：

  UPDATE target_table 
  SET value_col = current_value 
  WHERE key_col = current_key;
  

• 确保你的MySQL版本支持JSON函数（MySQL 5.7及以上支持基础JSON操作，8.0+支持JSON_TABLE）。

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