在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

PostgreSQL 生成隨機數(shù)字、字符串、日期、驗證碼以及 UUID

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net



文章目錄

        生成隨機數(shù)字
            生成 0 到 1 之間的隨機數(shù)
            生成指定范圍內(nèi)的隨機數(shù)
            生成 6 位數(shù)字手機驗證碼
            生成遵循正態(tài)分布的隨機數(shù)
        生成隨機字符串
            生成固定長度的隨機字符串
            生成可變長度的隨機字符串
        生成隨機日期和時間
        獲取表中的隨機記錄
        生成 UUID
        總結(jié)

大家好，我是只談技術不剪發(fā)的 Tony 老師。今天我們介紹一下如何在 PostgreSQL 中生成隨機數(shù)據(jù)，包括隨機數(shù)字、驗證碼、隨機字符串以及隨機日期和時間等。如果覺得文章有用，歡迎評論??、點贊??、推薦??

    ??計算機生成的都是偽隨機數(shù)，并不是真正的物理隨機數(shù)。

生成隨機數(shù)字
生成 0 到 1 之間的隨機數(shù)

PostgreSQL 中的RANDOM()函數(shù)可以用于生成一個大于等于 0 小于 1 的隨機數(shù)字。例如：

SELECT random();
random            |
------------------|
0.5573292311275964|

SELECT random();
random              |
--------------------|
0.017827744704202786|



該函數(shù)返回的數(shù)據(jù)類型為 double precision，每次調(diào)用都會返回不同的結(jié)果。

如果我們想要重現(xiàn)某個結(jié)果，需要生成相同的隨機數(shù)；這種情況下可以使用SETSEED(d)函數(shù)設置一個隨機數(shù)種子，d 的類型為 double precision，取值范圍從 -1.0 到 1.0。例如：

SELECT setseed(0);

SELECT random();
random            |
------------------|
0.0000000000000391|

random            |
------------------|
0.0009853946746503|
...

SELECT setseed(0);

SELECT random();
random            |
------------------|
0.0000000000000391|

random            |
------------------|
0.0009853946746503|


從結(jié)果可以看出，設置相同的種子之后，隨后的函數(shù)調(diào)用返回了一系列相同的隨機數(shù)。
生成指定范圍內(nèi)的隨機數(shù)

基于 RANDOM() 函數(shù)和一些運算，就可以返回任意兩個數(shù)字之間的隨機數(shù)：

low + RANDOM() * (high - low)


以上表達式將會返回一個大于等于 low，小于 high 的隨機數(shù)。例如：

SELECT 10 + random() * 10 AS rd;
rd                |
------------------|
15.680596127871453|



以上示例返回了一個大于等于 10 且小于 20 的隨機數(shù)字。

如果想要生成某個范圍內(nèi)的隨機整數(shù)，可以加上 FLOOR 函數(shù)。例如：

SELECT floor(10 + random() * 10);
floor|
-----|
 12.0|



該語句返回了一個大于等于 10，小于等于 19（不是 20）的隨機整數(shù)。
生成 6 位數(shù)字手機驗證碼

我們已經(jīng)知道了如何獲取指定范圍內(nèi)的隨機數(shù)，再加上 TO_CHAR 格式化函數(shù)就可以生成由 6 位數(shù)字字符組成的手機驗證碼。例如：

SELECT to_char(random() * 1000000, '099999') AS captcha;
captcha|
-------|
 076774|



TO_CHAR 函數(shù)可以確保數(shù)據(jù)不夠 6 位時在前面補足 0。
生成遵循正態(tài)分布的隨機數(shù)

PostgreSQL 提供了一個擴展模塊 tablefunc，可以用于生成遵循正態(tài)分布（normal distribution）的隨機數(shù)。首先，輸入以下命令啟用該模塊：

CREATE EXTENSION tablefunc;


然后使用該模塊中的NORMAL_RAND(n,mean, stddev)函數(shù)返回 n 個均值為 mean，標準差為 stddev 的隨機數(shù)。例如：

SELECT *
FROM normal_rand(10, 0, 1);
normal_rand         |
--------------------|
  0.0936639131151394|
   -1.26936035550923|
   2.006729235590952|
  0.7869592803653096|
 -1.5740650326039192|
