二十八、MySQL基础系列笔记之实战循环插入百万数据

这是存储过程与函数的一次合作，数据库中插入百万级数据，也可以插入千万级数据，不同级别的数据所花费的时间不同。

本文中将利用存储过程与函数往数据库中插入500W数据。

关闭二进制bin-log

# 禁用bin-log
set global log_bin_trust_function_creators=1;

当二进制日志启用后，上面的变量就会启用，启用后创建函数会报错，错误ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)。因此关闭这个函数

使用存储过程插入百万级数据

# 创建用户测试表
CREATE TABLE `test_user`(
	`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '',
    `username` VARCHAR(60) NOT NULL COMMENT '用户名',
    `age` TINYINT(3) NOT NULL DEFAULT 0 COMMENT '年龄'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


# 生成指定长度的随机数函数
DELIMITER //
DROP FUNCTION IF EXISTS randStr//
CREATE FUNCTION randStr(totalNum INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE randString VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < totalNum DO
    	SET randString = CONCAT(SUBSTR(str,FLOOR(RAND()*LENGTH(str))+1,1),randString);
    	SET i=i+1;
    END WHILE;
    RETURN randString;
END//

# 生成指定范围的随机数字
DELIMITER //
DROP FUNCTION IF EXISTS randNumber//
CREATE FUNCTION randNumber(beginNum INT, fromNum INT) RETURNS INT
BEGIN
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(beginNum+RAND() * (fromNum - beginNum + 1));
	RETURN i;
END//

# 创建存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS createData//
CREATE PROCEDURE createData(IN totalNum INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE i < totalNum DO
		INSERT INTO `test_user` (username,age) values (randStr(6),randNumber(1,70));
		SET i=i+1;
	END WHILE;
END//
mysql> call createData(500000)//
Query OK, 1 row affected (6 min 33.63 sec)

# 创建存储过程，批量插入数据
# 推荐使用此存储过程
DELIMITER //
DROP PROCEDURE IF EXISTS createData//
CREATE PROCEDURE createData(IN totalNum INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	# 关闭事务
	SET autocommit=0;
	WHILE i < totalNum DO
		INSERT INTO `test_user` (username,age) values (randStr(52),randNumber(1,70));
		SET i=i+1;
	END WHILE;
	# 提交事务
	COMMIT;
END//
mysql> call createData(500000)//
Query OK, 0 rows affected (34.81 sec)

从两中存储过程中可以看到，同样插入50W数据，但所花费的时间截然不同，使用哪个清晰可见。

2020-11-21