在PostgreSQL中将字符串散列为数值

本文介绍了在PostgreSQL中将字符串散列为数值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要将存储在数据库中的字符串转换为数值.结果可以是Integer(首选)或Bigint.此转换将在PL/pgSQL函数的数据库端完成.

I need to Convert Strings stored in my Database to a Numeric value. Result can be Integer (preferred) or Bigint. This conversion is to be done at Database side in a PL/pgSQL function.

有人可以给我指出一些算法或任何可用于实现此目的的API吗?

Can someone please point me to some algorithm or any API's that can be used to achieve this?

我已经在Google上搜索了几个小时，到目前为止，找不到有用的东西:(

I have been searching for this on Google for hours now, could not find anything useful so far :(

推荐答案

只需保留MD5哈希的前32位或64位.当然，它使md5的主要属性无效(=碰撞几率是无穷小)，但是您仍然会分散大量的值，这大概足以解决您的问题.

Just keep the first 32 bits or 64 bits of the MD5 hash. Of course, it voids the main property of md5 (=the probability of collision being infinitesimal) but you'll still get a wide dispersion of values which presumably is good enough for your problem.

从其他答案派生的SQL函数:

SQL functions derived from the other answers:

对于bigint:

create function h_bigint(text) returns bigint as $$ select ('x'||substr(md5($1),1,16))::bit(64)::bigint; $$ language sql;

对于int:

create function h_int(text) returns int as $$ select ('x'||substr(md5($1),1,8))::bit(32)::int; $$ language sql;