如何在資料庫存取組合旗標值

在程式設計當中，如果要建立可以組合列舉值清單的位元旗標列舉型別時，我們會以 2 的乘冪來定義列舉常數。事實上，在資料庫設計中我們也可以沿用位元旗標列舉的概念，使用單一整數型別的欄位來儲存多個整數常值的組合旗標。

範例資料表
本文將藉以下是範例資料表來示範如何對數值欄位存取組合旗標：

CREATE TABLE [dbo].[User] ( 
 [UserName] [varchar] (20) NOT NULL,
 [Status] [int] NOT NULL DEFAULT 0
)

其中 Status 欄位可以儲存的整數常值以 2 的乘冪定義如下：

狀態	整數	二進位值
APPROVED	1	0001
LOCKED_OUT	2	0010
BANNED	4	0100
DELETED	8	1000

使用 2 的乘冪來定義整數常值，是為了確保在結合的常數中的個別旗標不會重疊。

DELETED	BANNED	LOCKED_OUT	APPROVED	計算	結果
0	0	1	1	1+2	3
0	1	0	1	1+4	5
1	1	0	1	1+4+8	13

加入旗標值
這時我們所定義的常值便可透過位元 OR 運算來組合旗標值。

INSERT INTO [User] VALUES ('Nancy',1|2)
INSERT INTO [User] VALUES ('Robert',1|4)
INSERT INTO [User] VALUES ('Laura',1|2|4|8)
INSERT INTO [User] VALUES ('Andrew',0)
INSERT INTO [User] VALUES ('Janet',1)

當你要測試數值中是否包含特定旗標時，必須先將數值與特定的旗標值進行位元 AND 運算後，再將運算的結果與該旗標值進行相等比較。

SELECT * FROM [User] WHERE Status&2 = 2

UserName	Status
Nancy	3
Laura	15

移除旗標值
若要在數值中移除特定的旗標值，可以將數值與旗標值進行位元互斥 OR 運算。

UPDATE [User]
SET Status = Status^4
WHERE UserName = 'Robert' AND Status&4 = 4

值得注意的是，在對數值與特定的旗標進行位元互斥 OR 運算前，務必先確認該旗標已設定在數值中再執行，否則若貿然對不存在的旗標進行位元互斥 OR 運算將會得到反效果。

參考資料：
Storing Multiple Statuses Using an Integer Column
SQL Server: Updating Integer Status Columns
Integer Based Bit Manipulation - SQL

繼續閱讀...