postgresql - url的postgresql唯一的非顺序ID
问题描述
我已经查看了一些在 Stackoverflow 上创建字母数字 ID 的方法,但它们都有自己的弱点,有些没有检查冲突,有些使用的序列在使用逻辑复制时不是一个好的选择。
经过一番谷歌搜索后,我发现这个网站有以下脚本,它检查冲突并且不使用序列。但是,当将行插入表中时,这是作为触发器完成的。
-- Create a trigger function that takes no arguments.
-- Trigger functions automatically have OLD, NEW records
-- and TG_TABLE_NAME as well as others.
CREATE OR REPLACE FUNCTION unique_short_id()
RETURNS TRIGGER AS $$
-- Declare the variables we'll be using.
DECLARE
key TEXT;
qry TEXT;
found TEXT;
BEGIN
-- generate the first part of a query as a string with safely
-- escaped table name, using || to concat the parts
qry := 'SELECT id FROM ' || quote_ident(TG_TABLE_NAME) || ' WHERE id=';
-- This loop will probably only run once per call until we've generated
-- millions of ids.
LOOP
-- Generate our string bytes and re-encode as a base64 string.
key := encode(gen_random_bytes(6), 'base64');
-- Base64 encoding contains 2 URL unsafe characters by default.
-- The URL-safe version has these replacements.
key := replace(key, '/', '_'); -- url safe replacement
key := replace(key, '+', '-'); -- url safe replacement
-- Concat the generated key (safely quoted) with the generated query
-- and run it.
-- SELECT id FROM "test" WHERE id='blahblah' INTO found
-- Now "found" will be the duplicated id or NULL.
EXECUTE qry || quote_literal(key) INTO found;
-- Check to see if found is NULL.
-- If we checked to see if found = NULL it would always be FALSE
-- because (NULL = NULL) is always FALSE.
IF found IS NULL THEN
-- If we didn't find a collision then leave the LOOP.
EXIT;
END IF;
-- We haven't EXITed yet, so return to the top of the LOOP
-- and try again.
END LOOP;
-- NEW and OLD are available in TRIGGER PROCEDURES.
-- NEW is the mutated row that will actually be INSERTed.
-- We're replacing id, regardless of what it was before
-- with our key variable.
NEW.id = key;
-- The RECORD returned here is what will actually be INSERTed,
-- or what the next trigger will get if there is one.
RETURN NEW;
END;
$$ language 'plpgsql';
我有一个已经包含数据的表,我添加了一个名为的新列pid
是否可以修改它并使用函数调用作为默认值,以便我之前的所有数据都有一个短 id?
解决方案
假设你有一张桌子test
:
DROP TABLE IF EXISTS test;
CREATE TABLE test (foo text, bar int);
INSERT INTO test (foo, bar) VALUES ('A', 1), ('B', 2);
您可以向id
其中添加一列:
ALTER TABLE test ADD COLUMN id text;
并附加触发器:
DROP TRIGGER IF EXISTS unique_short_id_on_test ON test;
CREATE TRIGGER unique_short_id_on_test
BEFORE INSERT ON test
FOR EACH ROW EXECUTE PROCEDURE unique_short_id();
现在制作一个temp
与 test 结构相同的临时表 (但没有数据):
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (LIKE test INCLUDING ALL);
CREATE TRIGGER unique_short_id_on_temp
BEFORE INSERT ON temp
FOR EACH ROW EXECUTE PROCEDURE unique_short_id();
倒入:test
_temp
INSERT INTO temp (foo, bar)
SELECT foo, bar
FROM test
RETURNING *
产生类似的东西:
| foo | bar | id |
|------------+-----+----------|
| A | 1 | 9yt9XQwm |
| B | 2 | LCeiA-P8 |
如果其他表在该表上有外键引用,test
或者如果test
必须保持在线,则可能无法删除test
并重命名temp
为test
. test
相反,使用id
s from更新更安全temp
。
假设test
有一个主键(为了具体起见,我们称之为testid
),那么您可以test
使用id
s更新temp
:
UPDATE test
SET id = temp.id
FROM temp
WHERE test.testid = temp.testid;
然后你可以放下temp
桌子:
DROP TABLE temp;
推荐阅读
- postgresql - 将文本转换为 UTC 时间戳
- nginx - 在 Ubuntu 服务器上安装 Openresty
- javascript - DataTables 仅打印选定的选项
- laravel - Laravel 用户和多态性
- reactjs - 在 FlatList 中渲染的组件会被重新渲染,即使它们被声明为 PureComponent 并且它们的 props 引用没有改变
- python-3.x - 如何将列的值设置为数据框中的列?
- r - foreach 循环中的变量赋值问题(R)
- javascript - 使用令牌登录 - 赛普拉斯不重定向
- java - 在某些情况下解析管道分隔的字符串,中间没有值
- python - 如何在 Pandas 数据框中加入多个文本列