postgresql:md5消息摘要的数据类型? [英] postgresql: data type for md5 message digest?
问题描述
我想使用一些字符串的MD5消息摘要作为表的主键。我应该对这样的字段使用什么数据类型 ?应该为该字段写什么选择
和 insert
语句?
$ b
创建表t(d bytea);
insert into t(d)values
(digest('my_string','md5')),
(decode(md5('my_string'),'hex'));
上述两种形式都可以使用,但使用更简单的摘要
函数必须以超级用户身份安装 pgcrypto
扩展:
create extension pgcrypto;
使用摘要
decode
和 md5
如上搜索某个字符串:
select
octet_length(d)ba_length,
pg_column_size(d)ba_column,
encode(d,'hex')hex_representation,
从t
中获得的h_column
其中d = digest('my_string','''')'h_length(编码(d,'hex'))h_length,
pg_column_size md5')
;
ba_length | ba_column | hex_representation | h_length | h_column
----------- + ----------- + --------------------- ------------- + ---------- + ----------
16 | 17 | 3d212b21fad7bed63c1fb560c6a5c5d0 | 32 | 36
16 | 17 | 3d212b21fad7bed63c1fb560c6a5c5d0 | 32 | 36
pg_column_size
值是存储大小。 bytea
与六进制表示形式相比,不到一半。
I want to use the MD5 message digest of some string as the primary key of a table. What datatype should I use for such a field? What select
and insert
statements should I write for the field?
The md5 hash as bytea
will use only 16 bytes in instead of 32 for the hexa representation:
create table t (d bytea);
insert into t (d) values
(digest('my_string', 'md5')),
(decode(md5('my_string'), 'hex'));
Both forms above will work but to use the simpler digest
function it is necessary to install the pgcrypto
extension as superuser:
create extension pgcrypto;
Use the digest
function or the combination of decode
and md5
as above to search for a certain string:
select
octet_length(d) ba_length,
pg_column_size(d) ba_column,
encode(d, 'hex') hex_representation,
octet_length(encode(d, 'hex')) h_length,
pg_column_size(encode(d, 'hex')) h_column
from t
where d = digest('my_string', 'md5')
;
ba_length | ba_column | hex_representation | h_length | h_column
-----------+-----------+----------------------------------+----------+----------
16 | 17 | 3d212b21fad7bed63c1fb560c6a5c5d0 | 32 | 36
16 | 17 | 3d212b21fad7bed63c1fb560c6a5c5d0 | 32 | 36
The pg_column_size
value is the storage size. It is less than half for the bytea
compared to the hexa representation.
这篇关于postgresql:md5消息摘要的数据类型?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!