Mysql创建百万测试数据

效果图

注意：主要是"第一步" 和"第六步"，2、3、4、5 主要用于生成一些随机内容，给字段赋值

第一步：创建内存表和普通表

CREATE TABLE `test_user_memory`
( 
 id BIGINT ( 20 ) PRIMARY KEY NOT NULL auto_increment COMMENT 'id',
 user_name VARCHAR(36) NOT NULL DEFAULT '' COMMENT '用户名称',
 user_phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号码',
 gmt_create datetime NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '创建时间'
) ENGINE = MEMORY charset = utf8mb4;

CREATE TABLE `test_user`
( 
 id BIGINT ( 20 ) PRIMARY KEY NOT NULL auto_increment COMMENT 'id',
 user_name VARCHAR(36) NOT NULL DEFAULT '' COMMENT '用户名称',
 user_phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号码',
 gmt_create datetime NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '创建时间'
) ENGINE = INNODB charset = utf8mb4;

第二步：创建随机数函数（有时候会出现错误，报错的话请看第三步，否则跳过第三步）

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;

第三步：创建函数报错解决

报错信息

解决方案

• 临时方案（重启数据库后失效）：SET GLOBAL log_bin_trust_function_creators = 1;
• 根本方案（修改后需要重启数据库，永久生效）：在my.cnf配置文件里面设置 log-bin-trust-function-creators=1

第四步：结合随机数的函数，生成随机手机号，用于user_phone 字段的插入

#生成随机手机号码
# 定义常用的手机头 130 131 132 133 134 135 136 137 138 139 186 187 189 151 157
#SET starts = 1+floor(rand()*15)*4;   截取字符串的开始是从 1、5、9、13 ...开始的。floor(rand()*15)的取值范围是0~14
#SET head = substring(bodys,starts,3);在字符串bodys中从starts位置截取三位

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 ;

第五步：创建随机字符串，用于user_name 字段的插入

#创建随机字符串和随机时间的函数
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;

第六步：创建插入内存表的存储过程

# 创建插入内存表数据存储过程   入参n是多少就插入多少条数据
DELIMITER $$
CREATE PROCEDURE `add_test_user_memory`(IN n int)
 BEGIN
 DECLARE i INT DEFAULT 1;
 WHILE (i <= n) DO
  INSERT INTO test_user_memory (user_name, user_phone,gmt_create) VALUES (randStr(20), generatePhone(), NOW());
  SET i = i + 1;
 END WHILE;
 END $$
 DELIMITER ;

最后调用

#先调用存储过程往内存表插入一万条数据，然后再把内存表的一万条数据插入普通表
CALL add_test_user_memory(10000);
#一次性把内存表的数据插入到普通表，这个过程是很快的
INSERT INTO test_user SELECT * FROM test_user_memory;
#清空内存表数据
DELETE FROM test_user_memory;

结束提示

插入太多可能会报错

解决方案

临时方案：max_heap_table_size大小，查询修改成功之后，再创建临时表执行CALL add_test_user_memory(10000);

show variables like '%table_size%';
set global max_heap_table_size=1048576000
set global tmp_table_size=1048576000

windows下，永久根除

linux下，永久根除

打开/etc/mysql目录下的my.cnf并在后面添加

tmp_table_size = 256M
max_heap_table_size = 256M

然后生效。