CREATE TABLE 完整語法骨架
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(
create_definition,
...
)
[table_options]
[partition_options]
[select_statement]
欄位定義 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|DEFAULT}]
[reference_definition] -- 外鍵
常用資料類型選型指南
| 類別 | 類型 | 儲存/範圍 | 適用場景 |
|---|
| 整數 | TINYINT | 1 byte, -128~127 / 0~255 | 布林、狀態碼、極小計數 |
| SMALLINT | 2 bytes, -32768~32767 | 小範圍計數、年齡 |
| INT / MEDIUMINT | 4/3 bytes, ~21億 / ~800萬 | 通用 ID、外鍵、計數器 |
| BIGINT | 8 bytes, ~9×10¹⁸ | 大流水號、分散式 ID、時間戳毫秒 |
| 定點/浮點 | DECIMAL(p,s) | p+2 bytes, 精確十進制 | 金額、價格、比率 (絕不使用 FLOAT/DOUBLE) |
| FLOAT | 4 bytes, ~7 位有效數字 | 科學計算、非精確測量 |
| DOUBLE | 8 bytes, ~15 位有效數字 | 高精度科學計算 |
| 字串 | CHAR(n) | n bytes, 定長補空格 | 固定長度代碼 (MD5、UUID、ISO 代碼) |
| VARCHAR(n) | L+1/2 bytes, 變長 | 通用字串、名稱、描述 |
| TEXT / MEDIUMTEXT / LONGTEXT | 64KB/16MB/4GB | 長文本、JSON 文檔、日誌 |
| BLOB 系列 | 同 TEXT 二進位 | 圖片、檔案、序列化物件 |
| 日期時間 | DATE | 3 bytes, 1000-01-01 ~ 9999-12-31 | 生日、截止日、無時間需求 |
| TIME | 3 bytes, -838:59:59 ~ 838:59:59 | 時間段、工時 |
| DATETIME | 8 bytes, 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 通用時間戳、業務時間 |
| TIMESTAMP | 4 bytes, 1970-01-01 ~ 2038-01-19 (UTC) | 自動更新欄位、跨時區應用 (注意 2038 年問題) |
| JSON | LONGTEXT 內部 | 結構化文檔、動態屬性、8.0+ 原生索引 | 延伸屬性、配置、日誌 |
約束與索引定義
-- 主鍵 (單欄)
id INT AUTO_INCREMENT PRIMARY KEY
-- 主鍵 (複合)
PRIMARY KEY (order_id, item_id)
-- 唯一索引
UNIQUE KEY uk_email (email)
UNIQUE KEY uk_order_user (order_id, user_id)
-- 普通索引
INDEX idx_status_created (status, created_at)
INDEX idx_user (user_id)
-- 外鍵 (僅 InnoDB)
CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE RESTRICT ON UPDATE CASCADE
-- 檢查約束 (8.0.16+)
CONSTRAINT chk_amount_positive CHECK (amount > 0)
表選項 table_options 關鍵參數
| 選項 | 範例 | 說明 |
|---|
| ENGINE | ENGINE=InnoDB | 儲存引擎，生產首選 InnoDB |
| CHARSET / COLLATE | DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci | 預設字元集/排序規則，支援 Emoji |
| AUTO_INCREMENT | AUTO_INCREMENT=10000 | 自增起始值 |
| COMMENT | COMMENT='訂單主表' | 表註釋，顯示於 SHOW TABLE STATUS |
| ROW_FORMAT | ROW_FORMAT=DYNAMIC | InnoDB 行格式，DYNAMIC/COMPRESSED 支援大欄位溢出頁 |
| DATA DIRECTORY / INDEX DIRECTORY | DATA DIRECTORY='/mnt/ssd/mysql' | MyISAM 指定資料/索引目錄 (InnoDB 用符號連結/表空間) |
實戰範例：電商訂單表 (含分區)
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
order_no VARCHAR(32) NOT NULL,
amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
status TINYINT NOT NULL DEFAULT 0 COMMENT '0=待付款 1=已付款 2=已發貨 3=完成 4=取消',
payment_method TINYINT DEFAULT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id, created_at), -- 分區鍵必須包含在 PK
UNIQUE KEY uk_order_no (order_no),
KEY idx_user_created (user_id, created_at),
KEY idx_status_created (status, created_at),
CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC
COMMENT='訂單主表'
PARTITION BY RANGE (YEAR(created_at))
SUBPARTITION BY HASH (user_id)
SUBPARTITIONS 8 (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
TEMPORARY 表與 SELECT 建表
-- 臨時表 (連線結束自動刪除，可見性僅限當前 Session)
CREATE TEMPORARY TABLE tmp_user_stats
SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amt
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id;
-- 複製表結構 (不複製資料、索引、約束、AUTO_INCREMENT)
CREATE TABLE orders_archive LIKE orders;
-- 複製表結構 + 資料 (不複製索引/約束/AI)
CREATE TABLE orders_bak AS SELECT * FROM orders;
驗證建表結果
SHOW CREATE TABLE orders\G
SHOW INDEX FROM orders;
SHOW TABLE STATUS LIKE 'orders'\G
建表即設計索引、分區、約束、字元集。遵循「類型最小化、NOT NULL 默認、外鍵謹慎、分區鍵入 PK、utf8mb4 標準化」五大原則，減少後期 DDL 變更成本，提升查詢穩定性。
