在PostgreSQL中生成随机字符串

Question

我正在使用这个 SQL 查询在 PostgreSQL 中生成随机值

chr(ascii('B') + (random() * 25)::integer)

如何在同一查询中生成一个15个字符的随机字符串？

- Peter Penzov

---

把它连续拼接15次怎么样？ - Gordon Linoff

@GordonLinoff：永远不要低估暴力算法的威力。 - Mike Sherrill 'Cat Recall'

您能举个例子吗？ - Peter Penzov

Answer 1

另一个非常易于阅读的解决方案（性能应该是合理的，但没有进行基准测试）：

select substr(md5(random()::text), 0, 25);

如果您喜欢，可以使用大写字母：

select upper(substr(md5(random()::text), 0, 25));

- fncomp

Answer 2

这是我的贡献

postgres=# SELECT array_to_string(array(select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',((random()*(36-1)+1)::integer),1) from generate_series(1,50)),'');
                  array_to_string                   
----------------------------------------------------
 4XOS6TQG5JORLF3D1RPXUWR2FQKON9HIXV0UGH0CQFT1LN5D4L
(1 row)

它让您可以指定允许的字符集和字符串的长度。

- Lyndon S

---

(random()*(36-1)+1)::int会将1到36之间的随机数四舍五入为最接近的整数。这意味着第一个和最后一个字符的频率约为其余字符的一半。您可以使用floor(random()*36)::int+1来代替。 - undefined

Answer 3

这将会给你一个长度为15的随机词，由源值常量中配置的字母组成。

select
  string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as random_word
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')) as symbols(characters)
  -- length of word
  join generate_series(1, 15) on 1 = 1

编辑：要获取多个随机单词，您可以使用以下方法：

with symbols(characters) as (VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'))
select string_agg(substr(characters, (random() * length(characters) + 1) :: INTEGER, 1), '')
from symbols
join generate_series(1,8) as word(chr_idx) on 1 = 1 -- word length
join generate_series(1,10000) as words(idx) on 1 = 1 -- # of words
group by idx

- Bennit

---

这个解决方案在更新现有数据集时也可以非常高效，我真的很喜欢它。 - bobmarksie

“select min(n), max(n) from (select (random() * length('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789') + 1)::int n from generate_series(1, 10000000)) t0” 返回的值为1到63，包括1和63。这意味着对substr的调用可能会返回一个空字符串，这将导致返回的值少于8个字符。只是需要注意一下，但仍然是一个好的解决方案。 - François Beausoleil

Answer 4

您可以通过单个查询完成此操作，但是如果您希望每个字符根据范围分开，则上述解决方案是可行的。

SELECT array_to_string(ARRAY(
            SELECT chr((ascii('B') + round(random() * 25)) :: integer) 
            FROM generate_series(1,15)), 
             '');

- Himanshu sharma

Answer 5

我使用这个方法来生成随机字符串...

如果您不介意连接线并启用了uuid扩展...

select substr(uuid_generate_v4()::text,1,15);

例如，在名称列中生成随机字符串，我将使用：

select concat('name-', substr(uuid_generate_v4()::text,1,10)) as name;

例如，name-91fc72dc-d

否则，请使用来自@fncomp的优秀md5示例

注：要启用uuid扩展名

create extension if not exists "uuid-ossp";

- mlo55

---

或者您可以使用 gen_random_uuid()，它是自Postgres 13+以来的核心功能，无需扩展程序，例如：SELECT substr(gen_random_uuid()::text, 1, 8)。 - kino1

Answer 6

对我来说，最方便的方法是创建一个函数：

CREATE OR REPLACE FUNCTION random_string(int) RETURNS TEXT as $$
SELECT substr(md5(random()::text), 0, $1+1);                 
$$ language sql;  

这个函数名叫做random_string，它需要一个字符串长度作为参数。

然后我可以在任何地方使用这个函数

只需看一下结果：

select random_string(6);

进行单次插入：

insert into users values(nextval('users_sequence'), random_string(6), random_string(6));

生成多行随机数据：

do $$
begin
for i in 1..100 loop
insert into users values(nextval('users_sequence'), random_string(6), random_string(6));
end loop;
end;
$$;

等等。

- luke

Answer 7

我尝试使用 @Bennit 的解决方案，但注意到其中存在一些缺陷。随机部分的计算有点错误，导致结果有误：最终长度比期望的要短。

[快速查看了 @lyndon-s 的版本 - 很可能也存在同样的缺点]

因此，这里是更新后的 @bennit 版本：

select
string_agg(substr(characters, (random() * length(characters) + 0.5)::integer, 1), '') as random_word
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')) as symbols(characters)
-- length of word
join generate_series(1, 15) on 1 = 1

这里是需要进行更改的演示:

更正后:

select n,count(*) from (
select (random() * 10 + 0.5)::integer as n from dbfiles
join generate_series(1, 100000) on 1 = 1
) as s group by n
order by 1;

Original:

select n,count(*) from (
select (random() * 10 + 1)::integer as n from dbfiles
join generate_series(1, 100000) on 1 = 1
) as s group by n
order by 1;

- zpetukhov

Answer 8

这里是想法:

select (chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) ||
        chr(ascii('B') + (random() * 25)::integer) 
       ) as Random15

