MySQL存储过程单条插入和批量插入数据对比

先创建一张测试表t，SQL语句如下：

CREATE TABLE t
(
id INT(11) not NULL auto_increment,
name VARCHAR(20) NOT NULL,
address VARCHAR(20) NOT NULL,
PRIMARY KEY(id)
);

 可以执行DESC t；查看表结构，如图：

 

每次插入一条数据

插入语句如下：

insert into t values(null,'name','address'')；

存储过程如下：

delimiter //
# 删除表数据
TRUNCATE TABLE t;
DROP PROCEDURE IF EXISTS sp_test_single;
CREATE PROCEDURE sp_test_single(IN num INT)
BEGIN
	SET @count=0;
	WHILE @count < num DO
		INSERT INTO t VALUES(null,CONCAT('name',@count),CONCAT('address',@count));
		SET @count = @count + 1;
	END WHILE;
	# 数据插入完成后，查看表中总记录数
	SELECT COUNT(id) FROM t;
END
//
delimiter;

如下来调用该存储过程：

#调用存储过程
call sp_test_single(100000);

会发现使用这种方式插入数据非常慢。

 

每次插入多条数据

插入语句如下：

insert into t values(null,'name','address''),(null,'name2','address2'),(null,'name3','address3'),...

存储过程如下：

delimiter //
# 删除表数据
TRUNCATE TABLE t;
# 如果已经有sp_test_batch存储过程，将其删除，后面重新创建
DROP PROCEDURE IF EXISTS sp_test_batch;
# 创建存储过程，包含num和batch输入，num表示插入的总行数，batch表示每次插入的行数
CREATE PROCEDURE sp_test_batch(IN num INT,IN batch INT)
BEGIN
	SET @insert_value = '';
	# 已经插入的记录总行数
  SET @count = 0;
	# 
	SET @batch_count = 0;
	WHILE @count < num DO
		# 内while循环用于拼接INSERT INTO t VALUES (),(),(),...语句中VALUES后面部分
		WHILE (@batch_count < batch AND @count < num) DO
			IF @batch_count>0
			THEN 
				SET @insert_value = concat(@insert_value,',');
			END IF;
			SET @insert_value = concat(@insert_value,"('name", @count, "','address", @count, "')");
			SET @batch_count = @batch_count+1;	
		END WHILE;

		SET @count = @count + @batch_count;
		# 拼接SQL语句并执行
		SET @exesql = concat("insert into t(name,address) values ", @insert_value);	
		PREPARE stmt FROM @exesql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
		# 重置变量值
		SET @insert_value = '';
		SET @batch_count=0;
	END WHILE;
	# 数据插入完成后，查看表中总记录数
	SELECT COUNT(id) FROM t;
END
//
delimiter;

调用该存储过程：

#调用存储过程
CALL sp_test_batch(100000,1000);

速度会提升很多很多。