复制表时请禁用 - CREATE AS

会丢失 PRIMARY KEY、UNIQUE KEY、AUTO_INCREMENT

CREATE AS 语句在复制表结构 AND 表数据时，到底会丢失什么

mysql> create table uniq(id int primary key not null auto_increment, name varchar(12) not null unique, age int not null default 100, salary int default 10000);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table uniq;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| uniq  | CREATE TABLE `uniq` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) NOT NULL,
  `age` int(11) NOT NULL DEFAULT '100',
  `salary` int(11) DEFAULT '10000',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into uniq(name) values("a");
Query OK, 1 row affected (0.00 sec)

mysql> insert into uniq(name) values("b");
Query OK, 1 row affected (0.00 sec)

mysql> insert into uniq(name) values("c");
Query OK, 1 row affected (0.01 sec)

mysql> insert into uniq(name) values("d");
Query OK, 1 row affected (0.01 sec)

mysql> select * from uniq;
+----+------+-----+--------+
| id | name | age | salary |
+----+------+-----+--------+
|  1 | a    | 100 |  10000 |
|  2 | b    | 100 |  10000 |
|  3 | c    | 100 |  10000 |
|  4 | d    | 100 |  10000 |
+----+------+-----+--------+
4 rows in set (0.00 sec)

当使用 CREATE AS 语句后产生的效果

# 创建一个表，此处 as 其实可以省略
mysql> create table uniq_error as select * from uniq;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 查看表结构，ID 字段主键丢失、AUTO_INCREMENT属性丢失，NAME 字段唯一键丢失
mysql> show create table uniq_error;
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                       |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| uniq_error | CREATE TABLE `uniq_error` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(12) NOT NULL,
  `age` int(11) NOT NULL DEFAULT '100',
  `salary` int(11) DEFAULT '10000'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into uniq_error(name) values("e");
Query OK, 1 row affected (0.01 sec)

mysql> select * from uniq_error;
+----+------+-----+--------+
| id | name | age | salary |
+----+------+-----+--------+
|  1 | a    | 100 |  10000 |
|  2 | b    | 100 |  10000 |
|  3 | c    | 100 |  10000 |
|  4 | d    | 100 |  10000 |
|  0 | e    | 100 |  10000 |
+----+------+-----+--------+
5 rows in set (0.00 sec)

那么怎么样复制表才是规范的呢？

# 使用 like 先复制表结构
mysql> create table uniq_success like uniq;
Query OK, 0 rows affected (0.02 sec)

# 检验表结构是否正确
mysql> show create table uniq_success;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| uniq_success | CREATE TABLE `uniq_success` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(12) NOT NULL,
  `age` int(11) NOT NULL DEFAULT '100',
  `salary` int(11) DEFAULT '10000',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 再复制表数据
mysql> insert into uniq_success select * from uniq;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 插入数据并查询检验
mysql> insert into uniq_success(name) values("e");
Query OK, 1 row affected (0.00 sec)

mysql> select * from uniq_success;
+----+------+-----+--------+
| id | name | age | salary |
+----+------+-----+--------+
|  1 | a    | 100 |  10000 |
|  2 | b    | 100 |  10000 |
|  3 | c    | 100 |  10000 |
|  4 | d    | 100 |  10000 |
|  5 | e    | 100 |  10000 |
+----+------+-----+--------+
5 rows in set (0.00 sec)

随记：如何更改表名？更改表名语句也可以将表移动到其他已存在的数据库

# 仅更改表名
mysql> rename table old_name to new_name;
Query OK, 0 rows affected (0.01 sec)

# 更改表名并将其移动到其他数据库
mysql> create database itzone_bak;
Query OK, 1 row affected (0.00 sec)

mysql> rename table itzone.new_name to itzone_bak.latest_name;
Query OK, 0 rows affected (0.00 sec)

# 更改后查询当前库没有此表
mysql> show tables;
+------------------+
| Tables_in_itzone |
+------------------+
| itzone           |
| test             |
+------------------+
2 rows in set (0.00 sec)

# 查询 itzone_bak 数据库中有此表
mysql> show tables from itzone_bak;
+----------------------+
| Tables_in_itzone_bak |
+----------------------+
| latest_name          |
+----------------------+
1 row in set (0.00 sec)

随记：如何更改已创建数据库名称，使用如下脚本（实质上就是复制库）

[root@ethanz ~]# vim rename_database.sh
#!/bin/bash
mysqlconn=" mysql -uroot -proot  -hlocalhost -P3306 "
old_db="old_db_name"
new_db="new_db_name"
$mysqlconn -e "CREATE DATABASE $new_db"
params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '$old_db'")
for name in $params; do
$mysqlconn -e "RENAME TABLE $old_db.$name to $new_db.$name";
done;
$mysqlconn -e "DROP DATABASE $old_db"