MySQL 复制表详解

在MySQL中，复制表是一个常见的操作，用于创建表的副本，包括表结构、数据、索引和约束等。本章将详细介绍多种复制表的方法及其适用场景。

复制表的完整步骤

如果需要完整复制MySQL数据表，包括表结构、索引、默认值等，仅使用CREATE TABLE ... SELECT命令是不够的。以下是完整的复制步骤：

步骤1：获取原表的创建语句

-- 使用SHOW CREATE TABLE获取完整的建表语句
SHOW CREATE TABLE original_table\G

输出示例：

*************************** 1. row ***************************
       Table: original_table
Create Table: CREATE TABLE `original_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4

步骤2：修改表名并创建新表

-- 复制建表语句，修改表名，然后执行
CREATE TABLE `copied_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

步骤3：复制数据

-- 将原表的数据插入到新表
INSERT INTO copied_table SELECT * FROM original_table;

步骤4：验证复制结果

-- 检查表结构
DESC copied_table;

-- 检查数据行数
SELECT COUNT(*) FROM original_table;
SELECT COUNT(*) FROM copied_table;

-- 检查索引
SHOW INDEX FROM copied_table;

使用 CREATE TABLE ... LIKE 复制表结构

基本语法

CREATE TABLE new_table LIKE original_table;

完整示例

-- 创建与原表结构完全相同的新表（包括索引、约束等）
CREATE TABLE users_backup LIKE users;

-- 验证表结构
DESC users_backup;

-- 复制数据（可选）
INSERT INTO users_backup SELECT * FROM users;

CREATE TABLE ... LIKE 的特点

• 复制完整的表结构，包括索引、约束、默认值
• 不复制数据 - 需要单独使用INSERT语句复制数据
• 复制表的存储引擎、字符集等属性
• 自动递增字段的起始值重置为1

使用 CREATE TABLE ... SELECT 复制表和数据

基本语法

CREATE TABLE new_table [AS] SELECT * FROM original_table;

完整示例

-- 创建新表并复制所有数据
CREATE TABLE users_copy SELECT * FROM users;

-- 创建新表并复制部分数据
CREATE TABLE active_users
SELECT * FROM users WHERE status = 'active';

-- 创建新表并复制部分字段
CREATE TABLE user_basic_info
SELECT id, name, email FROM users;

CREATE TABLE ... SELECT 的特点

• 复制数据到新表
• 不复制索引、约束、默认值 - 只复制基本表结构和数据
• 新表的字段类型基于SELECT查询的结果
• 适合快速创建数据副本，但不保留完整表属性

完整的表复制方法比较

推荐的完整复制方法

方法1：CREATE TABLE ... LIKE + INSERT

-- 步骤1：复制表结构（包括索引和约束）
CREATE TABLE users_backup LIKE users;

-- 步骤2：复制数据
INSERT INTO users_backup SELECT * FROM users;

-- 步骤3：重置自增序列（如果需要）
ALTER TABLE users_backup AUTO_INCREMENT = 1;

方法2：使用mysqldump工具

# 导出表结构和数据
mysqldump -u username -p database_name table_name > table_backup.sql

# 导入到新表（先修改SQL文件中的表名）
mysql -u username -p database_name < table_backup.sql

方法3：在同一个操作中完成

-- 创建表并复制数据，然后添加索引
CREATE TABLE users_backup SELECT * FROM users;

-- 手动添加主键
ALTER TABLE users_backup ADD PRIMARY KEY (id);

-- 手动添加索引
ALTER TABLE users_backup ADD UNIQUE INDEX idx_email (email);
ALTER TABLE users_backup ADD INDEX idx_created_at (created_at);

-- 设置自增属性
ALTER TABLE users_backup MODIFY id INT AUTO_INCREMENT;

使用 PHP 脚本复制表

使用 MySQLi 复制表

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 原表和新表名
$original_table = "users";
$new_table = "users_backup";

try {
    // 方法1：使用CREATE TABLE ... LIKE
    $sql1 = "CREATE TABLE $new_table LIKE $original_table";
    if ($conn->query($sql1) === TRUE) {
        echo "表结构复制成功<br>";

        // 复制数据
        $sql2 = "INSERT INTO $new_table SELECT * FROM $original_table";
        if ($conn->query($sql2) === TRUE) {
            echo "数据复制成功，影响行数: " . $conn->affected_rows . "<br>";
        } else {
            throw new Exception("数据复制失败: " . $conn->error);
        }
    } else {
        throw new Exception("表结构复制失败: " . $conn->error);
    }

    echo "表复制完成！";

} catch (Exception $e) {
    echo "错误: " . $e->getMessage();
}

$conn->close();
?>

使用 PDO 复制表

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $original_table = "products";
    $new_table = "products_backup";

    // 开始事务
    $conn->beginTransaction();

    // 复制表结构
    $conn->exec("CREATE TABLE $new_table LIKE $original_table");

    // 复制数据
    $stmt = $conn->prepare("INSERT INTO $new_table SELECT * FROM $original_table");
    $stmt->execute();
    $row_count = $stmt->rowCount();

    // 提交事务
    $conn->commit();

    echo "表复制成功！复制了 $row_count 行数据。";

} catch(PDOException $e) {
    // 回滚事务
    $conn->rollBack();
    echo "错误: " . $e->getMessage();
}

