MySQL INSERT ... SELECT 實現依查詢結果插入指定表

MySQL INSERT ... SELECT 實現依查詢結果插入指定表

MySQL 支援將 SELECT 查詢結果直接插入目標表，適用於資料遷移、彙總表建立、跨表複製等場景。

語法規則

INSERT INTO 目標表 (欄位列表1)
SELECT 欄位列表2 FROM 來源表 WHERE 條件;

要求：

1. 欄位列表1 與 欄位列表2 欄位數量、順序、資料型別 必須一一對應。
2. 目標表欄位若有 NOT NULL 且無預設值，來源查詢必須提供非空值。
3. 來源表資料保持不變（僅讀取）。

實戰範例

場景：三表結構

-- 學生表
s(sno, sname, sex, age, dept)

-- 課程表
c(cno, cname, tname)

-- 選課表
sc(sno, cno, grade)

需求：將成績 ≥ 80 分學生的學號、姓名、系名插入已存在的 stu(sno, sname, dept) 表。

步驟 1：建立目標表

DROP TABLE IF EXISTS stu;
CREATE TABLE stu (
  sno INT(4) UNIQUE,
  sname VARCHAR(20),
  dept VARCHAR(10)
);

步驟 2：執行 INSERT ... SELECT

INSERT INTO stu (sno, sname, dept)
SELECT s.sno, s.sname, s.dept
FROM s
WHERE s.sno IN (SELECT sc.sno FROM sc WHERE sc.grade >= 80);

進階用法

1. 多表聯結查詢插入

INSERT INTO stu (sno, sname, dept)
SELECT s.sno, s.sname, s.dept
FROM s
JOIN sc ON s.sno = sc.sno
WHERE sc.grade >= 80;

2. 插入時指定常數值

INSERT INTO stu (sno, sname, dept, create_time)
SELECT s.sno, s.sname, s.dept, NOW()
FROM s
WHERE s.sno IN (SELECT sc.sno FROM sc WHERE sc.grade >= 80);

3. 避免重複插入（IGNORE / ON DUPLICATE KEY）

-- 忽略重複主鍵/唯一鍵
INSERT IGNORE INTO stu (sno, sname, dept)
SELECT s.sno, s.sname, s.dept FROM s ...;

-- 重複時更新
INSERT INTO stu (sno, sname, dept)
SELECT s.sno, s.sname, s.dept FROM s ...
ON DUPLICATE KEY UPDATE sname=VALUES(sname), dept=VALUES(dept);

效能提示

• 大量資料插入時，考慮 SET autocommit=0; 批次提交或使用 LOAD DATA INFILE。
• 確保來源查詢有適當索引，避免全表掃描。
• 目標表若有大量索引，插入前可 DISABLE KEYS（MyISAM）或暫時移除次要索引（InnoDB）。