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
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2014-12-12 11:16:40 |
赞(
操作
