如何实现MySQL中表的‘0’拷贝？

MySQL 拷贝表的几种方式

在数据库管理中，经常需要将一个表的数据或结构复制到另一个表中，MySQL 提供了多种方法来实现这一目的，本文将详细介绍这些方法并解释其具体操作步骤。

一、复制表结构及数据到新表

1. 使用CREATE TABLE ... SELECT

这种方法会将旧表的所有内容和结构都复制到新表中，但新表中不会保留旧表的主键和自增属性。

CREATE TABLE 新表 SELECT * FROM 旧表;

2. 示例

假设有一个名为employees 的表，我们希望将其复制到一个新表employees_copy：

CREATE TABLE employees_copy SELECT * FROM employees;

执行上述语句后，employees_copy 将包含与employees 完全相同的数据和列结构，但不包含主键和自增属性。

二、只复制表结构到新表

1. 使用CREATE TABLE ... LIKE

这种方法只会复制表的结构，不会复制数据。

CREATE TABLE 新表 LIKE 旧表;

2. 示例

CREATE TABLE employees_structure LIKE employees;

执行上述语句后，employees_structure 将包含与employees 相同的列结构，但没有任何数据。

3. 使用CREATE TABLE ... SELECT ... WHERE 1=2

这种方法也可以达到只复制表结构的目的。

CREATE TABLE 新表 AS SELECT * FROM 旧表 WHERE 1=2;

4. 示例

CREATE TABLE employees_structure AS SELECT * FROM employees WHERE 1=2;

执行上述语句后，employees_structure 将包含与employees 相同的列结构，但没有任何数据。

三、复制旧表的数据到新表（假设两个表结构一样）

1. 使用INSERT INTO ... SELECT

如果新旧表的结构相同，可以使用这种方法来复制数据。

INSERT INTO 新表 SELECT * FROM 旧表;

2. 示例

假设有两个结构相同的表employees 和new_employees，我们希望将employees 的数据复制到new_employees：

INSERT INTO new_employees SELECT * FROM employees;

执行上述语句后，new_employees 将包含与employees 相同的数据。

四、复制旧表的数据到新表（假设两个表结构不一样）

1. 使用INSERT INTO ... (字段1, 字段2) SELECT ...

如果新旧表的结构不同，可以指定要复制的字段。

INSERT INTO 新表(字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 旧表;

2. 示例

假设employees 表有id,name,position 三个字段，而employees_summary 表只有id,name 两个字段，我们希望将employees 的id 和name 复制到employees_summary：

INSERT INTO employees_summary(id, name) SELECT id, name FROM employees;

执行上述语句后，employees_summary 将包含与employees 相对应的id 和name。

五、复制部分字段和部分数据

1. 使用CREATE TABLE ... AS (SELECT ...)

可以选择性地复制部分字段和满足特定条件的数据。

CREATE TABLE 新表 AS (SELECT 字段1, 字段2 FROM 旧表 WHERE 条件);

2. 示例

假设我们希望复制employees 表中name 和position 字段，并且只复制职位为 ‘Manager’ 的员工：

CREATE TABLE managers AS (SELECT name, position FROM employees WHERE position = 'Manager');

执行上述语句后，managers 将包含与employees 中所有职位为 ‘Manager’ 的员工的name 和position。

六、不同数据库间的表复制

1. 使用CREATE TABLE ... LIKE ... 和INSERT INTO ... SELECT

如果需要在不同的数据库间复制表结构和数据，可以先复制结构，再复制数据。

CREATE TABLE db1.新表 LIKE db2.旧表;
INSERT INTO db1.新表 SELECT * FROM db2.旧表;

2. 示例

假设我们希望将db1.employees 复制到db2.employees_copy：

CREATE TABLE db2.employees_copy LIKE db1.employees;
INSERT INTO db2.employees_copy SELECT * FROM db1.employees;

执行上述语句后，db2.employees_copy 将包含与db1.employees 相同的结构和数据。

七、自我复制数据（蠕虫复制）

有时为了测试 SQL 语句的效率，需要创建大量数据，可以通过自我复制数据来实现，创建一个临时表，处理后复制回原表。

CREATE TEMPORARY TABLE my_tmp LIKE my_tab02;
INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02; -去除重复记录
DELETE FROM my_tab02; -清空原表记录
INSERT INTO my_tab02 SELECT * FROM my_tmp; -复制回原表
DROP TEMPORARY TABLE IF EXISTS my_tmp; -删除临时表

八、FAQs

1. Q: 如何只复制表结构而不复制数据？

A: 可以使用以下两种方法之一：

CREATE TABLE 新表 LIKE 旧表;
-OR
CREATE TABLE 新表 AS SELECT * FROM 旧表 WHERE 1=2;

2. Q: 如果两个表的结构不同，如何复制部分数据？

A: 可以指定要复制的字段：

INSERT INTO 新表(字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 旧表;

九、小编有话说

在实际操作中，可以根据具体需求选择合适的方法来复制表结构和数据，无论是简单的全量复制还是复杂的部分复制，MySQL 都提供了灵活的解决方案，希望本文能为大家在日常工作中提供帮助。