位掩码中，整数和bit(n)数据类型之间有什么区别吗？

Question

我正在使用PostgreSQL数据库中的一个表，其中包含多个布尔列来确定某些状态（例如已发布，可见等）。 我想创建一个单一状态列，以位掩码的形式存储所有这些值以及可能的新值。 在这种情况下，integer和bit(n)之间有什么区别吗？

由于该表存储用户通过Web界面创建的对象，因此它将是一个相当大的表。 因此，我认为我必须对此列使用（部分）索引。

- Igor Zinov'yev

Answer 1

如果您只有几个变量，我建议保持单独的布尔列。

• 索引很容易。特别是表达式索引和部分索引。
• 查询条件易于编写、阅读且有意义。
• 布尔列占用1字节（没有对齐填充）。对于少量变量，这占用最少的空间。
• 与其他选项不同，布尔列允许单独位使用NULL值，如果需要的话。如果不需要可以始终定义列NOT NULL。

如果您有超过一手的变量但不超过32个，一个整数列可能最适合。(或者bigint适用于高达64个变量。)

• 在磁盘上占用4个字节(可能需要对齐填充，取决于前面的列)。
• 非常快的索引确切匹配(=运算符)。
• 处理单个值可能比varbit或boolean慢/不方便。

如果您有更多的变量，或者想要大量操作值，或者没有巨大的表格或磁盘空间/RAM不是问题，或者不确定选择什么，我会考虑bit(n)或bit varying(n)(简称：varbit(n))。

• 占据至少5个字节（对于非常长的字符串为8个字节）加上每8位一组的1个字节（向上取整）。
• 您可以直接使用位字符串函数和运算符，以及某些标准SQL函数。

对于只有3个比特信息的情况，单独的boolean列需要3个字节，一个integer需要4个字节（可能还需要对齐填充），而一个bit string则需要6个字节（5 + 1）。

对于32个比特的信息，一个integer仍然需要4个字节（+ 填充），一个bit string则需要9个字节（5 + 4），而boolean列占用32个字节。

为了进一步优化磁盘空间，您需要了解PostgreSQL的存储机制，特别是数据对齐。 更多相关答案。

这篇关于如何转换类型布尔值、位(n)和整数的答案也可能有所帮助。

- Erwin Brandstetter

---

非常感谢您的解释，这正是我所需要的！我想我会使用 integer 列。 - Igor Zinov'yev

如何将整数用于像位一样的多个独立列？ - clarkk

@clarkk：在二进制表示中，整数是由允许的数字0和1组成的序列。（这也是数字系统中存储任何内容的方式。）每个数字都可以（滥用）用来存储一个独立的二进制信息。基本的数学运算符可以用来读取/写入各个位置。（Postgres无论如何都会为任何写入的更改写入新的行版本，所以几乎没有额外的开销。）点击给定链接了解一些基本技巧。 - Erwin Brandstetter

Answer 2

随着PostgreSQL（从版本12开始）中GENERATED列的出现，您可以像这样做（下面的所有代码都可以在fiddle链接中找到）：

基础表：

CREATE TABLE test
(
  t_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
  data TEXT,
  bitmask VARBIT(9)
);

但是，有了 GENERATED 列，现在你可以这样做：

CREATE TABLE test
(
  t_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
  data TEXT,
  bitmask VARBIT(9), -- choose 9 because it's not 8, to show that you don't have to 
                     -- select an INT or even a SMALLINT
  
  published   BOOLEAN GENERATED ALWAYS AS (GET_BIT(bitmask, 0)::BOOLEAN) STORED,
  visible     BOOLEAN GENERATED ALWAYS AS (GET_BIT(bitmask, 1)::BOOLEAN) STORED,
  rubbish     BOOLEAN GENERATED ALWAYS AS (GET_BIT(bitmask, 2)::BOOLEAN) STORED,
  masterpiece BOOLEAN GENERATED ALWAYS AS (GET_BIT(bitmask, 3)::BOOLEAN) STORED,
  meh         BOOLEAN GENERATED ALWAYS AS (GET_BIT(bitmask, 4)::BOOLEAN) STORED,
  arts        BOOLEAN GENERATED ALWAYS AS (GET_BIT(bitmask, 5)::BOOLEAN) STORED,
  legal       BOOLEAN GENERATED ALWAYS AS (GET_BIT(bitmask, 6)::BOOLEAN) STORED,
  sport       BOOLEAN GENERATED ALWAYS AS (GET_BIT(bitmask, 7)::BOOLEAN) STORED,
  politics    BOOLEAN GENERATED ALWAYS AS (GET_BIT(bitmask, 8)::BOOLEAN) STORED,

  
  
  CONSTRAINT subject_ck  -- so you can't have conflicting subjects - just for demo purposes
    CHECK                -- a document can't be art and legal at the same time!
    (
      CASE
        WHEN GET_BIT(bitmask, 5) = 1
          THEN GET_BIT(bitmask, 6) = 0 AND GET_BIT(bitmask, 7) = 0 AND GET_BIT(bitmask, 8) = 0

        WHEN GET_BIT(bitmask, 6) = 1
          THEN GET_BIT(bitmask, 5) = 0 AND GET_BIT(bitmask, 7) = 0 AND GET_BIT(bitmask, 8) = 0
  
        WHEN GET_BIT(bitmask, 7) = 1
          THEN GET_BIT(bitmask, 6) = 0 AND GET_BIT(bitmask, 5) = 0 AND GET_BIT(bitmask, 8) = 0

        WHEN GET_BIT(bitmask, 5) = 1
          THEN GET_BIT(bitmask, 8) = 0 AND GET_BIT(bitmask, 7) = 0 AND GET_BIT(bitmask, 5) = 0
      END
    ) 
  
);

现在，额外的9个布尔值将使表格大小增加约12字节-如果这不是问题，那么我们就可以开始了！此外，当（我假设很快-截至2022-09-09的写作）PostgreSQL被增强为VIRTUAL列时，就根本没有空间开销了。

这样做的好处是使你的SQL简短易读-而不是必须使用一堆丑陋的CASE语句，你只需要执行以下操作：

INSERT INTO test (data, bitmask) VALUES
('Document 1', '000100000'),
('Document 2', '100000000'),
('Document 3', '101000001');

而且，像这样的东西：

CREATE INDEX legal_ix ON test (legal) WHERE legal;

现在，获取所有记录变得更加易读 - 更加清晰易懂:

SELECT * FROM test;

结果：

t_id    data    bitmask   published visible rubbish masterpiece meh arts    legal   sport   politics
1   Document 1  000100000         f       f       f           t   f    f        f       f         f
2   Document 2  100000000         t       f       f           f   f    f        f       f         f
3   Document 3  101000001         t       f       t           f   f    f        f       f         t

你也可以这样做：

BEGIN TRANSACTION;  -- can't update the other way round or CHECK constraint will fail
                    -- CHECK constraints are not deferrable - can't be 8 & 6 simultaneously
UPDATE test  
  SET bitmask = SET_BIT(bitmask, 8, 0) WHERE data = 'Document 3';
UPDATE test
  SET bitmask = SET_BIT(bitmask, 6, 1) WHERE data = 'Document 3';
COMMIT;

结果：

SELECT t_id, published, legal FROM test;  -- legal has gone from f -> t

这个示例中还有一些其他的小组件。

- Vérace

Answer 3

你可以直接应用比特串函数处理比特串，无需将其转换为整数。

- Clodoaldo Neto