使用位运算符进行表连接是否真的禁忌？

• 下载源代码 - 12.3 KB

引言

听说过 T-SQL 中的按位运算符吗？ 以下两个 SQL 语句将产生相同的结果。请注意，第一个语句中的 join 使用了 Ampersand '&'。这个按位运算符消除了对整个表 join 的需求。

Select * From bp.Party bp   Inner Join bp.PartyItem pi  _
         On bp.PartyItemFlags & pi.BitFlag = pi.BitFlag   Where bp.ID = 99

Select * From bp.Party bp   Inner Join bp.PartyToPartyItem bppi  _
         On  bp.ID = bppi.ID   Inner Join  bp.PartyItem pi  _
         On  bppi.BitFlag = pi.BitFlag   Where bp.ID = 99

请继续阅读，了解 按位 表 连接 何时以及如何适合您的数据模型！

背景

我听说一些开发人员表示希望利用按位表连接来简化数据管理，以维护和利用多对多关系。我听到的回应各种各样，从“不不不，别这样做——你不会强制执行参照完整性！！”到“有时为了获得足够的好处，打破规则是有意义的。”

所以我想，“这真的是光谱两端的唯一两个选择吗？我们不能两者兼得吗？我们不能在实现按位运算符用于 SQL 多对多关系的好处的同时，还能保持参照完整性吗？它们真的必须是相互排斥的吗？” 当我思考这个问题时，我确信“即使有一些权衡，也一定有一些体面的折衷方案！”

于是我决定研究一下。我发现这不仅是可能的，而且实际上并不难。它还可以被表达为一种关系表设计模式，在某些用例中是相当合理的。实际上存在许多潜在的好处，比我预期的还要多。这些好处包括简化查询、减少磁盘 I/O、减少记录数、提高特定查询的性能、更好地支持应用程序开发、更细粒度地强制执行多对多关系规则以及提高灵活性。

以下是我计划在本文中涵盖的内容

• 快速按位运算教程
• “生日派对公司”方法比较
  • 方法一：仅使用按位运算符的两个表查询
  • 方法二：使用传统桥接表获得相同输出
  • 方法三：探索用两个更小的表替换大型桥接表的新方法
  • 比较返回大型数据集的查询
  • 比较执行更新的方法
  • 添加新派对项目的影像
• 实际用例的考虑
• 好处和注意事项
• 结论

注意：本文提供的代码是为 SQL Server 定制的，但可以轻松地重用于任何现代主流 RDBMS。

注意：我已最小化使用通用表表达式（即使用“with”），以使查询更直接。请随时自行改进。

“快速”按位运算教程

注意：快速不一定意味着“非常简短”！如果您已经熟悉按位运算，请随时跳至“生日派对公司”。

注意：本教程仅涉及正数值的按位运算。

位（“位”就是“它”！)

“位”，是“二进制数字”的缩写，是计算的基本构建块。它是一个单个二进制数字，其值可以设置为 0 或 1 中的一个。在编程中，这些值通常存储在布尔变量中，其中值可能被称为 TRUE 或 FALSE、YES 或 NO、ON 或 OFF 或简单地称为 1 或 0。

二进制数字和位标志

当一个位用于管理定义项的两种可能状态时，它通常被称为“位标志”或有时简称为“标志”。位标志的值（0 或 1）表示该项的位是设置为 ON（1）还是 OFF（0）。

二进制数字包含一串二进制数字，其中每个数字都是一个位，可以设置为 0 或 1。因此，二进制数字或字符串中的每个位都可以用作唯一定义项的位标志。

二进制数字、位标志和整数

二进制数字和整数之间存在特殊关系。除了 0 之外，二进制数字中的每个数字都对应于 2 的连续幂的整数。

Binary     Decimal  Bit Position
00000000 =  0       No bits set
00000001 =  1       First bit set
00000010 =  2       Second bit set
00000100 =  4       Third bit set
00001000 =  8       Fourth bit set
00010000 = 16      Fifth bit set
00100000 = 32      Sixth bit set
01000000 = 64      Seventh bit set
etc.

这是我们按位运算需要牢记的基本整数集。当定义项分配给这些整数时，它们可能被称为许多术语，包括“位标志集”、“位标志列表”、“标志列表”或简称为“标志”。在本文中，我可能会使用“位标志”或简称为“位”来指代标志列表中的单个整数元素。

示例

Party Item Bit Flags
  0 = Nothing
  1 = Cake
  2 = Ice Cream
  4 = Happy Birthday Banner
  8 = Party Streamers
16 = Balloons
32 = Party Hats
64 = Pinatta

在应用程序代码中，这些有时可以通过一组常量来分配。在各种语言中，语法可能看起来相当不同，但应该可以识别类似的模式。

VB 示例

'Party Item Bit Flags
'---------------------
Const NONE As Int         =   0   'Nothing
Const CAKE As Int         =   1   'Cake
Const ICE_CREAM As Int    =   2   'Ice Cream
Const BANNER As Int       =   4   'Happy Birthday Banner
Const STREAMERS As Int    =   8   'Party Streamers
Const BALLOONS As Int     =  16   'Balloons
Const HATS As Int         =  32   'Party Hats
Const PINATTA As Int      =  64   'Pinatta

位标志编码整数

那么整数 3 和 5 呢？ 或者 6、7 和 9？ 使用我们的基本位标志集作为上下文，“中间”整数代表基本标志的组合。任何给定整数中存在的标志组合告诉我们哪些位是打开的，哪些是关闭的。当一个整数包含某个标志时，据说其代表位被设置为 ON，而当其不存在时，据说其被设置为 OFF。

例如：

• 整数 3 表示第一个（1）和第二个（2）位都设置为 ON：3 = 2 + 1（如果你想要蛋糕和冰淇淋）
• 整数 5 表示第一个（1）和第三个（4）位都设置为 ON：5 = 4 + 1（生日快乐横幅 + 蛋糕）

