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

MySQL高级特性：CREATE TABLE … LIKE … 在表结构复制中的应用

大家好，今天我们来深入探讨 MySQL 中一个非常实用但可能被忽视的特性：CREATE TABLE ... LIKE ...，并详细讲解它在表结构复制中的应用。

1. CREATE TABLE ... LIKE ... 语法详解

CREATE TABLE ... LIKE ... 语句允许我们创建一个新表，该表拥有与现有表完全相同的表结构，包括列定义、数据类型、键（主键、外键、唯一键等）、索引、以及其他表属性（如字符集、排序规则、注释等）。它只复制表结构，不复制数据。

其基本语法如下：

CREATE TABLE new_table_name LIKE original_table_name;

CREATE TABLE new_table_name: 标准创建表语句的开头，指定要创建的新表的名称。
LIKE original_table_name: 关键部分，指示 MySQL 从 original_table_name 复制表结构。original_table_name 必须是已存在的表。

2. 复制表结构的具体行为

理解 CREATE TABLE ... LIKE ... 的复制行为至关重要。它复制的内容包括：

列定义: 列名、数据类型、长度、是否允许 NULL 值、默认值。
键和索引: 主键、外键、唯一键、普通索引，以及它们的定义。
表属性: 字符集、排序规则、注释、存储引擎（例如InnoDB, MyISAM, etc.）。
分区定义: 如果原始表使用了分区，新表也会继承相同的分区定义。
自增列: 如果原始表有自增列，新表也会继承自增列的属性。

需要注意的是，以下内容不会被复制：

表数据: 新表是空的，不包含任何数据。
表空间: 新表会分配到默认的表空间，而不是复制原始表的表空间配置（除非显式指定）。
触发器: 触发器是与特定表操作关联的代码块，不会被复制。
存储过程和函数: 这些是独立的对象，不会被复制。
权限: 新表的权限需要单独授予。

3. CREATE TABLE ... LIKE ... 的使用场景

CREATE TABLE ... LIKE ... 在以下场景中非常有用：

创建测试表: 在开发或测试环境中，需要一个与生产表结构相同的表进行测试，而不需要复制大量数据。
备份表结构: 在进行数据库迁移或重大变更之前，备份表结构以防止意外情况。
创建临时表: 在复杂查询中，创建一个具有特定结构的临时表来存储中间结果。
创建相似表: 创建多个具有相似结构的表，例如，按日期分割的日志表。
复制表结构到不同的数据库: 虽然不能直接跨数据库使用 CREATE TABLE ... LIKE ...，但可以先在一个数据库中创建新表，然后使用 mysqldump 工具导出表结构，再导入到另一个数据库。

4. 代码示例：详细演示 CREATE TABLE ... LIKE ... 的用法

