How to quickly create millions of test data

scene

During SQL optimization or query performance test, we need a lot of data test to simulate. At this time, a problem arises: data creation

How to quickly create large amounts of data

Preparation for operation

First of all, no matter which operation we choose, we must prepare a table first. There is no doubt about this; Then we will simply create a table as follows;

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,`c_user_id` varchar(36) NOT NULL DEFAULT '',`c_name` varchar(22) NOT NULL DEFAULT '',`c_province_id` int(11) NOT NULL,`c_city_id` int(11) NOT NULL,`create_time` datetime NOT NULL,PRIMARY KEY (`id`),KEY `idx_user_id` (`c_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Using stored procedures and memory tables

We first use functions and stored procedures to generate data in the memory table, and then insert ordinary tables from the memory table

1 create a memory table

CREATE TABLE `t_user_memory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,KEY `idx_user_id` (`c_user_id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

2 create functions and stored procedures

# 创建随机字符串和随机时间的函数
MysqL> delimiter $$
MysqL> CREATE DEFINER=`root`@`%` 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$$
Query OK,0 rows affected (0.00 sec)

MysqL> CREATE DEFINER=`root`@`%` FUNCTION `randDataTime`(sd DATETIME,ed DATETIME) RETURNS datetime
    ->     DETERMINISTIC
    -> BEGIN
    ->     DECLARE sub INT DEFAULT 0;
    ->     DECLARE ret DATETIME;
    ->     SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));
    ->     SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);
    ->     RETURN ret;
    -> END $$

MysqL> delimiter ;

# 创建插入数据存储过程
MysqL> CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_memory`(IN n int)
    -> BEGIN
    ->     DECLARE i INT DEFAULT 1;
    ->     WHILE (i <= n) DO
    ->         INSERT INTO t_user_memory (c_user_id,c_name,c_province_id,c_city_id,create_time) VALUES (uuid(),randStr(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100),Now());
    ->         SET i = i + 1;
    ->     END WHILE;
    -> END
    -> $$
Query OK,0 rows affected (0.01 sec)

Call stored procedure

MysqL> CALL add_t_user_memory(1000000);   //添加的数据量
ERROR 1114 (HY000): The table 't_user_memory' is full

PS: error 1114 (HY000) appears: the table't_ user_ Memory 'is full error, indicating that the memory is full

Processing method: modify Max_ heap_ table_ The default size of the size parameter is 32m or 64M. Don't try in the production environment

Insert normal table from memory table

MysqL> INSERT INTO t_user SELECT * FROM t_user_memory;
Query OK,218953 rows affected (1.70 sec)
Records: 218953  Duplicates: 0  Warnings: 0

Temporary table mode

Create temporary data table tmp_ table

CREATE TABLE tmp_table (
    id INT,PRIMARY KEY (id)
);

python: python -c "for i in range(1,1+1000000): print(i)" > base. txt

Import data to temporary table tmp_ In table