在项目开发过程中进行新功能测试时,时常会需要把生产环境中的数据库结构复制一份到测试数据库中,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. 表结构已经复制完成,从对应的表中复制数据
如果按照
- 仅复制表结构不复制数据
的操作完成表结构复制后,想再从对应表中复制全部或部分数据,可以使用如下语句
USE devp;
INSERT ppi SELECT * FROM product.ppi;