mysql中insert into select語句測試

mysql中insert into select語句是把指定表數據復制到我們新表中去，這個就是在insert into 的基礎加了一個select功能了，下面來看一些測試的例子。

mysql迅速制造大批數據，復制一個表中的（部分或全部）數據到另一個表中。

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

前提條件

 代碼如下 復制代碼

MySQL

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;

查看一下結構，更直觀

 代碼如下 復制代碼

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

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

mysql> INSERT INTO `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