MySQL-复制表的两种常见方式

在 MySQL 中，创建表的方式有很多种，其中 CREATE TABLE ... AS SELECT 和 CREATE TABLE ... LIKE 是两种常见的方法。这两种方式虽然都能用于创建新表，但它们的行为和结果有显著的区别。

以下是两种方式的详细对比：

方式 1：CREATE TABLE B AS SELECT * FROM A;

特点

• 数据复制：新表 B 不仅会复制表 A 的结构，还会复制表 A 中的数据。
• 列定义：
  • 新表 B 的列定义会基于 SELECT 查询的结果，而不是直接复制表 A 的列定义。
  • 某些属性（如主键、索引、外键、默认值等）不会被保留。
• 约束丢失：
  • 表 A 的主键、唯一键、外键、索引等约束不会被复制到表 B。
  • 只会保留列名和数据类型。
• 灵活性：
  • 可以通过 SELECT 查询对表 A 进行过滤或转换，从而创建一个不同的表结构或数据集。

示例

假设表 A 的定义如下：

CREATE TABLE A (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX (name)
);

执行以下语句：

CREATE TABLE B AS SELECT * FROM A;

结果：

• 表 B 包含表 A 的所有数据。
• 表 B 的列定义只包含 id、name 和 age，但没有主键、索引或其他约束。

方式 2：CREATE TABLE B LIKE A;

特点

• 结构复制：新表 B 会完全复制表 A 的结构，包括列定义、主键、索引、外键等。
• 数据不复制：表 B 不会包含表 A 中的数据，只会复制表的结构。
• 约束保留：
  • 表 A 的主键、唯一键、外键、索引等约束会被完整地复制到表 B。
• 无数据灵活性：
  • 无法通过 LIKE 语句对表结构进行修改或过滤。

示例

假设表 A 的定义如下：

CREATE TABLE A (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX (name)
);

执行以下语句：

CREATE TABLE B LIKE A;

结果：

• 表 B 的结构与表 A 完全相同，包括主键、索引等。
• 表 B 不包含任何数据。

两者的区别总结

适用场景

CREATE TABLE B AS SELECT * FROM A;

• 当需要复制表的数据，并且可以接受丢失约束（如主键、索引等）时。
• 当需要通过 SELECT 对数据进行过滤或转换时。
• 示例：

  CREATE TABLE B AS SELECT * FROM A WHERE age > 30;
  

CREATE TABLE B LIKE A;

• 当只需要复制表的结构，而不需要数据时。
• 当需要保留表的所有约束（如主键、索引等）时。
• 示例：

  CREATE TABLE B LIKE A;
  

注意事项

1. 性能：

   • CREATE TABLE ... AS SELECT 会涉及数据的复制，因此对于大表可能会消耗较多的时间和资源。
   • CREATE TABLE ... LIKE 只复制结构，性能更高。

2. 后续操作：

   • 如果使用 CREATE TABLE ... AS SELECT，可能需要手动添加主键、索引等约束。
   • 如果使用 CREATE TABLE ... LIKE，后续可以通过 INSERT INTO B SELECT * FROM A; 来复制数据。

总结

• 使用 CREATE TABLE ... AS SELECT 适合需要复制数据并允许结构调整的场景。
• 使用 CREATE TABLE ... LIKE 适合需要完整复制表结构但不需要数据的场景。