Postgres随机播放随机字符

问题描述：

我想改组（ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890）并从1到6获得子字符串。

I want to shuffle (ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890) and get substring from 1 to 6.

我已经尝试了很多，但是在postgres中找不到改组功能

I have tried a lot but was not able to find a shuffle function in postgres

update tablename 
    set  secureno=substring(shuffle(ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890) from 1 for 6)

答

您可以使用以下查询对字符串进行混洗：

You can shuffle a string using this query:

select substr('ABCD', i, 1)
from generate_series(1, 4) i
order by random();

 substr 
--------
 B
 C
 A
 D
(4 rows)

使用它作为密钥（62个字符），获取6个字符并汇总为字符串：

Use it for your key (with 62 chars), get 6 chars and aggregate to string:

select string_agg(ch, '')
from (  
    select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', i, 1) ch
    from generate_series(1, 62) i
    order by random()
    limit 6
    ) s

如果经常需要，您可以基于算法创建一个函数：

You can create a function based on the algorithm if you often need this::

create or replace function shuffle(text)
returns text language sql as $$
    select string_agg(ch, '')
    from (
        select substr($1, i, 1) ch
        from generate_series(1, length($1)) i
        order by random()
        ) s
$$;

select left(shuffle('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890'), 6);

Postgres随机播放随机字符

相关推荐