MySQL 复制表 - MySQL 教程 | 小傅哥技术教程

复制表概述

在 MySQL 中，复制表是一个常见的操作，用于创建表的副本、备份数据、测试环境搭建或数据迁移。MySQL 提供了多种方法来复制表的结构和数据。

🏗️ 结构复制

只复制表的结构（字段定义、索引、约束等），不包含数据

📊 数据复制

复制表中的所有数据，通常需要先有目标表结构

🔄 完整复制

同时复制表结构和数据，创建完全相同的表副本

🎯 选择性复制

根据条件复制部分数据或特定字段

                    复制表的应用场景：
                    数据备份和恢复
测试环境数据准备
数据迁移和同步
历史数据归档
表结构版本管理
性能测试数据准备

                

示例数据准备

-- 创建原始表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id VARCHAR(10) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    job_title VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    manager_id INT,
    status ENUM('active', 'inactive', 'terminated') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_department (department),
    INDEX idx_hire_date (hire_date),
    INDEX idx_salary (salary),
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- 插入示例数据
INSERT INTO employees (employee_id, first_name, last_name, email, phone, hire_date, job_title, department, salary, manager_id) VALUES
('EMP001', '张', '三', 'zhangsan@company.com', '13800138001', '2020-01-15', '软件工程师', 'IT部', 8000.00, NULL),
('EMP002', '李', '四', 'lisi@company.com', '13800138002', '2020-03-20', '产品经理', '产品部', 12000.00, NULL),
('EMP003', '王', '五', 'wangwu@company.com', '13800138003', '2020-06-10', '设计师', '设计部', 7000.00, NULL),
('EMP004', '赵', '六', 'zhaoliu@company.com', '13800138004', '2021-01-08', '高级工程师', 'IT部', 15000.00, 1),
('EMP005', '钱', '七', 'qianqi@company.com', '13800138005', '2021-04-12', '测试工程师', 'IT部', 6500.00, 1),
('EMP006', '孙', '八', 'sunba@company.com', '13800138006', '2021-07-20', '运营专员', '运营部', 5500.00, NULL),
('EMP007', '周', '九', 'zhoujiu@company.com', '13800138007', '2022-02-14', '数据分析师', '数据部', 9000.00, NULL),
('EMP008', '吴', '十', 'wushi@company.com', '13800138008', '2022-05-30', '前端工程师', 'IT部', 7500.00, 4),
('EMP009', '郑', '十一', 'zhengshiyi@company.com', '13800138009', '2022-09-15', '后端工程师', 'IT部', 8500.00, 4),
('EMP010', '王', '十二', 'wangshier@company.com', '13800138010', '2023-01-10', '实习生', 'IT部', 3000.00, 5);

-- 创建另一个示例表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dept_code VARCHAR(10) UNIQUE NOT NULL,
    dept_name VARCHAR(50) NOT NULL,
    manager_id INT,
    budget DECIMAL(12,2),
    location VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO departments (dept_code, dept_name, manager_id, budget, location) VALUES
('IT', 'IT部', 1, 500000.00, '北京'),
('PROD', '产品部', 2, 300000.00, '上海'),
('DESIGN', '设计部', 3, 200000.00, '深圳'),
('OPS', '运营部', 6, 150000.00, '广州'),
('DATA', '数据部', 7, 250000.00, '杭州');
                

复制表结构

方法一：CREATE TABLE ... LIKE

-- 复制表结构（包括索引，但不包括数据）
CREATE TABLE employees_backup LIKE employees;

-- 查看复制的表结构
DESCRIBE employees_backup;
SHOW CREATE TABLE employees_backup;

-- 查看索引
SHOW INDEX FROM employees_backup;

-- 验证表是否为空
SELECT COUNT(*) FROM employees_backup;
                

方法二：CREATE TABLE ... AS SELECT（仅结构）

-- 复制表结构（不包括索引和约束）
CREATE TABLE employees_structure AS
SELECT * FROM employees WHERE 1=0;

