MySQL - INSERT INTO SELECT

MySQL：使用 INSERT INTO SELECT 复制数据

INSERT INTO ... SELECT 语句是一个强大的命令，用于将数据从一个表复制到另一个表。它非常高效，因为数据传输完全在数据库服务器内部发生。这常用于归档旧数据、创建汇总表或用生产数据填充测试环境。

基本语法

该语句结合了 INSERT INTO 和 SELECT。SELECT 查询检索数据，而 INSERT INTO 部分指定存放位置。

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

记住的关键点：

• 目标表必须在运行命令之前存在。
• SELECT 列表中的列数据类型必须与 INSERT 列表中的列数据类型兼容。
• SELECT 和 INSERT 列表中的列数必须匹配。

示例：归档旧订单

假设我们有一个 orders 表，它正在变得非常大。我们希望将所有一年以前已完成的订单移动到一个 orders_archive 表中，以使主表更小更快。

首先，让我们设置源表和目标表。

-- 包含当前和近期订单的主表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'shipped', 'completed', 'cancelled') NOT NULL
);

-- 结构相同的归档表
CREATE TABLE orders_archive (
    id INT PRIMARY KEY, -- 注意：此处不使用 AUTO_INCREMENT
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'shipped', 'completed', 'cancelled') NOT NULL
);

-- 向主订单表插入一些示例数据
INSERT INTO orders (customer_id, order_date, total_amount, status) VALUES
(101, '2023-10-25', 150.75, 'shipped'),
(102, '2022-05-10', 99.50, 'completed'),  -- 这条应该被归档
(103, '2023-11-01', 24.00, 'pending'),
(101, '2022-03-15', 305.00, 'completed'); -- 这条也应该被归档

现在，我们可以使用 INSERT INTO ... SELECT 来复制旧的、已完成的订单。

INSERT INTO orders_archive (id, customer_id, order_date, total_amount, status)
SELECT id, customer_id, order_date, total_amount, status
FROM orders
WHERE status = 'completed' AND order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- 复制完成后，通常会从主表中删除已归档的行
-- DELETE FROM orders
-- WHERE status = 'completed' AND order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

高级用法：处理重复数据和不匹配的列

忽略重复项

如果你运行相同的归档脚本两次，由于主键 id 重复，它会失败。你可以使用 INSERT IGNORE 来告诉 MySQL 简单地丢弃会导致重复键冲突的行。

INSERT IGNORE INTO orders_archive (...)
SELECT ... FROM orders WHERE ...;

更新重复项

更强大的选项是 ON DUPLICATE KEY UPDATE。这用于“upsert”（插入或更新）逻辑：如果存在具有相同唯一/主键的行，则更新它；否则，插入它。这非常适合创建和更新汇总表。

-- 假设有一个按客户汇总销售额的表
CREATE TABLE customer_summary (
    customer_id INT PRIMARY KEY,
    total_spent DECIMAL(12, 2) NOT NULL,
    last_order_date DATE NOT NULL
);

-- 这条语句可以每天运行以更新汇总数据
INSERT INTO customer_summary (customer_id, total_spent, last_order_date)
SELECT customer_id, SUM(total_amount), MAX(order_date)
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
ON DUPLICATE KEY UPDATE
    total_spent = customer_summary.total_spent + VALUES(total_spent),
    last_order_date = VALUES(last_order_date);

插入时转换数据

SELECT 语句不必是直接的逐列复制。你可以使用表达式、字面量和函数来在插入时转换数据。

-- 让我们从客户数据中创建一个“邮件列表”
CREATE TABLE mailing_list (
    email VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255),
    subscribed_on DATETIME NOT NULL
);

-- 假设我们有一个包含 'id', 'name', 'email' 的 'customers' 表
INSERT INTO mailing_list (email, name, subscribed_on)
SELECT email, name, NOW() -- 插入当前时间戳
FROM customers
WHERE has_consented = TRUE;

实际应用：数据迁移脚本

虽然你可以直接运行这些查询，但它们通常是使用 Python、Node.js 或 PHP 等语言编写的大型脚本的一部分。下面是你在现代 Node.js 脚本中执行订单归档任务的方法。

使用 mysql2/promise 的 Node.js 示例

此示例使用 async/await 来实现清晰、可读的代码，并使用事务来确保数据要么成功复制和删除，要么两项操作都不发生。

// 设置：npm install mysql2 dotenv
// 创建一个 .env 文件，包含你的数据库凭据：
// DB_HOST=localhost
// DB_USER=your_user
// DB_PASSWORD=your_password
// DB_NAME=your_db

require('dotenv').config();
const mysql = require('mysql2/promise');

async function archiveOldOrders() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            database: process.env.DB_NAME
        });

        await connection.beginTransaction();
        console.log('事务已启动。');

        const archiveSql = `
            INSERT INTO orders_archive (id, customer_id, order_date, total_amount, status)
            SELECT id, customer_id, order_date, total_amount, status
            FROM orders
            WHERE status = 'completed' AND order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
        `;

        const [result] = await connection.execute(archiveSql);
        console.log(`${result.affectedRows} 行已归档。`);

        if (result.affectedRows > 0) {
            const deleteSql = `
                DELETE FROM orders
                WHERE status = 'completed' AND order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
            `;
            const [deleteResult] = await connection.execute(deleteSql);
            console.log(`${deleteResult.affectedRows} 行已从主表删除。`);
        }

        await connection.commit();
        console.log('事务成功提交。');

    } catch (error) {
        console.error('归档过程中出错：', error);
        if (connection) {
            await connection.rollback();
            console.log('事务已回滚。');
        }
    } finally {
        if (connection) {
            await connection.end();
            console.log('连接已关闭。');
        }
    }
}

archiveOldOrders();