MySQL 數據表操作全指南：建表、字段類型、約束、索引與表類型詳解

一、建表基礎語法

CREATE TABLE <表名> (
    <字段定義>, <字段定義>, ...
) ENGINE=<表類型>;

表名、字段名遵循標準識別符規則；若包含保留字，需用反引號 ` 包裹。

二、字段類型體系

三、字段約束修飾符

1. NULL / NOT NULL

• NULL（預設）：允許插入空值。
• NOT NULL：禁止空值，插入時必須提供數據。
• 自增列、TIMESTAMP 列不適用此修飾符（插入 NULL 會觸發自動賦值）。

2. DEFAULT

• 指定字段默認值，如 DEFAULT 'unknown'、DEFAULT 0、DEFAULT CURRENT_TIMESTAMP。
• 若未顯式指定，MySQL 根據 NULL/NOT NULL 隱式設置：數值型 0、字符串 ''、時間戳當前時間、ENUM 第一個值。
• 注意：MySQL 5.0+ 若字段為 NOT NULL 且無 DEFAULT，插入時省略該字段將報錯。

3. AUTO_INCREMENT

• 僅適用於整數類型（通常為 INT/BIGINT），自動生成遞增序列。
• 一張表只能有一個自增列，且必須被定義為鍵（主鍵或唯一鍵）。

四、索引設計

優點

• 大幅加速 WHERE、ORDER BY、GROUP BY、JOIN 查詢。
• 支持多列組合索引（最多 15 列）。

缺點

• 佔用額外磁盤空間。
• 降低 INSERT、UPDATE、DELETE 寫入性能（需維護索引樹）。

適用場景

頻繁出現在 WHERE、ORDER BY、GROUP BY、JOIN ON 中的列。

創建/刪除語法

-- 建表時創建
CREATE TABLE t (..., INDEX idx_name (col1, col2), ...);

-- 事後添加
CREATE INDEX idx_name ON tbl_name (col1, col2);

-- 刪除
DROP INDEX idx_name ON tbl_name;

特殊索引類型

• UNIQUE 索引：值唯一，允許多個 NULL。
• FULLTEXT 全文索引：僅 MyISAM 支持（5.6+ InnoDB 也支持），用於自然語言/布爾模式全文檢索，配合 MATCH() AGAINST() 使用。忽略過短詞（默認 <4 字符）、停用詞、高頻詞（>50% 行出現）。

五、主鍵

• 唯一標識每一行，等價於 NOT NULL + UNIQUE。
• 自動創建聚簇索引（InnoDB）或唯一索引（MyISAM）。
• 建表時指定：PRIMARY KEY (id)。

六、外鍵

• 建立表間記錄級關聯，維護參照完整性。
• 僅 InnoDB 支持，其他引擎忽略定義。
• 外鍵列必須顯式建立索引（InnoDB 不自動創建）。
• 刪除/更新規則：CASCADE、SET NULL、RESTRICT、NO ACTION。
• 刪除外鍵：ALTER TABLE tbl DROP FOREIGN KEY fk_id。

七、表存儲引擎對比

八、複製表

-- 完整複製（含數據）
CREATE TABLE target SELECT * FROM source;

-- 部分列/條件複製
CREATE TABLE target SELECT col1, col2 FROM source WHERE ...;

-- 僅複製結構（空表）
CREATE TABLE target SELECT * FROM source WHERE 0=1;
-- 或
CREATE TABLE target LIKE source;

注意：上述方式不複製索引、主鍵、外鍵、AUTO_INCREMENT 等約束，需手動補充。

九、臨時表

• CREATE TEMPORARY TABLE tmp ...
• 生命週期 = 當前會話（連接斷開自動銷毀）。
• 不同會話可同名共存，互不干擾。
• 適用於複雜查詢中間結果暫存、ETL 中轉。