将PostgreSQL bytea存储的序列化Java UUID转换为PostgreSQL UUID。

Question

我们的其中一个软件项目使用了一个带有bytea类型“guid”列的PostgreSQL表。

在使用Hibernate 3.3.2.GA和PostgreSQL 8.4时，它使用Java对象序列化来序列化Java UUID类型。结果是类似以下escape格式的bytea文字：

'\254\355\000\005sr\000\016java.util.UUID\274\231\003\367\230m\205/\002\000\002‌​J\000\014leastSigBitsJ\000\013mostSigBitsxp\273\222)\360*r\322\262u\274\310\020\3‌​42\004M '

...它们在查询中很难作为选择或条件使用来检索相关行。

是否有人知道如何在查询的选择或条件部分中读取或使用bytea列（例如通过psql或pgadmin3），而不设置一些Hibernate查询？

- FvHovell

Answer 1

更新：请参见问题的编辑，这个答案适用于普通的16字节uuid序列化；问题已经修改以反映java序列化。

---

有趣的问题。我最终编写了一个简单的C扩展来高效地解决它，但使用下面的PL/Python版本可能更明智。

由于uuid是一个固定大小的类型，而bytea是varlena，所以你不能只是create cast ... as implicit将它们二进制强制转换，因为变长字段头会妨碍这一过程。

没有内置函数可以将bytea输入返回为uuid。这将是一个方便的功能，但我不认为有人已经做到了。

最简单的方法

更新：实际上有一种简单的方法可以做到这一点。一旦去掉\x，bytea的十六进制形式实际上是一个有效的uuid文本，因为uuid_in接受没有-或{}的纯十六进制形式。因此，只需：

regress=> SET bytea_output = 'hex';
SET
regress=> SELECT CAST( substring(CAST (BYTEA '\x0FCC6350118D11E4A5597DE5338EB025' AS text) from 3) AS uuid);
              substring               
--------------------------------------
 0fcc6350-118d-11e4-a559-7de5338eb025
(1 row)

涉及一些字符串复制和十六进制编码/解码循环，但这比我之前建议的任何PL答案都要快得多，但比C慢。

其他选项

个人建议使用PL/Perl或pl/pythonu。我将用一个例子跟进。

假设您的uuid是十六进制格式的bytea文字：

'\x0FCC6350118D11E4A5597DE5338EB025'

您可以使用以下方法将其转换为uuid类型：

PL/Perl

create language plperlu;

create or replace function to_uuid(bytea) returns uuid language plperlu immutable as $$
use Data::UUID;
my $ug = new Data::UUID;
my $uuid = $ug->from_hexstring(substr($_[0],2));
return $ug->to_string($uuid);
$$
SET bytea_output = hex;

SELECT to_uuid(BYTEA '\x0FCC6350118D11E4A5597DE5338EB025');

PL/Python

在 PL/Python 中，由于 PL/Python 接口将 bytea 作为原始字节而不是十六进制字符串传递，因此使用 Python 可能更快且更清晰：

CREATE LANGUAGE plpythonu;

CREATE or replace function to_uuid(uuidbytes bytea) 
RETURNS uuid LANGUAGE plpythonu IMMUTABLE 
AS $$
import uuid
return uuid.UUID(bytes=uuidbytes)
$$;

SELECT to_uuid(BYTEA '\x0FCC6350118D11E4A5597DE5338EB025');

在C语言中，仅是为了好玩。丑陋的hack。

您可以查看此处的C扩展模块。

但实际上，我说它很丑陋是认真的。如果想要在C中正确地完成此操作，最好是实际修补PostgreSQL而不是使用扩展。

- Craig Ringer

---

也许我应该更清楚地提到，bytea列的内容是UUID实例的Java序列化版本，例如：select guid from documents limit 1;"\254\355\000\005sr\000\016java.util.UUID\274\231\003\367\230m\205/\002\000\002J\000\014leastSigBitsJ\000\013mostSigBitsxp\273\222)\360*r\322\262u\274\310\020\342\004M " - FvHovell

@FvHovell 嗯，是的。至少说一下这将会很有用。这就是为什么你应该始终包含示例数据。 - Craig Ringer

Answer 2

经过一些试错，我创建了以下函数来提取postgresql-UUID值： CREATE OR REPLACE FUNCTION bytea2uuid (x bytea) RETURNS uuid as $$ SELECT encode(substring(x, 73, 8) || substring(x, 65, 8), 'hex')::uuid $$ language sql; 此方法通过提取用于leastSigBits和mostSigBits的java long-values中的字节（以相反的顺序存储），然后编码为十六进制并转换为类型'uuid'。

如下所示使用： select bytea2uuid(guid) as guid from documents limit 1; "75bcc810-e204-4d20-bb92-29f02a72d2b2"

- FvHovell

---

你最好也验证一下serialVersionUid字段，确保你没有解码错误的内容。 - Craig Ringer

在通用情况下，您是正确的。但是对于我的目的，我可以确定所有UUID序列化都是使用Java 6执行的，因为这是我们所有项目都使用的版本。因此，在我的情况下，我不需要检查_serialVersionUid_，因为保证对于所有_guid_-values都是相同的。 - FvHovell

Answer 3

这对我起作用：

ALTER TABLE myTable ALTER COLUMN id TYPE uuid USING CAST(ENCODE(id, 'hex') AS uuid);

- jwebuser

---

这不是所询问的正确编码：例如一个java.util.UUID序列化值，如：“\254\355\000\005sr\000\016java.util.UUID\274\231\003\367\230m\205/\002\000\002J\000\014leastSigBitsJ\000\013mostSigBitsxp\273\222)\360*r\322\262u\274\310\020\342\004M”。 - FvHovell