MySQL quickly generates local test data

Generate test data using stored procedures for data:

We can add data to a normal data table directly in the stored procedure through the INSERT statement of the database, but

When we add millions of data, the performance of inserting test data into a normal table is significantly reduced. So here's the suggestion

Use the memory table to do a middle cushion, in the memory table after the generation of millions data, in a one-time insert ordinary data table

To complete the millions test data generation. Here we look at the process:

The first step is to create the memory table:

CREATE TABLE' vote_record_memory ' (' ID ')INT( One) not NULLauto_increment, 'user_id`VARCHAR( -) not NULL, ' vote_id 'INT( One) not NULL, ' group_id 'INT( One) not NULL, ' Create_time 'datetime  not NULL,      PRIMARY KEY(' id '),KEY' Index_id ' ('user_id`) USING HASH) ENGINE= MEMORYAuto_increment= 1 DEFAULTCHARSET=Utf8

The second step is to create a normal table:

CREATE TABLE' Vote_record ' (' ID ')INT( One) not NULLauto_increment, 'user_id`VARCHAR( -) not NULL, ' vote_id 'INT( One) not NULL, ' group_id 'INT( One) not NULL, ' Create_time 'datetime  not NULL,      PRIMARY KEY(' id '),KEY' Index_user_id ' ('user_id`) USING HASH) ENGINE= INNODBAuto_increment= 1 DEFAULTCHARSET=Utf8

Note: The difference between a memory table and a normal table differs from their storage engine in that the SQL code is marked with red bold.

In the process of inserting, in order to make the data closer to authenticity, we create a random parameter of the function auxiliary data generation.

The third step is to create a random build function:

DELIMITER//
CREATE FUNCTION' Rand_string ' (nINT)RETURNS varchar(255) CHARSET latin1BEGIN DECLAREChars_strvarchar( -)DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLAREReturn_strvarchar(255)DEFAULT "' ;DECLAREIINT DEFAULT 0;  whileI<N doSETReturn_str=Concat (RETURN_STR,substring(Chars_str, Floor(1 + RAND()* +),1)); SETI=I+1; END  while; RETURNReturn_str;END//

The fourth step is to create the stored procedure:

DELIMITER//
CREATE  PROCEDURE' Add_vote_memory ' (nint,len int)BEGIN    DECLAREIINT DEFAULT 1;  while(I<=N) doINSERT  intoVote_record_memory (user_id, Vote_id,group_id,create_time)VALUEs(Rand_string (Len), Floor(RAND()*  +), Floor(RAND()*  -), now ()); SetI=I+1; END  while;END//

The fifth step executes the stored procedure : Generate the data of the million , user_id length of three characters

Call Add_vote_memory (1000000);

Note: There may be insufficient memory or overflow hints in the generated process, you need to set the MY.CNF parameter max_heap_table_size

Max_heap_table_size = 4096M

Define max_heap_table_size to 4G to represent the storage size of the data table in memory, ensuring sufficient space

Store data because the memory table stores the table structure on disk and puts the data in memory

Sixth step to view the number of data bars in the memory table:

Select Count (* from Vote_record_memory;

Note: If the number of bars does not reach the number of bars you generate, wait a moment or refresh the statistics bar number to reach the number of books you want to generate, because

Data volume The Conference has a certain delay, in order to find out the results of the final data will prevail. (If you don't care how many data bars can be ignored)

The seventh step is to insert the data from the memory table into the normal table:

INSERT  into SELECT *  from  Vote_record_memory;

The eighth step is to view the data for the normal table:

Select Count (* from Vote_record;

The Nineth step is to delete the data from the memory table:

Delete  from Vote_record_memory;

This is the complete.

MySQL quickly generates local test data