MariaDB/MySQL 如何在数据库之间复制表结构

在项目开发过程中进行新功能测试时，时常会需要把生产环境中的数据库结构复制一份到测试数据库中，MariaDB/MySQL 提供了方便的 SQL 语句可以做到这一点。

1. 仅复制表结构不复制数据

假设在同一个数据库系统中，生产环境下的数据库名为 product，我们新创建的测试数据库名为 devp，现在需要把 product 数据库中名为 ppi 的表结构复制到 devp 中但不复制数据，SQL 语句如下

USE devp;
CREATE TABLE ppi LIKE product.ppi;

现在我们来查看 product 数据库中的 ppi 表和 devp 中的 ppi 表结构是否完全一致，首先查看 devp 的 ppi 生成语句：

USE devp;
SHOW CREATE TABLE ppi;

结果为

CREATE TABLE `ppi` (
  `protein1` varchar(20) NOT NULL,
  `protein2` varchar(20) NOT NULL,
  `neighborhood` int(11) NOT NULL DEFAULT '0',
  `neighborhood_transferred` int(11) NOT NULL DEFAULT '0',
  `fusion` int(11) NOT NULL DEFAULT '0',
  `cooccurence` int(11) NOT NULL DEFAULT '0',
  `homology` int(11) NOT NULL DEFAULT '0',
  `coexpression` int(11) NOT NULL DEFAULT '0',
  `coexpression_transferred` int(11) NOT NULL DEFAULT '0',
  `experiments` int(11) NOT NULL DEFAULT '0',
  `experiments_transferred` int(11) NOT NULL DEFAULT '0',
  `database` int(11) NOT NULL DEFAULT '0',
  `database_transferred` int(11) NOT NULL DEFAULT '0',
  `textmining` int(11) NOT NULL DEFAULT '0',
  `textmining_transferred` int(11) NOT NULL DEFAULT '0',
  `combined_score` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`protein1`,`protein2`),
  KEY `protein1` (`protein1`),
  KEY `protein2` (`protein2`),
  KEY `combined_score` (`combined_score`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

然后查看 devp 的 ppi 索引情况：

SHOW INDEX FROM ppi;

结果为

+--------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ppi |          0 | PRIMARY        |            1 | protein1       | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| ppi |          0 | PRIMARY        |            2 | protein2       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ppi |          1 | protein1       |            1 | protein1       | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| ppi |          1 | protein2       |            1 | protein2       | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| ppi |          1 | combined_score |            1 | combined_score | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

然后使用如下语句查看 product 中的 ppi 生成语句及索引情况

SHOW CREATE TABLE product.ppi;
SHOW INDEX FROM product.ppi;

会发现结果完全一致，说明表结构复制成功。

2. 同时复制表结构和数据

如果需要同时复制 product 中表 ppi 的结构和数据，可以使用如下 SQL 语句：

USE devp;
CREATE TABLE ppi as SELECT * FROM product.ppi;

也可以加上 where 语句作为限制条件复制其中的部分内容：

USE devp;
CREATE TABLE ppi SELECT * FROM product.ppi WHERE combined_score > 200;

3. 表结构已经复制完成，从对应的表中复制数据

如果按照

1. 仅复制表结构不复制数据

的操作完成表结构复制后，想再从对应表中复制全部或部分数据，可以使用如下语句

USE devp;
INSERT ppi SELECT * FROM product.ppi;