MySQL / 系统课时
复制查询结果到数据表
复制查询结果到数据表
本课解决“把查询出来的多行数据一次性写入另一张表”的问题。你将用 INSERT SELECT 完成筛选、映射与批量写入,并掌握列对应和重复数据的基本风险。
学习目标
- 掌握
INSERT INTO ... SELECT ...的基本语法和执行过程。 - 能将查询结果按指定列映射到目标表,并验证实际写入结果。
- 能识别列顺序不匹配、重复数据和全量复制等常见风险。
基本语法
INSERT SELECT 不需要先把查询结果取到程序里再逐行插入,而是由 MySQL 直接把 SELECT 产生的结果作为插入数据源。最常见的写法是明确列出目标字段,随后让 SELECT 按相同顺序返回对应值。
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(50) NOT NULL,
salary DECIMAL(10, 2) NOT NULL
);
CREATE TABLE high_salary_employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50) NOT NULL,
salary DECIMAL(10, 2) NOT NULL
);
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'Engineering', 18000.00),
(2, 'Bob', 'Sales', 12000.00),
(3, 'Carol', 'Engineering', 22000.00),
(4, 'David', 'HR', 15000.00);
INSERT INTO high_salary_employees (employee_id, employee_name, salary)
SELECT id, name, salary
FROM employees
WHERE salary >= 18000;预期结果
Query OK, 2 rows affected这里 SELECT 返回 Alice 和 Carol 两行,分别映射到目标表的 employee_id、employee_name 和 salary。目标列与查询列必须按位置一一对应,列名不要求相同。
用查询条件控制写入范围
INSERT SELECT 的关键不是“复制全部数据”,而是把普通 SELECT 的筛选能力直接用于写入。因此可以通过 WHERE、连接和表达式决定哪些行进入目标表。
例如只复制 Engineering 部门,并把薪资提高后的结果写入归档表,可以直接在 SELECT 中完成计算:
CREATE TABLE engineering_salary_snapshot (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50) NOT NULL,
adjusted_salary DECIMAL(10, 2) NOT NULL
);
INSERT INTO engineering_salary_snapshot (employee_id, employee_name, adjusted_salary)
SELECT id, name, salary * 1.10
FROM employees
WHERE department = 'Engineering';预期结果
Query OK, 2 rows affectedSELECT 中的表达式会先计算,再写入目标列。因此 adjusted_salary 会保存计算后的数值,而不是源表中的原始 salary。
列映射与常见错误
目标列和查询列最好始终显式写出。不要依赖目标表的默认列顺序,也不要为了省字符使用 SELECT *,因为源表结构一旦变化,复制语句可能出现列错位或执行失败。
还要注意目标表中的约束。目标主键或唯一键已经存在时,普通 INSERT SELECT 会因重复键而失败;目标字段为 NOT NULL 时,查询结果也不能产生不合法的 NULL。
验证目标表时,可以直接查询刚刚写入的数据:
SELECT employee_id, employee_name, salary
FROM high_salary_employees
ORDER BY employee_id;预期结果
+-------------+---------------+----------+
| employee_id | employee_name | salary |
+-------------+---------------+----------+
| 1 | Alice | 18000.00 |
| 3 | Carol | 22000.00 |
+-------------+---------------+----------+什么时候适合使用
INSERT SELECT 适合数据归档、从明细表生成汇总表、按条件建立快照,以及把一张表中的部分数据迁移到结构兼容的目标表。它最大的优势是数据库内部直接完成查询与写入,通常比应用层“查询后循环 INSERT”更简单。
使用前只需确认三件事:源查询结果是否正确、目标列映射是否正确、目标表约束是否允许这些数据写入。把这三项先验证清楚,再执行批量 INSERT SELECT,能显著降低误写风险。