同样，每个整数（直到所有标志的总和）都可以分解为我们一个或多个位标志的集合。我们称从基本位标志派生的所有整数的完整集合为“位标志编码整数”。

使用我们的一些派对项目，以下显示了如何使用完整的整数列表来编码我们原始位标志列表的任何组合。

1 = 1                  Just Cake
2 = 2                  Just Ice Cream
3 = 2 + 1              Ice Cream + Cake
4 = 4                  Just Happy Birthday Banner
5 = 4 + 1              Happy Birthday Banner + Cake
6 = 4 + 2              Happy Birthday Banner + Ice Cream
7 = 4 + 2 + 1          Happy Birthday Banner + Ice Cream + Cake
8 = 8                  Just Party Streamers
9 = 8 + 1              Party Streamers + Cake
10 = 8 + 2             Party Streamers + Ice Cream
11 = 8 + 2 + 1         Party Streamers + Ice Cream + Cake
12 = 8 + 4             Party Streamers + Happy Birthday Banner
13 = 8 + 4 + 1         Party Streamers + Happy Birthday Banner + Cake
14 = 8 + 4 + 2         Party Streamers + Happy Birthday Banner + Ice Cream
15 = 8 + 4 + 2 + 1     Party Streamers + Happy Birthday Banner + Ice Cream + Cake
16 = 16                Just Balloons
17 = 16 + 1            Balloons + Cake
etc.

按位运算符

按位运算符是核心计算过程的基础，但与日常数学中使用的运算符不同。它们不一定难以使用，只是不同。按位运算符的美妙之处在于它们允许我们对位标志编码整数做一些很酷的事情。

• AND 运算符（SQL Server 中的 &）
  • 此运算符获取两个整数中设置为 ON 的位之间的交集，并返回一个第三个整数，其中只有这些位设置为 ON。
  • 示例:
    • 2 + 1 = 3；4 + 2 = 6；3 & 6 = 2；2 是 3 和 6 共同拥有的唯一位
    • 3 = 冰淇淋 + 蛋糕
    • 6 = 生日快乐横幅 + 蛋糕
    • 3 & 6 = 蛋糕 (2)
  • 示例:
    • 8 + 4 + 1 = 13；16 + 8 + 4 + 2 = 30；13 & 30 = 12；12 = 13 和 30 共有的位组合：8 + 4 = 12
    • 13 = 派对彩带 + 生日快乐横幅 + 蛋糕
    • 30 = 气球 + 派对彩带 + 生日快乐横幅 + 冰淇淋
    • 13 & 30 = 派对彩带 + 生日快乐横幅 (12)
• OR 运算符（SQL Server 中的 |）
  • OR 运算符获取两个整数中设置为 ON 的所有位的并集，并返回一个第三个整数，其中所有这些位都设置为 ON。
  • 示例
    • 2 + 1 = 3；4 + 2 = 6；3 | 6 = 7；在 3 和 6 中设置为 ON 的位是 1、2 和 4；4 + 2 + 1 = 7
    • 3 = 冰淇淋 + 蛋糕
    • 6 = 生日快乐横幅 + 蛋糕
    • 3 | 6 = 生日快乐横幅 + 蛋糕 + 冰淇淋 (7)
• NOT 运算符（SQL Server 中的 ~）
  • NOT 运算符反转整数中的位值，因此 ON 的位现在是 OFF，OFF 的位现在是 ON。单独来看，这对于我们的目的来说不太有用，但与 AND 运算符结合使用会非常方便。
• AND 加上 NOT 的组合（SQL Server 中的 &~）
  • 此运算符组合将第一个整数中设置为 ON 的位减去第二个整数中设置为 ON 的位，然后返回一个第三个整数，其中只有剩余的位设置为 ON。
  • 示例:
    • 8 + 4 + 2 = 14; 14 &~ 8 = 6
    • 14 = 派对彩带（我们要移除） + 生日快乐横幅 + 冰淇淋
    • 从 14 中移除 派对彩带 (8) 会剩下 生日快乐横幅 + 冰淇淋 (6)
• XOR 运算符（SQL Server 中的 ^）
  • XOR 运算符获取两个整数中设置为 ON 的位的并集减去它们的交集，并返回一个第三个整数，其中包含结果位设置为 ON。
  • 此操作更复杂，通常用于加密目的。我们在这里不处理 XOR 运算符。

SQL 的按位使用模式

以下是本文将涵盖的典型 SQL 使用模式

• & (AND)：& 运算符在确定位标志编码整数是否包含一个或多个位标志时特别有用。
  • 示例:
    • “Select 7 & 4” 返回 4，因为 7 = 1 + 2 + 4。
• | (OR)：| 运算符在向新的或现有的位标志编码整数添加位标志时非常有用。
  • 示例:
    • “Select 1 | 2 | 4 | 8” 返回 15，因为 15 = 1 + 2 + 4 + 8
    • “Select 15 | 16” 返回 31，因为 31 = 15 (1 + 2 + 4 + 8) + 16
  • 请注意，在生成位标志编码整数时，可以使用 ADD 运算符（+）代替 OR（|）运算符，但前提是只使用基本标志集的值，并且每个标志只使用一次。这在从头开始创建位标志组合时非常有用。区别在于，重复的位标志可以被 OR 多次而不会产生负面影响，而 ADD 运算符则不能。这是因为一旦一个位被设置为 ON，再次使用 OR 将其设置为 ON 没有影响，但使用 ADD 运算符会错误地将整数添加第二次，而第二次它不会被视为标志。
  • 示例:
    • “Select (1 | 2)”等于“Select (1 + 2)”但“Select (1 | 1)”不等于“Select (1 + 1)”
    • “Select (1 | 2 | 1 | 2)”等于“Select (1 | 2)”但它不等于“Select (1 + 2 + 1 + 2)”
