sql - PostgreSQL Varchar UID 到 Int UID，同时保持唯一性

Question

假设我有一个独特的 VarChar(32) 列。

例如。 13bfa574e23848b68f1b7b5ff6d794e1。

我想在将列转换为 int 时保留它的唯一性。我想我可以将所有字母转换为它们的 ascii 等价物，同时保留数字和字符位置。为此，我将使用翻译功能。

伪代码:select translate(uid, '[^0-9]', ascii('[^0-9]'))

我的问题是最初要找到 VarChar 列中的所有字母。我试过了

select uid, substring(uid from '[^0-9]') from test_table;

但它只返回它遇到的第一个字母。使用上面的例子，我会寻找 bfaebfbbffde

感谢任何帮助!

Answer 1

首先，我同意两位评论者所说的您应该使用 UID 数据类型。

除此之外......

您的 UID 看起来像传统的 UID，因为它不是字母数字，而是十六进制。如果是这种情况，您可以使用此解决方案将十六进制转换为数值:

PostgreSQL: convert hex string of a very large number to a NUMERIC

请注意，公认的解决方案(我的，很遗憾)不如列出的其他解决方案好，因为我的解决方案不适用于这么大的十六进制值。

就是说，哎呀，这是一个多么庞大的数字。圣烟。

根据您的表中有多少记录以及插入/更新的频率，我会考虑一种完全不同的方法。简而言之，我将创建另一个列来存储您的数字 ID，其值将由序列确定。

如果你真的想让它变得无懈可击，你也可以创建一个交叉引用表来存储将要发生的关系

1. 重复使用 ID(我知道 UID 不会，但这将涵盖记录被错误删除、重新出现并且您想保留原始 ID 的情况)
2. 如果 UID 重复(比如这是一个每个 UID 有多个记录的子表)，它也会涵盖这种情况

如果这些都不适用，您可以将其简化一些。

解决方案看起来像这样:

添加一个 ID 列，该列将是与 UID 等效的数字:

alter table test_table
add column id bigint

创建一个序列:

CREATE SEQUENCE test_id

创建一个交叉引用表(同样，对于简化版本来说不是必需的):

create table test_id_xref (
   uid varchar(32) not null,
   id bigint not null,
   constraint test_id_xref_pk primary key (uid)
)

然后进行一次性更新，为交叉引用表和实际表的每个 UID 分配一个代理 ID:

insert into test_id_xref
with uids as (
  select distinct uid
  from test_table
)
select uid, nextval ('test_id')
from uids;

update test_table tt
set id = x.id
from test_id_xref x
where tt.uid = x.uid;

最后，对于所有 future 的插入，创建一个触发器来分配下一个值:

CREATE OR REPLACE FUNCTION test_table_insert_trigger()
  RETURNS trigger AS
$BODY$
BEGIN
  select t.id
  from test_id_xref t
  into NEW.id
  where t.uid = NEW.uid;

  if NEW.id is null then
    NEW.id := nextval('test_id');
    insert into test_id_xref values (NEW.uid, NEW.id);
  end if;

  return NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER insert_test_table_trigger
  BEFORE INSERT
  ON test_table
  FOR EACH ROW
  EXECUTE PROCEDURE test_table_insert_trigger();