MySQL高级特性之：`MySQL`的`CREATE TABLE … LIKE …`：其在表结构复制中的应用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 高级特性之 `CREATE TABLE ... LIKE ...`：表结构复制深度剖析

各位同学，大家好！今天我们来深入探讨 MySQL 中一个非常实用的高级特性：CREATE TABLE ... LIKE ...。这个特性主要用于快速复制现有表的结构，极大地简化了数据库开发和管理工作。我们将从语法、应用场景、注意事项以及一些高级用法等方面进行详细讲解，并结合实际代码示例，力求让大家对这个特性有一个全面而深入的理解。

一、`CREATE TABLE ... LIKE ...` 语法详解

CREATE TABLE ... LIKE ... 语句的基本语法如下：

CREATE TABLE new_table_name LIKE original_table_name;

这条语句的功能非常简单明了：创建一个名为 new_table_name 的新表，其表结构完全复制自 original_table_name 表。这里的“表结构”包括：

列定义: 列名、数据类型、长度、是否允许 NULL 等属性。
索引: 包括 PRIMARY KEY、UNIQUE KEY、INDEX 等。
约束: 包括 NOT NULL 约束、DEFAULT 约束、FOREIGN KEY 约束（MySQL 8.0 之前的版本不复制外键约束，8.0 及以后版本可以通过设置 default_table_format 为 innodb 实现外键约束的复制，具体后文会提到）。
注释: 表和列的注释。
存储引擎: 例如 InnoDB、MyISAM 等。

需要注意的是，CREATE TABLE ... LIKE ... 语句不复制数据。新创建的表是空的，没有任何数据。

更完整的语法形式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] new_table_name
    LIKE original_table_name;

TEMPORARY: 指定创建临时表。临时表仅在当前会话中有效，会话结束后自动删除。
IF NOT EXISTS: 如果表已经存在，则不会创建，避免抛出错误。

二、应用场景分析

CREATE TABLE ... LIKE ... 语句在实际应用中非常广泛，以下是一些常见的场景：

创建备份表: 在执行重要的数据操作之前，为了防止数据丢失，可以先使用 CREATE TABLE ... LIKE ... 创建一个备份表，然后再将原始表的数据复制到备份表中。

-- 创建备份表
CREATE TABLE users_backup LIKE users;

-- 将数据复制到备份表
INSERT INTO users_backup SELECT * FROM users;

-- 执行可能存在风险的操作，例如删除数据
DELETE FROM users WHERE registration_date < '2023-01-01';

-- 如果操作失败，可以从备份表恢复数据
-- INSERT INTO users SELECT * FROM users_backup;

创建测试表: 在开发和测试阶段，经常需要创建与生产环境类似的表结构。使用 CREATE TABLE ... LIKE ... 可以快速创建测试表，避免手动定义表结构的繁琐工作。

-- 创建测试表
CREATE TABLE products_test LIKE products;

-- 在测试表中插入一些测试数据
INSERT INTO products_test (product_name, price) VALUES ('Test Product 1', 10.00), ('Test Product 2', 20.00);

-- 进行测试操作，例如查询、更新、删除等
SELECT * FROM products_test WHERE price > 15.00;

创建中间表: 在进行复杂的数据处理时，可能需要创建一些中间表来存储临时结果。CREATE TABLE ... LIKE ... 可以快速创建与现有表结构相似的中间表。

-- 创建中间表
CREATE TABLE orders_summary LIKE orders;

-- 将订单数据汇总到中间表
INSERT INTO orders_summary (customer_id, total_amount)
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

-- 使用中间表进行后续的数据分析
SELECT customer_id, total_amount
FROM orders_summary
WHERE total_amount > 1000.00;

数据库迁移: 在不同数据库之间进行迁移时，可以先在目标数据库中创建与源数据库表结构相同的表，然后再将数据导入到目标数据库中。

-- 在目标数据库中创建表
CREATE TABLE new_database.users LIKE old_database.users;

-- 将数据从源数据库导入到目标数据库
-- (需要使用相应的数据库迁移工具，例如 mysqldump)

表结构变更的预演: 在对线上表结构进行重大变更之前，可以先使用 CREATE TABLE ... LIKE ... 创建一个影子表，然后在新表上进行变更操作，验证变更方案的正确性和性能影响。

-- 创建影子表
CREATE TABLE users_shadow LIKE users;

-- 在影子表上进行表结构变更
ALTER TABLE users_shadow ADD COLUMN email VARCHAR(255);

-- 验证变更方案的正确性和性能影响
-- (例如，执行一些模拟查询)

三、注意事项

在使用 CREATE TABLE ... LIKE ... 语句时，需要注意以下几点：

