mysql中insert into select语句测试

mysql中insert into select语句是把指定表数据复制到我们新表中去,这个就是在insert into 的基础加了一个select功能了,下面来看一些测试的例子.

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

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

前提条件,代码如下:

1. MySQL
2. CREATETABLE`user`(
3. `id`int(10)NOTNULLAUTO_INCREMENT,
4. `username`varchar(30)NOTNULL,
5. `password`char(32)NOTNULL,
6. PRIMARYKEY(`id`)
7. )ENGINE=InnoDBDEFAULTCHARSET=utf8;
9. CREATETABLE`user_his`(
10. `his_id`int(10)NOTNULLAUTO_INCREMENT,
11. `id`int(10)NOTNULL,
12. `username`varchar(30)NOTNULL,
13. `password`char(32)NOTNULL,
14. PRIMARYKEY(`his_id`)
15. )ENGINE=InnoDBDEFAULTCHARSET=utf8;

查看一下结构,更直观,代码如下:

1. mysql>descuser;
2. +----------+-------------+------+-----+---------+----------------+
3. |Field|Type|Null|Key|Default|Extra|
4. +----------+-------------+------+-----+---------+----------------+
5. |id|int(10)|NO|PRI|NULL|auto_increment|
6. |username|varchar(30)|NO||NULL||
7. |password|char(32)|NO||NULL||
8. +----------+-------------+------+-----+---------+----------------+
9. 3rowsinset(0.00sec)
11. mysql>descuser_his;
12. +----------+-------------+------+-----+---------+----------------+
13. |Field|Type|Null|Key|Default|Extra|
14. +----------+-------------+------+-----+---------+----------------+
15. |his_id|int(10)|NO|PRI|NULL|auto_increment|
16. |id|int(10)|NO||NULL||
17. |username|varchar(30)|NO||NULL||
18. |password|char(32)|NO||NULL||
19. +----------+-------------+------+-----+---------+----------------+
20. 4rowsinset(0.01sec)

插入原始数据,MySQL

1. 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

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

附加mysql大批量复制数据,时间变化:在自己的电脑上测试（Ubuntu14.04 LTS 64位 + xampp）,前3000条数据速度比较快,3000条以后执行时间成倍增加,2万5千条数据执行时间1分钟,314万数据,两分29秒,代码如下:

1. mysql>insertintouser(username,password)selectusername,passwordfromuser;
2. QueryOK,12rowsaffected,1warning(0.07sec)
3. Records:12Duplicates:0Warnings:1
4. mysql>insertintouser(username,password)selectusername,passwordfromuser;
5. QueryOK,24rowsaffected,1warning(0.08sec)
6. Records:24Duplicates:0Warnings:1
7. mysql>insertintouser(username,password)selectusername,passwordfromuser;
8. QueryOK,48rowsaffected,1warning(0.11sec)
9. Records:48Duplicates:0Warnings:1
10. mysql>insertintouser(username,password)selectusername,passwordfromuser;
11. QueryOK,96rowsaffected,1warning(0.10sec)
12. Records:96Duplicates:0Warnings:1
13. mysql>insertintouser(username,password)selectusername,passwordfromuser;
14. QueryOK,192rowsaffected,1warning(0.10sec)
15. Records:192Duplicates:0Warnings:1
16. mysql>insertintouser(username,password)selectusername,passwordfromuser;
17. QueryOK,384rowsaffected,1warning(0.10sec)
18. Records:384Duplicates:0Warnings:1
19. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
20. QueryOK,768rowsaffected,1warning(0.13sec)
21. Records:768Duplicates:0Warnings:1
22. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
23. QueryOK,1536rowsaffected,1warning(0.15sec)
24. Records:1536Duplicates:0Warnings:1
25. mysql>
26. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
27. QueryOK,3072rowsaffected,1warning(0.17sec)
28. Records:3072Duplicates:0Warnings:1
29. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
30. QueryOK,6144rowsaffected,1warning(0.28sec)
31. Records:6144Duplicates:0Warnings:1
32. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
33. QueryOK,12288rowsaffected,1warning(0.42sec)
34. Records:12288Duplicates:0Warnings:1
35. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
36. QueryOK,24576rowsaffected,1warning(0.99sec)
37. Records:24576Duplicates:0Warnings:1
38. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
39. QueryOK,49152rowsaffected,1warning(1.98sec)
40. Records:49152Duplicates:0Warnings:1
41. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
42. QueryOK,98304rowsaffected,1warning(4.04sec)
43. Records:98304Duplicates:0Warnings:1
44. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
45. QueryOK,196608rowsaffected,1warning(8.89sec)
46. Records:196608Duplicates:0Warnings:1
47. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
48. QueryOK,393216rowsaffected,1warning(17.14sec)
49. Records:393216Duplicates:0Warnings:1
50. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
51. QueryOK,786432rowsaffected,1warning(38.07sec)
52. Records:786432Duplicates:0Warnings:1
53. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼
54. QueryOK,1572864rowsaffected,1warning(1min11.91sec)
55. Records:1572864Duplicates:0Warnings:1
56. mysql>insertintouser(username,password)selectusername,passwordfromuser;＼//开源代码phpfensi.com
57. QueryOK,3145728rowsaffected,1warning(2min29.04sec)
58. Records:3145728Duplicates:0Warnings:1