MySQL 進階：CREATE TABLE 完整語法解析與選項詳解

CREATE TABLE 是 DDL 核心，選項極多。本文拆解標準語法、各子句含義、欄位定義細節、索引與約束定義，助你精準建表。

完整語法骨架

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
    [table_options]
    [select_statement]

關鍵選項解析

欄位定義 create_definition 詳細語法

col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
    [PRIMARY KEY] [COMMENT '...']
    [UNIQUE [KEY] | [INDEX] [index_name] (col_name, ...)]
    [FOREIGN KEY [index_name] (col_name, ...) REFERENCES ...]
    [CHECK (expr)]  -- 8.0.16+ 開始強制執行

核心屬性

• NOT NULL：預設為 NULL，明確聲明提升查詢優化器判斷。
• DEFAULT：數值預設 0，字串預設 ''，日期預設 '0000-00-00'，首個 TIMESTAMP 預設 CURRENT_TIMESTAMP。
• AUTO_INCREMENT：僅整數型，每表一個，需建索引（通常為 PK）。
• PRIMARY KEY：隱含 NOT NULL + UNIQUE + 索引。

索引類型

外鍵約束

CONSTRAINT fk_order_user FOREIGN KEY (user_id)
    REFERENCES user(id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT

僅 InnoDB 支援，ON DELETE/UPDATE 可選 CASCADE、SET NULL、RESTRICT、NO ACTION。

實戰範例：訂單表

CREATE TABLE `order` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '訂單ID',
    `user_id`     BIGINT UNSIGNED NOT NULL COMMENT '用戶ID',
    `order_no`    VARCHAR(32)     NOT NULL COMMENT '訂單號',
    `amount`      DECIMAL(10,2)   NOT NULL DEFAULT 0.00 COMMENT '金額',
    `status`      TINYINT         NOT NULL DEFAULT 0 COMMENT '0待付款 1已付款 2已發貨 3完成 4取消',
    `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_order_no` (`order_no`),
    KEY `idx_user_id` (`user_id`),
    CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單主表';

避坑指南

• 表名、欄位名用反引號 ` 包裹，防關鍵字衝突（如 order、group）。
• 字符集統一 utf8mb4（支援 Emoji），排序規則 utf8mb4_general_ci 或 utf8mb4_unicode_ci。
• 所有表預設 ENGINE=InnoDB，除非有特殊唯讀分析需求用 MyISAM。
• AUTO_INCREMENT 起始值可用 table_options 指定：AUTO_INCREMENT=10000。