萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL SET 欄位類型查詢與操作完全指南:位元運算、FIND_IN_SET 與實戰技巧

MySQL 的 SET 類型允許單一欄位儲存多個預定義值的組合(最多 64 個成員),底層以位元遮罩(整數)儲存,極適合標籤、權限、狀態等多值屬性。本文系統整理 SET 欄位的查詢、修改、位元運算技巧,並附上實戰範例。

SET 儲存原理速覽

假設定義 Status SET('ForSale','AuthSuccess','AuditSuccess','IntentionReached','SaleCanceled'),各成員對應的十進位與二進位值如下:

成員十進位值二進位值
ForSale10001
AuthSuccess20010
AuditSuccess40100
IntentionReached81000
SaleCanceled1610000

儲存值 9(二進位 1001)代表 ForSale 與 IntentionReached 同時為真。成員順序固定,新增成員不影響既有資料邏輯。

查詢 SET 欄位的多種方法

1. 字串比對:FIND_IN_SET() / LIKE

-- 推薦:精確匹配成員名稱,自動處理逗號分隔
SELECT * FROM tbl_name WHERE FIND_IN_SET('ForSale', Status) > 0;

-- 模糊匹配(可能誤判 'ForSale' 匹配 'ForSaleExtra')
SELECT * FROM tbl_name WHERE Status LIKE '%ForSale%';

2. 完整集合精確匹配(須符合定義順序)

-- 僅當 Status 同時包含 ForSale 與 IntentionReached,且無其他成員
SELECT * FROM tbl_name WHERE Status = 'ForSale,IntentionReached';

3. 數值直接比對(最高效、最穩健)

-- 查詢包含 ForSale(1) + IntentionReached(8) = 9 的記錄
SELECT * FROM tbl_name WHERE Status = 9;

-- 查詢包含 ForSale(1) 的記錄(位元 AND 運算)
SELECT * FROM tbl_name WHERE Status & 1 = 1;

修改 SET 欄位的位元運算技巧

設定單一/多個成員為真(位元 OR)

-- 設定 ForSale 為真
UPDATE tbl_name SET Status = Status | 1 WHERE Id = 333;

-- 同時設定 ForSale(1) + AuthSuccess(2) + IntentionReached(8) = 11
UPDATE tbl_name SET Status = Status | 1 | 2 | 8 WHERE Id = 333;

移除特定成員(位元 AND NOT)

-- 確認目前狀態為 7 (1+2+4),移除 ForSale(1) → 變為 6 (2+4)
UPDATE tbl_name SET Status = Status & ~1 WHERE Id = 333 AND Status = 7;

-- 直接設定為特定組合(覆蓋)
UPDATE tbl_name SET Status = 6 WHERE Id = 333; -- 僅保留 AuthSuccess + AuditSuccess

進階應用:權限系統設計範例

CREATE TABLE user_permissions (
  user_id INT PRIMARY KEY,
  perms SET('READ','WRITE','DELETE','ADMIN','EXPORT','IMPORT') NOT NULL DEFAULT ''
);

-- 授予 READ(1) + WRITE(2) = 3
UPDATE user_permissions SET perms = perms | 1 | 2 WHERE user_id = 1001;

-- 撤銷 WRITE 權限
UPDATE user_permissions SET perms = perms & ~2 WHERE user_id = 1001;

-- 查詢擁有 ADMIN(8) 權限的用戶
SELECT * FROM user_permissions WHERE perms & 8 = 8;

注意事項與最佳實踐

  • 成員上限 64 個,超過請改用 JSON 或關聯表。
  • 避免使用 LIKE '%value%' 進行精確查詢,易產生誤判;優先用 FIND_IN_SET 或位元運算。
  • 新增成員只能加在尾端(ALTER TABLE ... MODIFY col SET(...,'new')),中間插入會改變既有資料含義。
  • 索引支援有限:SET 欄位可建立索引,但 FIND_IN_SET、位元運算通常無法用到索引,大量資料建議冗餘布林欄位或用關聯表。
  • 嚴格模式下 插入非定義成員會報錯;非嚴格模式會存入空字串並產生警告。

善用 SET 類型的位元特性,能以極簡結構實現靈活的多值屬性管理,是權限、標籤、狀態機等場景的優雅選擇。

copyright © 萬盛學電腦網 all rights reserved