6.5 复制表（CREATE TABLE…LIKE语句）-MySQL从入门到精通(二)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(二)

### 6.5 复制表（CREATE TABLE…LIKE语句）

在数据库管理系统中，尤其是在使用MySQL这类关系型数据库时，表的复制是一项常见且重要的操作。复制表的需求可能源于多种场景，比如备份、数据迁移、测试环境准备或是为了优化查询性能而创建表结构的副本等。MySQL提供了多种方法来实现表的复制，其中`CREATE TABLE ... LIKE`语句是一种简单而直接的方式，用于创建与现有表相同结构的新表，但不包括任何数据。

#### 6.5.1 引言

在深入探讨`CREATE TABLE ... LIKE`语句之前，我们需要理解为什么需要复制表。复制表的需求可能源于多种业务和技术上的考虑。例如，你可能需要为某个表创建一个结构相同的备份表，以便在不干扰原始数据的情况下进行数据分析或测试；或者，你可能希望基于一个复杂的表结构快速创建一个新表，以便进行数据的重新组织或优化。

#### 6.5.2 `CREATE TABLE ... LIKE`语法

`CREATE TABLE ... LIKE`语句的基本语法非常直观，其基本形式如下：

```sql
CREATE TABLE new_table LIKE existing_table;
```

- `new_table`：是你要创建的新表的名称。
- `existing_table`：是已经存在的、其结构将被复制用于创建新表的表名。

这个语句会创建一个新表，其列名、列的数据类型、索引、默认值等都与`existing_table`相同，但不会复制任何数据行。如果`existing_table`有AUTO_INCREMENT属性（如自增主键），新表也会继承这一属性，但AUTO_INCREMENT的计数器不会被复制，新表的AUTO_INCREMENT值将从1开始（或者从指定的起始值开始，如果在创建表时明确指定了）。

#### 6.5.3 复制表并指定存储引擎

默认情况下，`CREATE TABLE ... LIKE`语句创建的新表将使用与原始表相同的存储引擎。但是，你可以通过指定`ENGINE`选项来为新表选择不同的存储引擎。这在需要优化存储性能或满足特定业务需求时非常有用。

```sql
CREATE TABLE new_table LIKE existing_table ENGINE=InnoDB;
```

上述命令会创建一个与`existing_table`结构相同但使用InnoDB存储引擎的新表。

#### 6.5.4 复制表并修改字符集和校对规则

除了存储引擎，你还可以为新表指定不同的字符集（character set）和校对规则（collation），以适应不同的语言或地区需求。

```sql
CREATE TABLE new_table LIKE existing_table CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

这个命令创建了一个新表，其结构与`existing_table`相同，但使用了`utf8mb4`字符集和`utf8mb4_unicode_ci`校对规则，这对于支持更广泛的Unicode字符（包括表情符号）特别有用。

#### 6.5.5 复制表并排除特定属性

虽然`CREATE TABLE ... LIKE`语句直接复制了表的结构，但它不提供直接排除特定属性（如索引、外键约束等）的选项。如果你需要这样的灵活性，你可能需要手动创建一个新表，并通过查询原始表的`INFORMATION_SCHEMA`或使用`SHOW CREATE TABLE`语句来获取原始表的定义，然后按需修改这个定义。

然而，对于索引，你可以在复制表后使用`ALTER TABLE`语句来添加或删除索引。

#### 6.5.6 复制表并包含数据

需要注意的是，`CREATE TABLE ... LIKE`语句仅复制表结构，不复制数据。如果你需要同时复制数据，可以结合使用`INSERT INTO ... SELECT`语句。

```sql
CREATE TABLE new_table LIKE existing_table;
INSERT INTO new_table SELECT * FROM existing_table;
```

这两行命令首先复制了`existing_table`的结构到`new_table`，然后将`existing_table`中的所有数据插入到`new_table`中。

#### 6.5.7 复制表的高级应用

`CREATE TABLE ... LIKE`语句的灵活性和易用性使其在许多高级应用场景中也非常有用。例如，你可以使用它来：

- **快速创建测试环境**：通过复制生产数据库中的关键表，可以快速搭建一个包含真实数据结构的测试环境，便于开发和测试。
- **数据分区**：在需要按特定规则将大表拆分为多个较小表时，可以先复制表结构，然后根据需要修改表名或添加分区键，最后将数据迁移到这些新表中。
- **数据迁移**：在将数据迁移到另一个数据库系统之前，可以先使用`CREATE TABLE ... LIKE`语句在新系统中创建结构相同的表，以便后续的数据导入。

#### 6.5.8 注意事项

- 在使用`CREATE TABLE ... LIKE`语句时，请确保原始表（`existing_table`）存在且你有足够的权限来创建新表。
- 复制表结构时，不会复制触发器、存储过程或外键约束等数据库对象。如果需要这些对象，请单独创建。
- 如果原始表使用了特定的分区方案，新表将不会继承这些分区设置。你需要在创建新表后手动配置分区。
- 当使用`INSERT INTO ... SELECT`语句复制数据时，请注意可能的数据类型不匹配问题，以及可能引发的性能问题（特别是在处理大型表时）。

#### 6.5.9 结论

`CREATE TABLE ... LIKE`语句是MySQL中一个非常实用的功能，它允许开发者快速而准确地复制表结构，为数据库管理、数据迁移、测试和开发等任务提供了极大的便利。通过理解并掌握这个语句的用法和注意事项，你可以更有效地管理和优化你的MySQL数据库。

该分类下的相关小册推荐：

SQL零基础到熟练应用(增删改查)

MySQL 实战 45 讲

MySQL从入门到精通(四)

MySQL从入门到精通(一)

MySQL8.0入门与实践

细说MySQL(零基础到高级应用)

MySQL从入门到精通(三)

MySQL必会核心问题

MySQL从入门到精通(五)