postgresql md5() 适用于文字，但不适用于列数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15601551/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
md5() works with literal, but not with column data
提问by Jeromy French
While testing out PostgreSQL's md5()functionI noticed very bizarre behavior:
在测试PostgreSQL 的md5()功能时，我注意到非常奇怪的行为：
Works as expected
按预期工作
SELECT md5('abc')
--"900150983cd24fb0d6963f7d28e17f72"
But using the md5() function in a query:
但是在查询中使用 md5() 函数：
SELECT request_id, md5(request_id)
FROM Request
ORDER BY request_id
results in this error:
导致此错误：
ERROR: function md5(integer) does not exist
LINE 1: SELECT request_id, md5(request_id)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********
ERROR: function md5(integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 20
How can the function notexist if it worked in the first query? What am I doing wrong; what is the correct way to use md5()in a SELECT query?
如果它在第一个查询中工作，该函数怎么可能不存在？我究竟做错了什么; md5()在 SELECT 查询中使用的正确方法是什么？
回答by Clodoaldo Neto
The function expects text as parameter. Cast it:
该函数需要文本作为参数。投它：
SELECT request_id, md5(request_id::text)
FROM Request
ORDER BY request_id
A function named md5 accepting an integer parameter does not exist but you can create it:
接受整数参数的名为 md5 的函数不存在，但您可以创建它：
create function md5(integer)
returns text as $$
select md5(::text);
$$ language sql immutable;
Then there will be 3 signatures for md5:
那么md5会有3个签名：
=> \df md5
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+--------
pg_catalog | md5 | text | bytea | normal
pg_catalog | md5 | text | text | normal
public | md5 | text | integer | normal
As pointed in the comments to this answer the md5 hash of the integer's text representation may not be what you want. To have the hash of the binary the md5 signature accepting a byteaparameter should be used:
正如对此答案的评论所指出的，整数文本表示的 md5 哈希可能不是您想要的。要获得二进制文件的哈希值，bytea应使用接受参数的 md5 签名：
select md5(('\x' || right('0000000' || to_hex(200), 8))::bytea);
md5
----------------------------------
b7b436d004c1cc0501bee9e296d2eaa4
And replace the previously created function:
并替换之前创建的函数：
create or replace function md5(integer)
returns text as $$
select md5(('\x' || right('0000000' || to_hex(), 8))::bytea);
$$ language sql immutable;
回答by Craig Ringer
In general it doesn't make much sense to take the md5 of an integer. It seems likely that you trying to obscure a sequence so it appears semi-random in order. If so, there's a much better way:
一般来说，取整数的 md5 没有多大意义。您似乎很可能试图模糊一个序列，使其按顺序显示为半随机。如果是这样，有一个更好的方法：
Use the pseudo_encryptfunction listed on the PostgreSQL wiki. It's way saner than trying to take the md5 of an integer then (presumably) truncate it.
使用pseudo_encryptPostgreSQL wiki 上列出的函数。这比尝试取整数的 md5 然后（大概）截断它要理智得多。
The above does notprovide strong cryptographic randomness, but neither does your approach. If you need your request IDs to be genuinely unpredictable for security reasons rather than just non-obvious at a casual glance you should be using a strong cryptographic random numbers generator and be prepared to cope with duplicates using time windows, etc.
上面没有提供强大的加密随机性，但你的方法也没有。如果您出于安全原因需要您的请求 ID 真正不可预测，而不仅仅是不经意间一目了然，您应该使用强大的加密随机数生成器，并准备好使用时间窗口等处理重复项。
回答by IamIC
I'm just trying to generate a non-obvious, non-repeating, value which can be used to look-up a record.
我只是想生成一个非显而易见的、非重复的值，可用于查找记录。
What you need is a bijective hash. I use the CRCCPU function in a custom C function. For CPUs that don't have the function, one can use a lookup table.
你需要的是一个双射散列。我CRC在自定义 C 函数中使用CPU 函数。对于没有该功能的CPU，可以使用查找表。
This approach is guaranteed to give you a unique "random looking" number for every unique 32-bit input.
这种方法保证为每个唯一的 32 位输入提供一个唯一的“随机外观”数字。
If you know how to generate C functions (not trivial), it will be a simple task to look up how to use CRC.
如果您知道如何生成 C 函数（不是微不足道的），那么查找如何使用 CRC 将是一项简单的任务。
回答by Jeromy French
The error is a little misleading; the md5()function exists, just not for working on integers. Use an embedded CAST()function to convert the integer field into text, and it will work:
这个错误有点误导；该md5()函数存在，只是不用于处理整数。使用嵌入式CAST()函数将整数字段转换为文本，它将起作用：
SELECT request_id, md5(CAST(request_id AS TEXT))
FROM Request
ORDER BY request_id
--1;"c4ca4238a0b923820dcc509a6f75849b"
--2;"c81e728d9d4c2f636f067f89cc14862c"
--etc
