mysql循环绑定变量

-- 示例：使用预处理语句进行批量插入
DELIMITER $$
CREATE PROCEDURE BatchInsert(IN data TEXT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE value TEXT;
  SET @sql = CONCAT('INSERT INTO table_name (column_name) VALUES (?)');
  PREPARE stmt FROM @sql;
  WHILE i <= LENGTH(data) DO
    SET value = SUBSTRING(data, i, 1);
    EXECUTE stmt USING value;
    SET i = i + 1;
  END WHILE;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

-- 示例：分批次插入数据
DELIMITER $$
CREATE PROCEDURE BatchInsert(IN data TEXT, IN batchSize INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE value TEXT;
  SET @sql = CONCAT('INSERT INTO table_name (column_name) VALUES (?)');
  PREPARE stmt FROM @sql;
  WHILE i <= LENGTH(data) DO
    IF i % batchSize = 1 THEN
      DEALLOCATE PREPARE stmt;
      SET @sql = CONCAT('INSERT INTO table_name (column_name) VALUES (?)');
      PREPARE stmt FROM @sql;
    END IF;
    SET value = SUBSTRING(data, i, 1);
    EXECUTE stmt USING value;
    SET i = i + 1;
  END WHILE;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;