简单记录mysql中inser into select语句测试_MySQL

mysql迅速制造大批数据，复制一个表中的（部分或全部）数据到另一个表中。

用法：INSERT INTO table_name1 (field1,field2) SELECT field1,field2 FROM table_name2;

前提条件

CREATE TABLE `user` (`id` int(10) NOT NULL AUTO_INCREMENT,`username` varchar(30) NOT NULL,`password` char(32) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `user_his` (`his_id` int(10) NOT NULL AUTO_INCREMENT,`id` int(10) NOT NULL,`username` varchar(30) NOT NULL,`password` char(32) NOT NULL,PRIMARY KEY (`his_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATETABLE`user`(

  `id`int(10)NOT NULLAUTO_INCREMENT,

  `username`varchar(30)NOT NULL,

  `password`char(32)NOT NULL,

  PRIMARY KEY(`id`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8;

CREATETABLE`user_his`(

  `his_id`int(10)NOT NULLAUTO_INCREMENT,

  `id`int(10)NOT NULL,

  `username`varchar(30)NOT NULL,

  `password`char(32)NOT NULL,

  PRIMARY KEY(`his_id`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8;

CREATETABLE`user`(

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)

mysql>descuser;

---------- ------------- ------ ----- --------- ----------------

|Field    |Type        |Null|Key|Default|Extra          |

---------- ------------- ------ ----- --------- ----------------

|id      |int(10)    |NO  |PRI|NULL    |auto_increment|

|username|varchar(30)|NO  |    |NULL    |                |

|password|char(32)    |NO  |    |NULL    |                |

---------- ------------- ------ ----- --------- ----------------

3rowsinset(0.00sec)

mysql>descuser_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    |                |

---------- ------------- ------ ----- --------- ----------------

4rowsinset(0.01sec)

  `id`int(10)NOT NULLAUTO_INCREMENT,

  `username`varchar(30)NOT NULL,

mysql> INSERT INTO `user`(`username`,`password`) VALUES ('hello','123456'),('twitter','123456'),('baidu','123456'),('google','123456'),('facebook','123456'),('linux','123456'),('cisco','123456'),('huawei','123456'),('lenovo','123456'),('apple','123456'),('oracle','123456'),('sun','123456');

mysql>INSERTINTO`user`(`username`,`password`)VALUES('hello','123456'),('twitter','123456'),('baidu','123456'),('google','123456'),('facebook','123456'),('linux','123456'),('cisco','123456'),('huawei','123456'),('lenovo','123456'),('apple','123456'),('oracle','123456'),('sun','123456');

  `password`char(32)NOT NULL,

  PRIMARY KEY(`id`)

mysql> INSERT INTO `user_his`(`id`,`username`,`password`) select `id`,`username`,`password` from `user`;

mysql>INSERTINTO`user_his`(`id`,`username`,`password`)select`id`,`username`,`password`from`user`;

)ENGINE=InnoDBDEFAULTCHARSET=utf8;CREATETABLE`user_his`(  `his_id`int(10)NOT NULLAUTO_INCREMENT,  `id`int(10)NOT NULL,  `username`varchar(30)NOT NULL,  `password`char(32)NOT NULL,  PRIMARY KEY(`his_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;

mysql>descuser; ---------- ------------- ------ ----- --------- ---------------- |Field    |Type        |Null|Key|Default|Extra          | ---------- ------------- ------ ----- --------- ---------------- |id      |int(10)    |NO  |PRI|NULL    |auto_increment||username|varchar(30)|NO  |    |NULL    |                ||password|char(32)    |NO  |    |NULL    |                | ---------- ------------- ------ ----- --------- ---------------- 3rowsinset(0.00sec)mysql>descuser_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    |                | ---------- ------------- ------ ----- --------- ---------------- 4rowsinset(0.01sec)

插入原始数据

mysql>INSERTINTO`user`(`username`,`password`)VALUES('hello','123456'),('twitter','123456'),('baidu','123456'),('google','123456'),('facebook','123456'),('linux','123456'),('cisco','123456'),('huawei','123456'),('lenovo','123456'),('apple','123456'),('oracle','123456'),('sun','123456');

复制数据到历史表

mysql>INSERTINTO`user_his`(`id`,`username`,`password`)select`id`,`username`,`password`from`user`;

附加mysql大批量复制数据，时间变化：

在自己的电脑上测试（Ubuntu14.04 LTS 64位 xampp），前3000条数据速度比较快，3000条以后执行时间成倍增加，2万5千条数据执行时间1分钟。314万数据，两分29秒

mysql> insert into user(username,password) select username,password from user;

Query OK, 12 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, 24 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, 48 rows 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, 96 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, 192 rows affected, 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