MySQL 建表與刪表操作全攻略：從基礎語法到臨時表、LIKE 克隆、SELECT 派生的實戰技法

前言：建表是數據建模的「落筆生花」

CREATE TABLE 看似基礎，實則蘊含「存儲引擎選型」「字符集規範」「主鍵/索引佈局」「臨時表隔離」「LIKE 克隆」「SELECT 派生」等工程化決策。本文系統梳理全語法變體、核心選項語義、實戰建模模板與避坑指南，助您寫出**規範、高效、可演進**的建表腳本。

一、基礎建表語法與核心選項

1. 最簡形式

CREATE TABLE person (
    number INT(11),
    name VARCHAR(255),
    birthday DATE
);

2. 生產級規範模板（強烈建議全量顯式聲明）

CREATE TABLE IF NOT EXISTS `person` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
    `number` INT(11) NOT NULL DEFAULT 0 COMMENT '業務編號',
    `name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '姓名',
    `birthday` DATE DEFAULT NULL COMMENT '出生日期',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_number` (`number`),
    KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='人員信息表';

• 反引號包裹：識別符轉義，防關鍵字衝突。
• NOT NULL + DEFAULT：避免 NULL 語義陷阱，利於索引與優化器。
• 顯式 COMMENT：數據字典自文檔化。
• utf8mb4：完整 Unicode 支援（含 Emoji），棄用 utf8。
• InnoDB：事務、行鎖、MVCC、崩潰恢復，生產首選。

3. IF NOT EXISTS：幂等部署守護神

CREATE TABLE IF NOT EXISTS person2 (...);

• 部署腳本可重複執行不報錯。
• 注意：不校驗現有表結構與定義是否一致；CREATE ... SELECT 時無論表是否存在，SELECT 結果都會插入。

二、進階建表技法

1. 臨時表：會話級隔離沙箱

CREATE TEMPORARY TABLE temp_person (
    number INT(11),
    name VARCHAR(255),
    birthday DATE
);

• 僅當前連接可見，斷開自動銷毀。
• 不同連接可同名共存，互不干擾；遮蓋同名永久表（永久表隱藏至臨時表銷毀）。
• 需 CREATE TEMPORARY TABLES 權限。
• 適用場景：複雜報表中間結果、會話級緩存、ETL 階段數據清洗。

2. LIKE 克隆：結構完美複製（含索引/約束/屬性）

CREATE TABLE new_tbl LIKE orig_tbl;

• 複製：列定義、索引、外鍵、字符集、引擎、注释、AUTO_INCREMENT 值。
• **不複製**：數據、觸發器、分區定義（5.7+ 支援）、權限。
• 極適合：建表備份、分表模板、藍綠部署結構同步。

3. SELECT 派生：結構+數據一鍵複製 / 列投影重塑

-- 全量複製結構與數據（索引/約束/屬性**不複製**，僅列定義+數據）
CREATE TABLE new_tbl SELECT * FROM orig_tbl;

-- 指定列投影+別名重塑
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

-- 列定義在前，SELECT 派生列在後（附加在右側）
CREATE TABLE bar (m INT) SELECT n FROM foo;
-- 結果：bar(m, n)  m 為 NULL(無默認值), n 來自 foo

• 核心差異：LIKE 克隆骨架，SELECT 複製血肉（但丟失索引/約束）。
• 生產複製表標準流程：CREATE TABLE new LIKE old; INSERT INTO new SELECT * FROM old;（保留索引/約束）。

4. 完整約束與索引定義範例

CREATE TABLE `people` (
    `peopleid` SMALLINT(6) NOT NULL AUTO_INCREMENT,
    `firstname` CHAR(50) NOT NULL,
    `lastname` CHAR(50) NOT NULL,
    `age` SMALLINT(6) NOT NULL,
    `townid` SMALLINT(6) NOT NULL,
    PRIMARY KEY (`peopleid`),
    UNIQUE KEY `uk_fname_lname` (`firstname`, `lastname`),
    KEY `idx_fname_lname_age` (`firstname`, `lastname`, `age`)
);

• 主鍵：peopleid 自增。
• 唯一索引：firstname+lastname 業務唯一鍵。
• 普通聯合索引：支援 WHERE firstname=? AND lastname=? AND age=? 及最左前綴查詢。

三、查看表結構：驗證與文檔化

-- 顯示建表語句（含引擎、字符集、AUTO_INCREMENT 等）
SHOW CREATE TABLE person;

-- 顯示所有列詳細信息（類型、排序規則、NULL、鍵、默認值、額外屬性、權限、註釋）
SHOW FULL COLUMNS FROM person;

四、刪表操作：謹慎與幂等

-- 直接刪除（表不存在報錯）
DROP TABLE tbl_name;

-- 幂等刪除（表不存在不報錯，部署腳本推薦）
DROP TABLE IF EXISTS tbl_name;

-- 同時刪除多表
DROP TABLE IF EXISTS t1, t2, t3;

• DROP TABLE 釋放磁碟空間、移除數據字典條目、失效相關權限/觸發器/視圖引用。
• InnoDB innodb_file_per_table=ON 時，對應 `.ibd` 文件同步刪除。

五、避坑指南與最佳實踐

六、實戰部署腳本模板（Flyway/Liquibase 風格）

-- V1.0.1__create_person_table.sql
CREATE TABLE IF NOT EXISTS `person` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
    `number` INT(11) NOT NULL DEFAULT 0 COMMENT '工號',
    `name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '姓名',
    `birthday` DATE DEFAULT NULL COMMENT '生日',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_number` (`number`),
    KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='人員主檔';

-- 回滾腳本
-- DROP TABLE IF EXISTS `person`;

結語

建表非一時之舉，乃數據架構基石。從「顯式約束」到「utf8mb4 統一」，從「LIKE 克隆骨架」到「SELECT 派生血肉」，從「臨時表隔離」到「幂等部署腳本」——每一個細節都在為**數據質量、演進靈活性、運維可控性**鋪路。願本文攻略助您在建模筆下，落筆生花、行穩致遠。