MySQL 复制表：结构、数据及索引的完整复制

三、CREATE TABLE … LIKE 方法

3.1 快速复制表结构

CREATE TABLE new_table LIKE original_table;

特点：

• 完全复制原表结构，包括索引
• 不复制数据
• 简单快捷

3.2 结合数据复制

CREATE TABLE employees_clone LIKE employees;
INSERT INTO employees_clone SELECT * FROM employees;

四、mysqldump 工具方法

4.1 使用 mysqldump 导出表

mysqldump -u username -p database_name original_table > table_dump.sql

4.2 导入到新表

mysql -u username -p database_name < table_dump.sql

高级选项：

• --no-data：仅导出结构
• --where：条件导出部分数据
• --skip-triggers：不导出触发器

五、不同复制方法对比

六、高级复制技巧

6.1 部分数据复制

-- 复制特定列
CREATE TABLE partial_copy AS SELECT id, name FROM employees WHERE department = 'IT';

-- 复制满足条件的行
INSERT INTO employees_copy SELECT * FROM employees WHERE salary > 5000;

6.2 跨数据库复制

-- 在同一 MySQL 实例中
CREATE TABLE database2.new_table LIKE database1.original_table;
INSERT INTO database2.new_table SELECT * FROM database1.original_table;

-- 不同 MySQL 实例间
# 导出 mysqldump -u user1 -p db1 table1 > table1_dump.sql
# 导入 mysql -u user2 -p db2 < table1_dump.sql

6.3 复制表并修改结构

-- 复制表但修改某些列
CREATE TABLE modified_copy (
  id INT PRIMARY KEY AUTO_INCREMENT,
  full_name VARCHAR(100) NOT NULL COMMENT '将 first+last 合并',
  department VARCHAR(50),
  monthly_salary DECIMAL(10,2),
  INDEX idx_dept (department)
) AS SELECT id, CONCAT(first_name, ' ', last_name) AS full_name, department, salary/12 AS monthly_salary FROM employees;

七、复制表时的注意事项

1. 外键约束：复制表时外键关系不会自动保留
2. 自增列：注意自增列的起始值可能需要重置
3. 触发器：使用 SHOW CREATE TABLE 方法会丢失触发器
4. 存储引擎：确保目标服务器支持原表的存储引擎
5. 权限问题：需要有原表的 SELECT 权限和新表的 CREATE 权限

八、性能优化建议

大数据表复制分批插入禁用索引使用 LOAD DATA 减少锁表时间插入后重建索引更快

优化代码示例：

-- 方法 1：分批插入
INSERT INTO large_table_copy SELECT * FROM large_table LIMIT 0, 10000;
INSERT INTO large_table_copy SELECT * FROM large_table LIMIT 10000, 10000;
-- 重复直到所有数据复制完成

-- 方法 2：禁用索引优化
ALTER TABLE large_table_copy DISABLE KEYS;
INSERT INTO large_table_copy SELECT * FROM large_table;
ALTER TABLE large_table_copy ENABLE KEYS;

-- 方法 3：使用临时文件
SELECT * FROM large_table INTO OUTFILE '/tmp/large_table_data.csv';
LOAD DATA INFILE '/tmp/large_table_data.csv' INTO TABLE large_table_copy;

九、常见问题解决方案

9.1 复制表时自增 ID 处理

-- 保留原自增 ID
CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

-- 重置自增 ID
CREATE TABLE new_table LIKE original_table;
ALTER TABLE new_table AUTO_INCREMENT=1;
INSERT INTO new_table (col1, col2) SELECT col1, col2 FROM original_table;

9.2 只复制表结构不复制数据

-- 方法 1
CREATE TABLE new_table LIKE original_table;

-- 方法 2
CREATE TABLE new_table AS SELECT * FROM original_table WHERE 1=0;

-- 方法 3（使用 mysqldump）
mysqldump -d -u username -p dbname original_table > table_structure.sql
mysql -u username -p dbname < table_structure.sql

9.3 大表复制时的性能问题

-- 使用事务分批提交
START TRANSACTION;
INSERT INTO new_table SELECT * FROM original_table WHERE id BETWEEN 1 AND 10000;
COMMIT;
START TRANSACTION;
INSERT INTO new_table SELECT * FROM original_table WHERE id BETWEEN 10001 AND 20000;
COMMIT;
-- 继续直到完成

十、总结与最佳实践

10.1 方法选择指南

是否需要复制什么？

• 需要完整结构？
• 需要数据？

使用 CREATE TABLE...SELECT SHOW CREATE TABLE + INSERT 或 CREATE...LIKE + INSERT CREATE TABLE...LIKE 适合简单结构 + 数据复制

10.2 最佳实践清单

1. 结构精确复制：使用 SHOW CREATE TABLE 或 CREATE TABLE...LIKE
2. 大数据量复制：考虑分批插入或使用 LOAD DATA INFILE
3. 跨服务器复制：使用 mysqldump 工具
4. 部分复制：结合 WHERE 条件的 CREATE TABLE...SELECT
5. 性能优化：大表复制时禁用索引、使用事务
6. 权限检查：确保有足够的权限执行操作
7. 验证结果：复制后检查行数、索引等是否一致

通过掌握这些 MySQL 表复制技术，您可以根据不同场景选择最合适的方法，高效完成表复制任务，同时保证数据的完整性和一致性。