MySQL 中使用 INSERT INTO SELECT 的示例操作

INSERT INTO SELECT 是 MySQL 中一个非常有用的语句，它允许您将一个表中的数据插入到另一个表中。以下是详细的用法和示例。

基本语法

INSERT INTO 目标表 (列1, 列2, 列3, ...)
SELECT 列1, 列2, 列3, ...
FROM 源表
[WHERE 条件];

常见使用场景示例

1. 简单复制数据

将 employees 表中的所有数据复制到 employees_backup 表中：

INSERT INTO employees_backup
SELECT * FROM employees;

2. 复制部分列

只复制 employees 表中的 id, name 和 email 列到 employees_contact 表：

INSERT INTO employees_contact (employee_id, full_name, email_address)
SELECT id, name, email FROM employees;

3. 带条件的复制

只复制工资大于 5000 的员工数据：

INSERT INTO high_salary_employees
SELECT * FROM employees
WHERE salary > 5000;

4. 从多个表复制数据

从 orders 和 customers 表联合查询并插入到 order_summary 表：

INSERT INTO order_summary (order_id, customer_name, order_date, amount)
SELECT o.id, c.name, o.order_date, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.id;

5. 复制数据并添加额外值

复制数据时添加固定值或计算值：

INSERT INTO employee_stats (employee_id, salary, bonus, total_compensation)
SELECT id, salary, 1000, salary + 1000
FROM employees;

6. 复制数据到不同结构的表

源表和目标表结构不同时的处理：

INSERT INTO department_summary (dept_id, dept_name, employee_count, avg_salary)
SELECT 
    d.id, 
    d.name, 
    COUNT(e.id), 
    AVG(e.salary)
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.name;

高级用法

1. 使用 INSERT IGNORE 避免重复

INSERT IGNORE INTO unique_employees
SELECT * FROM employees;

2. 使用 ON DUPLICATE KEY UPDATE 处理冲突

INSERT INTO employee_salary (employee_id, salary)
SELECT id, salary FROM employees
ON DUPLICATE KEY UPDATE salary = VALUES(salary);

3. 使用 REPLACE 替代现有数据

REPLACE INTO product_inventory
SELECT * FROM temp_inventory;

实际应用示例

场景：每月销售数据归档

-- 将当月的销售记录归档到历史表
INSERT INTO sales_history (sale_id, product_id, sale_date, amount, customer_id)
SELECT id, product_id, sale_date, amount, customer_id
FROM sales
WHERE sale_date BETWEEN '2023-10-01' AND '2023-10-31';

-- 然后可以从主表中删除已归档的数据
DELETE FROM sales
WHERE sale_date BETWEEN '2023-10-01' AND '2023-10-31';

场景：创建数据快照

-- 创建产品库存快照
INSERT INTO inventory_snapshots (snapshot_date, product_id, quantity)
SELECT 
    CURRENT_DATE(),
    product_id,
    quantity
FROM inventory;