【Mysql】写个批量插入百万数据的存储过程

累积1w条插入一次，很快。比如累积10w再插更好。

CREATE DEFINER=`root`@`localhost` PROCEDURE `data_insert`( )
BEGIN

	set @i = 2, @total = 1000000, @phone = '', @insert_sql = 'insert into member values(1, 13000000001)', @target = 10000;
	WHILE
			@i <= @total 
	DO
		set @phone = '' + @i;
		while
			length(@phone) < 9
		do
			set @phone = concat('0', @phone);
		END WHILE;
			
		if ((@i / @target) >= 1) then
			PREPARE stmt FROM @insert_sql;
			EXECUTE stmt;
			deallocate prepare stmt; 
			set @target = @target + 10000;
			set @insert_sql = concat('insert into member values(', @i,',', concat('13', @phone), ')');
		else
			set @insert_sql = concat(@insert_sql, ',(', @i, ',', concat('13', @phone) , ')');
		end if;
			
			
		SET @i = @i + 1;

	END WHILE;
	insert into member values(1000000, 13001000000);
	
END

插入数据后再去加索引。47秒可以接受。