sql - 是否可以将 Sqlite Blob 列转换为十六进制字符串 (GUID)？

Question

我有一个 SQLite 表，其中有一列将 GUID 存储为字节数组

我正在尝试从 SQL 查询中获取 Guid 字符串。到目前为止我已经尝试过:

从 [MyTable] 中选择 BlobGuidColumn

但这会返回一个blob列

是否可以从 SQL 查询返回 Hex 字符串？

Answer 1

已接受的答案存在一些问题，但最重要的一个是它并不总是生成正确的结果。如果目标只是将 128 位从二进制映射到类似 GUID (8-4-4-4-12) 的表示形式，那就可以了。但是，如果您确实希望某些内容计算为相同的 GUID，则不会这样做，因为不考虑基础数据的字节顺序。

以下面的guid为例:

BC7CAE8C-E4D7-49CA-86E4-5FD540106CC0

表示为字节数组，你不一定能得到

BC-7C-AE-8C-E4-D7-49-CA-86-E4-5F-D5-40-10-6C-C0

作为答案，因为 GUID/UUID 的内部表示形式取决于字节顺序，具体取决于所讨论的 UUID 的变体。变体 1 UUID 严格遵循网络字节顺序(小端字节序)，而变体 2 UUID 在各个方面都相同，只是一半分组以大端字节顺序存储在内存中(或数据库中的磁盘上)，而另一半分组则以大端字节顺序存储在内存中(或数据库中的磁盘上)。采用网络字节顺序。

我就此事写了一篇长文，深入探讨了 UUID/GUID 的历史以及两种变体之间的差异，并提供了有关将 UUID 从二进制转换为文本的说明:Converting a binary/blob guid column to text in SQL: it's a lot harder than you think .

根据相关变体，您可以直接使用 SQL HEX() 函数 as in the currently accepted answer :

SELECT substr(hex(guid), 1, 8)
|| '-' || substr(hex(guid), 9, 4)
|| '-' || substr(hex(guid), 13, 4)
|| '-' || substr(hex(guid), 17, 4)
|| '-' || substr(hex(guid), 21, 12)
FROM [MyTable]

或者，如果源数据来自 Microsoft 世界(例如 COM/WIN32/.NET 应用程序)，您将需要使用这种更为复杂的方法，该方法为每个分组考虑适当的字节顺序:

SELECT
substr(hguid, 7, 2) || substr(hguid, 5, 2) || substr(hguid, 3, 2) || substr(hguid, 1, 2) || '-'
|| substr(hguid, 11, 2) || substr(hguid, 9, 2) || '-'
|| substr(hguid, 15, 2) || substr(hguid, 13, 2) || '-'
|| substr(hguid, 17, 4) || '-'
|| substr(hguid, 21, 12)

AS guid

FROM (SELECT hex(guid) AS hguid FROM messages)

(此处，guid 列在最后一行仅转换为十六进制一次，以避免对每个字节重复执行此操作的开销。)

我实际上不清楚(如在OP的特定场景中)Microsoft SQL Server是否以网络字节顺序或(部分)大端存储在磁盘上的GUID，尽管我倾向于认为是后者鉴于在 Microsoft 技术方面，其他任何地方都普遍存在这种情况(这不是通用规则，但总的来说，“guid”意味着 Little Endian，而“uuid”意味着 Big - see the article 有关更多信息)。

提示:如果结果以零开头，则说明您做错了。考虑到 UUIDv1/UUIDv2(时间戳)中第一组的源，许多分辨率有限的源在第一组十六进制数字的末尾处都有零。但是，如果转换不正确，该组将最终出现在开头:这是一个确定的信号，表明您需要在特定情况下使用替代答案。