• &~ (AND NOT)：&~ 运算符组合在从现有的位标志编码整数中删除一个或多个标志（如果存在）时特别有用。
  • 示例:
    • “Select 15 &~ 8” 从位标志编码整数 15 中删除位标志 8，返回 7。
    • “Select 31 &~ 6” 从位标志编码整数 31 中删除位标志 4 和 2（6 = 2 + 4），返回 25。
  • 请注意，减法运算符（-）可能可以用于删除位标志，但我强烈建议不要这样做。在使用减法之前，必须绝对确保位标志在整数中设置为 ON。以下是一个例子，说明您如何获得意外后果。
    • 任务：从位标志编码整数中移除位标志 32（如果设置为 ON）。
    • 使用 AND NOT (&~) 的示例:
      • “Select 31 &~ 32”正确返回 31，因为位标志 32 未包含在位标志编码整数 31 中，并且将已经为 OFF 的位设置为 OFF 没有影响。
    • 使用减法的示例（-）:
      • “Select 31 - 32”返回（-1），因为我们没有事先确保位标志编码整数 31 包含标志 32——我们本不应该执行此操作。
    • 对于实际整数值，很容易看出问题，但试想一下，在变量或字段上使用（-）而不是（&~）来处理位标志编码整数时，引入错误的容易程度。

使用按位运算符与传统方法比较

生日派对公司

现在，让我们设置一个数据库示例，以与传统方法进行比较，展示这些运算符如何用于表操作。

继续以我们的生日派对项目为例，假设您拥有一家承办生日派对的公司。您的业务相当不错，因为您有一个包含一百万个派对的数据库！

您提供十三种不同的派对项目，并且正在考虑再添加几个。对于任何给定的派对，包中都可以包含零个或多个派对项目的任何组合。您需要查询和更新哪些派对包含哪些派对项目。

首先，在您的 SQL Server 环境中，执行“Setup Script 1a”和“Setup Script 1b”来设置初始环境。

注意：使用提供的脚本，表“bp.Party”加载了一百万条记录。如果您不想加载这么多记录，请修改“Setup Script 1b.sql”以减少用于生成“bp.Party”记录的临时表中的名称数量。

/*-----------------*/
/* Setup Script 1a */
/*-----------------*/
Create Schema bp   -- bp for Birthday Parties - change as desired
GO
--------------------------------------------
-- Create table to hold all the bit flags --
--------------------------------------------
-- Drop Table bp.PartyItem
-- Truncate Table bp.PartyItem
Create Table bp.PartyItem(
BitFlag  Int NOT NULL
, ItemName Varchar(40) NOT NULL
)
GO

