在 MySQL 数据库中将表 A 的某些数据插入到表 B 中,可以使用 SQL 语句 insert-into-select 轻松实现,这就是 MySQL 数据复制的一种实现,除了这个语句
外还有另外 select-into-from 这种写法,现在,我们就从这 2 个语句的异同开始说起,介绍一下 MySQL 常用的表结构与数据的复制操作。
语法 insert-into-from
语法格式:insert into 表名 select 查询字段 from 另一个表名;
实例1:全部字段插入
将表 tt_user 中 id 为偶数的数据插入到表 tt_user_copy 中
insert into tt_user_copy select * from tt_user where id%2=0;
实例2:部分字段插入
insert into tt_user_copy(username,password) select username,password from tt_user where id%2=0;
2. 语法 select-into-from
SELECT...INTO 用来将查询结果存储在变量或者写入文件中,其目的是将查询结果进行存储,存储方式可以有多种,不仅能 INTO 基本表,还能 INTO 外部文件、DUMP文件、变量。
虽然该语法能将查询结果转存到多种形式,但是大多数场景下并不适用,这种语法支持只是对 Load Data 的补充,有以下使用限制:
- INTO 表时,要求表名必须不存在,执行语句时会自动创建,SQLServer 支持 INTO 表, MySQL 不支持
- INTO 变量时,可以定义一个或多个变量,可以是用户自定义的变量,也可以是存储、函数中的变量
- INTO 文件时,文件会在服务器主机上创建,所以用户必须拥有FILE权限才能使用该语法
- INTO 文件时,要求文件必须不存在,这种要求是为了防止诸如/etc/passwd和数据库表等文件被损毁
- INTO 文件时,文件必须在服务器主机上,不能是外部其它主机,因为无法写出该文件相对于服务器主机的文件系统的路径
2.1 语法格式
SELECT
...
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
2.2 MySQL不支持 INTO 新表
特别注意,MySQL 数据库不支持 SELECT...INTO table_name 这种写法,会报错:Undeclared variable: tt_user_copy
[SQL]select *
into tt_user_copy
from tt_user where id%2=0;
[Err] 1327 - Undeclared variable: tt_user_copy
可以用另外一种写法替代:
create table tt_user_bb ( select * from tt_user where id%2=0);
2.3 与 insert-into-select 的区别
现在来总结一下 select-into-from 与 insert-into-select 的区别:
- MySQL 支持 insert-into-select 语法格式,但是不支持 select-into-from 语法格式
- select-into-from 要求目标表不存在,必须在插入时创建新表
- insert-into-select 要求目标表存在,如果不存在则无法执行,它不会自动创建新表
3. 数据复制的总结
在 MySQL 中将一个表的数据复制到另外一个表中,这是一个常见的数据复制需求,有多种方式可以实现:
- 通过 insert-into-select 语法实现,这是最常用的方式
- 通过 create-table-select 语法实现,这种语法要求必须创建一个新表,不能是已存在的表结构;
4. 表结构复制的总结
在 MySQL 中进行表结构的复制也是常见的需求,可以使用 create table 语法实现,实现方式有多种:
- 通过 show create table xxx 语句查看指定表的建表 SQL,然后修改该SQL并执行,完成新表的创建;
- 使用 create table like 语法快速创建表结构
- 使用 create table as 语法创建表结构并复制数据
## 语法一:复制表结构,这是专门的复制表结构的语法
create table tt_user_bd like tt_user;
## 语法二:复制表结构及数据,若添加特定条件可以实现只复制表结构
create table tt_user_bc as select * from tt_user where 1=2;
特别注意 create table like 和 create table as 两种写法差别巨大,区别如下:
- 语法 create table like 可以完全复制一个表的结构,包括复制表的备注、索引、主键外键、存储引擎等
- 语法 create table as 不能完全复制表结构,可以复制字段及属性,但是主键、索引、表备注、存储引擎等均无法复制
- 适用场景:create table like 适用于复制表结构, create table as 适用于备份表结构及数据,后续可能会用作数据恢复(使用 insert-into-select语法)
5. 小结
现在,总结一下 MySQL 数据复制、表结构复制的实现方式:
- 语法 insert-into-select 实现将一个表的指定数据快速插入到另外一个表中,实现指定数据的复制,常用于数据恢复场景;
- 语法 select-into-from 实现的是查询结果转存,转存方式很多(包括变量、新表、文件等),MySQL 不支持将其转存到新表;
- 语法 create-table-like 实现的是复制一个表的完整结构,包括复制表的备注、索引、主键、外键、存储引擎等;
- 语法 create-table-as 实现的是复制一个表的部分结构,包括字段及属性,不包括复制表的备注、索引、主键、外键、存储引擎等;
- 实现表的数据复制,常用 insert-into-select 语法,要求表结构必须存在;
- 实现表的结构复制,常用 create-table-like 语法,可以复制完整表结构;
再总结一个小场景:表的数据的备份与恢复
- 使用 create-table-as 快速备份表结构与数据到一个新表,实现数据的备份;
- 使用 insert-into-from 快速恢复数据到原表,实现数据的恢复;
附录:参考资料
- MySQL SELECT语法(二)SELECT...INTO语法: https://www.cnblogs.com/bigbigbigo/p/10958743.html
- MySQL 复制表结构 : https://www.cnblogs.com/chenmh/p/5644644.html
- select into from 和 insert into select : https://www.cnblogs.com/sunbingqiang/p/8663644.html
- insert into select(insert select)和select into from语句 : https://blog.csdn.net/kaixuansui/article/details/88861226
- 官方文档:13.2.9.1 SELECT ... INTO Statement:https://dev.mysql.com/doc/refman/5.7/en/select-into.html