mysql 一句SQL批量插入数据(存储过程，循环插入)

-- 如果该名字存储过程已存在，则删除
DROP PROCEDURE IF EXISTS proc_initData1;
-- 创建
CREATE PROCEDURE proc_initData1 () 
BEGIN

    -- 声明一堆变量
	DECLARE 
		i INT DEFAULT 0;
	DECLARE
		uid VARCHAR ( 255 ) DEFAULT 0; 
	DECLARE
		count DECIMAL(20,8) DEFAULT 0;
	DECLARE
		op INT DEFAULT 0;
	DECLARE
		age INT DEFAULT 0;
	DECLARE
		ed varchar(255) DEFAULT '0';
	DECLARE
		size varchar(255) DEFAULT '0';
	DECLARE
		start varchar(255) DEFAULT '0';
	DECLARE
		open varchar(255) DEFAULT '0';
	DECLARE
		x_name varchar(255) DEFAULT '0';
	DECLARE
		s_name varchar(255) DEFAULT '0';
	DECLARE
		v_name varchar(255) DEFAULT '0';
	DECLARE
		period varchar(255) DEFAULT '0';
	DECLARE
		xhold varchar(255) DEFAULT '0';

    -- 开启事务
	START TRANSACTION;
	
    -- do while 循环
	WHILE
			i <= 10000 DO

        -- 将变量赋值
		SELECT
			CONCAT( '1', CEILING( RAND() * 9000000000+1000000000 ) ) INTO uid;
		SELECT
			FLOOR( 18 + ( RAND() * 9 ) ) INTO age;
		SELECT
			ROUND( ( 10 + ( RAND() * 1001 ) ), 8 ) INTO count;
		select 
		  ROUND( RAND() * 10 , 0 ) INTO op;  
		select
		  concat(left('qwertyuioadhncndhs',round(rand()*10,0)) ,         right('qwertyuioadhncndhs',round(rand()*10,0))) into ed;
		select
		  concat(left('1574524147474',round(rand()*10,0)) , right('8785452521754',round(rand()*10,0))) into size;
		select
		  concat(left('whoiekdjane',round(rand()*10,0)) , right('atlefjehsnhf',round(rand()*10,0))) into start;
		select
		  concat(left('qwertyuioadhncndhs',round(rand()*10,0)) , right('qwertyuioadhncndhs',round(rand()*10,0))) into open;
	  select
		  concat(left('ereresdfswsdf',round(rand()*10,0)) , right('cvdfwsdfweef',round(rand()*10,0))) into x_name;
		select
		  concat(left('sdfcxvxcsdfesfdsfe',round(rand()*10,0)) , right('dgdfgdfgdfgsdfsdf',round(rand()*10,0))) into s_name;
		select
		  concat(left('qwertyuioadhncndhs',round(rand()*10,0)) , right('ertertertedgdfgd',round(rand()*10,0))) into v_name;
		select
		  concat(left('ertertsdfwesdf',round(rand()*10,0)) , right('ertersdfcxvgrg',round(rand()*10,0))) into period;
		select
		  concat(left('reggergdfgsdfwe',round(rand()*10,0)) , right('ertfgdsdfserg',round(rand()*10,0))) into xhold;
        
        -- 实际的插入操作
		INSERT INTO user3 ( uid, age, count,op, status ,ed,size,start,open,x_name,s_name,v_name,period,xhold ) 
			VALUES
		(uid, age, count,op, 1,ed,size,start,open,x_name,s_name,v_name,period,xhold);
		
        -- 条件自增长
		SET i = i + 1;
		
    -- 结束循环
	END WHILE;
    -- 提交
	COMMIT;
	
END -- 可以先执行到这里，下面的调用可以单独运行

-- 调用
CALL proc_initData1();

0.0.0 后面

数据都是固定的，虽说使用了随机，这个随机太固定了。这样搞是不想使用其他手段，单靠一句sql语句来搞。当然能使用java写个循环就更好了

搞这个循环插入只是为了看看在大量数据的情况下各个sql语句的执行效率

为了看看sql优化的效果