INSERT INTO SELECT statement test in MySQL

Source: Internet
Author: User
Tags one table

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.