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

作者: 温新

分类: 【MySql】

阅读: 1895

时间: 2020-11-21 09:11:46

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

本文中将利用存储过程与函数往数据库中插入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 logbintrustfunctioncreators 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)

mysql> call createData(5000000)//
Query OK, 0 rows affected (18 min 17.03 sec)

差距很大,一次插入50W与500W所花费的时间太大了,en e~~~~~

我是小白,期待和优秀的你一起同行!

小白

2020年11月27日

请登录后再评论