MySQL - 克隆表

MySQL - 克隆表

在数据库管理中，经常需要创建现有表的副本。这可能是出于备份目的、创建测试环境或生成汇总表。MySQL 提供了高效的方法来克隆表，而不是手动编写 CREATE TABLE 语句来镜像原始表。

克隆创建一个与原始表完全独立的新表。对克隆表所做的任何修改（插入、更新或删除数据，或更改其结构）都不会影响源表。这种隔离对于安全测试和开发至关重要。

克隆表的策略

在 MySQL 中克隆表有三种主要策略，每种策略都有特定的用例：

• 浅克隆（仅结构）： 创建一个新空表，其结构与原始表完全相同，包括列、数据类型、索引和分区。这非常适合创建将保存类似数据的新表。
• 部分克隆（结构和数据，基本）： 创建一个新表并复制原始表中的所有数据。但是，此方法 不会 复制索引、触发器或 PRIMARY KEY（主键）或 FOREIGN KEY（外键）等约束。
• 深克隆（结构和数据，完整）： 一个两步过程，首先创建浅克隆（复制完整结构），然后用原始表中的数据填充它。这会产生一个真正的完整副本。

浅克隆：CREATE TABLE ... LIKE

CREATE TABLE ... LIKE 语句是根据另一个表的定义创建空表的标准方法。

语法

CREATE TABLE new_table LIKE original_table;

示例

首先，我们来设置一个示例 PRODUCTS 表。

CREATE TABLE PRODUCTS (
    ID INT AUTO_INCREMENT,
    SKU VARCHAR(20) NOT NULL,
    NAME VARCHAR(100) NOT NULL,
    PRICE DECIMAL(10, 2),
    PRIMARY KEY(ID),
    UNIQUE KEY `uk_sku` (SKU)
) ENGINE=InnoDB;

INSERT INTO PRODUCTS(SKU, NAME, PRICE) VALUES
('TS-01', 'Modern T-Shirt', 25.50),
('MUG-01', 'Developer Mug', 15.00);

现在，我们来创建一个名为 PRODUCTS_ARCHIVE 的浅克隆。

CREATE TABLE PRODUCTS_ARCHIVE LIKE PRODUCTS;

验证

如果我们查询新表，它将是空的。但检查其结构会发现它与原始表相同。

-- Check the data (will be empty)
SELECT * FROM PRODUCTS_ARCHIVE;
-- Result: Empty set

-- Check the structure
SHOW CREATE TABLE PRODUCTS_ARCHIVE;

SHOW CREATE TABLE 的输出将确认 PRODUCTS_ARCHIVE 具有与 PRODUCTS 相同的列、PRIMARY KEY 和 UNIQUE KEY。

部分克隆：CREATE TABLE ... AS SELECT

此命令创建一个新表并立即用 SELECT 语句的结果填充它。这是一种快速复制数据的方法，但在结构方面有显著限制。

语法

CREATE TABLE new_table AS SELECT * FROM original_table;

示例

让我们创建 PRODUCTS 表的部分克隆。

CREATE TABLE PRODUCTS_QUICK_COPY AS SELECT * FROM PRODUCTS;

验证

新表将包含数据，但关键的结构部分将丢失。

-- Check the data (will contain records)
SELECT * FROM PRODUCTS_QUICK_COPY;

-- Check the structure
SHOW CREATE TABLE PRODUCTS_QUICK_COPY;

当您检查结构时，您会注意到 AUTO_INCREMENT 属性、PRIMARY KEY 和 UNIQUE KEY 未被复制。此方法最适合创建临时表或汇总表，而不适用于创建真正的备份。

深克隆：两全其美

要创建包含结构和数据的完美克隆，我们结合使用前两种方法。这确保了所有索引、约束和数据都得到保留。

语法

-- Step 1: Create an exact structural copy
CREATE TABLE new_table LIKE original_table;

-- Step 2: Copy all the data into the new table
INSERT INTO new_table SELECT * FROM original_table;

示例

让我们创建 PRODUCTS 表的深克隆，命名为 PRODUCTS_BACKUP。

-- Step 1: Shallow clone the structure
CREATE TABLE PRODUCTS_BACKUP LIKE PRODUCTS;

-- Step 2: Populate with data
INSERT INTO PRODUCTS_BACKUP SELECT * FROM PRODUCTS;

验证

PRODUCTS_BACKUP 表现在是原始表的完整独立副本。

SELECT * FROM PRODUCTS_BACKUP;

ID	SKU	NAME	PRICE
1	TS-01	Modern T-Shirt	25.50
2	MUG-01	Developer Mug	15.00

最佳实践和性能

在大型表上，克隆操作可能会占用大量资源，并可能锁定源表，从而影响应用程序性能。对于生产环境，请考虑以下最佳实践：

• 使用事务 (Transactions)： 执行深克隆时，将 CREATE 和 INSERT 语句封装在事务中以确保数据一致性，尽管 CREATE TABLE 会隐式提交。
• 零停机工具： 对于实时系统中的超大型表，请使用 Percona 的 pt-online-schema-change 或 GitHub 的 gh-ost 等外部工具。这些工具执行模式更改和数据复制，同时最大限度地减少对生产数据库的影响。
• 备份实用程序： 对于创建完整备份，专门的工具（如 mysqldump）通常是更好的选择。它们会生成一个逻辑备份脚本，可用于恢复表或整个数据库。

使用客户端程序克隆表

自动化表克隆是部署脚本或测试框架中的常见任务。以下是使用现代 Node.js 应用程序执行深克隆的方法。

示例：Node.js 与 mysql2/promise

NodeJS




const mysql = require('mysql2/promise');

// --- Configuration ---
const dbConfig = {
    host: '127.0.0.1',
    user: 'root',
    password: 'password',
    database: 'TUTORIALS'
};

const sourceTable = 'PRODUCTS';
const newTable = 'PRODUCTS_DEEP_CLONE';

// --- Main Logic with async/await ---
async function cloneTable() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        console.log('Successfully connected to the database.');

        console.log(`Starting deep clone of '${sourceTable}' to '${newTable}'...`);

        // Use backticks for table names to avoid issues with reserved words
        const createLikeQuery = `CREATE TABLE IF NOT EXISTS \`${newTable}\` LIKE \`${sourceTable}\`;`;
        const [createResult] = await connection.execute(createLikeQuery);
        console.log(`1. Structure for '${newTable}' created successfully.`);

        // Truncate the table in case it already existed with data
        await connection.execute(`TRUNCATE TABLE \`${newTable}\`;`);

        const insertSelectQuery = `INSERT INTO \`${newTable}\` SELECT * FROM \`${sourceTable}\`;`;
        const [insertResult] = await connection.execute(insertSelectQuery);
        console.log(`2. Data copied successfully. ${insertResult.affectedRows} rows affected.`);

        console.log('Deep clone complete.');

    } catch (error) {
        console.error('An error occurred during the cloning process:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('Database connection closed.');
        }
    }
}

cloneTable();


Output
The expected output would be:

Successfully connected to the database.
Starting deep clone of 'PRODUCTS' to 'PRODUCTS_DEEP_CLONE'...
1. Structure for 'PRODUCTS_DEEP_CLONE' created successfully.
2. Data copied successfully. 2 rows affected.
Deep clone complete.
Database connection closed.