MySQL 學習筆記核心知識點：字符類型差異、枚舉集合、正則匹配、BLOB/TEXT、權限、表操作、快取控制與 COUNT 優化思考

整理 MySQL 核心知識點速查表，涵蓋字符類型選擇、ENUM/SET 差異、LIKE/REGEXP 匹配、BLOB/TEXT 特性、權限授予、表結構/數據拷貝、字段操作、查詢快取控制、COUNT 效能思考，適合開發者隨時翻閱鞏固。

1. CHAR vs VARCHAR

CHAR(5) 存 'bob' 實際存 'bob '（兩空格）。

2. ENUM vs SET

ALTER TABLE books ADD COLUMN transport SET ('hk','sh') NOT NULL AFTER count;
UPDATE books SET transport = 'hk,sh' WHERE id = 1;
-- 存儲為 'hk,sh'，可用 FIND_IN_SET('hk', transport) 查詢

3. LIKE vs REGEXP

-- 以 s 結尾
SELECT * FROM books WHERE name REGEXP 's$';

-- 以 w 或 h 開頭
SELECT * FROM books WHERE name REGEXP '^[wh]';

-- 精確 5 字符
SELECT * FROM books WHERE name REGEXP '^.{5}$';

-- 包含 window 後跟 s
SELECT * FROM books WHERE name REGEXP 'window[s]';

4. TEXT vs BLOB

• TEXT：不區分大小寫的 BLOB（字符類型，有字符集/校對規則）
• BLOB：二進制數據（無字符集，按字節比較）
• 兩者不能有 DEFAULT 值
• 超過最大長度會被截斷
• TEXT 類型：TINYTEXT (255), TEXT (64K), MEDIUMTEXT (16M), LONGTEXT (4G)
• BLOB 類型：對應大小同上

5. 權限授予速查

-- 全域所有權限（超管）
GRANT ALL ON *.* TO root@'10.10.11.91' IDENTIFIED BY 'root' WITH GRANT OPTION;

-- 特定庫讀寫
GRANT SELECT, INSERT, UPDATE, DELETE ON db.* TO 'user'@'%' IDENTIFIED BY 'pass';

6. 表結構拷貝

-- 方法 1：WHERE 1<>1 保證無數據
CREATE TABLE to_table AS SELECT * FROM from_table WHERE 1<>1;

-- 方法 2：指定欄位
CREATE TABLE to_table (id, name, num) AS SELECT id, name, num FROM from_table WHERE 1<>1;

7. 表數據拷貝

INSERT INTO to_table SELECT * FROM from_table WHERE 1;
INSERT INTO to_table (id, name, num) SELECT id, name, num FROM from_table WHERE 1;

8. 字段操作

-- 表頭增加主鍵自增列
ALTER TABLE table_name ADD COLUMN id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;

-- 表尾增加列
ALTER TABLE table_name ADD COLUMN new_col VARCHAR(50) DEFAULT '';

9. 查詢快取控制

-- 清除快取
RESET QUERY CACHE;
-- 或
FLUSH TABLES table_name;

-- 強制不使用快取
SELECT SQL_NO_CACHE COUNT(*) FROM table;

-- 強制使用快取（需 query_cache_type=2）
SELECT SQL_CACHE COUNT(*) FROM table_name;

-- my.cnf 設置按需快取
query_cache_type = 2  -- 只有 SQL_CACHE 才快取

10. 思考題：COUNT 效能比較

-- 表：主鍵 id INT(10) NOT NULL AUTO_INCREMENT
-- 哪個更快？
SELECT COUNT(0) FROM table WHERE id > 0;
SELECT COUNT(0) FROM table;

分析：

• MyISAM：表鎖統計行數，COUNT(*) 直接讀元數據極快；WHERE id>0 需掃描索引
• InnoDB：無統計行數，COUNT(*) 需全表/索引掃描；WHERE id>0 同樣掃描主鍵索引
• 結論：InnoDB 兩者接近（主鍵索引掃描）；MyISAM 無 WHERE 更快。實際以 EXPLAIN 為準。

基礎知識點是解決複雜問題的基石。定期回顧、實測驗證、建立個人知識庫，比死記硬背更有效。