MySQL 學習筆記：表約束（主鍵/自增/唯一/非空/外鍵）與分頁查詢 LIMIT 完整指南

MySQL 表級/欄級約束系統整理：主鍵、自增、唯一、非空、外鍵定義與行為差異，以及 LIMIT 分頁語法、排序分頁、參數化分頁模板。

一、五大約束詳解

1. PRIMARY KEY 主鍵

• 唯一標識行，**非空 + 唯一**，每表最多一個
• 複合主鍵：PRIMARY KEY (col1, col2)

CREATE TABLE user1 (id INT PRIMARY KEY, name VARCHAR(32));
INSERT INTO user1 VALUES (1, 'hb');  -- OK
INSERT INTO user1 VALUES (1, 'hb');  -- ERROR 1062 Duplicate entry
INSERT INTO user1 (name) VALUES ('hb'); -- ERROR 1364 Field 'id' doesn't have default value

2. AUTO_INCREMENT 自增

• 整數類型專用，通常搭配主鍵
• 插入時省略或給 NULL/0 自動分配

CREATE TABLE user2 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(34));
INSERT INTO user2 (name) VALUES ('name1'), ('name2'), ('name3');
-- id 自動生成 1,2,3

3. UNIQUE 唯一約束

• 值不可重複，**允許多個 NULL**
• 可作業務唯一性鍵（手機、郵箱、身分證）

CREATE TABLE user3 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(34) UNIQUE);
INSERT INTO user3 (name) VALUES ('name3');  -- OK
INSERT INTO user3 (name) VALUES ('name3');  -- ERROR 1062
INSERT INTO user3 (name) VALUES (NULL);     -- OK
INSERT INTO user3 (name) VALUES (NULL);     -- OK（多個 NULL 允許）

4. NOT NULL 非空約束

• 欄位預設允許 NULL，加此約束強制非空

CREATE TABLE user4 (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) NOT NULL);
INSERT INTO user4 (name) VALUES (NULL); -- ERROR 1048 Column 'name' cannot be null

5. FOREIGN KEY 外鍵

• 維護引用完整性，**僅 InnoDB 支援**
• 指向主表 **主鍵或唯一索引**
• 類型、長度、字符集**必須一致**
• 值域：NULL 或主表存在的值
• 可自引用（指向本表主鍵）

-- 主表
CREATE TABLE dept (id INT PRIMARY KEY, name VARCHAR(32));
-- 從表
CREATE TABLE emp (
  id INT PRIMARY KEY,
  name VARCHAR(32),
  deptid INT,
  CONSTRAINT my_fk FOREIGN KEY (deptid) REFERENCES dept(id)
);

INSERT INTO dept VALUES (1, '技術部');
INSERT INTO emp VALUES (1, '張三', 1);      -- OK
INSERT INTO emp VALUES (2, '李四', NULL);   -- OK（允許 NULL）
INSERT INTO emp VALUES (3, '王五', 2);      -- ERROR 1452 Referenced key not found

MySQL **不支援 CHECK 約束**（語法通過但不生效），業務驗證請在應用層或觸發器實現。

二、分頁查詢 LIMIT

基本語法

SELECT * FROM 表 [WHERE 條件] LIMIT 偏移量, 行數;
-- 從 0 開始計數
LIMIT 5, 10   -- 第 6-15 行
LIMIT 5       -- 前 5 行（等同 LIMIT 0,5）

排序分頁

SELECT * FROM student ORDER BY chinese DESC LIMIT 3, 2;
-- 語文降序，取第 4-5 名

參數化分頁模板

-- pageNow: 當前頁碼（1-based），pageSize: 每頁條數
SELECT * FROM 表
WHERE 條件
[GROUP BY ... HAVING ...]
[ORDER BY ...]
LIMIT (pageNow - 1) * pageSize, pageSize;

深分頁優化參考

-- 子查詢定位 ID + 範圍掃描
SELECT * FROM t
WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 90000, 1)
LIMIT 100;

約束是資料完整性的守門員，分頁是業務呈現的基石，兩者皆為基本功必修課。