mysql 存储过程批量插入数据

DELIMITER $$

CREATE PROCEDURE BatchInsertData(IN tableName VARCHAR(255), IN data JSON)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE rowCount INT;
    DECLARE columns VARCHAR(255);
    DECLARE values VARCHAR(255);
    DECLARE sqlQuery VARCHAR(1000);

    SET rowCount = JSON_LENGTH(data);
    SET columns = JSON_UNQUOTE(JSON_EXTRACT(data, CONCAT('$[', i, '].columns')));
    SET values = JSON_UNQUOTE(JSON_EXTRACT(data, CONCAT('$[', i, '].values')));

    SET sqlQuery = CONCAT('INSERT INTO ', tableName, ' (', columns, ') VALUES (', values, ')');

    WHILE i < rowCount DO
        SET i = i + 1;
        SET columns = JSON_UNQUOTE(JSON_EXTRACT(data, CONCAT('$[', i, '].columns')));
        SET values = JSON_UNQUOTE(JSON_EXTRACT(data, CONCAT('$[', i, '].values')));
        SET sqlQuery = CONCAT(sqlQuery, '; INSERT INTO ', tableName, ' (', columns, ') VALUES (', values, ')');
    END WHILE;

    PREPARE stmt FROM sqlQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

CALL BatchInsertData('your_table_name', '[{"columns": "column1, column2", "values": "value1, value2"}, {"columns": "column1, column2", "values": "value3, value4"}]');