insert exec 语句不能嵌套_「MySQL」从 insert-into-select 说说 mysql 的数据备份与恢复

MySQL

在 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 的补充，有以下使用限制：

1. INTO 表时，要求表名必须不存在，执行语句时会自动创建，SQLServer 支持 INTO 表， MySQL 不支持
2. INTO 变量时，可以定义一个或多个变量，可以是用户自定义的变量，也可以是存储、函数中的变量
3. INTO 文件时，文件会在服务器主机上创建，所以用户必须拥有FILE权限才能使用该语法
4. INTO 文件时，要求文件必须不存在，这种要求是为了防止诸如/etc/passwd和数据库表等文件被损毁
5. 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_copyfrom 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 的区别：

1. MySQL 支持 insert-into-select 语法格式，但是不支持 select-into-from 语法格式
2. select-into-from 要求目标表不存在，必须在插入时创建新表
3. insert-into-select 要求目标表存在，如果不存在则无法执行，它不会自动创建新表

3. 数据复制的总结

在 MySQL 中将一个表的数据复制到另外一个表中，这是一个常见的数据复制需求，有多种方式可以实现：

1. 通过 insert-into-select 语法实现，这是最常用的方式
2. 通过 create-table-select 语法实现，这种语法要求必须创建一个新表，不能是已存在的表结构；

4. 表结构复制的总结

在 MySQL 中进行表结构的复制也是常见的需求，可以使用 create table 语法实现，实现方式有多种：

1. 通过 show create table xxx 语句查看指定表的建表 SQL，然后修改该SQL并执行，完成新表的创建；
2. 使用 create table like 语法快速创建表结构
3. 使用 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 两种写法差别巨大，区别如下：

1. 语法 create table like 可以完全复制一个表的结构，包括复制表的备注、索引、主键外键、存储引擎等
2. 语法 create table as 不能完全复制表结构，可以复制字段及属性，但是主键、索引、表备注、存储引擎等均无法复制
3. 适用场景：create table like 适用于复制表结构， create table as 适用于备份表结构及数据，后续可能会用作数据恢复(使用 insert-into-select语法)

5. 小结

现在，总结一下 MySQL 数据复制、表结构复制的实现方式：

1. 语法 insert-into-select 实现将一个表的指定数据快速插入到另外一个表中，实现指定数据的复制，常用于数据恢复场景；
2. 语法 select-into-from 实现的是查询结果转存，转存方式很多(包括变量、新表、文件等)，MySQL 不支持将其转存到新表；
3. 语法 create-table-like 实现的是复制一个表的完整结构，包括复制表的备注、索引、主键、外键、存储引擎等；
4. 语法 create-table-as 实现的是复制一个表的部分结构，包括字段及属性，不包括复制表的备注、索引、主键、外键、存储引擎等；
5. 实现表的数据复制，常用 insert-into-select 语法，要求表结构必须存在；
6. 实现表的结构复制，常用 create-table-like 语法，可以复制完整表结构；

再总结一个小场景：表的数据的备份与恢复

1. 使用 create-table-as 快速备份表结构与数据到一个新表，实现数据的备份；
2. 使用 insert-into-from 快速恢复数据到原表，实现数据的恢复；

附录：参考资料

1. MySQL SELECT语法(二)SELECT...INTO语法： https://www.cnblogs.com/bigbigbigo/p/10958743.html
2. MySQL 复制表结构 ： https://www.cnblogs.com/chenmh/p/5644644.html
3. select into from 和 insert into select ： https://www.cnblogs.com/sunbingqiang/p/8663644.html
4. insert into select(insert select)和select into from语句 ： https://blog.csdn.net/kaixuansui/article/details/88861226
5. 官方文档：13.2.9.1 SELECT ... INTO Statement：https://dev.mysql.com/doc/refman/5.7/en/select-into.html

头条搜索 MySQL