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

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

範例資料表
本文將藉以下是範例資料表來示範如何對數值欄位存取組合旗標:
CREATE TABLE [dbo].[User] ( 
[UserName] [varchar] (20) NOT NULL,
[Status] [int] NOT NULL DEFAULT 0
)

其中 Status 欄位可以儲存的整數常值以 2 的乘冪定義如下:
狀態整數二進位值
APPROVED10001
LOCKED_OUT20010
BANNED40100
DELETED81000

使用 2 的乘冪來定義整數常值,是為了確保在結合的常數中的個別旗標不會重疊。
DELETEDBANNEDLOCKED_OUTAPPROVED計算結果
00111+23
01011+45
11011+4+813

加入旗標值
這時我們所定義的常值便可透過位元 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

UserNameStatus
Nancy3
Laura15

移除旗標值
若要在數值中移除特定的旗標值,可以將數值與旗標值進行位元互斥 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


Share/Save/Bookmark

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

張貼留言