所谓儒虫复制,就是针对一个表的数据,进行快速的复制并插入到所需要的表中,以期在短时间内具备“大量数据”,以用于测试或其他特殊场合,比如:
将 test 表中的 name age 数据查询出来并插入到 user 表中
insert into user (name, age) select name,age from test;
也可以查出自身数据插入到自身表中,这样可以快速产生大量的测试数据
insert into user (name, age) select name,age from user;
要求:
1、插入语句的字段个数,跟 select 语句的字段个数相等。
2、插入语句的字段类型,跟 select 语句的字段类型相同。
3、表之间的字段名可以不相同,只要满足以上两点就行了。
测试数据,将 test 表的数据迁移到 user 表中:
mysql> desc test;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(1) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
mysql> desc user;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
mysql> select * from test;
+----+-------+------+------+
| id | name | sex | age |
+----+-------+------+------+
| 1 | name1 | 男 | 5 |
| 2 | name2 | 女 | 10 |
| 3 | name3 | 男 | 15 |
| 4 | name4 | 男 | 20 |
+----+-------+------+------+
mysql> select * from user;
Empty set (0.00 sec)
mysql> insert into user (name, age) select name, age from test;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from user;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | name1 | 5 |
| 2 | name2 | 10 |
| 3 | name3 | 15 |
| 4 | name4 | 20 |
+----+-------+------+