-- 查看表结构
DESCRIBE employees_structure;

-- 注意：这种方法不会复制索引、外键等约束
SHOW INDEX FROM employees_structure;
                

方法三：手动创建表结构

-- 获取原表的创建语句
SHOW CREATE TABLE employees;

-- 基于原表结构手动创建新表（可以修改表名和部分结构）
CREATE TABLE employees_modified (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id VARCHAR(10) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,  -- 合并 first_name 和 last_name
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    job_title VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    manager_id INT,
    status ENUM('active', 'inactive', 'terminated') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_department (department),
    INDEX idx_hire_date (hire_date)
    -- 注意：外键约束需要目标表存在
);
                

复制表数据

完整数据复制

-- 向已存在的表中插入所有数据
INSERT INTO employees_backup 
SELECT * FROM employees;

-- 验证数据复制
SELECT COUNT(*) FROM employees_backup;
SELECT * FROM employees_backup LIMIT 5;

-- 比较原表和备份表的数据
SELECT 
    (SELECT COUNT(*) FROM employees) as original_count,
    (SELECT COUNT(*) FROM employees_backup) as backup_count;
                

选择性数据复制

-- 创建目标表
CREATE TABLE active_employees LIKE employees;

-- 只复制活跃员工的数据
INSERT INTO active_employees
SELECT * FROM employees 
WHERE status = 'active';

-- 创建高薪员工表
CREATE TABLE high_salary_employees AS
SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) as full_name,
    email,
    job_title,
    department,
    salary,
    hire_date
FROM employees
WHERE salary > 8000;

-- 查看结果
SELECT * FROM active_employees;
SELECT * FROM high_salary_employees;
                

跨数据库复制

-- 假设要复制到另一个数据库
-- 首先创建目标数据库（如果不存在）
-- CREATE DATABASE backup_db;

-- 复制表结构到另一个数据库
-- CREATE TABLE backup_db.employees LIKE employees;

-- 复制数据到另一个数据库
-- INSERT INTO backup_db.employees SELECT * FROM employees;

-- 或者一步完成（结构+数据）
-- CREATE TABLE backup_db.employees_copy AS SELECT * FROM employees;
                

完整表复制

方法一：CREATE TABLE ... AS SELECT

-- 一步完成结构和数据的复制
CREATE TABLE employees_complete_copy AS
SELECT * FROM employees;

-- 验证复制结果
SELECT COUNT(*) FROM employees_complete_copy;
DESCRIBE employees_complete_copy;

-- 注意：需要手动添加索引和约束
ALTER TABLE employees_complete_copy 
ADD PRIMARY KEY (id),
ADD UNIQUE KEY unique_employee_id (employee_id),
ADD UNIQUE KEY unique_email (email),
ADD INDEX idx_department (department),
ADD INDEX idx_hire_date (hire_date),
ADD INDEX idx_salary (salary);

-- 修改自增属性
ALTER TABLE employees_complete_copy 
MODIFY COLUMN id INT AUTO_INCREMENT;
                

方法二：分步复制（推荐）

-- 第一步：复制表结构
CREATE TABLE employees_best_copy LIKE employees;

-- 第二步：复制数据
INSERT INTO employees_best_copy SELECT * FROM employees;

-- 验证复制结果
SELECT COUNT(*) FROM employees_best_copy;
SHOW INDEX FROM employees_best_copy;

-- 这种方法保留了所有索引和约束（除了外键）
                

处理外键约束

-- 如果原表有外键约束，需要特殊处理

-- 方法1：临时禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;

-- 复制表结构和数据
CREATE TABLE employees_with_fk LIKE employees;
INSERT INTO employees_with_fk SELECT * FROM employees;

-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- 方法2：删除外键约束后复制
CREATE TABLE employees_no_fk LIKE employees;

-- 删除外键约束
ALTER TABLE employees_no_fk DROP FOREIGN KEY employees_ibfk_1;