-0.18656503408337746|
  1.0665080022417979|
 -1.1240167023021148|
  1.1073155396442795|
 0.09360901134478303|



以上查詢返回了 10 個遵循標準正態(tài)分布的隨機數(shù)。

我們也可以驗證一下該函數(shù)是否遵循正態(tài)分布，例如：

SELECT count(*), avg(v), stddev(v)
FROM normal_rand(1000000, 0, 1) AS v;
count  |avg               |stddev            |
-------|------------------|------------------|
1000000|0.0001662571158423|0.9992607627843408|



另一種方法就創(chuàng)建一個存儲函數(shù)來模擬正態(tài)分布的隨機數(shù)：

CREATE OR REPLACE FUNCTION normal_distrib(mean DOUBLE PRECISION, stdev DOUBLE PRECISION)
RETURNS DOUBLE PRECISION
LANGUAGE plpgsql
AS $$
DECLARE
    x DOUBLE PRECISION;
    y DOUBLE PRECISION;
    rd DOUBLE PRECISION;
BEGIN
    x := random();
    y := random();
    rd = (sqrt(-2 * ln(x)) * cos(2 * pi() * y)) * stdev + mean;

    RETURN rd;

END $$;



該函數(shù)利用 Box-Muller 變換算法通過兩個平均分布的隨機數(shù)生成正態(tài)分布的隨機數(shù)。以下語句通過 normal_distrib 函數(shù)生成了一個遵循正態(tài)分布的隨機數(shù)：

SELECT normal_distrib(0,1);
normal_distrib   |
-----------------|
0.404847649020953|



以下語句可以用于驗證 normal_distrib 函數(shù)是否遵循正態(tài)分布：

WITH RECURSIVE d(n, v) AS (
  SELECT 1 AS n, normal_distrib(0, 1) AS v
  UNION ALL
  SELECT n+1, normal_distrib(0, 1) FROM d WHERE n<1000000
)
count  |avg                  |stddev           |
-------|---------------------|-----------------|
1000000|-0.001243494839949032|0.999320444731066|



經(jīng)過測試，自定義的 normal_distrib 函數(shù)執(zhí)行時間為 12.5 s，normal_rand 函數(shù)只需要 1.5 s。
生成隨機字符串
生成固定長度的隨機字符串

除了隨機數(shù)字之外，有時候我們也需要生成一些隨機的字符串。PostgreSQL 沒有提供專門生成隨機字符串的函數(shù)，但是可以通過其他函數(shù)進行模擬。例如：

SELECT chr(floor(random() * 26)::integer + 65);
chr|
---|
V  |



以上查詢返回了一個隨機的大寫字母，chr 函數(shù)用于將 ASCII 碼轉(zhuǎn)換為對應的字符。我們可以基于該查詢進一步創(chuàng)建一個存儲函數(shù)：

CREATE OR REPLACE FUNCTION random_string(
  num INTEGER,
  chars TEXT default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
) RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  res_str TEXT := '';
BEGIN
  IF num < 1 THEN
      RAISE EXCEPTION 'Invalid length';
  END IF;
  FOR __ IN 1..num LOOP
    res_str := res_str || substr(chars, floor(random() * length(chars))::int + 1, 1);
  END LOOP;
  RETURN res_str;
END $$;



random_string 函數(shù)可以返回由指定字符（默認為所有數(shù)字、大小寫字母）組成的隨機字符串。例如：

SELECT random_string(10);
random_string|
-------------|
etP3odkRgA   |



以上示例返回了一個由字母和數(shù)字組成、長度為 10 的隨機字符串。以下語句也可以用于返回一個 6 位隨機數(shù)字組成的手機驗證碼：

SELECT random_string(6, '0123456789');
random_string|
-------------|
082661       |



生成可變長度的隨機字符串

那么，怎么返回一個長度可變的隨機字符串呢？很簡單，為 random_string 函數(shù)指定一個隨機的長度參數(shù)即可。例如：

SELECT random_string(floor(10 + random() * 11)::int);
random_string   |
----------------|
8tz5zHcbKVKoVg4S|



以上示例返回了一個長度大于等于 10 且小于等于 20 的隨機字符串。
生成隨機日期和時間

將指定日期增加一個隨機的數(shù)字，就可以得到隨機的日期。例如：

