mysql储存过程插入500w数据

一、准备工作

1.开启慢查询日志

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

## 开启
SET GLOBAL log_bin_trust_function_creators = 1;

2.定义一个新的命令结束符

delimiter $

## 如果执行不了去cmd中执行

二、准备2个随机函数

1.随机创建字符串

​ 使用 rand_string(5)

CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE
		return_str VARCHAR ( 255 ) DEFAULT '';
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < n DO
			SET return_str = concat(
				return_str,
			substring( chars_str, floor( 1+rand ()* 52 ), 1 ));
		SET i = i + 1;
	END WHILE;
	RETURN return_str;
END $

2.随机数字

​ 使用方式： rand_num(1000, 2000)

CREATE FUNCTION rand_num (
    start_num INTEGER,
    end_num INTEGER
) RETURNS INTEGER
BEGIN
    RETURN FLOOR(start_num + RAND() * (end_num - start_num + 1));
END $

三、建立存储过程

-- 创建存储过程
CREATE PROCEDURE insert_table (
	IN START INT ( 10 ),
	IN max_num INT ( 10 )) BEGIN
	DECLARE
		i INT DEFAULT START;
	#set autocommit = 0  //把autocommit设置成0，手动提交（批量插入）
	SET autocommit = 0;
	REPEAT
			SET i = i + 1;
		INSERT INTO test ( id, number, str )
		VALUES
			(   concat('X1001', i) ,
                rand_num (),
				rand_string ( 6 )
            );
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;
END $

四、执行插入

这里记得先改回结束符

id从X1001100000起 到 X1001600000 一次插入80万，分几次操作效率更高
call insert_table(100000,600000);

当然，对其他的表创建百万测试数据只需要修改储存过程中的 INSERT 语句即可