MySQL quickly generates millions test data

Ext. 54691590

1. Generating ideas
Using MySQL memory table insertion speed features, first use functions and stored procedures in the memory table to generate data, and then inserted from the memory table in the normal table
2. Create memory table and normal 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=MEMORY auto_increment= 1 DEFAULTCHARSET=Utf8

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=INNODB auto_increment= 1 DEFAULTCHARSET=Utf8

3. Creating functions and stored procedures

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

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

4. Call the stored procedure

CALL add_vote_memory(1000000)

According to the computer performance can not take the same time, about time in the hour level, if the error memory is full, only modify max_heap_table_size parameters can be, Win7 modify the location as follows, Linux, Modify the my.cnf file to restart MySQL after the change, the memory table data will be lost after reboot

5, insert the ordinary table

INSERT into vote_record SELECT * from vote_record_memory

• 1

6. Results

MySQL quickly generates millions test data