【MySQL】insert into 和select 搭配使用进行表间复制_insert into和select连用

首先这只是一个用法：
下面是演示。
首先我们准备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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

然后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)
1
2
3
4
5
6
7
8
9
10
11
12
13

可以看到，现在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)
1
2
3
4
5
6
7
8

然后我们还要注意，匹配最好还是一模一样的，不然可能会导致错误，像下面的示例，虽然插入成功了，但是是因为我们的实际数据并没有那么大，所以仍旧插入成功，但凡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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16