MySQL CREATE TABLE 完整語法詳解：從列定義到表選項、分區與外鍵

MySQL CREATE TABLE 完整語法詳解

CREATE TABLE 是數據庫架構的基石。MySQL 提供豐富的列屬性、索引定義、表選項、分區、外鍵約束等特性。本文系統梳理完整語法樹、各子句語義、實戰範例與避坑指南。

完整語法骨架

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] `tbl_name` 
  (create_definition, ...)
  [table_options]
  [partition_options]
  [select_statement];  -- CREATE TABLE ... SELECT

核心子句詳細解析

1. TEMPORARY 臨時表

• 僅當前會話可見，會話結束自動刪除。
• 不支持外鍵、分區、全文索引 (部分版本)。
• 主從複製不傳播 (Statement 格式下)。
• 常用於複雜 ETL 中間結果、會話級緩存。

2. IF NOT EXISTS

• 避免「表已存在」報錯，腳本冪等執行必備。
• 不檢查結構是否一致，僅檢查名稱。

3. create_definition (列定義、索引、約束) - 核心

col_name data_type [NOT NULL | NULL] [DEFAULT default_value] 
  [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] 
  [COMMENT 'string'] 
  [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] 
  [STORAGE {DISK|MEMORY}] 
  [reference_definition]  -- 列級外鍵
  
-- 或表級約束
[CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...)
[CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name, ...)
[CONSTRAINT [symbol]] INDEX [index_name] [index_type] (index_col_name, ...)
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition
CHECK (expr)  -- 8.0.16+ 真正生效

列屬性詳解

索引類型與選項

• index_type：USING BTREE (默認、通用) / USING HASH (Memory 引擎、精確查找) / USING RTREE (空間索引)。
• index_col_name：col_name [(length)] [ASC|DESC]。前綴長度用於長字串；DESC 8.0+ 支持 (降序索引優化 ORDER BY col DESC)。

外鍵 reference_definition 完整語法

REFERENCES `tbl_name` (index_col_name, ...)
  [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]  -- MySQL 僅支持 SIMPLE (默認)
  [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
  [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

• RESTRICT/NO ACTION：拒絕父表刪改 (默認)。
• CASCADE：級聯刪改子表行。
• SET NULL：子表外鍵列置 NULL (需列允許 NULL)。
• SET DEFAULT：置默認值 (極少用，InnoDB 不支持)。

4. table_options (表級選項)

ENGINE = InnoDB          -- 存儲引擎 (核心選項)
AUTO_INCREMENT = N       -- 自增起始值
CHARACTER SET = utf8mb4  -- 表默認字符集
COLLATE = utf8mb4_unicode_ci  -- 表默認排序規則
COMMENT = '表描述'        -- 表註釋
ROW_FORMAT = DYNAMIC     -- 行格式 (Dynamic/Compressed/Compact/Redundant)
KEY_BLOCK_SIZE = 8       -- 頁壓縮塊大小 (KB)
TABLESPACE = ts_name     -- 通用表空間
STATS_PERSISTENT = 1     -- 持久化統計信息
STATS_AUTO_RECALC = 1    -- 自動重新計算統計
PACK_KEYS = 0            -- MyISAM 壓縮鍵

5. partition_options (分區表，大數據量核心)

PARTITION BY 
  RANGE (expr) | LIST (expr) | HASH (expr) | KEY (col_list) | LINEAR HASH/KEY
  (partition_definition, ...)

partition_definition:
  PARTITION p_name VALUES LESS THAN (expr) | IN (value_list)
  [COMMENT '...'] [DATA DIRECTORY='...'] [INDEX DIRECTORY='...']
  [MAX_ROWS=N] [MIN_ROWS=N] [TABLESPACE=ts]
  [SUBPARTITION BY ... (...)]

• RANGE/LIST 按業務範圍/枚舉分區 (最常用，如按月/地區)。
• HASH/KEY 均勻分布 (無熱點)，但範圍查詢需全分區掃描。
• 分區表限制：主鍵/唯一鍵必須包含分區鍵；外鍵不支持；最大 8192 分區 (8.0 移除限制)。

實戰範例：標準業務表

CREATE TABLE `orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
  `order_no` CHAR(32) NOT NULL COMMENT '業務訂單號',
  `user_id` BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '狀態: 0待付款 1已付款 2已發貨 3已完成 4已取消',
  `total_amount` DECIMAL(19,4) NOT NULL DEFAULT 0.0000 COMMENT '訂單總金額',
  `currency` CHAR(3) NOT NULL DEFAULT 'CNY' COMMENT '幣種',
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間',
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新時間',
  `deleted_at` DATETIME(3) NULL DEFAULT NULL COMMENT '軟刪除時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_status` (`user_id`, `status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci 
  COMMENT='訂單主表'
  ROW_FORMAT=DYNAMIC
  PARTITION BY RANGE (YEAR(`created_at`)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
  );

CREATE TABLE ... SELECT (複製表結構+數據)

-- 僅複製結構 (WHERE 1=0)
CREATE TABLE `new_table` LIKE `old_table`;

-- 複製結構+數據 (無索引/約束/觸發器/分區/外鍵)
CREATE TABLE `new_table` AS SELECT * FROM `old_table` WHERE `status` = 1;

-- 指定列、別名、表達式
CREATE TABLE `user_summary` AS 
SELECT `id`, `name`, CONCAT(`first_name`, ' ', `last_name`) AS `full_name`
FROM `users`;

• 目標表不會繼承：主鍵、索引、外鍵、觸發器、分區、AUTO_INCREMENT、DEFAULT、COMMENT、字符集 (除顯式指定)。
• 僅複製列名、類型、數據。

常見避坑清單