我有一个处理区块链数据的应用程序，所以postgres表有很多恒定大小的十六进制值字段。
其中最重要的一列是user表的public_address列，它是一个20字节的十六进制值，也是表的主键。我说重要的原因是因为这个列几乎在JOIN或WHERE子句中的所有其他查询中都使用，所以它的索引和查询速度至关重要。
另一方面，在transaction表中还有另一列nonce，它是一个32字节的十六进制值，上面有一个单一的约束NOT NULL。这在任何JOIN或WHERE子句中都没有使用，只在SELECT语句中使用，以获取列的值。
我知道bytea在存储方面比varchar更高效，但我不知道哪一个更适合用于索引和更快的查询。
所以我的问题是，在什么情况下bytea应该在varchar(N)上使用十六进制值，反之亦然，考虑到索引和查询速度？
2条答案
按热度按时间jdzmm42g1#
你所说的“十六进制值”只是二进制数据的文本表示，这才是重要的，它是简单的、原始的、任意的二进制数据，只是一个字节序列。
索引和查询速度（虽然我不期望它们有太大的不同）并不重要，只有一个合适的data type for arbitrary byte sequences:
bytea。不要使用
varchar：“* 可存储在任何这些数据类型中的字符由数据库字符集确定，该数据库字符集在创建数据库时选择。无论特定的字符集是什么，都不能存储代码为零的字符（有时称为NUL）。*”dw1jzc5e2#
tl;dr。使用
bytea。它可能性能更好，但更重要的是它是正确的;有一些字节对于varchar是非法的。从the docs...首先，二进制字符串特别允许存储值为零的八位字节和其他“不可打印”的八位字节（通常是十进制范围32到126之外的八位字节）。字符串不允许零八位字节，并且也不允许根据数据库的所选字符集编码无效的任何其他八位字节值和八位字节值序列。其次，对二进制字符串的操作处理实际字节，简而言之，二进制字符串适合于存储程序员认为是“原始字节”的数据，而字符串适合于存储文本。
varchar存储使用给定字符编码表示 * 字符 * 的字节。在许多字符编码中，某些字节组合是非法的;例如，在UTF-8中，7 F以上的单个字节是非法的。因此，您不能可靠地将字节存储为varchar中的字节。相反，
varchar将存储字节的字符串表示。因此，\x00AA将不会存储为两个字节00 AA，而是存储为字符串"\x00AA"，在UTF-8中为5C 78 30 30 41 41。Demonstration .
在十六进制字符和真实的字节之间来回转换是更多的工作和更多的错误机会。使用bytea更有效，更正确。