Microsoft 可以将三值字段存储在一位中吗？
我对 SQL/数据库完全一无所知，但我正在与一位从事大量数据库工作的朋友聊天，了解某些数据库如何使用“布尔”字段，该字段除了 true 和 false 之外还可以采用 NULL 值。
对此，他做了这样的评论：“值得赞扬的是，微软从未将这种字段称为布尔值，他们只是将其称为位。而且它是一个真正的位 - 如果您有八个或更少的位字段在一条记录中，只需要一个字节来存储它们。”
当然，这对我来说似乎是不可能的 - 如果该字段可以容纳三个值，那么您就不会将其中的八个值放入一个字节中。我的朋友同意这看起来很奇怪，但恳求他对底层内部结构一无所知，并说据他所知，从 SQL 端来看，这些字段可以保存三个值，而且它确实需要一个字节的存储空间。我想象我们中的一个人有一根电线交叉了。谁能解释一下这里到底发生了什么？
I'm completely ignorant of SQL/databases, but I was chatting with a friend who does a lot of database work about how some databases use a "boolean" field that can take a value of NULL in addition to true and false.
Regarding this, he made a comment along these lines: "To Microsoft's credit, they have never referred to that kind of field as a boolean, they just call it a bit. And it's a true bit - if you have eight or fewer bit fields in a record, it only requires one byte to store them all."
Naturally that seems impossible to me - if the field can hold three values you're not going to fit eight of them into a byte. My friend agreed that it seemed odd, but begged ignorance of the low-level internals and said that so far as he knew, such fields can hold three values when viewed from the SQL side, and it does work out to require a byte of storage. I imagine one of us has a wire crossed. Can anyone explain what's really going on here?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我建议阅读此内容以获得空存储的良好解释： How SQL Server 是否真的存储 NULL-s。简而言之，空/非空位存储在不同的位置，即该行的空位图。
来自文章：
因此，虽然 8 位列的实际值存储在 1 个字节中，但该行的 null 中还有额外的位指示该列是否为 NULL 的位图...所以取决于您的计数方式。为了完全准确，8位列使用2字节，只是分成2个不同的位置。
I recommend reading this for a good explanation of null storage: How does SQL Server really store NULL-s. In short, the null/not null bit is stored in a different place, the null bitmap for the row.
From the article:
So while the actual values for 8 bit columns are stored in 1 byte, there are extra bits in the row's null bitmap that indicate if that column is NULL or not...so depends on how you're counting. To be completely accurate, 8 bit columns use 2 bytes, just split up in 2 different locations.
空指示符是单独存储的，因此可空位实际上需要两个位。严格来说，“null”不是第三个值；而是第三个值。它就像一个占位符，表示“这里可能有一个值，但我们不知道它是什么。”因此，如果某个位为 null，则可以将其与 true 进行比较，比较会失败，但也可以将其与 false 进行比较，比较也会失败。
The null indicator is stored separately, so a nullable bit actually requires two bits. And strictly speaking, "null" isn't a third value; it's sort of a placeholder that says, "There could be a value here, but we don't know what it is." So if a bit is null, you can compare it to true and the comparison will fail, but you can also compare it to false and the comparison will fail.
你是对的。您可以将八个真/假值打包到一个字节中，但您仍然需要额外的存储空间来指示它是否为 NULL。仅用 28 来表示 38 种不同的状态是不可能的。
You are correct. You can pack the eight true/false values into a single byte, but you still need additional storage to indicate whether it is NULL or not. Representing 38 different states with only 28 is impossible.
你的朋友是对的，但同时也是错的。 BIT 字段可能被视为能够维护三个不同的值，但根据定义，NULL 表示不存在值。
此外，在位字段上允许 NULL 意味着该字段将使用 2 位（一位用于值，一位用于是否为 NULL）。但字段的 NULL 状态（NULL 位）存储在该行的位图中，而不是存储在给定列的确切内存空间中。
Your friend is right, but wrong at the same time. It's possible for a BIT field to be considered as being able to maintain three different values, but by definition NULL is the absence of a value.
Additionally, allowing NULL on the bit fields, means that 2 bits will be used for that field (one for the value, and one for if it is NULL or not). But the NULL state of the field (the NULL Bit) is stored in a bitmap for the row, and not in the exact memory space for the given column.
其他人已经说过，BIT 需要 2 位，而不是 1 位。
另一个经常被忘记的重要点是：SQL Server 中的 Bit 不是布尔型或逻辑数据类型；它是一种数据类型。它是数字（整数）数据类型。 "可以采用 1、0 或 NULL 值的整数数据类型" 。 Bit 仅支持数字运算符（<、>、+、-）。它不支持任何逻辑运算符（AND、OR、NOT 等）。
Others have already said that BIT requires 2 bits, not one.
Another important point that is often forgotten: Bit in SQL Server is not a Boolean or logic data type; it's a numeric (integer) data type. "An integer data type that can take a value of 1, 0, or NULL". Bit supports only numeric operators (<, >, +, -). It does not support any of the logic operators (AND, OR, NOT, etc).