-- 插入数据
INSERT INTO employees_no_fk SELECT * FROM employees;

-- 如果需要，重新添加外键约束
-- ALTER TABLE employees_no_fk 
-- ADD FOREIGN KEY (manager_id) REFERENCES employees_no_fk(id);
                

高级复制技巧

带条件的复制

-- 复制最近一年入职的员工
CREATE TABLE recent_employees AS
SELECT * FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- 复制特定部门的员工
CREATE TABLE it_employees AS
SELECT * FROM employees
WHERE department = 'IT部';

-- 复制薪资前50%的员工
CREATE TABLE top_salary_employees AS
SELECT * FROM employees
WHERE salary >= (
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) 
    FROM employees
);

-- MySQL 8.0 之前的版本可以使用这种方法计算中位数
CREATE TABLE top_50_percent_employees AS
SELECT e1.* FROM employees e1
WHERE (
    SELECT COUNT(*) FROM employees e2 WHERE e2.salary >= e1.salary
) <= (
    SELECT COUNT(*) * 0.5 FROM employees
);
                

数据转换复制

-- 复制时进行数据转换
CREATE TABLE employees_transformed AS
SELECT 
    id,
    employee_id,
    CONCAT(first_name, ' ', last_name) as full_name,
    UPPER(email) as email_upper,
    phone,
    hire_date,
    UPPER(job_title) as job_title_upper,
    department,
    ROUND(salary * 1.1, 2) as adjusted_salary,  -- 薪资上调10%
    manager_id,
    status,
    YEAR(hire_date) as hire_year,
    DATEDIFF(CURDATE(), hire_date) as days_employed,
    CASE 
        WHEN salary > 10000 THEN '高薪'
        WHEN salary > 7000 THEN '中薪'
        ELSE '基础薪资'
    END as salary_level,
    created_at,
    updated_at
FROM employees;

-- 查看转换结果
SELECT full_name, adjusted_salary, salary_level, days_employed 
FROM employees_transformed 
LIMIT 5;
                

分区表复制

