问题描述
我想用自己定制的ID生成器替换我在postgresql数据库中用于ID的某些序列。生成器将产生一个随机数,最后带有一个校验位。因此:
I would like to replace some of the sequences I use for id's in my postgresql db with my own custom made id generator. The generator would produce a random number with a checkdigit at the end. So this:
SELECT nextval('customers')
将被这样的东西替换:
SELECT get_new_rand_id('customer')
然后,该函数将返回一个数值,例如: [1 -9] [0-9] {9}
,其中最后一位是校验和。
The function would then return a numerical value such as: [1-9][0-9]{9}
where the last digit is a checksum.
我的关注点是:
- 如何使事物具有原子性
- 如何避免两次返回相同的id(这将是通过尝试将其插入具有唯一约束的列中而被捕获,但是后来到了我认为很晚)
- 这是个好主意吗?
注意1 :我不想使用uuid,因为它可以与客户交流,而且10位数字的交流比36字符的uuid容易得多
Note1: I do not want to use uuid since it is to be communicated with customers and 10 digits is far simpler to communicate than the 36 character uuid.
注2 :很少使用 SELECT get_new_rand_id()
调用该函数,但是会d在id列上被指定为默认值,而不是 nextval()
。
Note2: The function would rarely be called with SELECT get_new_rand_id()
but would be assigned as default value on the id-column instead of nextval()
.
EDIT :好的,下面的讨论很好!以下是为什么的一些解释:
EDIT: Ok, good discussusion below! Here are some explanation for why:
-
那我为什么要用这种方式使事情过于复杂呢?目的是向客户隐藏主键。
So why would I over-comlicate things this way? The purpouse is to hide the primary key from the customers.
为什么要输入校验位?
Why the check digit?
在阅读讨论之后,我当然可以看到我的方法不是解决问题的最佳方法,但是我对解决这个问题没有其他好主意,所以请在这里为我提供帮助。
After reading the discussion I can certainly see that my approach is not the best way to solve my problem, but I have no other good idea of how to solve it, so please help me out here.
- 我应该在我将暴露给客户的ID放入其中的同时添加一个额外的列并保留序列号作为主键?
- 我如何生成以合理有效的方式公开的id?
- 校验位是否必要?
推荐答案
Fo使用密码从序列中生成唯一且具有随机外观的标识符可能是个好主意。由于它们的输出是双射的(输入值和输出值之间存在一对一的映射)-与散列不同,您将没有任何冲突。
For generating unique and random-looking identifiers from a serial, using ciphers might be a good idea. Since their output is bijective (there is a one-to-one mapping between input and output values) -- you will not have any collisions, unlike hashes. Which means your identifiers don't have to be as long as hashes.
大多数加密密码都适用于64位或更大的块,但是PostgreSQL Wiki上有示例PL / pgSQL过程 int
类型。免责声明:我尚未尝试使用此功能。
Most cryptographic ciphers work on 64-bit or larger blocks, but the PostgreSQL wiki has an example PL/pgSQL procedure for a "non-cryptographic" cipher function that works on (32-bit) int
type. Disclaimer: I have not tried using this function myself.
要将其用于主键,请从Wiki页面运行CREATE FUNCTION调用,然后在您的空表执行:
To use it for your primary keys, run the CREATE FUNCTION call from the wiki page, and then on your empty tables do:
ALTER TABLE foo ALTER COLUMN foo_id SET DEFAULT pseudo_encrypt(nextval('foo_foo_id_seq')::int);
瞧!
pg=> insert into foo (foo_id) values(default);
pg=> insert into foo (foo_id) values(default);
pg=> insert into foo (foo_id) values(default);
pg=> select * from foo;
foo_id
------------
1241588087
1500453386
1755259484
(4 rows)
这篇关于用随机数替换序列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!