mysql 存储过程效率

DELIMITER //

CREATE PROCEDURE BatchInsert(IN tableName VARCHAR(255), IN data JSON)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE rowCount INT;
    DECLARE value JSON;

    SET rowCount = JSON_LENGTH(data);

    WHILE i < rowCount DO
        SET value = JSON_EXTRACT(data, CONCAT('$[', i, ']'));
        SET @sql = CONCAT('INSERT INTO ', tableName, ' VALUES (', value, ')');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

DELIMITER //

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

    SET rowCount = JSON_LENGTH(data);
    SET columns = 'column1, column2, column3'; -- 根据实际情况修改列名
    SET placeholders = '?, ?, ?'; -- 根据实际情况修改占位符数量

    WHILE i < rowCount DO
        SET value = JSON_EXTRACT(data, CONCAT('$[', i, ']'));
        SET values = CONCAT(values, '(', value, '), ');
        SET i = i + 1;
    END WHILE;

    SET values = SUBSTRING(values, 1, LENGTH(values) - 2); -- 去掉最后一个逗号和空格

    SET @sql = CONCAT('INSERT INTO ', tableName, ' (', columns, ') VALUES ', values);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;