-- Add primary key
ALTER TABLE bp.PartyItem ADD  CONSTRAINT PK_PartyItem_BitFlag PRIMARY KEY CLUSTERED
(
 BitFlag ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
       SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, _
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- Ensure entries can only be powers of 2
ALTER TABLE bp.PartyItem
ADD CONSTRAINT chkFlagValue CHECK (BitFlag & (BitFlag - 1) = 0);
GO 
--------------------------------------------
-- Create table to hold all the bit flags --
--------------------------------------------
-------------------------------------------------
-- Create entries for the intial bit flag list --
-------------------------------------------------
-- Notes:
-- * Strive to keep flag lists short
-- * If you find that your list gets too long, you may want to consider 
--   logically splitting it
Insert bp.PartyItem(BitFlag, ItemName) Values(0, 'Nothing')
Insert bp.PartyItem(BitFlag, ItemName) Values(1, 'Cake')
Insert bp.PartyItem(BitFlag, ItemName) Values(2, 'Ice Cream')
Insert bp.PartyItem(BitFlag, ItemName) Values(4, 'Happy Birthday Banner')
Insert bp.PartyItem(BitFlag, ItemName) Values(8, 'Party Streamers')
Insert bp.PartyItem(BitFlag, ItemName) Values(16, 'Balloons')
Insert bp.PartyItem(BitFlag, ItemName) Values(32, 'Party Hats')
Insert bp.PartyItem(BitFlag, ItemName) Values(64, 'Pinatta')
Insert bp.PartyItem(BitFlag, ItemName) Values(128, 'Party Favors')
Insert bp.PartyItem(BitFlag, ItemName) Values(256, 'Confetti Poppers')
Insert bp.PartyItem(BitFlag, ItemName) Values(512, 'Party Blowers')
Insert bp.PartyItem(BitFlag, ItemName) Values(1024, 'Games')
Insert bp.PartyItem(BitFlag, ItemName) Values(2048, 'Prizes')

-- Additional flags we'll add later:
--Insert bp.PartyItem(BitFlag, ItemName) Values(4096, 'Clown')
--Insert bp.PartyItem(BitFlag, ItemName) Values(8192, 'Pizza')
-------------------------------------------------
-- Create entries for the intial bit flag list --
-------------------------------------------------
/*-----------------*/
/* Setup Script 1a */
/*-----------------*/

在执行上述脚本后，打开并执行“Setup Script 1b.sql”（它太长了，无法在此处发布）。

现在我们已经设置了初始环境，让我们来看看我们的第一种方法。

方法一：仅使用按位运算符的两个表查询

注意：方法一、方法二和方法三的查询输出应相同。

关联的 ERD

/*-----------------------------------------------------*/
/* Method 1: Use bitwise operators to simplify queries */
/*-----------------------------------------------------*/
-- What a sad birthday!
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
       bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyItem pi On bp.PartyItemFlags & pi.BitFlag = pi.BitFlag
Where bp.ID = 401409

-- Note: 0 is always returned when using the AND (&) operator 
-- when either value is 0 - just filter it or ignore it

-- Wow, what a party!
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
       bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyItem pi On bp.PartyItemFlags & pi.BitFlag = pi.BitFlag
Where bp.ID = 503808
And  pi.BitFlag <> 0  -- Filter out the 0 record if desired

-- This party is more affordable!
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
       bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyItem pi On bp.PartyItemFlags & pi.BitFlag = pi.BitFlag
Where bp.ID = 600120
And  pi.BitFlag <> 0  -- Filter out the 0 record if desired
/*-----------------------------------------------------*/
/* Method 1: Use bitwise operators to simplify queries */
/*-----------------------------------------------------*/

这是我听说过有人希望使用或实际使用过的方法，以简化多对多关系数据管理。

方法一的好处

• 允许以应用程序编码实践中常见的小空间存储大量标志的布尔信息
• 支持使用 AND、OR、NOT 和 XOR 按位运算符（SQL Server 中的“&”、“|”、“~”和“^”）来简化查询
• 为管理更改提供原地更新，最大限度地减少数据库磁盘 I/O

缺点

• 不强制执行参照完整性
• 通常不提供索引的好处

让我们继续方法二…

方法二：使用传统桥接表获得相同输出

关联的 ERD

方法二的附加设置

/*----------------*/
/* Setup Script 2 */
/*----------------*/
/*----------------------------*/
/* Traditional bridging table */
/*----------------------------*/
-- Drop Table bp.PartyToPartyItem
-- Truncate Table bp.PartyToPartyItem
Create Table bp.PartyToPartyItem(
ID   Int
, BitFlag Int
)
GO

-- Primary / Foreign Relationships
-- To Party
ALTER TABLE bp.PartyToPartyItem  WITH CHECK ADD  CONSTRAINT _
            [FK_PartyToPartyItem_Party] FOREIGN KEY([ID])
REFERENCES bp.Party ([ID])
GO

ALTER TABLE bp.PartyToPartyItem CHECK CONSTRAINT [FK_PartyToPartyItem_Party]
GO

-- To PartyItem
ALTER TABLE bp.PartyToPartyItem  WITH CHECK ADD  CONSTRAINT _
            [FK_PartyToPartyItem_PartyItem] FOREIGN KEY([BitFlag])
REFERENCES bp.PartyItem ([BitFlag])
GO

ALTER TABLE bp.PartyToPartyItem CHECK CONSTRAINT [FK_PartyToPartyItem_PartyItem]
GO

-- Load bridging table
-- Use our bitflag ANDing (&) trick between Party.PartyItemFlags 
-- and PartyItem.BitFlag to load the values (now that's handy!)
-- Truncate Table bp.PartyToPartyItem
-- 5,999,349 Rows
Insert bp.PartyToPartyItem(ID, BitFlag)
Select bp.ID
,  pi.BitFlag
From bp.Party bp
  Inner Join bp.PartyItem pi On bp.PartyItemFlags & pi.BitFlag = pi.BitFlag
Where ((bp.PartyItemFlags = 0 And pi.BitFlag = 0)  -- Include the 0 entry only 
                                                   -- if both are 0
Or  (bp.PartyItemFlags > 0 And pi.BitFlag > 0))    -- Otherwise, load the matching 
                                                   -- bit flags greater than 0
And Not Exists (Select ID From bp.PartyToPartyItem Where ID = bp.ID _
        And BitFlag = pi.BitFlag) -- Just so we don't try to load a record twice
/*----------------------------*/
/* Traditional bridging table */
/*----------------------------*/
/*----------------*/
/* Setup Script 2 */
/*----------------*/

好的，让我们来试试方法二！

/*-----------------------------------------------------------------------*/
/* Method 2 (Traditional: Use bridging table between data and code table */
/*-----------------------------------------------------------------------*/
-- What a sad birthday!
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
       bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyToPartyItem bppi On bp.ID = bppi.ID
  Inner Join bp.PartyItem pi On bppi.BitFlag = pi.BitFlag
Where bp.ID = 401409

-- Wow, what a party!
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
       bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyToPartyItem bppi On bp.ID = bppi.ID
  Inner Join bp.PartyItem pi On bppi.BitFlag = pi.BitFlag
Where bp.ID = 503808
-- And  pif.BitFlag <> 0  -- We already filtered out the 0 entries 
                          -- when we loaded the bridging table
Order By pi.BitFlag

-- This party is more affordable!
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
              bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyToPartyItem bppi On bp.ID = bppi.ID
  Inner Join bp.PartyItem pi On bppi.BitFlag = pi.BitFlag
Where bp.ID = 600120
-- And  pif.BitFlag <> 0  -- We already filtered out the 0 entries 
                          -- when we loaded the bridging table
Order By pi.BitFlag
/*-----------------------------------------------------------------------*/
/* Method 2 (Traditional: Use bridging table between data and code table */
/*-----------------------------------------------------------------------*/

方法二的好处

• 强制执行参照完整性
• 轻松支持索引

缺点

• 需要维护大量记录（在本例中为 5,999,349 条）
• 查询可能很快变得复杂（正如我们稍后将看到的）
• 条目不断插入和删除到桥接表中，增加了磁盘 I/O

现在让我们来看看方法三…

方法三：探索用两个更小的表替换大型桥接表的新方法

关联的 ERD

方法三的设置

/*----------------*/
/* Setup Script 3 */
/*----------------*/
/*--------------------------------------------------*/
/* Table to hold all possible bit flag combinations */
/*--------------------------------------------------*/
-- Drop Table bp.PartyItems
Create Table bp.PartyItems(
BitFlags Int NOT NULL
)
GO

-- Primary Key
ALTER TABLE bp.PartyItems ADD  CONSTRAINT PK_PartyItems_BitFlags PRIMARY KEY CLUSTERED
(
 BitFlags ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, _
       SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, _
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- Load the table
-- Create entries for all possible bit flag combinations (bfc)
-- Result: 4,096 records
-- Truncate Table bp.PartyItems
With bfc As (
 Select 0 As RowNumber
 UNION
 -- Just a trick to get a bunch of records with sequential integer values
 Select Row_Number() Over (Order By pi1.BitFlag) As RowNumber
 From bp.PartyItem pi1
   Cross Join bp.PartyItem pi2
   Cross Join bp.PartyItem pi3
   Cross Join bp.PartyItem pi4
)
Insert bp.PartyItems(BitFlags)   -- PartyItems stores all combinations 
                                 -- of bit flags from PartyItem
Select RowNumber
From bfc
Where RowNumber <= (Select Sum(BitFlag) From bp.PartyItem) -- We only need this 
                                                           -- many integer combinations
And Not Exists (Select * From bp.PartyItems Where BitFlags = bfc.RowNumber) -- Just so 
                                                 -- we don't try to load a record twice

-- Primary / Foreign Relationship between Party and PartyItems
ALTER TABLE bp.Party  WITH CHECK ADD  CONSTRAINT [FK_Party_PartyItems] _
                      FOREIGN KEY([PartyItemFlags])
REFERENCES bp.PartyItems ([BitFlags])
GO

ALTER TABLE bp.Party CHECK CONSTRAINT [FK_Party_PartyItems]
GO
/*--------------------------------------------------*/
/* Table to hold all possible bit flag combinations */
/*--------------------------------------------------*/
/*-----------------------------------------------------------------------------------*/
/* Bridging table between the all possible combinations table and the bit flag table */
/*-----------------------------------------------------------------------------------*/
-- Drop Table bp.PartyItemToPartyItems
Create Table bp.PartyItemToPartyItems(
BitFlag  Int
, BitFlags Int
)
GO

-- Primary / Foreign Relationships
-- To PartyItem
ALTER TABLE bp.PartyItemToPartyItems  WITH CHECK ADD  _
            CONSTRAINT [FK_PartyToPartyItems_PartyItem] FOREIGN KEY([BitFlag])
REFERENCES bp.PartyItem ([BitFlag])
GO

ALTER TABLE bp.PartyItemToPartyItems CHECK CONSTRAINT [FK_PartyToPartyItems_PartyItem]
GO

-- To PartyItems
ALTER TABLE bp.PartyItemToPartyItems  _
  WITH CHECK ADD  CONSTRAINT [FK_PartyToPartyItem_PartyItems] FOREIGN KEY([BitFlags])
REFERENCES bp.PartyItems ([BitFlags])
GO

ALTER TABLE bp.PartyItemToPartyItems CHECK CONSTRAINT [FK_PartyToPartyItem_PartyItems]
GO

-- Load the bridging table to create the connection between 
-- the base bit flags and all possible bit flag combinations
-- Result: 24,577 records
Insert bp.PartyItemToPartyItems(BitFlag, BitFlags)
Select pi.BitFlag
,  pis.BitFlags
From bp.PartyItem pi
  Inner Join bp.PartyItems pis On pi.BitFlag & pis.BitFlags = pi.BitFlag
Where ((pis.BitFlags = 0 And pi.BitFlag = 0)   -- Include the 0 entry if both are 0
Or  (pis.BitFlags > 0 And pi.BitFlag > 0))     -- Otherwise, only load the matching 
                                               -- bit flags greater than 0
And Not Exists (Select BitFlag From bp.PartyItemToPartyItems _
    Where BitFlag = pi.BitFlag And BitFlags = pis.BitFlags) -- Just so we don't try 
                                                            -- to load a record twice
/*-----------------------------------------------------------------------------------*/
/* Bridging table between the all possible combinations table and the bit flag table */
/*-----------------------------------------------------------------------------------*/
/*----------------*/
/* Setup Script 3 */
/*----------------*/

然后，使用传统查询测试方法三

/*-----------------------------------------------------------------*/
/* Method 3 (New Method): Add new combinations and bridging tables */
/*-----------------------------------------------------------------*/
-- What a sad birthday!
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, bp.LastName, _
              bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyItems pis On bp.PartyItemFlags = pis.BitFlags
  Inner Join bp.PartyItemToPartyItems pipis On bp.PartyItemFlags = pipis.BitFlags
  Inner Join bp.PartyItem pi On pipis.BitFlag = pi.BitFlag
Where bp.ID = 401409

-- Wow, what a party!
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
       bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyItems pis On bp.PartyItemFlags = pis.BitFlags
  Inner Join bp.PartyItemToPartyItems pipis On bp.PartyItemFlags = pipis.BitFlags
  Inner Join bp.PartyItem pi On pipis.BitFlag = pi.BitFlag
Where bp.ID = 503808
-- And  pi.BitFlag <> 0  -- We already filtered out the 0 entries 
                         -- when we loaded the second bridging table
Order By pi.BitFlag

-- This party is more affordable!
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
              bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyItems pis On bp.PartyItemFlags = pis.BitFlags
  Inner Join bp.PartyItemToPartyItems pipis On bp.PartyItemFlags = pipis.BitFlags
  Inner Join bp.PartyItem pi On pipis.BitFlag = pi.BitFlag
Where bp.ID = 600120
-- And  pi.BitFlag <> 0  -- We already filtered out the 0 entries 
                         -- when we loaded the second bridging table
Order By pi.BitFlag
/*-----------------------------------------------------------------*/
/* Method 3 (New Method): Add new combinations and bridging tables */
/*-----------------------------------------------------------------*/

我们添加了 28,673 条新记录，但现在使用了位标志值强制执行了参照完整性。这意味着我们不再需要原来的 5,999,349 行桥接表。

这是一个相当不错的权衡……我们用两个 28,673 条记录的表替换了一个 5,999,349 条记录的表！

• 5,999,349 - (4,096 + 24,577) = 5,970,676 条已保存记录

由于我们现在已经成功使用实际的按位标志值强制执行了参照完整性，因此，如果合理，开发人员可以毫无疑问地使用方法一中原始的更简化的查询！

方法三的好处

• 方法一的所有好处，外加…
• 强制执行参照完整性
• 提供使用按位运算符和/或传统方法构建查询的灵活性
• 轻松支持索引的使用以进行传统查询

缺点

• 增加了表总数的表
• 仅适用于某些多对多关系场景（并非真正的缺点，只是一个警告）

比较返回大型数据集的查询

现在让我们看看当我们尝试返回包含特定派对项目的所有派对时会发生什么。这就是我们开始看到复杂性权衡的地方。

任务 1：查找包含“蛋糕”、“冰淇淋”、“生日快乐横幅”、“派对彩带”、“气球”和“派对帽”的所有派对

/*---------------------------------------------------------------------------------------------------------------------------*/
/* Task: Find all the parties with 'Cake', 'Ice Cream', 'Happy Birthday Banner', 'Party Streamers', 'Balloons', 'Party Hats' */
/*---------------------------------------------------------------------------------------------------------------------------*/
-- Note: All the queries below return the same results
---------------------
-- Bit Flag Method --
---------------------
-- Using Integer Containing all Flags for the Items of Interest: 63 = 1 + 2 + 4 + 8 + 16 + 32
-- 15,625 Rows
Select * From bp.Party Where PartyItemFlags & 63 = 63
And PartyItemFlags >= 63  -- Added to partially leverage the index to improve performance
-- OR
-- Performing the OR on the fly
-- Note: You can OR (|) a bit flag number more than once while ending up 
-- with the same resulting number - that is because that bit is already ON
Select * From bp.Party Where PartyItemFlags & (1|2|4|8|16|32) = (1|2|4|8|16|32)
And PartyItemFlags >= (1|2|4|8|16|32)
-- OR
-- Using addition (+) of Flags instead of OR (|)
-- Note: Produces the same result from scratch as OR, 
-- but ONLY if you're using integers from the bit flag list 
-- AND DO NOT add a flag more than once
Select * From bp.Party Where PartyItemFlags & (1+2+4+8+16+32) = (1+2+4+8+16+32)
And PartyItemFlags >= (1+2+4+8+16+32)
-- OR
-- Using subqueries of bit flag values from PartyItem
Select *
From bp.Party
Where PartyItemFlags
  & (Select Sum(BitFlag) From bp.PartyItem Where ItemName _
     In ('Cake', 'Ice Cream', 'Happy Birthday Banner', 'Party Streamers', _
    'Balloons', 'Party Hats'))
  = (Select Sum(BitFlag) From bp.PartyItem Where ItemName In _
    ('Cake', 'Ice Cream', 'Happy Birthday Banner', 'Party Streamers', _
     'Balloons', 'Party Hats'))
And PartyItemFlags >= (Select Sum(BitFlag) From bp.PartyItem _
    Where ItemName In ('Cake', 'Ice Cream', 'Happy Birthday Banner', _
    'Party Streamers', 'Balloons', 'Party Hats'))
-- OR
-- Using correlated subquery and subquery from PartyItem
Select Distinct p.*
From bp.Party p
Where (Select Sum(BitFlag) & p.PartyItemFlags From bp.PartyItem _
       Where ItemName In ('Cake', 'Ice Cream', 'Happy Birthday Banner', _
       'Party Streamers', 'Balloons', 'Party Hats'))
  = (Select Sum(BitFlag) From bp.PartyItem Where ItemName _
     In ('Cake', 'Ice Cream', 'Happy Birthday Banner', 'Party Streamers', _
     'Balloons', 'Party Hats'))
And PartyItemFlags >= (Select Sum(BitFlag) From bp.PartyItem _
    Where ItemName In ('Cake', 'Ice Cream', 'Happy Birthday Banner', _
    'Party Streamers', 'Balloons', 'Party Hats'))
-- OR
-- Using individual references to PartyItem for each item interested in
Select a.*
From bp.Party a
,  bp.PartyItem b
,  bp.PartyItem c
,  bp.PartyItem d
,  bp.PartyItem e
,  bp.PartyItem f
,  bp.PartyItem g
Where a.PartyItemFlags & b.BitFlag = b.BitFlag And b.ItemName = 'Cake'
And  a.PartyItemFlags & c.BitFlag = c.BitFlag And c.ItemName = 'Ice Cream'
And  a.PartyItemFlags & d.BitFlag = d.BitFlag And d.ItemName = 'Happy Birthday Banner'
And  a.PartyItemFlags & e.BitFlag = e.BitFlag And e.ItemName = 'Party Streamers'
And  a.PartyItemFlags & f.BitFlag = f.BitFlag And f.ItemName = 'Balloons'
And  a.PartyItemFlags & g.BitFlag = g.BitFlag And g.ItemName = 'Party Hats'
---------------------
-- Bit Flag Method --
---------------------
------------------------
-- Traditional Method --
------------------------
-- Using the traditional 5,999,349 row bridging table 
-- without using any bitwise operations
-- Don't use SUM of BitFlags because that would be cheating per the traditional method!
Select p.*
From bp.Party p
  Inner Join
  ( Select ppi.ID
   From bp.PartyToPartyItem ppi
     Inner Join bp.PartyItem pi On ppi.BitFlag = pi.BitFlag _
     And pi.ItemName In ('Cake', 'Ice Cream', 'Happy Birthday Banner', _
     'Party Streamers', 'Balloons', 'Party Hats')
   Group By ppi.ID
   Having Count(ppi.ID) = (Select Count(BitFlag) From bp.PartyItem _
   Where ItemName In ('Cake', 'Ice Cream', 'Happy Birthday Banner', _
   'Party Streamers', 'Balloons', 'Party Hats'))
  ) fss On p.ID = fss.ID
------------------------
-- Traditional Method --
------------------------
/*---------------------------------------------------------------------------------------------------------------------------*/
/* Task: Find all the parties with 'Cake', 'Ice Cream', 'Happy Birthday Banner', 'Party Streamers', 'Balloons', 'Party Hats' */
/*---------------------------------------------------------------------------------------------------------------------------*/

任务 2：查找仅包含“蛋糕”、“冰淇淋”、“生日快乐横幅”、“派对彩带”、“气球”和“派对帽”的所有派对。在这里，我们看到了复杂性的降低和性能的提高。

/*--------------------------------------------------------------------------------------------------------------------------------*/
/* Task: Find all the parties with ONLY 'Cake', 'Ice Cream', 'Happy Birthday Banner', 'Party Streamers', 'Balloons', 'Party Hats' */
/*--------------------------------------------------------------------------------------------------------------------------------*/
---------------------
-- Bit Flag Method --
---------------------
-- Definitely better performance here versus using traditional bridging table
-- Using Integer Containing all Flags for the Items of Interest: 
-- 63 = 1 + 2 + 4 + 8 + 16 + 32
-- 245 Rows
Select * From bp.Party Where PartyItemFlags = 63
-- OR
-- Using subquery of bit flag values from PartyItem
Select * From bp.Party Where PartyItemFlags = (Select Sum(BitFlag) _
From bp.PartyItem Where ItemName In ('Cake', 'Ice Cream', _
'Happy Birthday Banner', 'Party Streamers', 'Balloons', 'Party Hats'))
---------------------
-- Bit Flag Method --
---------------------
------------------------
-- Traditional Method --
------------------------
-- Using the traditional 5,999,349 row bridging table without 
-- using any bitwise operations
-- Don't use SUM of BitFlags because that would be 
-- cheating per the traditional method!
Select p.*
From bp.Party p
  Inner Join
  ( Select ppi.ID
   From bp.PartyToPartyItem ppi
     Inner Join bp.PartyItem pi On ppi.BitFlag = pi.BitFlag _
     And pi.ItemName In ('Cake', 'Ice Cream', 'Happy Birthday Banner', _
     'Party Streamers', 'Balloons', 'Party Hats')
   Group By ppi.ID
   Having Count(ppi.ID) = (Select Count(BitFlag) From bp.PartyItem _
   Where ItemName In ('Cake', 'Ice Cream', 'Happy Birthday Banner', _
   'Party Streamers', 'Balloons', 'Party Hats'))
  ) fss On p.ID = fss.ID
Where (Select Count(BitFlag) From bp.PartyToPartyItem Where ID = p.ID) = _
      (Select Count(BitFlag) From bp.PartyItem Where ItemName In _
      ('Cake', 'Ice Cream', 'Happy Birthday Banner', 'Party Streamers', _
      'Balloons', 'Party Hats'))
------------------------
-- Traditional Method --
------------------------
/*--------------------------------------------------------------------------------------------------------------------------------*/
/* Task: Find all the parties with ONLY 'Cake', 'Ice Cream', 'Happy Birthday Banner', 'Party Streamers', 'Balloons', 'Party Hats' */
/*--------------------------------------------------------------------------------------------------------------------------------*/

比较执行更新的方法

那么这些方法在更新数据时如何比较呢？这正是按位运算符真正闪光的地方！

/*---------------------------------*/
/* Add / remove items from a party */
/*---------------------------------*/
------------------------------------------------------------------------------------
-- Bit Flag Method - In Place Updates Minimize Disk I/O for Day-to-Day Operations --
------------------------------------------------------------------------------------
-- Adding a party item to a record is trivial:
-- Note: Executing this multiple times will not add Pinatta more than once
Update bp.Party Set PartyItemFlags = PartyItemFlags | 64  -- Add Pinata
Where ID = 600120

-- Pinata has now been added only by updating the party record
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
       bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyItem pi On bp.PartyItemFlags & pi.BitFlag = pi.BitFlag
Where bp.ID = 600120
And  pi.BitFlag <> 0

-- Oops, that was a mistake, remove Pinata after all
Update bp.Party Set PartyItemFlags = PartyItemFlags & ~64  -- Remove Pinata
Where ID = 600120

-- What we really wanted to do was add Games
Update bp.Party Set PartyItemFlags = PartyItemFlags | 1024 -- Add Games instead
Where ID = 600120

-- We could have performed both operations at the same time
Update bp.Party Set PartyItemFlags = PartyItemFlags &~ 64 | 1024
Where ID = 600120

-- Check to see that Pinata is gone and Games is added
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
       bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyItem pi On bp.PartyItemFlags & pi.BitFlag = pi.BitFlag
Where bp.ID = 600120
And  pi.BitFlag <> 0
------------------------------------------------------------------------------------
-- Bit Flag Method - In Place Updates Minimize Disk I/O for Day-to-Day Operations --
------------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- Traditional Method - Ongoing Inserts / Deletes Incur Additional Disk I/O --
------------------------------------------------------------------------------
-- Add Pinatta
-- Don't try to execute this more than once!
Insert bp.PartyToPartyItem(ID, BitFlag)
Select ID = 600120
,  BitFlag = 64

-- Remove Pinatta
Delete bp.PartyToPartyItem Where ID = 600120 And BitFlag = 64

-- Add Games
Insert bp.PartyToPartyItem(ID, BitFlag)
Select ID = 600120
,  BitFlag = 1024

-- Check to see that Pinata is gone and Games is added
Select bp.ID, bp.GenderCode, bp.FirstName, bp.MiddleName, _
       bp.LastName, bp.PartyItemFlags, pi.BitFlag, pi.ItemName
From bp.Party bp
  Inner Join bp.PartyToPartyItem bppi On bp.ID = bppi.ID
  Inner Join bp.PartyItem pi On bppi.BitFlag = pi.BitFlag
Where bp.ID = 600120
-- And  pif.BitFlag <> 0  -- We already filtered out the 
                          -- 0 entries when we loaded the bridging table
Order By pi.BitFlag
------------------------------------------------------------------------------
-- Traditional Method - Ongoing Inserts / Deletes Incur Additional Disk I/O --
------------------------------------------------------------------------------
/*---------------------------------*/
/* Add / remove items from a party */
/*---------------------------------*/

添加新派对项目的影像

如果我们想添加新的派对项目怎么办？我们的新方法会产生一次性插入少量记录的成本，但可以持续避免传统桥接表每天的插入和删除需求。

/*---------------------*/
/* Add new party items */
/*---------------------*/
------------------------------------------------------
-- Needed for both Bit Flag and Traditional Methods --
------------------------------------------------------
-- Add new entries to the bit flag table:
Insert bp.PartyItem(BitFlag, ItemName) Values(4096, 'Clown')
Insert bp.PartyItem(BitFlag, ItemName) Values(8192, 'Pizza')
------------------------------------------------------
-- Needed for both Bit Flag and Traditional Methods --
------------------------------------------------------
--------------------------------
-- Needed for Bit Flag Method --
--------------------------------
-- For the bit flag method, there is a one time disk I/O cost 
-- of inserting records into our two additional tables
-- The payoff is that there is no longer a need to insert and 
-- delete from the large briding table on a day-to-day basis

-- Note: The following two inserts could be placed into an 
-- INSERT trigger on bp.PartyItem to ensure execution if desired

-- Add new party item combinations for the added bit flags
-- Note: Adding two entries in the flag table results in 
-- quadrupling the size of the combinations table, 
-- in this case from 4,096 records to 16,384
With bfc As (
 Select 0 As RowNumber
 UNION
 Select Row_Number() Over (Order By pi1.BitFlag) As RowNumber
 From bp.PartyItem pi1
   Cross Join bp.PartyItem pi2
   Cross Join bp.PartyItem pi3
   Cross Join bp.PartyItem pi4
)
Insert bp.PartyItems(BitFlags)
Select RowNumber
From bfc
Where RowNumber <= (Select Sum(BitFlag) From bp.PartyItem)
And Not Exists (Select * From bp.PartyItems Where BitFlags = bfc.RowNumber)

-- Add to the bridging table to ensure we have all the new combinations
-- Note: In this case, record count increases from 24,577 records to 114,689 records
Insert bp.PartyItemToPartyItems(BitFlag, BitFlags)
Select pi.BitFlag
,  pis.BitFlags
From bp.PartyItem pi
  Inner Join bp.PartyItems pis On pi.BitFlag & pis.BitFlags = pi.BitFlag
Where ((pis.BitFlags = 0 And pi.BitFlag = 0) -- Include the 0 entry if both are 0
Or  (pis.BitFlags > 0 And pi.BitFlag > 0))   -- Otherwise, only load the 
                                             -- matching bit flags greater than 0
And Not Exists (Select BitFlag From bp.PartyItemToPartyItems _
                Where BitFlag = pi.BitFlag And BitFlags = pis.BitFlags)
--------------------------------
-- Needed for Bit Flag Method --
--------------------------------
/*---------------------*/
/* Add new party items */
/*---------------------*/

实际用例的考虑

那么这可能在实际场景中用于何处？

此模式可能很有帮助的一个示例是存储和检索用户/角色权限，这些权限在应用程序中动态使用，特别是当必须在后台聚合多个用户角色的权限以确定有效权限时，例如，返回最大和/或最小权限。

考虑以下情况，其中 1 = 创建，2 = 读取，4 = 更新，8 = 删除，16 = 执行

• 用户角色 1：具有读取和执行 = 2 + 16 = 18
• 用户角色 2：具有创建、读取、更新和删除 = 1 + 2 + 4 + 8 = 15

有效权限

• 加法（OR）：18 | 15 = 31（创建、读取、更新、删除、执行）
• 交集（AND）：18 & 15 = 2（读取）

摘要

好处和注意事项

所以，这是我看到情况的分解。

一般好处

• 可以使用按位运算符来简化某些多对多关系查询
• 允许以应用程序编码实践中也常见的方法，在小空间存储大量标志的位信息
• 继续强制执行参照完整性
• 有潜力大大减少记录数
• 可以提高某些查询的性能
• 提供更大的灵活性
  • 支持使用按位运算符
  • 支持不熟悉按位运算符的查询设计者的传统查询方法
  • 支持任何有效的组合
• 通过提供原地更新而不是昂贵的插入和删除，最大限度地减少日常磁盘 I/O

对应用程序开发的好处

• 应用程序开发人员可以选择依赖数据库而不是硬编码常量来获取其位标志列表。
• 位标志表可用于数据库中，以强制执行应用程序中已存在的位标志和位编码值。
• 数据已采用所需格式，可执行应用程序逻辑可能需要的按位运算。

其他好处

• 组合表中的无效组合条目可以提前删除，从而使参照完整性能够轻松地强制执行有效组合，而无需诉诸更复杂的设计或仅仅依赖于应用程序中的 if/then/else 逻辑。

  • 这是比使用传统桥接表多出的一个好处。

注意事项

• 添加的位标志条目越多，从记录数量的角度来看，收益就越小——每添加一个条目，combinations 表中的记录数就会翻倍，相关桥接表中的记录数会翻倍以上。添加更多条目的临界点需要根据具体用例来确定。
• 参照完整性不会阻止将无效的项添加到 Combinations 表中（根据 Bit Flag 表的内容）。
  • 解决方案：向 Combinations 表添加一个 INSERT 触发器以执行验证，如果无效则取消插入。

结论

在合适的用例下，可以将位标志合并到数据库设计中，以更好地支持应用程序开发人员、强制执行参照完整性、可能减少记录数、提供一些特定的性能优势，并肯定地增加提供更多选项的灵活性。

我认为这是数据库设计工具箱中的一个附加工具和模式。它可能对某些数据库设计有帮助，但肯定不是解决所有多对多关系数据架构问题的答案。对于某些在多对多配置中使用列表的场景可能有利，列表越短、越静态，效果越好。如果您认为自己可能想尝试这种模式，我的建议是：做好尽职调查，仔细权衡一切，特别是未来增长的影响，并确保不要（编码）将自己置于困境！

玩得开心或 (|) 编码愉快！

历史

• 2017 年 7 月 14 日：初稿
• 2017 年 7 月 26 日：提交审阅