SQL 数据库行中的标志，最佳实践
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/124844/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Flags in a database rows, best practices
提问by Evan Teran
I am asking this out of a curiosity. Basically my question is when you have a database which needs a row entry to have things which act like flags, what is the best practice? A good example of this would be the badges on stack overflow, or the operating system field in bugzilla. Any subset of the flags may be set for a given entry.
我是出于好奇问这个的。基本上我的问题是当你有一个数据库需要一个行条目来拥有像标志一样的东西时，最佳实践是什么？一个很好的例子是堆栈溢出的标志，或 bugzilla 中的操作系统字段。可以为给定条目设置标志的任何子集。
Usually, I do c and c++ work, so my gut reaction is to use an unsigned integer field as a set of bits which can be flipped... But i know that isn't a good solution for several reasons. The most obvious of which is scale-ability, there will be a hard upper limit on how many flags I can have.
通常，我做 c 和 c++ 工作，所以我的直觉反应是使用无符号整数字段作为一组可以翻转的位......但我知道由于几个原因这不是一个好的解决方案。其中最明显的是可扩展性，我可以拥有多少个标志会有一个硬性上限。
I can also think of a couple of other solutions which scale better but would have performance issues because they would require multiple selects to get all the information.
我还可以想到一些其他解决方案，它们可以更好地扩展但会出现性能问题，因为它们需要多次选择才能获取所有信息。
So, what is the "right" way to do this?
那么，执行此操作的“正确”方法是什么？
采纳答案by Daniel Spiewak
If you really need an unbounded selection from a closed set of flags (e.g. stackoverflow badges), then the "relational way" would be to create a table of flags and a separate table which relates those flags to your target entities. Thus, users, flags and usersToFlags.
如果您真的需要从一组封闭的标志（例如 stackoverflow 徽章）中进行无限制的选择，那么“关系方式”将是创建一个标志表和一个单独的表，将这些标志与您的目标实体相关联。因此，users、flags 和 usersToFlags。
However, if space efficiency is a serious concern and query-ability is not, an unsigned mask would work almost as well.
然而，如果空间效率是一个严重的问题，而查询能力不是，那么无符号掩码几乎也能正常工作。
回答by Jeremiah Peschka
Generally speaking, I avoid bitmask fields. They're difficult to read in the future and they require a much more in-depth knowledge of the data to understanding.
一般来说，我避免位掩码字段。它们将来难以阅读，并且需要对数据有更深入的了解才能理解。
The relational solution has been proposed previously. Given the example you outlined, I would create something like this (in SQL Server):
之前已经提出了关系解决方案。鉴于您概述的示例，我将创建如下内容（在 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)
);
回答by Dan Udey
For many cases, it depends on a lot of things - like your database backend. If you're using MySQL, for example, the SET datatypeis exactly what you want.
在很多情况下，这取决于很多事情——比如你的数据库后端。例如，如果您使用 MySQL，则SET 数据类型正是您想要的。
Basically, it's just a bitmask, with values assigned to each bit. MySQL supports up to 64-bit values (meaning 64 different toggles). If you only need 8, then it only takes a byte per row, which is pretty awesome savings.
基本上，它只是一个位掩码，为每个位分配了值。MySQL 最多支持 64 位值（意味着 64 种不同的切换）。如果你只需要 8 个，那么每行只需要一个字节，这是非常棒的节省。
If you honestly have more than 64 values in a single field, your field might be getting more complicated. You may want to expand then to the BLOB datatype, which is just a raw set of bits that MySQL has no inherent understanding of. Using this, you can create an arbitrary number of bit fields that MySQL is happy to treat as binary, hex, or decimal values, however you need. If you need more than 64 options, create as many fields as is appropriate for your application. The downside is that is is difficult to make the field human readable. The BIT datatypeis also limited to 64.
如果您在单个字段中拥有超过 64 个值，那么您的字段可能会变得更加复杂。您可能想要扩展到 BLOB 数据类型，它只是 MySQL 没有固有理解的一组原始位。使用它，您可以创建任意数量的位字段，MySQL 很乐意将其视为二进制、十六进制或十进制值，但是您需要。如果您需要超过 64 个选项，请根据您的应用程序创建尽可能多的字段。缺点是很难使该字段具有人类可读性。该BIT数据类型也被限制为64。
回答by Seun Osewa
A Very Relational Approach
一种非常相关的方法
For databases without the set type, you could open a new table to represent the set of entities for which each flag is set.
对于没有设置类型的数据库，您可以打开一个新表来表示为其设置了每个标志的实体集。
E.g. for a Table "Students" you could have tables "RegisteredStudents", "SickStudents", TroublesomeStudents etc. Each table will have only one column: the student_id. This would actually be very fast if all you want to know is which students are "Registered" or "Sick", and would work the same way in every DBMS.
例如，对于表“Students”，您可以有表“RegisteredStudents”、“SickStudents”、TroublesomeStudents 等。每个表将只有一列：student_id。如果您只想知道哪些学生是“注册的”或“生病的”，并且在每个 DBMS 中的工作方式相同，那么这实际上会非常快。
回答by vog
If the flags have very different meanings and are used directly in SQL queries or VIEWS, then using multiple columns of type BOOLEANmight be a good idea.
如果标志具有非常不同的含义并且直接在 SQL 查询或视图中使用，那么使用多个类型的列BOOLEAN可能是一个好主意。
Put each flag into an extra column, because you'll read and modify them separately anyway. If you want to group the flags, just give their column names a common prefix, i.e. instead of:
将每个标志放入一个额外的列中，因为无论如何您将分别阅读和修改它们。如果你想对标志进行分组，只需给它们的列名一个共同的前缀，即而不是：
CREATE TABLE ... (
warnings INTEGER,
errors INTEGER,
...
)
you should use:
你应该使用：
CREATE TABLE ... (
warning_foo BOOLEAN,
warning_bar BOOLEAN,
warning_...
error_foo BOOLEAN,
error_bar BOOLEAN,
error_... BOOLEAN,
...
)
Although MySQL doesn't have a BOOLEAN type, you can use the quasi standard TINYINT(1) for that purpose, and set it only to 0 or 1.
尽管 MySQL 没有 BOOLEAN 类型，但您可以为此使用准标准 TINYINT(1)，并将其仅设置为 0 或 1。
回答by WW.
I would recommend using a BOOLEAN datatype if your database supports this.
如果您的数据库支持，我建议使用 BOOLEAN 数据类型。
Otherwise, the best approach is to use NUMBER(1) or equivalent, and put a check constraint on the column that limits valid values to (0,1) and perhaps NULL if you need that. If there is no built-in type, using a number is less ambiguous that using a character column. (What's the value for true? "T" or "Y" or "t")
否则，最好的方法是使用 NUMBER(1) 或等效项，并在列上放置一个检查约束，将有效值限制为 (0,1)，如果需要，可能还为 NULL。如果没有内置类型，则使用数字比使用字符列更没有歧义。（true 的值是多少？“T”或“Y”或“t”）
The nice thing about this is that you can use SUM() to count the number of TRUE rows.
这样做的好处是您可以使用 SUM() 来计算 TRUE 行的数量。
SELECT COUNT(1), SUM(ActiveFlag)
FROM myusers;
回答by Eevee
If there are more than just a few flags, or likely to be so in the future, I'll use a separate table of flags and a many-to-many table between them.
如果不仅仅是几个标志，或者将来可能会这样，我将使用单独的标志表和它们之间的多对多表。
If there are a handful of flags and I'm never going to use them in a WHERE, I'll use a SET() or bitfield or whatever. They're easy to read and more compact, but a pain to query and sometimes even more of a headache with an ORM.
如果有少量标志并且我永远不会在 WHERE 中使用它们，我将使用 SET() 或位域或其他任何东西。它们易于阅读且更紧凑，但查询起来很痛苦，有时甚至更令人头疼的是 ORM。
If there are only a few flags -- and only ever goingto be a few flags -- then I'll just make a couple BIT/BOOLEAN/etc columns.
如果只有几个标志-只有永远会是几个标志-那么我就做一对夫妇的BIT / BOOLEAN /等列。
