MySQL使用存储过程快速生成千万条数据

一、实现思路

1.先创建好插入数据时需要的自动生成数据的函数。

2.再创建好存储过程，实现调用创建好的函数自动生成数据插入数据表。

3.通过不断循环插入内存表，再从内存表获取数据插入普通表，最后删除内存表，以此循环直至循环结束。

二、实现步骤

1.创建存储表和内存表

#创建内存表
CREATE TABLE `user_memory`
(
    `id`          int(11)     NOT NULL AUTO_INCREMENT comment 'ID',
    `user_name`   varchar(30) NOT NULL comment '用户名',
    `phone`       varchar(20) NOT NULL comment '手机号',
    `create_time` datetime    NOT NULL comment '创建时间',
    PRIMARY KEY (`id`)
) ENGINE = MEMORY
  DEFAULT CHARSET = utf8mb4;

#创建普通表
CREATE TABLE `user_list`
(
    `id`          int(11)     NOT NULL AUTO_INCREMENT comment 'ID',
    `user_name`   varchar(30) NOT NULL comment '用户名',
    `phone`       varchar(20) NOT NULL comment '手机号',
    `create_time` datetime    NOT NULL comment '创建时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

2.创建所需函数

2.1创建生成n个随机数字的函数

DELIMITER $$
CREATE FUNCTION randNum(n int) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str varchar(20) DEFAULT '0123456789';
    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() * 10), 1));
            SET i = i + 1;
        END WHILE;
    RETURN return_str;
END $$
DELIMITER ;

该函数中所用到的MySQL函数及其功能如下：
        ❶ concat()：将多个字符串连接成一个字符串。
        ❷ Floor()：向下取整。
        ❸ substring(string, position, length)：
                第一个参数：string指的是需要截取的原字符串。
                第二个参数：position指的是从哪个位置开始截取子字符串，这里字符的位置编码序号是从1开始，若position为负数则从右往左开始数位置。
                第三个参数：length指的是需要截取的字符串长度，如果不写，则默认截取从position开始到最后一位的所有字符。
        ❹ RAND()：只能生成0到1之间的随机小数。

2.2创建随机生成手机号的函数

DELIMITER $$
CREATE FUNCTION generatePhone() RETURNS varchar(20)
BEGIN
    DECLARE head char(3);
    DECLARE phone varchar(20);
    DECLARE bodys varchar(100) default "130 131 132 133 134 135 136 137 138 139 186 187 189 151 157";
    DECLARE starts int;
    SET starts = 1 + floor(rand() * 15) * 4;
    SET head = trim(substring(bodys, starts, 3));
    SET phone = trim(concat(head, randNum(8)));
    RETURN phone;
END $$
DELIMITER ;

2.3创建随机生成用户名的函数

DELIMITER $$
CREATE FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4
    DETERMINISTIC
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() * 62), 1));
            SET i = i + 1;
        END WHILE;
    RETURN return_str;
END$$
DELIMITER ;

 3.创建存储过程

3.1创建插入内存表数据存储过程(入参n是多少就插入多少条数据)

DELIMITER $$
CREATE PROCEDURE `add_user_memory`(IN n int)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n)
        DO
            INSERT INTO user_memory (user_name, phone, create_time) VALUES (randStr(20), generatePhone(), NOW());
            SET i = i + 1;
        END WHILE;
END $$
DELIMITER ;

 

3.2创建内存表数据插入普通表存储过程

    处理：利用对内存表的循环插入和删除来实现批量生成数据。

    优势：不需要更改mysql默认的max_heap_table_size值。

max_heap_table_size 默认值是16M。
max_heap_table_size 的作用是配置用户创建内存临时表的大小，配置的值越大，能存进内存表的数据就越多。

DELIMITER $$
CREATE PROCEDURE `add_user_list`(IN n int, IN count int)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE (i <= n)
        DO
            CALL add_user_memory(count);
            INSERT INTO user_list SELECT * FROM user_memory;
            delete from user_memory;
            SET i = i + 1;
        END WHILE;
END $$
DELIMITER ;

 

 4.调用存储过程插入数据

通过调用add_user_memory存储过程，不断循环插入内存表，再从内存表获取数据插入普通表，最后删除内存表，以此循环直至循环结束。循环1000次，每次生成10000条数据，共生成一千万条数据。

CALL add_user_list(1000,10000);

5.统计数量

SELECT COUNT(*) FROM user_list;

5.最终用时

我使用的本地数据库，最终用时26分34秒

三、问题

在插入大量数据到数据库时，需要考虑MySQL内存表存储大小，当数据量超出MySQL内存表存储大小时会报内存表已满的异常。

查看MySQL内存表存储大小的值：

show VARIABLES like 'max_heap_table_size'

修改MySQL内存表存储大小的值的方法如下：

① 通过执行MySQL命令修改 

SET GLOBAL tmp_table_size=2147483648;
SET GLOBAL max_heap_table_size=2147483648;

② 通过修改MySQL配置文件

vi /etc/my.cnf
[mysqld]
max_heap_table_size = 2048M
tmp_table_size = 2048M