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

mysql迅速制造大批数据,复制一个表中的(部分或全部)数据到另一个表中。

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

前提条件

查看一下结构,更直观

插入原始数据

复制数据到历史表

附加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