MySQL CREATE TABLE ... SELECT 依查詢結果建立新表

MySQL CREATE TABLE ... SELECT 依查詢結果建立新表

MySQL 允許直接以 SELECT 結果集的結構與資料建立新表，結合 CREATE TABLE 與 INSERT 兩步為一，極適合建立彙總表、備份快照或測試資料集。

語法

CREATE TABLE 新表名
SELECT 欄位列表 FROM 來源表 WHERE 條件;

實戰範例

同樣使用學生/課程/選課三表：

-- 需求：將成績 ≥ 80 分學生的學號、姓名、系名建立新表 stu
CREATE TABLE stu
SELECT s.sno, s.sname, s.dept
FROM s
WHERE s.sno IN (SELECT sc.sno FROM sc WHERE sc.grade >= 80);

特性與限制

補充：建表後補齊索引與主鍵

CREATE TABLE stu ENGINE=InnoDB DEFAULT CHARSET=utf8
SELECT s.sno, s.sname, s.dept
FROM s
WHERE s.sno IN (SELECT sc.sno FROM sc WHERE sc.grade >= 80);

-- 事後添加主鍵與索引
ALTER TABLE stu ADD PRIMARY KEY (sno);
ALTER TABLE stu ADD INDEX idx_dept (dept);

應用場景

• 快速建立報表快照表：CREATE TABLE sales_2023_q1 SELECT ... FROM sales WHERE date BETWEEN '2023-01-01' AND '2023-03-31';
• 開發/測試環境抽取樣本資料。
• 資料遷移前驗證轉換邏輯。

注意

若 SELECT 返回大量資料，建表過程會長時間鎖定元資料，生產環境謹慎使用；可先 CREATE TABLE ... LIKE 再 INSERT ... SELECT 分步執行。