首先这只是一个用法:
下面是演示。
首先我们准备2张表:
mysql> use z1;
Database changed
mysql> show tables;
+--------------+
| Tables_in_z1 |
+--------------+
| class |
| customer |
| exam |
| gender |
| goods |
| purchase |
| student |
+--------------+
7 rows in set (0.01 sec)
mysql> drop table student;
Query OK, 0 rows affected (0.03 sec)
mysql> create table student(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into student values(null,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> update student set name ='z1' where name ='张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into student values(null,'z2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(null,'z3');
Query OK, 1 row affected (0.00 sec)
mysql> create table student_copy(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.13 sec)
mysql> select * from student;
+----+------+
| id | name |
+----+------+
| 1 | z1 |
| 2 | z2 |
| 3 | z3 |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from student_copy;
Empty set (0.00 sec)
然后insert into搭配select使用:
mysql> insert into student_copy select * from student;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student_copy;
+----+------+
| id | name |
+----+------+
| 1 | z1 |
| 2 | z2 |
| 3 | z3 |
+----+------+
3 rows in set (0.00 sec)
可以看到,现在student_copy和student表一样了。
但是我们需要注意这个边界的复制操作是有限制的,必须是数据类型相同的两张表,而且插入的顺序要类型匹配。
mysql> insert into student_copy select name from student;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into student_copy select name,id from student;
ERROR 1366 (HY000): Incorrect integer value: 'z1' for column 'id' at row 1
mysql> insert into student_copy select id from student;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> drop table student_copy;
Query OK, 0 rows affected (0.02 sec)
然后我们还要注意,匹配最好还是一模一样的,不然可能会导致错误,像下面的示例,虽然插入成功了,但是是因为我们的实际数据并没有那么大,所以仍旧插入成功,但凡name>19,那就不能插入了。
mysql> drop table student_copy;
Query OK, 0 rows affected (0.02 sec)
mysql> create table student2(id int primary key auto_increment,name varchar(99));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into student2 select * from student;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table student3(id int primary key auto_increment,name varchar(19));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into student3 select * from student;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0