mysql如何快速⽣成百万测试数据
mysql 如何快速⽣成百万测试数据
实现思路
在我们平时⼯作或学习的过程中，有时需要在数据库中⽣成⼤量的测试数据，这个时候，我们可以利⽤mysql内存表插⼊速度快的特点，先利⽤函数和存储过程在内存表中⽣成数据，然后再从内存表插⼊普通表中。经过我的测试，这种⽅案插⼊数据是⾮常快的。
下⾯是实现步骤。
1、创建内存表和普通表
#创建内存表
CREATE TABLE `test_user_memory` (
`id` int(11) NOT NULL AUTO_INCREMENT comment '主键id',
`user_id` varchar(36) NOT NULL comment '⽤户id',
`user_name` varchar(30) NOT NULL comment '⽤户名称',
eclipse导入包全是错误`phone` varchar(20) NOT NULL comment '⼿机号码',
`lan_id` int(9) NOT NULL comment '本地⽹',
`region_id` int(9) NOT NULL comment '区域',
`create_time` datetime NOT NULL comment '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
#创建普通表
CREATE TABLE `test_user` (
`id` int(11) NOT NULL AUTO_INCREMENT comment '主键id',
`user_id` varchar(36) NOT NULL comment '⽤户id',
`user_name` varchar(30) NOT NULL comment '⽤户名称',
`phone` varchar(20) NOT NULL comment '⼿机号码',
`lan_id` int(9) NOT NULL comment '本地⽹',
`region_id` int(9) NOT NULL comment '区域',
`create_time` datetime NOT NULL comment '创建时间',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
小程序制作单位2、创建函数及存储过程
创建⽣成n个随机数字的函数
⽣成⼿机号码的时候要⽤到
#⽣成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;
创建⽣成号码函数
#⽣成随机⼿机号码
# 定义常⽤的⼿机头 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)
java三层架构和mvc
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 ;
创建随机字符串函数
#创建随机字符串和随机时间的函数
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_id, user_name, phone, lan_id,region_id, create_time) VALUES (uuid(), randStr(20), generatePhone(), FLOOR(R AND() * 1000), FLOOR(RAND() * 100), NOW());
SET i = i + 1;
END WHILE;
END $$
DELIMITER ;
手机mysql安装配置教程创建内存表数据插⼊普通表的存储过程
此处利⽤对内存表的循环插⼊和删除来实现批量⽣成数据，这样可以不需要更改mysql默认的max_heap_table_size值也照样可以⽣成百万或者千万的数据。
max_heap_table_size默认值是16M。
max_heap_table_size的作⽤是配置⽤户创建内存临时表的⼤⼩，配置的值越⼤，能存进内存表的数据就越多。
#循环从内存表获取数据插⼊普通表
#参数描述 n表⽰循环调⽤⼏次；count表⽰每次插⼊内存表和普通表的数据量
DELIMITER $$
CREATE PROCEDURE `add_test_user_memory_to_outside`(IN n int, IN count int) BEGIN
DECLARE i INT DEFAULT 1;
servletWHILE (i <= n) DO
CALL add_test_user_memory(count);
INSERT INTO test_user SELECT * FROM test_user_memory;
delete from test_user_memory;
SET i = i + 1;
END WHILE;
collapse在html什么作用END $$
DELIMITER ;
3、调⽤存储过程插⼊数据
#先调⽤存储过程往内存表插⼊⼀万条数据，然后再把内存表的⼀万条数据插⼊普通表CALL add_test_user_memory(10000);
#⼀次性把内存表的数据插⼊到普通表，这个过程是很快的
INSERT INTO test_user SELECT * FROM test_user_memory;
#清空内存表数据
delete from test_user_memory;
先简单测试⼀下插⼊内存表⼀万条数据要花多少时间
把内存表的⼀万条数据⼀次性插⼊普通表，只需要很短的时间就完成了，如下图所⽰
查询刚刚插⼊的普通表的数据
因为我没有更改数据库内存表内存⼤⼩，所以单次插⼊内存表⼀万条数据是没问题的，但是单次插⼊内存表⼗万条数据就不⾏了，会报内存表已满的异常。如下图所⽰
如果想调⽤⼀次存储过程插⼊普通表⼗万或百万的数据要如何实现呢？这⾥有两种⽅案
⼀、修改mysql内存表存储⼤⼩的值
1、通过执⾏mysql命令修改
SET GLOBAL tmp_table_size=2147483648;
SET GLOBAL max_heap_table_size=2147483648;
2、通过修改mysql配置⽂件
vi /etc/myf
[mysqld]
max_heap_table_size = 2048M
tmp_table_size = 2048M
可以通过下⾯的⽅式查看内存表存储⼤⼩
⼆、调⽤我写的另⼀个存储过程 add_test_user_memory_to_outside
这个存储过程就是通过不断循环插⼊内存表，再从内存表获取数据插⼊普通表，最后删除内存表，以此循环直⾄循环结束。
#循环100次，每次⽣成10000条数据总共⽣成⼀百万条数据
CALL add_test_user_memory_to_outside(100,10000);
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论