Oracle数据库存储33字节十六进制值的最佳数据类型咨询

核心结论 #

对于你提供的33字节十六进制值（0x54906a620d3c1e82c8e812b0f8d1214beafc05c74820c1dd3675b0c38f4a83aa），RAW类型是满足空间最小化需求的最优选择，同时可以通过转换后的查询语句实现完整匹配查询。

分问题解答 #

1. 满足要求的首选数据类型 #

• a) 占用磁盘空间最小：RAW(n)类型直接存储二进制数据，33字节的十六进制值（去掉0x前缀后为66位十六进制字符，每两位对应1字节）使用RAW(33)存储时，仅占用33字节磁盘空间，是所有类型中空间效率最高的。
• b) 可通过完整字符串进行查询：RAW支持完整匹配查询，但需要将十六进制字符串转换为RAW类型后再执行查询，具体方式见下文。

2. VARCHAR2(132)的磁盘占用 #

使用VARCHAR2(132)存储该值时，实际占用字节数取决于字符串的实际长度和数据库字符集：

• 该字符串包含0x前缀+66位十六进制字符，共68个ASCII字符；
• 在默认的AL32UTF8字符集下，每个ASCII字符占用1字节，因此实际占用68字节，而非定义的132字节（VARCHAR2的长度是最大存储限制，实际按真实字符数计算）。

3. 33字节存储的替代类型及查询方式 #

• 33字节存储的类型：RAW(33)是最优选择，直接存储二进制数据，无额外开销。BLOB虽然也能存储二进制数据，但作为大对象类型会有额外的存储管理开销，不适合这类小型数据。
• 查询方式：RAW类型不支持直接用'0x54906a620d3c1e82c8e812b0f8d1214beafc05c74820c1dd3675b0c38f4a83aa'形式的字符串进行等值查询，需要先将十六进制字符串转换为RAW类型：
  • 去掉0x前缀后，用HEXTORAW函数转换：

    SELECT * FROM TABLE WHERE FIELD = HEXTORAW('54906a620d3c1e82c8e812b0f8d1214beafc05c74820c1dd3675b0c38f4a83aa')
    

  • 如果要保留0x前缀的查询字符串，可以通过SUBSTR函数截取有效部分后转换：

    SELECT * FROM TABLE WHERE FIELD = HEXTORAW(SUBSTR('0x54906a620d3c1e82c8e812b0f8d1214beafc05c74820c1dd3675b0c38f4a83aa', 3))
    

内容的提问来源于stack exchange，提问作者Michael C