MySQL 核心資料類型深度解析：TEXT、DATE、SET 與通配符實戰

前言：資料類型選擇決定系統上限

MySQL 提供約 25 種資料類型，大多直觀易懂。但 TEXT、DATE、SET 及通配符使用存在常見誤區，本文釐清核心概念並提供實戰指引。

TEXT：並非單一類型，而是家族統稱

誤區：許多教材將 TEXT 視為獨立類型。
真相：TEXT 是 LONG VARCHAR 或 MEDIUMTEXT 的別名，實際包含四個具體類型：

最佳實踐：根據實際最大長度選擇最小足夠的類型，節省儲存與記憶體。避免預設用 LONGTEXT 導致緩衝區浪費。

DATE：格式固定，運算強大

格式標準

YYYY-MM-DD (ISO 8601)，例：1999-12-08。PHP 取得當前日期：date("Y-m-d")。

日期運算：直接相減得天數差

SELECT DATEDIFF('2024-12-31', '2024-01-01');  -- 365
SELECT '2024-12-31' - '2024-01-01';           -- 365 (隱含轉換)

注意：MySQL 允許日期字串直接相減，但建議使用 DATEDIFF()、DATE_ADD()、DATE_SUB() 等標準函數，語意清晰且跨版本穩定。

SET：多值單欄的優雅解法

核心特性

• 單欄位可存儲 0 到 64 個預定義值 的任意組合
• 與 ENUM (單值、最多 65,535 個預定義) 互補
• 內部以位元組儲存，FIND_IN_SET()、& 位運算查詢極快

定義與插入

CREATE TABLE tags (
    id INT PRIMARY KEY,
    categories SET('tech','life','travel','food','sport') NOT NULL
);

INSERT INTO tags VALUES (1, 'tech,life');           -- 合法
INSERT INTO tags VALUES (2, 'tech,life,invalid');   -- 警告：'invalid' 被忽略
INSERT INTO tags VALUES (3, '');                    -- 空集合

常用查詢模式

-- 包含 'tech'
SELECT * FROM tags WHERE FIND_IN_SET('tech', categories);

-- 精確匹配集合 (順序無關)
SELECT * FROM tags WHERE categories = 'life,tech';

-- 交集查詢：同時包含 tech 和 life
SELECT * FROM tags WHERE FIND_IN_SET('tech', categories) 
  AND FIND_IN_SET('life', categories);

突破 64 個限制

若需超過 64 個預定義值，採用多 SET 欄位組合或改用關聯表（標準化設計），避免單表過度複雜。

通配符：三種符號、三種語境

關鍵規則

1. % 與 _ 必須搭配 LIKE (或 NOT LIKE) 使用，單獨出現無特殊含義
2. * 僅用於 SELECT 選欄位，不參與條件判斷
3. 查詢所有資料：SELECT * FROM table WHERE col LIKE '%' (兩者皆用)

效能提醒

• LIKE '%keyword%' 開頭有萬用字元 → 全表掃描，無法使用索引
• LIKE 'keyword%' 開頭固定 → 可用索引 (Range Scan)
• 全文搜尋需求請用 FULLTEXT 索引或 Elasticsearch

綜合實戰範例：文章標籤系統

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content MEDIUMTEXT NOT NULL,
    publish_date DATE NOT NULL,
    -- SET 存儲最多 64 個標籤，查詢高效
    tags SET('mysql','php','python','linux','docker','k8s','aws','security') DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查詢 2024 年發布且包含 'mysql' 標籤的文章
SELECT id, title, publish_date 
FROM articles 
WHERE publish_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND FIND_IN_SET('mysql', tags)
ORDER BY publish_date DESC;

總結

• TEXT 家族按容量分級，按需選擇最小足夠類型
• DATE 運算用標準函數，別依賴隱含轉換
• SET 適合固定、少量、多選場景，超過 64 個值改用關聯表
• 通配符三劍客分工明確：* 選欄、% 配 LIKE 模糊值、_ 配 LIKE 單字元
• 避免 LIKE '%...%' 導致全表掃描，大量模糊搜尋上全文索引或搜尋引擎