- Gordon Linoff

---

有没有简单的解决方案？ - Peter Penzov

Answer 9

这是我的PL/pgSQL实现：

• 有一个选项可以生成英文或俄文符号的文本，并且很容易扩展到更多语言；
• 可选择使用数字、标点符号、空格和换行符；

create or replace function random_string (
    str_length integer, lang varchar(2) default 'en', 
    w_dig boolean default true, w_punct boolean default true, 
    w_space boolean default false, w_newline boolean default false
)

returns text
language plpgsql
as $function$
     declare
         chars_eng text[] := '{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
         chars_rus text[] := '{А,Б,В,Г,Д,Е,Ё,Ж,З,И,Й,К,Л,М,Н,О,П,Р,С,Т,У,Ф,Х,Ц,Ч,Ш,Щ,Ъ,Ы,Ь,Э,Ю,Я,а,б,в,г,д,е,ё,ж,з,и,й,к,л,м,н,о,п,р,с,т,у,ф,х,ц,ч,ш,щ,ъ,ы,ь,э,ю,я}';
         chars_dig text[] := '{}';
         chars_punct text[] := '{}';
         chars_space text[] := '{}';
         chars_newline text[] := '{}';
         chars_final text[] := '{}';
         result text := '';
         i integer := 0;
    begin

        -- checking string length arg
        if str_length < 0 then
            raise exception 'Length of string cannot be a negative value';
        end if;

        -- checking chars selection
        if w_dig = true then
            chars_dig := '{0,1,2,3,4,5,6,7,8,9}';
        end if;
        if w_punct = true then
            chars_punct := string_to_array(E'!d"d#d$d%d&d\'d(d)d*d+d,d-d.d/d:d;d<d=d>d?d@d[d\\d]d^d_d`d{d|d}d~','d');
        end if;
        if w_space = true then
            chars_space := string_to_array(' ',',');
        end if;
        if w_newline = true then
            chars_newline := string_to_array(E'\r\n',',');
        end if;

        -- checking language selection
        if lang = 'en' then
            chars_final := chars_eng||chars_dig||chars_punct||chars_space||chars_newline;
        elsif lang = 'ru' then
            chars_final := chars_rus||chars_dig||chars_punct||chars_space||chars_newline;
        else 
            raise exception 'Characters set for that language is not defined';
        end if;

        -- filling the string
        for i in 1..str_length loop
            result := result || chars_final[1 + round(random() * (array_length(chars_final, 1) - 1))];
        end loop;

        -- trimming extra symbols that may appear from /r/n usage
        if length(result) > str_length then
            result := left(result, str_length);
        end if;

        -- getting the result
        return result;

    end;
$function$ ;

- NickNal

Answer 10

在这里提供我的意见。我需要随机字符串来进行一些基准测试，因此对我来说真正重要的是这些字符串彼此之间是唯一的。

select rpad(generate_series::varchar, 1000, 'hi') from generate_series(1,10);

rpad - 右侧填充至长度(1000)，填充内容为'hi' generate_series(1,10) - 生成10行

结合上面的答案，你也可以这样做：

select rpad(generate_series::varchar, 1000, md5(random()::text)) from generate_series(1,10)

这将确保您有200个字符，或任何所需的长度。

- Ying