INSERT INTO SELECT statement test in MySQL

MySQL quickly creates large amounts of data, copying (some or all) of the data from one table to another.

Usage: INSERT into table_name1 (field1,field2) SELECT field1,field2 from Table_name2;

Prerequisite conditions

The code is as follows

Copy Code

Mysql CREATE TABLE ' user ' ( ' ID ' int (a) not NULL auto_increment, ' username ' varchar not NULL, ' Password ' char (not NULL), PRIMARY KEY (' id ') ) Engine=innodb DEFAULT Charset=utf8; CREATE TABLE ' User_his ' ( ' his_id ' int (a) not NULL auto_increment, ' ID ' int (a) not NULL, ' username ' varchar not NULL, ' Password ' char (not NULL), PRIMARY KEY (' his_id ') ) Engine=innodb DEFAULT Charset=utf8;

Look at the structure, more intuitive

The code is as follows

Copy Code

mysql> desc User; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | ID | Int (10) | NO | PRI | NULL | auto_increment | | Username | varchar (30) |     NO | |                NULL | | | password | char (32) |     NO | |                NULL | | +----------+-------------+------+-----+---------+----------------+ 3 Rows in Set (0.00 sec) mysql> desc user_his; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | his_id | Int (10) | NO | PRI | NULL | auto_increment | | ID | Int (10) |     NO | |                NULL | | | Username | varchar (30) |     NO | |                NULL | | | password | char (32) |     NO | |                NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in Set (0.01 sec) Insert raw Data Mysql mysql> INSERT into ' user ' (' username ', ' password ') VALUES (' Hello ', ' 123456 '), (' Twitter ', ' 123456 '), (' Baidu ', ' 123456 '), (' Google ', ' 123456 '), (' Facebook ', ' 123456 '), (' Linux ', ' 123456 '), (' Cisco ', ' 123456 '), (' 123456 '), ( ' Lenovo ', ' 123456 '), (' Apple ', ' 123456 '), (' Oracle ', ' 123456 '), (' Sun ', ' 123456 '); Copy Data to History table Mysql mysql> INSERT into ' user_his ' (' IDs ', ' username ', ' password ') Select ' id ', ' username ', ' password ' from ' user ';

Additional MySQL bulk copy data, time change:

Test on their own computer (Ubuntu14.04 LTS 64-bit + XAMPP), the first 3,000 data faster, 3,000 after the execution time multiplied, 25,000 data execution time 1 minutes. 3.14 million data, 2 minutes, 29 seconds.

The code is as follows

Copy Code

mysql> INSERT INTO User (Username,password) select Username,password from user; Query OK, rows affected, 1 warning (0.07 sec) Records:12 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user; Query OK, rows affected, 1 warning (0.08 sec) Records:24 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user; Query OK, affected, 1 warning (0.11 sec) records:48 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user; Query OK, rows affected, 1 Warning (0.10 sec) records:96 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user; Query OK, affected rows, 1 Warning (0.10 sec) records:192 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user; Query OK, 384 rows affected, 1 Warning (0.10 sec) records:384 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 768 rows affected, 1 warning (0.13 sec) records:768 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 1536 rows affected, 1 warning (0.15 sec) records:1536 duplicates:0 warnings:1 Mysql> mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 3072 rows affected, 1 warning (0.17 sec) records:3072 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 6144 rows affected, 1 warning (0.28 sec) records:6144 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 12288 rows affected, 1 warning (0.42 sec) records:12288 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 24576 rows affected, 1 Warning (0.99 sec) records:24576 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 49152 rows affected, 1 Warning (1.98 sec) records:49152 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 98304 rows affected, 1 Warning (4.04 sec) records:98304 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 196608 rows affected, 1 Warning (8.89 sec) records:196608 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 393216 rows affected, 1 Warning (17.14 sec) records:393216 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 786432 rows affected, 1 Warning (38.07 sec) records:786432 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 1572864 rows affected, 1 warning (1 min 11.91 sec) records:1572864 duplicates:0 warnings:1 mysql> INSERT INTO User (Username,password) select Username,password from user;\ Query OK, 3145728 rows affected, 1 warning (2 min 29.04 sec) records:3145728 duplicates:0 warnings:1