PostgreSQL 生成隨機數字、字串、日期、驗證碼以及 UUID

不剪髮的Tony老師發表於2020-10-22
SQL隨機字串UI

隨機數

文章目錄

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

?計算機生成的都是偽隨機數，並不是真正的物理隨機數。

生成隨機數字

生成 0 到 1 之間的隨機數

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

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

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

該函式返回的資料型別為 double precision，每次呼叫都會返回不同的結果。

如果我們想要重現某個結果，需要生成相同的隨機數；這種情況下可以使用SETSEED(d)函式設定一個隨機數種子，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|

從結果可以看出，設定相同的種子之後，隨後的函式呼叫返回了一系列相同的隨機數。

生成指定範圍內的隨機數

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

low + RAND() * (high - low)

以上表示式將會返回一個大於等於 low，小於 high 的隨機數。例如：

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

以上示例返回了一個大於等於 10 且小於 20 的隨機數字。

如果想要生成某個範圍內的隨機整數，可以加上 FLOOR 函式。例如：

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

該語句返回了一個大於等於 10，小於等於 19（不是 20）的隨機整數。

生成 6 位數字手機驗證碼

我們已經知道了如何獲取指定範圍內的隨機數，再加上 TO_CHAR 格式化函式就可以生成由 6 位數字字元組成的手機驗證碼。例如：

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

TO_CHAR 函式可以確保資料不夠 6 位時在前面補足 0。

生成遵循正態分佈的隨機數

PostgreSQL 提供了一個擴充套件模組 tablefunc，可以用於生成遵循正態分佈（normal distribution）的隨機數。首先，輸入以下命令啟用該模組：

CREATE EXTENSION tablefunc;

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

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 個遵循標準正態分佈的隨機數。

我們也可以驗證一下該函式是否遵循正態分佈，例如：

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

另一種方法就建立一個儲存函式來模擬正態分佈的隨機數：

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 $$;

該函式利用 Box-Muller 變換演算法通過兩個平均分佈的隨機數生成正態分佈的隨機數。以下語句通過 normal_distrib 函式生成了一個遵循正態分佈的隨機數：

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

以下語句可以用於驗證 normal_distrib 函式是否遵循正態分佈：

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|

經過測試，自定義的 normal_distrib 函式執行時間為 12.5 s，normal_rand 函式只需要 1.5 s。

生成隨機字串

生成固定長度的隨機字串

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

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

以上查詢返回了一個隨機的大寫字母，chr 函式用於將 ASCII 碼轉換為對應的字元。我們可以基於該查詢進一步建立一個儲存函式：

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 函式可以返回由指定字元（預設為所有數字、大小寫字母）組成的隨機字串。例如：

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

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

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

生成可變長度的隨機字串

那麼，怎麼返回一個長度可變的隨機字串呢？很簡單，為 random_string 函式指定一個隨機的長度引數即可。例如：

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

以上示例返回了一個長度大於等於 10 且小於等於 20 的隨機字串。

生成隨機日期和時間

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

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

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

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 函式用於將代表時、分、秒的整數轉換為時間。

獲取表中的隨機記錄

對於返回多行資料的查詢語句，RANDOM 函式每次都會返回不同的隨機值。例如：

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

利用這個特性，我們可以從表中返回隨機的資料行。例如：

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

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

如果表中存在自增主鍵，也可以基於主鍵生成一個隨機資料。例如：

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

然後基於這個隨機數返回一條隨機的記錄：

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 子句，可以實現資料的抽樣。例如：

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

除了 BERNOULLI 之外，也可以指定 SYSTEM 抽樣方法，引數代表了抽樣近似百分比。

生成 UUID

UUID（Universal Unique Identifier）或者 GUID（Globally Unique Identifier）是一個 128 位元的數字，可以用於唯一標識每個網路物件或資源。由於它的生成機制，一個 UUID 可以保證幾乎不會與其他 UUID 重複，因此常常用於生成資料庫中的主鍵值。

PostgreSQL 提供了一個用於加/解密的擴充套件模組 pgcrypto，其中的 gen_random_uuid() 函式可以用於返回一個 version 4 的隨機 UUID。首先，輸入以下命令啟用該模組（gen_random_uuid() 從 PostgreSQL 13 開始成為了一個內建函式）：

CREATE EXTENSION pgcrypto;

然後，通過該函式返回一個 UUID：

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

該函式返回的資料型別為 uuid。如果想要生成沒有中劃線（-）的 UUID 字串，可以使用 REPLACE 函式：

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

總結

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

相關文章