MySQL quickly generates a large amount of test data (1million, 10million, 100million)

Time:2022-6-9

Build table

drop table if exists t1;

CREATE TABLE  t1(

  id int NOT NULL AUTO_ Increment primary key comment 'primary key', 

  person_ ID tinyint not null comment 'user id',

  person_ Name varchar (30) comment 'user name',

  gmt_ Create datetime not null comment 'create time', 

  gmt_ Modified datetime comment 'modified time'

)Comment 'test table 2048';

Insert a piece of data

insert into t1 values(1,1,'user_1', NOW(), now());

Use MySQL pseudo column rownum to set the starting point of pseudo column to 1

select (@i:[email protected]+1) as rownum, person_name from t1, (select @i:=100) as init;
MySQL quickly generates a large amount of test data (1million, 10million, 100million)

image.png
set @i=1;
MySQL quickly generates a large amount of test data (1million, 10million, 100million)

Run the following SQL

Execute 20 times, i.e. the 20th power of 2 =1048576 records (1million)

23 times of execution, i.e. the 23rd power of 2 =8388608 records (8million)

Execute 24 times, i.e. the 24th power of 2 =16777216 records (16million)

25 times of execution, i.e. the 25th power of 2 =33554432 records (33million)

Executed 26 times, i.e. the 26th power of 2 =67108864 records (67million)

Execute 27 times, i.e. the 27th power of 2 =134217728 records (130million)


insert into t1(id, person_id, person_name, gmt_create, gmt_modified) 

select @i:[email protected]+1,

  left(rand()*10,1) as person_id,

  concat('user_',@i%2048),

  date_add(gmt_create,interval + @i*cast(rand()*100 as signed) SECOND),

  date_add(date_add(gmt_modified,interval [email protected]*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND)

from t1;

MySQL quickly generates a large amount of test data (1million, 10million, 100million)

image.png

explain

The left() function is a string function that returns the left part of a string of a specified length.
The following is the syntax for the left() function-

LEFT(str,length);
SQL
The left() function accepts two parameters:
STR is the string to extract the substring. Length is a positive integer that specifies the number of characters to be returned from the left

Interval keyword
The interval keyword can be used to calculate a time interval. It can be used in the following ways.
1. Calculate the time interval directly.
Example 1: query the date 2 hours before the current time:

SELECT NOW()-INTERVAL '2' HOUR;
SELECT
    id,
    percent,
    t_date,
    t_date - INTERVAL 2 HOUR
FROM
    test
where t_date - INTERVAL 2 HOUR>'2020-11-02';
The numbers after interval can be in number format or character format. When the time unit is year_ In the case of month, the character format must be used

MySQL DATE_ Add() function
Definition and Usage
DATE_ The add() function adds the specified time interval to the date.

grammar
DATE_ADD(date,INTERVAL expr type)
We want to add 2 days to “OrderDate”
DATE_ADD(OrderDate,INTERVAL 2 DAY)

Cast functionConverts a value of any type to a value of the specified typehttps://www.yiibai.com/mysql/cast.html
rand()The value range of is [0,1)
Tinyint field exampleunsignedThe value range of the field is 0-255, andsignedThe range of is -128 – 127
SECONDsecond

Create index

create index idx_person_id on t1(person_id);

create index idx_gmt_create on t1(gmt_create);

create index idx_gmt_modified on t1(gmt_modified);