您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 表設計優化與索引（十）：位元組緊湊儲存類型 BIT、SET、整數位運算完整解析與權限控制實戰

編輯：MySQL 資料庫相關知識

位元組緊湊儲存類型概覽

MySQL 提供 BIT、SET 及整數位運算三種機制，在單一欄位/數值中以位元級粒度儲存多個布林旗標或枚舉值，極大節省空間並簡化特定查詢。本質上皆為字串類型（底層儲存），操作語意各異。

BIT 類型：5.0+ 全新實現

特性	說明
語法	`BIT(M)`，M=1~64 位元，`BIT` 預設 1 位
儲存	MyISAM：多欄位打包儲存（17 個 BIT(1) ≈ 3 bytes） InnoDB/Memory：各欄位獨立，按最小整數對齊（1B/2B/4B/8B），不節省空間
檢索行為	字串語境：回傳二進位字串（如 `0x39` 顯示為 '9' ASCII）數值語境：回傳數值（`0x39` → 57）
字面值	`b'0101'`、`0b0101` (5.6+)
避坑	極易混淆：`SELECT bit_col` 顯示 '9'，`SELECT bit_col+0` 顯示 57。建議大多數應用避免使用 BIT

SET 類型：MySQL 專屬枚舉集合

特性	說明
語法	`SET('val1','val2',...)`，最多 64 個元素
儲存	位圖：每元素佔 1 bit，1~8 元素 1 byte，9~16 元素 2 bytes，以此類推
查詢函數	`FIND_IN_SET(str, set_col)`、`FIELD(str, set_col)`
優點	空間極致緊湊、語意清晰、內建查詢函數
缺點	修改結構需 ALTER TABLE（大表極慢）、難建立高效索引、元素值不可重複、排序按定義順序非字母順

整數位運算：通用替代方案

將 TINYINT/SMALLINT/INT/BIGINT 視為位元組集合，應用層定義常數映射：

-- 定義權限常數（應用層代碼）
const CAN_READ  = 1 << 0;  // 1
const CAN_WRITE = 1 << 1;  // 2
const CAN_DELETE = 1 << 2; // 4
const CAN_ADMIN = 1 << 3;  // 8

-- 建表
CREATE TABLE acl (
    perms TINYINT UNSIGNED NOT NULL DEFAULT 0
);

-- 插入：READ + DELETE = 1 + 4 = 5
INSERT INTO acl (perms) VALUES (5);

-- 查詢：擁有 READ 權限
SELECT * FROM acl WHERE perms & 1;  -- 位與運算

-- 查詢：同時擁有 READ 和 WRITE
SELECT * FROM acl WHERE perms & 3 = 3;  -- (1|2)=3

-- 更新：授予 ADMIN 權限
UPDATE acl SET perms = perms | 8 WHERE id = 1;

-- 更新：撤銷 WRITE 權限
UPDATE acl SET perms = perms & ~2 WHERE id = 1;

三方案完整對比

維度	BIT	SET	整數位運算
空間效率	MyISAM 高，InnoDB 低	極高（位圖）	高（取決於整數型別）
結構變更	ALTER TABLE（重建表）	ALTER TABLE（重建表）	無需 DDL，應用層常數調整即可
查詢便利性	低（語境陷阱）	中（專用函數）	低（位運算語法、需文檔常數）
索引支援	可建索引但效用有限	難建立高效索引	可建索引，但位運算條件通常不走索引
跨語言相容	差（MySQL 專有行為）	差（MySQL 專有）	優，位運算通用
適用場景	極少，僅 MyISAM 多欄位打包	固定枚舉集、極少變更、需語意查詢	權限控制(ACL)、功能開關、狀態機、標籤系統

實戰：ACL 權限控制系統設計

-- 方案 A：SET 類型（枚舉固定、語意清晰）
CREATE TABLE acl_set (
    user_id BIGINT UNSIGNED NOT NULL,
    perms SET('READ','WRITE','DELETE','ADMIN','EXPORT','IMPORT') NOT NULL DEFAULT '',
    PRIMARY KEY (user_id)
);
INSERT INTO acl_set (user_id, perms) VALUES (1001, 'READ,DELETE,EXPORT');
SELECT * FROM acl_set WHERE FIND_IN_SET('READ', perms);

-- 方案 B：整數位運算（擴展性強、跨語言、無 DDL 變更）
CREATE TABLE acl_bitwise (
    user_id BIGINT UNSIGNED NOT NULL,
    perms INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id)
);
-- 應用層常數映射，資料庫僅存數值
-- 1=READ, 2=WRITE, 4=DELETE, 8=ADMIN, 16=EXPORT, 32=IMPORT
INSERT INTO acl_bitwise (user_id, perms) VALUES (1001, 1+4+16); -- 21
SELECT * FROM acl_bitwise WHERE perms & 1;  -- 有 READ
SELECT * FROM acl_bitwise WHERE perms & 6 = 6; -- 有 WRITE+DELETE

-- 方案 C：關聯表（完全正規化、支援屬性、查詢靈活）
CREATE TABLE user_permissions (
    user_id BIGINT UNSIGNED NOT NULL,
    permission VARCHAR(32) NOT NULL,
    granted_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    granted_by BIGINT UNSIGNED,
    PRIMARY KEY (user_id, permission)
);
-- 適合：權限帶屬性（過期時間、授予者、範圍）、需 JOIN 查詢、審計追蹤