数据不复制: CREATE TABLE ... LIKE ... 语句只复制表结构，不复制数据。新创建的表是空的。
存储引擎: 新表的存储引擎默认与源表相同。如果需要指定不同的存储引擎，可以在 CREATE TABLE 语句之后使用 ALTER TABLE 语句修改存储引擎。
```
CREATE TABLE new_table LIKE original_table;
ALTER TABLE new_table ENGINE=MyISAM;
```
自增列: 如果源表包含自增列，新表的自增列也会被复制，但自增的起始值不会被复制。新表的自增值会从 1 开始。如果需要保持自增值的连续性，需要手动设置新表的自增起始值。
```
-- 获取源表的自增最大值
SELECT MAX(id) FROM original_table;

-- 设置新表的自增起始值
ALTER TABLE new_table AUTO_INCREMENT = 源表自增最大值 + 1;
```
外键约束 (MySQL 8.0 之前): 在 MySQL 8.0 之前的版本中，CREATE TABLE ... LIKE ... 语句不会复制外键约束。如果需要复制外键约束，需要手动创建外键约束。从MySQL 8.0开始，默认复制外键约束，但是需要满足一定的条件。
权限: 执行 CREATE TABLE ... LIKE ... 语句需要具有 CREATE 权限。
视图和触发器: CREATE TABLE ... LIKE ... 不能用于复制视图和触发器，它只能复制表。
分区表: 可以复制分区表，新表也会继承源表的分区定义。

四、高级用法：复制外键约束 (MySQL 8.0+)

在 MySQL 8.0 及更高版本中，CREATE TABLE ... LIKE ... 语句可以复制外键约束，但这取决于 default_table_format 系统变量的设置。

default_table_format=InnoDB: 如果 default_table_format 设置为 InnoDB (默认值)，则 CREATE TABLE ... LIKE ... 语句会复制外键约束。

-- 确保 default_table_format 设置为 InnoDB
SHOW VARIABLES LIKE 'default_table_format';
-- 如果不是 InnoDB，则设置它
-- SET GLOBAL default_table_format = 'InnoDB';  (需要 SUPER 权限)

-- 创建表，包含外键约束
CREATE TABLE parent (
    id INT PRIMARY KEY
);

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

-- 复制表结构，包括外键约束
CREATE TABLE child_copy LIKE child;

-- 检查 child_copy 的外键约束
SHOW CREATE TABLE child_copy; -- 查看 CREATE TABLE 语句，确认外键约束已复制

default_table_format=MyISAM: 如果 default_table_format 设置为 MyISAM，则 CREATE TABLE ... LIKE ... 语句不会复制外键约束。这是因为 MyISAM 存储引擎不支持外键约束。

如果需要手动添加外键约束，可以使用 ALTER TABLE 语句：

ALTER TABLE child_copy
ADD CONSTRAINT fk_child_copy_parent
FOREIGN KEY (parent_id) REFERENCES parent(id);

五、代码示例：综合应用

下面是一个综合应用的代码示例，演示了如何使用 CREATE TABLE ... LIKE ... 创建备份表、设置自增起始值和添加外键约束。

-- 创建原始表 users
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    registration_date DATE
);

-- 插入一些数据
INSERT INTO users (username, email, registration_date) VALUES
('john_doe', '[email protected]', '2023-01-15'),
('jane_smith', '[email protected]', '2023-02-20');

-- 创建备份表 users_backup
CREATE TABLE users_backup LIKE users;

-- 将数据复制到备份表
INSERT INTO users_backup SELECT * FROM users;

-- 获取 users 表的自增最大值
SELECT MAX(id) FROM users;
-- 假设查询结果为 2

-- 设置 users_backup 表的自增起始值
ALTER TABLE users_backup AUTO_INCREMENT = 3;

-- 创建另一个表 orders，包含外键约束
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 创建 orders_backup 表
CREATE TABLE orders_backup LIKE orders;

-- 检查 orders_backup 表的外键约束 (MySQL 8.0+)
SHOW CREATE TABLE orders_backup;
-- 如果 default_table_format=InnoDB，则外键约束已经复制

-- 如果 default_table_format=MyISAM，则需要手动添加外键约束
-- ALTER TABLE orders_backup
-- ADD CONSTRAINT fk_orders_backup_users
-- FOREIGN KEY (user_id) REFERENCES users(id);

-- 清理测试数据
-- DROP TABLE users_backup;
-- DROP TABLE orders_backup;
-- DROP TABLE orders;
-- DROP TABLE users;

六、一些补充说明

除了以上介绍的特性和用法，CREATE TABLE ... LIKE ... 还有一些其他的细节点：

表选项: 源表的表选项 (例如 ROW_FORMAT，KEY_BLOCK_SIZE 等) 也会被复制。
字符集和排序规则: 新表的字符集和排序规则默认与源表相同。可以使用 ALTER TABLE 语句修改字符集和排序规则。
```
CREATE TABLE new_table LIKE original_table;
ALTER TABLE new_table CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
Generated Columns: 如果源表包含 Generated Columns (计算列), CREATE TABLE ... LIKE ... 也会复制这些列的定义。
空间索引: 如果源表包含空间索引 (Spatial Index), CREATE TABLE ... LIKE ... 也会复制空间索引。
虚拟列: CREATE TABLE ... LIKE ... 可以复制虚拟列 (Virtual Columns) 的定义。虚拟列不实际存储数据，而是根据表达式计算得到。

快速复制，简化开发

今天我们详细介绍了 MySQL 的 CREATE TABLE ... LIKE ... 特性，包括其语法、应用场景、注意事项和高级用法。掌握这个特性可以极大地提高数据库开发和管理效率，尤其是在创建备份表、测试表和中间表等场景下。希望大家在实际工作中能够灵活运用这个特性，简化开发流程。

MySQL 高级特性之 CREATE TABLE ... LIKE ...：表结构复制深度剖析

一、CREATE TABLE ... LIKE ... 语法详解