首先，我们创建一个原始表 employees：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone_number VARCHAR(20),
    hire_date DATE NOT NULL,
    job_id INT,
    salary DECIMAL(10, 2),
    department_id INT,
    INDEX idx_last_name (last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE employees AUTO_INCREMENT=1000;

INSERT INTO employees (first_name, last_name, email, hire_date, salary) VALUES
('John', 'Doe', '[email protected]', '2023-01-15', 60000.00),
('Jane', 'Smith', '[email protected]', '2023-02-20', 75000.00);

现在，使用 CREATE TABLE ... LIKE ... 创建一个新表 employees_backup，该表具有与 employees 相同的结构：

CREATE TABLE employees_backup LIKE employees;

验证 employees_backup 的结构：

SHOW CREATE TABLE employees_backup;

输出结果会显示与 employees 表完全相同的 CREATE TABLE 语句，但没有数据。例如：

CREATE TABLE `employees_backup` (
  `employee_id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` int DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `department_id` int DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

注意，employees_backup 的 AUTO_INCREMENT 值也会被复制。

示例2：包含外键的表

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL
);

ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY (department_id) REFERENCES departments(department_id);

INSERT INTO departments (department_name) VALUES ('Sales'), ('Marketing');
UPDATE employees SET department_id = 1 WHERE employee_id = 1000;
UPDATE employees SET department_id = 2 WHERE employee_id = 1001;

CREATE TABLE departments_backup LIKE departments;
CREATE TABLE employees_backup2 LIKE employees;

SHOW CREATE TABLE employees_backup2;

可以看到，外键约束 fk_department_id 也被复制到了 employees_backup2 表中。

5. CREATE TABLE ... LIKE ... 与 CREATE TABLE ... AS SELECT ... 的区别

另一个常用的表复制语句是 CREATE TABLE ... AS SELECT ...。它不仅复制表结构，还复制数据。理解它们之间的区别非常重要：

特性	`CREATE TABLE ... LIKE ...`	`CREATE TABLE ... AS SELECT ...`
复制内容	表结构	表结构 + 数据
数据	无数据	有数据
适用场景	仅需表结构，无需数据	需要表结构和数据
性能	通常更快	较慢（取决于数据量）
列定义	完全复制	基于 `SELECT` 语句的结果集确定
索引和约束	完全复制	可能不会完全复制，取决于数据库版本和 `SELECT` 语句
自增列	完全复制	可能会丢失自增属性

例如：

CREATE TABLE employees_copy AS SELECT * FROM employees;

这条语句会创建一个名为 employees_copy 的表，该表具有与 employees 相同的结构，并且包含 employees 表中的所有数据。

6. 潜在问题与注意事项

存储引擎: 确保新表的存储引擎与原始表兼容。如果新表的存储引擎与默认存储引擎不同，可以使用 ENGINE 选项显式指定。
字符集和排序规则: 如果需要使用不同的字符集或排序规则，可以在创建新表后修改。
外键约束: 如果复制的表包含外键约束，确保引用的表也存在，并且新表的外键约束配置正确。否则，可能会出现外键约束错误。
自增列: 复制自增列时，需要注意自增值的起始值。如果原始表已经插入了数据，新表的自增值可能会与原始表冲突。建议重置新表的自增值。
权限: 创建新表后，需要显式授予用户访问权限。
分区表: 如果复制的表是分区表，确保新表的分区定义与原始表一致。
空间占用: 虽然CREATE TABLE ... LIKE ... 不复制数据，但仍然会占用一定的磁盘空间来存储表结构。
版本兼容性: 在较低版本的MySQL中，某些表属性可能无法完全复制。建议在相同的MySQL版本中使用该语句。

7. 扩展：复制表结构到不同的数据库服务器

虽然 CREATE TABLE ... LIKE ... 不能直接跨数据库服务器使用，但可以使用 mysqldump 工具来实现。

导出表结构: 使用 mysqldump 命令导出原始表的结构：
```
mysqldump -u [用户名] -p[密码] -d [数据库名] [表名] > [表结构文件.sql]
```
- -u [用户名]：指定 MySQL 用户名。
- -p[密码]：指定 MySQL 密码。注意 -p 和密码之间不能有空格。
- -d：表示只导出表结构，不导出数据。
- [数据库名]：指定数据库名。
- [表名]：指定表名。
- [表结构文件.sql]：指定导出的 SQL 文件名。
导入表结构: 将导出的 SQL 文件导入到目标数据库服务器：
```
mysql -u [用户名] -p[密码] [数据库名] < [表结构文件.sql]
```
- [数据库名]：指定目标数据库名。

8. 高级用法：结合存储过程和事件调度器实现定期备份表结构

我们可以结合存储过程和事件调度器，实现定期备份表结构的功能。

首先，创建一个存储过程，用于备份表结构：

DELIMITER //
CREATE PROCEDURE backup_table_structure(IN original_table VARCHAR(255), IN backup_table VARCHAR(255))
BEGIN
    SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ', backup_table, ' LIKE ', original_table);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

然后，创建一个事件调度器，定期调用存储过程：

CREATE EVENT backup_employees_event
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
    CALL backup_table_structure('employees', 'employees_backup_' + DATE_FORMAT(NOW(), '%Y%m%d'));

这个事件调度器每天执行一次，创建一个名为 employees_backup_YYYYMMDD 的表，该表具有与 employees 表相同的结构。

9. 总结：CREATE TABLE ... LIKE ... 是高效复制表结构的利器

CREATE TABLE ... LIKE ... 是 MySQL 中一个非常实用的特性，它允许我们快速复制表结构，而无需复制数据。它在测试、备份、临时表创建等场景中发挥着重要作用。理解其复制行为、注意事项和与其他语句的区别，可以帮助我们更有效地使用它。

10. 最后的思考，掌握这个特性，能让你事半功倍

掌握 CREATE TABLE ... LIKE ... 这个特性，能帮助你更高效地管理数据库结构，避免重复劳动，提高开发效率。它是 MySQL 工具箱中一个不可或缺的工具。希望今天的讲解对你有所帮助。

MySQL高级特性：CREATE TABLE … LIKE … 在表结构复制中的应用

发表回复 取消回复

发表回复取消回复