MySQL自动生成亿级数据

MySQL自动生成亿级数据

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

drop database `user_list`;
create database `user_list` character set `utf8mb4`;
use `user_list`;
#创建内存表
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;

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 ;

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 ;

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 ;
 
 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 ;
 
  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 ;
 
 CALL add_user_memory(1000000);
 
--  SELECT COUNT(*) FROM user_list;
--  
--  SELECT COUNT(*) FROM user_list;    

-- SET SESSION tmp_table_size=64*1024*1024*1024;
-- SET SESSION max_heap_table_size=32*1024*1024*1024;

-- SELECT @@tmp_table_size;
-- SELECT @@max_heap_table_size;

遇到的问题

1. Error Code: 2013. Lost connection to MySQL server during query 30.000 sec

MySQLworkbench 默认查询超时是30秒，修改位置Edit->Performance->SQL Editor

2. Error Code: 1114. The table 'user_memory' is full

可能的原因：

1. 存放数据的磁盘整的已经放满，不能再写入数据，需要优化磁盘的存储大小。

2. 超过了MysqL的临时表大小 和内存表大小

解决方案：

执行SQL语句，设置临时表大小，tmp_table_size默认值16777216，也就是16MB，最大值18446744073709551615。

SET SESSION tmp_table_size=64*1024*1024;    #临时表的内存缓存大小
SET SESSION max_heap_table_size=32*1024*1024;     #MEMORY内存引擎的表大小

3. 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) 0.000 sec

可能的原因：

本地环境和服务器环境不一样就报错了

解决办法：

1.刷入sql语句，重启后失效
set global log_bin_trust_function_creators=TRUE;

2.在配置文件 my.ini 的 [mysqld] 配置，永久生效
log_bin_trust_function_creators

4. MySQL Workbench显示不全的问题

如图所示，新增的数据最多只显示1000行

需要在设置修改，如图：

参考：

• mysql生成千万级数据

• MySQL利用存储过程自动生成千万条数据