-- 创建按年份分区的员工表
CREATE TABLE employees_partitioned (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id VARCHAR(10) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    job_title VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    manager_id INT,
    status ENUM('active', 'inactive', 'terminated') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE (YEAR(hire_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 复制数据到分区表
INSERT INTO employees_partitioned 
SELECT * FROM employees;

-- 查看分区信息
SELECT 
    PARTITION_NAME,
    TABLE_ROWS,
    PARTITION_EXPRESSION
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'employees_partitioned' AND PARTITION_NAME IS NOT NULL;
                

复制方法比较

方法	结构	数据	索引	约束	外键	适用场景
CREATE TABLE ... LIKE	✅	❌	✅	✅	❌	只需要表结构
CREATE TABLE ... AS SELECT	✅	✅	❌	❌	❌	快速复制数据
LIKE + INSERT SELECT	✅	✅	✅	✅	❌	完整复制（推荐）
手动创建 + INSERT	✅	✅	✅	✅	✅	需要修改结构

批量复制和自动化

批量复制多个表

-- 使用存储过程批量复制表
DELIMITER //
CREATE PROCEDURE CloneMultipleTables()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_name VARCHAR(64);
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME IN ('employees', 'departments');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 创建备份表
        SET @sql = CONCAT('CREATE TABLE ', table_name, '_backup LIKE ', table_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 复制数据
        SET @sql = CONCAT('INSERT INTO ', table_name, '_backup SELECT * FROM ', table_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程
CALL CloneMultipleTables();

-- 验证结果
SHOW TABLES LIKE '%_backup';
SELECT COUNT(*) FROM employees_backup;
SELECT COUNT(*) FROM departments_backup;

-- 清理存储过程
DROP PROCEDURE CloneMultipleTables;
                

定时备份表

-- 创建带时间戳的备份表
SET @backup_suffix = DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s');
SET @sql = CONCAT('CREATE TABLE employees_backup_', @backup_suffix, ' AS SELECT * FROM employees');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DELIMITER PREPARE stmt;

-- 查看创建的备份表
SHOW TABLES LIKE 'employees_backup_%';

-- 创建存储过程用于定时备份
DELIMITER //
CREATE PROCEDURE CreateDailyBackup(IN table_name VARCHAR(64))
BEGIN
    DECLARE backup_table_name VARCHAR(128);
    SET backup_table_name = CONCAT(table_name, '_backup_', DATE_FORMAT(NOW(), '%Y%m%d'));
    
    -- 检查备份表是否已存在
    SET @check_sql = CONCAT(
        'SELECT COUNT(*) INTO @table_exists FROM information_schema.TABLES ',
        'WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = "', backup_table_name, '"'
    );
    PREPARE stmt FROM @check_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 如果不存在则创建备份
    IF @table_exists = 0 THEN
        SET @sql = CONCAT('CREATE TABLE ', backup_table_name, ' AS SELECT * FROM ', table_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SELECT CONCAT('Backup created: ', backup_table_name) AS result;
    ELSE
        SELECT CONCAT('Backup already exists: ', backup_table_name) AS result;
    END IF;
END //
DELIMITER ;

-- 使用存储过程创建备份
CALL CreateDailyBackup('employees');
CALL CreateDailyBackup('departments');
                

复制表的性能优化

大表复制优化

-- 对于大表，分批复制数据
-- 首先创建表结构
CREATE TABLE large_employees_copy LIKE employees;

-- 分批插入数据（假设按ID分批）
INSERT INTO large_employees_copy 
SELECT * FROM employees WHERE id BETWEEN 1 AND 1000;

INSERT INTO large_employees_copy 
SELECT * FROM employees WHERE id BETWEEN 1001 AND 2000;

-- 或者使用循环批量插入
DELIMITER //
CREATE PROCEDURE BatchCopyData()
BEGIN
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE offset_val INT DEFAULT 0;
    DECLARE row_count INT;
    
    -- 获取总行数
    SELECT COUNT(*) INTO row_count FROM employees;
    
    WHILE offset_val < row_count DO
        SET @sql = CONCAT(
            'INSERT INTO large_employees_copy ',
            'SELECT * FROM employees LIMIT ', batch_size, ' OFFSET ', offset_val
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET offset_val = offset_val + batch_size;
    END WHILE;
END //
DELIMITER ;

-- 调用批量复制过程
-- CALL BatchCopyData();
                

复制性能监控

-- 监控复制操作的性能
SET profiling = 1;

-- 执行复制操作
CREATE TABLE employees_perf_test AS SELECT * FROM employees;

-- 查看性能分析
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- 关闭性能分析
SET profiling = 0;

-- 比较不同复制方法的性能
-- 方法1：CREATE AS SELECT
SET @start_time = NOW(6);
CREATE TABLE test1 AS SELECT * FROM employees;
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS method1_microseconds;

-- 方法2：LIKE + INSERT
SET @start_time = NOW(6);
CREATE TABLE test2 LIKE employees;
INSERT INTO test2 SELECT * FROM employees;
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS method2_microseconds;

-- 清理测试表
DROP TABLE test1, test2, employees_perf_test;
                

复制表的维护和管理

验证复制完整性

-- 比较原表和复制表的行数
SELECT 
    'employees' as table_name,
    COUNT(*) as row_count
FROM employees
UNION ALL
SELECT 
    'employees_backup' as table_name,
    COUNT(*) as row_count
FROM employees_backup;

-- 比较数据完整性（使用校验和）
SELECT 
    'original' as source,
    BIT_XOR(CRC32(CONCAT_WS('|', id, employee_id, first_name, last_name, email))) as checksum
FROM employees
UNION ALL
SELECT 
    'backup' as source,
    BIT_XOR(CRC32(CONCAT_WS('|', id, employee_id, first_name, last_name, email))) as checksum
FROM employees_backup;

-- 查找差异数据
SELECT 'in_original_not_backup' as diff_type, e.*
FROM employees e
LEFT JOIN employees_backup eb ON e.id = eb.id
WHERE eb.id IS NULL
UNION ALL
SELECT 'in_backup_not_original' as diff_type, eb.*
FROM employees_backup eb
LEFT JOIN employees e ON eb.id = e.id
WHERE e.id IS NULL;
                

清理旧的备份表

-- 查找所有备份表
SELECT 
    TABLE_NAME,
    CREATE_TIME,
    TABLE_ROWS,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME LIKE '%_backup%'
ORDER BY CREATE_TIME DESC;

-- 创建清理旧备份的存储过程
DELIMITER //
CREATE PROCEDURE CleanOldBackups(IN days_to_keep INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_name VARCHAR(64);
    DECLARE table_date DATE;
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() 
          AND TABLE_NAME REGEXP '_backup_[0-9]{8}$'
          AND CREATE_TIME < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET @sql = CONCAT('DROP TABLE ', table_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SELECT CONCAT('Dropped old backup: ', table_name) AS message;
        
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

-- 清理7天前的备份
-- CALL CleanOldBackups(7);
                

最佳实践和注意事项

                    最佳实践：
                    使用 CREATE TABLE ... LIKE + INSERT SELECT 方法进行完整复制
大表复制时考虑分批处理
复制前检查磁盘空间是否充足
为备份表使用有意义的命名规范
定期验证备份数据的完整性
及时清理不需要的备份表
在生产环境中谨慎处理外键约束

                

注意事项：

CREATE TABLE ... AS SELECT 不会复制索引和约束
外键约束在复制时需要特殊处理
AUTO_INCREMENT 值可能需要重新设置
复制大表时会消耗大量磁盘空间和时间
复制过程中原表可能被锁定
触发器不会被复制到新表
分区信息需要单独处理

场景	推荐方法	注意事项
数据备份	LIKE + INSERT SELECT	保留完整结构和约束
测试环境	CREATE AS SELECT	快速创建，后续添加索引
数据迁移	手动创建 + INSERT	可以修改结构
历史归档	CREATE AS SELECT + 条件	只复制需要的数据
性能测试	批量复制	避免影响生产环境

清理示例表

-- 清理本教程创建的所有示例表
DROP TABLE IF EXISTS 
    employees_backup,
    employees_structure,
    employees_modified,
    active_employees,
    high_salary_employees,
    employees_complete_copy,
    employees_best_copy,
    employees_with_fk,
    employees_no_fk,
    recent_employees,
    it_employees,
    top_salary_employees,
    top_50_percent_employees,
    employees_transformed,
    employees_partitioned,
    large_employees_copy,
    departments_backup;

-- 删除存储过程
DROP PROCEDURE IF EXISTS CreateDailyBackup;
DROP PROCEDURE IF EXISTS BatchCopyData;
DROP PROCEDURE IF EXISTS CleanOldBackups;

SELECT 'All example tables and procedures have been cleaned up.' AS message;
                

📋 MySQL 复制表

复制表概述

🏗️ 结构复制

📊 数据复制

🔄 完整复制

🎯 选择性复制

示例数据准备

复制表结构

方法一：CREATE TABLE ... LIKE

方法二：CREATE TABLE ... AS SELECT（仅结构）

方法三：手动创建表结构

复制表数据

完整数据复制

选择性数据复制

跨数据库复制

完整表复制

方法一：CREATE TABLE ... AS SELECT

方法二：分步复制（推荐）

处理外键约束

高级复制技巧

带条件的复制

数据转换复制

分区表复制

复制方法比较

批量复制和自动化

批量复制多个表

定时备份表

复制表的性能优化

大表复制优化

复制性能监控

复制表的维护和管理

验证复制完整性

清理旧的备份表

最佳实践和注意事项

清理示例表