数据库行中的标志，最佳实践

Question

我出于好奇提出这个问题。基本上我的问题是，当你有一个需要一行条目拥有类似标志的数据库时，最佳实践是什么？一个很好的例子是stackoverflow上的徽章，或者bugzilla中的操作系统字段。对于给定的条目，任何子集的标志都可以设置。

通常，我做c和c ++工作，所以我的直觉是使用无符号整数字段作为可以翻转的位集合...但我知道这不是一个好的解决方案，原因有几个。其中最明显的是可扩展性，我可以拥有多少标志将有一个硬上限。

我也可以想到其他几种更好的可扩展解决方案，但它们会有性能问题，因为它们需要多个查询来获取所有信息。

那么，做这件事的“正确”方法是什么？

- Evan Teran

Answer 1

一般来说，我避免使用位掩码字段。因为它们在未来很难阅读，并且需要更深入地了解数据才能理解。

关系型的解决方案之前已经被提出过。 针对你所描述的例子，在 SQL Server 中，我会创建类似这样的表结构：

CREATE TABLE Users (
  UserId INT IDENTITY(1, 1) PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  EmailAddress VARCHAR(255)
);

CREATE TABLE Badges (
  BadgeId INT IDENTITY(1, 1) PRIMARY KEY,
  [Name] VARCHAR(50),
  [Description] VARCHAR(255)
);

CREATE TABLE UserBadges (
  UserId INT REFERENCES Users(UserId),
  BadgeId INT REFERENCES Badges(BadgeId)
);

- anon

---

我很想看到一个使用这个设置的插入和选择示例。 - Gakuo

Answer 2

如果您确实需要从一个封闭的标志集合（例如stackoverflow徽章）中进行无界限制的选择，则“关系型方式”是创建一个标志表和一个将这些标志与目标实体相关联的单独表。因此，有用户、标志和用户到标志之间的关系。

但是，如果空间效率是一个严重问题而可查询性不是，则无符号掩码几乎可以达到同样的效果。

- Daniel Spiewak

---

关于未签名掩码的警告。如果您必须编写过滤掉设置了特定位的行的查询，当行数变得很大时，性能会受到极大的影响，因为where子句中的逻辑and/or操作不能高效地使用索引。 - JohnFx

Answer 3

对于很多情况，它取决于很多因素 - 比如你的数据库后端。例如，如果你使用MySQL，那么SET数据类型恰好是你想要的。

基本上，它只是一个位掩码，每个位都分配了值。 MySQL支持最高64位值（即64个不同的切换）。如果你只需要8个，那么每行只需要一个字节，这可以节省很多空间。

如果你的单个字段中有超过64个值，那么你的字段可能变得更加复杂。你可能想将其扩展为BLOB数据类型，这只是一组MySQL没有固有理解的原始位。使用此方法，你可以创建任意数量的位字段，MySQL可以将其视为二进制、十六进制或十进制值，以满足你的需求。如果你需要超过64个选项，请根据你的应用程序需要创建适当数量的字段。缺点是很难使该字段可读。BIT数据类型也仅限于64。

- Dan Udey

---

不是我会做的，但这是一个很好的位掩码解决方案的实现。 - Daniel Spiewak

SET datatype的链接已经失效。这里提供MySQL 8.0关于SET的文档链接：https://dev.mysql.com/doc/refman/8.0/en/set.html - Sgnl

Answer 4

非常关联的方法

对于没有集合类型的数据库，您可以打开一个新表来表示每个标志设置的实体集。

例如，对于“学生”表，您可以有“注册学生”，“生病学生”，“有问题的学生”等表。每个表只有一列：student_id。如果您想知道哪些学生已经“注册”或“生病”，这实际上会非常快速，并且在每个DBMS中都可以使用相同的方法。

- Seun Osewa

---

当您想查询SickStudents时，如果病人很少，那确实非常快。如果您要像填充实体对象这样的操作，则会增加额外的联接。 - WW.

Answer 5

如果标志的含义非常不同，并且直接用于SQL查询或视图中，则使用多个类型为BOOLEAN的列可能是一个好主意。

将每个标志放入一个额外的列中，因为您无论如何都会单独读取和修改它们。如果要对标志进行分组，请给它们的列名加上一个共同的前缀，例如，而不是：

CREATE TABLE ... (
    warnings INTEGER,
    errors   INTEGER,
    ...
)

你应该使用：

你应该使用：

CREATE TABLE ... (
    warning_foo BOOLEAN,
    warning_bar BOOLEAN,
    warning_...
    error_foo   BOOLEAN,
    error_bar   BOOLEAN,
    error_...   BOOLEAN,
    ...
)

虽然MySQL没有BOOLEAN类型，但是您可以使用准标准的TINYINT（1）来实现此目的，并将其仅设置为0或1。

- vog

Answer 6

我建议如果你的数据库支持，可以使用BOOLEAN数据类型。

否则，最好的方法是使用NUMBER(1)或等效类型，并在列上放置一个检查约束，限制有效值为(0,1)，如果需要还可以包括NULL。如果没有内置类型，则使用数字比使用字符列更不含糊。（真的值是多少？"T"或"Y"或"t"）

这样做的好处是，您可以使用SUM()函数来计算TRUE行的数量。

SELECT COUNT(1), SUM(ActiveFlag)
FROM myusers;

- WW

Answer 7

如果有很多标记，或者未来可能会有很多标记，我会使用单独的标记表和它们之间的多对多关系表。如果只有一些标记，并且永远不会在WHERE语句中使用它们，则我会使用SET()或位字段等。它们易于阅读且更紧凑，但查询起来很麻烦，有时甚至使用ORM也更加头痛。如果只有很少的标记 - 而且只会有很少的标记 - 那么我将只创建几个BIT/BOOLEAN等列。

- Eevee

Answer 8

我在思考如何在数据库中存储位掩码标志（类似于OP最初使用整数的方式）时发现了这个。其他答案都是有效的解决方案，但我认为值得一提的是，如果您选择直接将位掩码存储在数据库中，则可能不必将自己逼入可怕的查询问题。如果您正在开发使用位掩码的应用程序，并且真的希望将它们作为一个整数或字节列存储在数据库中以获得方便性，请继续执行。以后，您可以编写一个小实用程序，从主工作表中的位掩码生成另一个标志表（以您选择的任何行/列模式）。然后，您仍然可以在计算/派生表上执行普通的SQL查询。这样，您的应用程序就可以方便地读取/写入位掩码字段/列，但如果以后需要深入研究数据，仍然可以使用SQL。

- JamesHoux