$conn = null;
?>

高级复制技巧

复制表到其他数据库

-- 复制到同一MySQL实例的不同数据库
CREATE TABLE new_database.users_backup LIKE original_database.users;
INSERT INTO new_database.users_backup SELECT * FROM original_database.users;

条件复制数据

-- 只复制符合条件的数据
CREATE TABLE active_orders LIKE orders;
INSERT INTO active_orders
SELECT * FROM orders
WHERE status = 'active' AND order_date >= '2024-01-01';

复制表结构但不复制数据

-- 创建空表副本
CREATE TABLE users_template LIKE users;

-- 或者使用WHERE FALSE条件
CREATE TABLE users_empty SELECT * FROM users WHERE FALSE;

重命名复制过程中的字段

-- 复制时重命名字段
CREATE TABLE user_report
SELECT
    id AS user_id,
    name AS full_name,
    email AS email_address,
    created_at AS registration_date
FROM users;

处理复制过程中的特殊情况

处理自增字段

-- 方法1：重置自增序列
CREATE TABLE users_backup LIKE users;
ALTER TABLE users_backup AUTO_INCREMENT = 1;
INSERT INTO users_backup SELECT * FROM users;

-- 方法2：在插入时排除自增字段
CREATE TABLE users_backup LIKE users;
INSERT INTO users_backup (name, email, created_at)
SELECT name, email, created_at FROM users;

处理外键约束

-- 暂时禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;

-- 执行复制操作
CREATE TABLE orders_backup LIKE orders;
INSERT INTO orders_backup SELECT * FROM orders;

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

处理大数据表的复制

-- 分批复制数据（避免锁表时间过长）
CREATE TABLE large_table_backup LIKE large_table;

-- 第一次复制（例如前10000行）
INSERT INTO large_table_backup
SELECT * FROM large_table
WHERE id BETWEEN 1 AND 10000;

-- 继续复制剩余数据
INSERT INTO large_table_backup
SELECT * FROM large_table
WHERE id > 10000;

复制表的最佳实践

选择合适的复制方法

• 完整备份：使用CREATE TABLE ... LIKE + INSERT
• 快速数据复制：使用CREATE TABLE ... SELECT
• 结构模板：使用CREATE TABLE ... LIKE不复制数据
• 跨数据库复制：使用SHOW CREATE TABLE方法

性能优化建议

• 在系统低峰期执行大表复制操作
• 对于大表，考虑分批复制数据
• 复制前暂时禁用索引更新，复制后重建
• 使用事务确保数据一致性

安全注意事项

• 复制前确认目标表不存在或可以覆盖
• 在生产环境操作前在测试环境验证
• 确保有足够的磁盘空间
• 记录复制操作的时间和结果

常见问题与解决方案

1. 表已存在错误

ERROR 1050 (42S01): Table 'table_name' already exists

解决方案：先删除已存在的表，或使用不同的表名。

2. 磁盘空间不足

ERROR 1114 (HY000): The table 'table_name' is full

解决方案：清理磁盘空间或使用分批复制。

3. 外键约束错误

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

解决方案：暂时禁用外键检查，或按依赖顺序复制表。

4. 数据类型不兼容

ERROR 1366 (HY000): Incorrect integer value

解决方案：检查源表和目标表的字段类型是否匹配。

实用脚本和工具

自动复制表的存储过程

DELIMITER //

CREATE PROCEDURE CopyTable(
    IN source_db VARCHAR(64),
    IN source_table VARCHAR(64),
    IN target_db VARCHAR(64),
    IN target_table VARCHAR(64)
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE create_stmt TEXT;

    -- 获取源表的创建语句
    SET @sql = CONCAT('SHOW CREATE TABLE ', source_db, '.', source_table);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    -- 这里需要应用程序逻辑来处理SHOW CREATE TABLE的结果
    -- 修改表名并执行创建语句

    -- 复制数据
    SET @sql = CONCAT('INSERT INTO ', target_db, '.', target_table,
                     ' SELECT * FROM ', source_db, '.', source_table);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;
END//

DELIMITER ;

-- 使用存储过程
CALL CopyTable('source_db', 'users', 'backup_db', 'users_backup');

使用mysqldump进行表复制

#!/bin/bash

# 配置参数
DB_HOST="localhost"
DB_USER="username"
DB_PASS="password"
SOURCE_DB="production_db"
TARGET_DB="backup_db"
TABLE_NAME="users"

# 导出表结构和数据
mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS $SOURCE_DB $TABLE_NAME > /tmp/table_backup.sql

# 修改表名（可选）
sed -i 's/CREATE TABLE `'$TABLE_NAME'`/CREATE TABLE `'$TABLE_NAME'_backup`/g' /tmp/table_backup.sql
sed -i 's/INSERT INTO `'$TABLE_NAME'`/INSERT INTO `'$TABLE_NAME'_backup`/g' /tmp/table_backup.sql

# 导入到目标数据库
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $TARGET_DB < /tmp/table_backup.sql

# 清理临时文件
rm /tmp/table_backup.sql

echo "表复制完成"