每日一记-数据库表的复制：select into、create as、insert into

背景

假设有一个数据库，如果想要备份数据库内的表或者定期对一张表进行归档记录，比如按照月份或者星期归档为原表名_月数/周数，然后以此为规则方便在程序内的调用进行历史记录的增删查改。最笨也是我曾经用过的一种方法是每个周期去将表导出成sql，修改sql内的表名，然后运行sql文件插入，再将原表清空；现在发现sql本身就带有这样的语法，按照个人理解的便利性，三种方法排序为：
⑴SELECT INTO、⑵CREATE AS、⑶INSERT INTO

SELECT INTO、CREATE AS、INSERT INTO

之所以认为这三个便利性依次减少是因为前俩种方法只要写好执行的脚本，那么数据库会自动完成建表并插入数据的操作，而第三种方法需要手动建好表及表内的字段。

SELECT INTO

如果需要备份一个整表的数据，直接用下列语句即可完成操作，数据库会自动创建一个tablename_backup的表，然后将tablename表内的数据插入其中：

SELECT *
INTO tablename_backup
FROM tablename

在备份时候，可能表内有一些字段的数据在以后使用并不被需要，那么在备份时候可以通过指定列名来备份指定的数据字段：

SELECT column1,column2,...columnN
INTO tablename_backup
FROM tablename

而select into我举得最强大的一点功能在于其可以跨数据库进行操作，只需要加入一个IN字句就可以实现：

SELECT *
INTO tablename_backup IN 'otherdb.mdb'
FROM otherdb_tablename

CREATE AS

create as的使用很简单，通过搭配select字句进行使用。

CREATE TABLE test_backup8 AS (
SELECT * FROM test)

执行之后，会自动新建表test_backup8，然后将select字句查询到的test表内的数据插入进去。create as语句括号中的select字句不会受create as的影响，可以使用的所有的条件控制字句，所以这种备份表方式比较灵活强大，

但是有一点问题的地方在于：在网上看到有博主说使用CREATE AS字句创建的表中复制字段会丢失原表中的默认值，比如原表内有一个varchar字段默认值a，复制到新表之后会丢失。但是我使用mysql5.1.48，在实验过程中发现默认值还在，此处存有疑惑。

INSERT INTO

使用INSERT INTO备份表不同于插入数据的INSERT，需要先建立好新表，并且新表内必须要有备份的表的指定字段或者全部字段。

Insert INTO test(col1, col2, col3...colN) 
SELECT col1, col2,  col3...colN FROM test

需要注意字段值的问题，如果有主键存在且不为空或者其他不为空的字段存在，则在(col1, col2, col3…colN) 内必须有主键和不能为空的字段，否则会出错。

注意事项

1. mysql不支持SELECT INTO语句，会报未声明的变量错误，如下：
   
   可以使用CREATE AS和INSERT INTO。

2. 在5.6及以上的mysql中使用CREATE AS的时候可能会报如下的错误：

   ERROR 1786 (HY000): CREATE TABLE ... SELECTis forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1
   这是因为在5.6及以上的版本内，开启了enforce_gtid_consistency=true功能导致的，MySQL官方解释说当启用enforce_gtid_consistency功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。《本段转载于网络》