SELECT current_date + floor((random() * 15))::int rand_date;
rand_date |
----------|
2020-11-04|



以上示例返回了當前日期 14 天之內(nèi)的某個隨機日期。以下語句則返回了一天中的某個隨機時間：

SELECT make_time(floor((random() * 12))::int, floor((random() * 60))::int, floor((random() * 60))::int) AS rand_time;
rand_time|
---------|
 10:04:52|


其中，make_time 函數(shù)用于將代表時、分、秒的整數(shù)轉(zhuǎn)換為時間。
獲取表中的隨機記錄

對于返回多行數(shù)據(jù)的查詢語句，RANDOM 函數(shù)每次都會返回不同的隨機值。例如：

SELECT random() FROM employee;
random             |
-------------------|
0.10449782906204419|
 0.3345344734009643|
 0.7295074473683592|
...



利用這個特性，我們可以從表中返回隨機的數(shù)據(jù)行。例如：

SELECT emp_id, emp_name
FROM employee
ORDER BY random()
LIMIT 5;
emp_id|emp_name |
------|---------|
     2|關羽      |
     9|趙云      |
    13|關興      |
    25|孫乾      |
    17|馬岱      |



以上示例從 employee 表中返回了 5 行隨機記錄。該方法需要為表中的每行數(shù)據(jù)都生成一個隨機數(shù)，然后進行排序；所以會隨著表中的數(shù)據(jù)量增加而逐漸變慢。

如果表中存在自增主鍵，也可以基于主鍵生成一個隨機數(shù)據(jù)。例如：

SELECT round(rand() * (SELECT max(emp_id) FROM employee)) AS id;
id  |
----|
10.0|



然后基于這個隨機數(shù)返回一條隨機的記錄：

SELECT e.emp_id, e.emp_name
FROM employee e
INNER JOIN (SELECT round(random() * (SELECT max(emp_id) FROM employee)) AS id
     ) AS t
ON e.emp_id >= t.id
LIMIT 1;
emp_id|emp_name|
------|--------|
    10|廖化     |



這種方法一次只能返回一條隨機記錄，而且只有當自增字段的值沒有間隙時才會返回均勻分布的隨機記錄。

另外，PostgreSQL 中的查詢語句支持 TABLESAMPLE 子句，可以實現(xiàn)數(shù)據(jù)的抽樣。例如：

SELECT emp_id, emp_name
FROM employee
TABLESAMPLE BERNOULLI (10);
emp_id|emp_name|
------|--------|
     4|諸葛亮   |
    13|關興     |



除了 BERNOULLI 之外，也可以指定 SYSTEM 抽樣方法，參數(shù)代表了抽樣近似百分比。
生成 UUID

UUID（Universal Unique Identifier）或者 GUID（Globally Unique Identifier）是一個 128 比特的數(shù)字，可以用于唯一標識每個網(wǎng)絡對象或資源。由于它的生成機制，一個 UUID 可以保證幾乎不會與其他 UUID 重復，因此常常用于生成數(shù)據(jù)庫中的主鍵值。

PostgreSQL 提供了一個用于加/解密的擴展模塊 pgcrypto，其中的 gen_random_uuid() 函數(shù)可以用于返回一個 version 4 的隨機 UUID。首先，輸入以下命令啟用該模塊（gen_random_uuid() 從 PostgreSQL 13 開始成為了一個內(nèi)置函數(shù)）：

CREATE EXTENSION pgcrypto;



然后，通過該函數(shù)返回一個 UUID：

SELECT gen_random_uuid();
gen_random_uuid                     |
------------------------------------|
2d757cf5-c18c-469c-8b5e-eed914eacc93|



該函數(shù)返回的數(shù)據(jù)類型為 uuid。如果想要生成沒有中劃線（-）的 UUID 字符串，可以使用 REPLACE 函數(shù)：

SELECT replace(gen_random_uuid()::text,'-','');
replace                         |
--------------------------------|
cabbfcdc62c54e2889bdd2b7095f1270|



總結(jié)

本文介紹了在 PostgreSQL 中生成隨機數(shù)據(jù)的方法，包括隨機數(shù)字、驗證碼、隨機字符串以及隨機日期和時間等，同時還介紹了如何從表中返回隨機記錄，以及如何生成 UUID。