MySQL 學習筆記：表約束詳解與分頁查詢最佳實踐

MySQL 學習筆記：表約束詳解與分頁查詢最佳實踐

表約束是數據完整性的守門人，分頁查詢是業務開發的高頻操作。本文通過實戰案例系統講解 MySQL 五大約束（主鍵、自增、唯一、非空、外鍵）的創建、驗證與陷阱，並給出生產級分頁查詢優化方案。

一、 五大核心約束實戰

1. PRIMARY KEY 主鍵：唯一標識，非空非重

CREATE TABLE `user2` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(34)
);

INSERT INTO `user2` (`name`) VALUES ('name1'), ('name2'), ('name3');
-- 自動生成 1,2,3

要點：

• 僅限整數類型
• 通常配合主鍵使用
• 重啟後可能重用已刪除的最大值（InnoDB 內存計數器），持久化需 8.0+

3. UNIQUE 唯一約束：業務唯一性保障

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

最佳實踐：所有列默認 NOT NULL + DEFAULT 值，除非業務明確需要「未知/缺失」語義。NULL 導致索引失效、統計函數忽略、三值邏輯陷阱。

5. FOREIGN KEY 外鍵：引用完整性守護者

-- 5.7/8.0.16 前：語法通過但不檢查
CREATE TABLE `user99` (`age` INT CHECK (`age` > 13));
INSERT INTO `user99` VALUES (99);  -- 5.7 成功插入，違反邏輯

-- 8.0.16+：真正生效
-- INSERT INTO `user99` VALUES (99); -- ERROR 3819: Check constraint violated

二、 分頁查詢最佳實踐

基礎語法

-- 學生表
-- 按語文降序，取第 3-4 名（offset=2, limit=2）
SELECT * FROM `student` 
ORDER BY `chinese` DESC 
LIMIT 2, 2;

高性能分頁優化（百萬級數據）

問題：`LIMIT 1000000, 20` 需掃描 100 萬行再丟棄，極慢。

方案 1：延遲關聯 / 子查詢定位主鍵（推薦）

-- 首頁
SELECT * FROM `student` ORDER BY `chinese` DESC, `id` DESC LIMIT 20;

-- 下一頁（記住上一頁最後一行的排序值 + 主鍵）
SELECT * FROM `student` 
WHERE (`chinese` < 85) OR (`chinese` = 85 AND `id` < 1005)
ORDER BY `chinese` DESC, `id` DESC LIMIT 20;

分頁參數化模板