個人中心
退出
Microsoft SQL Server 生成隨機數(shù)字、字符串、日期、驗證碼以及 UUID
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
生成隨機數(shù)字
生成 0 到 1 之間的隨機數(shù)
生成指定范圍內(nèi)的隨機數(shù)
生成 6 位數(shù)字手機驗證碼
生成遵循正態(tài)分布的隨機數(shù)
生成隨機字符串
生成固定長度的隨機字符串
生成可變長度的隨機字符串
生成隨機日期和時間
生成 UUID
獲取表中的隨機記錄
總結(jié)
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。
隨機數(shù)對于我們而言并不陌生，例如手機短信驗證碼就是一個隨機的數(shù)字字符串；對于統(tǒng)計分析、機器學(xué)習(xí)等領(lǐng)域而言，通常也需要生成大量的隨機數(shù)據(jù)用于測試、數(shù)據(jù)抽樣、算法驗證等。那么今天我們就來介紹一下如何在 Microsoft SQL Server 中生成隨機數(shù)據(jù)，包括隨機數(shù)字、驗證碼、隨機字符串以及隨機日期和時間等。
如果覺得文章有用，歡迎評論??、點贊??、推薦??
??計算機生成的都是偽隨機數(shù)，并不是真正的物理隨機數(shù)。
生成隨機數(shù)字
生成 0 到 1 之間的隨機數(shù)
Microsoft SQL Server 提供了內(nèi)置的RAND()函數(shù)，可以用于生成一個大于 0 小于 1 的隨機數(shù)字。例如：
SELECT RAND() AS rd;
rd |
------------------|
0.8186902226508435|
SELECT RAND() AS rd;
rd |
------------------|
0.7467745597058656|
該函數(shù)返回的數(shù)據(jù)類型為 float，每次調(diào)用都會返回不同的結(jié)果。
如果想要重現(xiàn)某些場景，需要確保每次運行時生成相同的隨機數(shù)。這種情況下可以為 RAND 函數(shù)傳遞一個輸入?yún)?shù)，設(shè)置一個隨機數(shù)種子。例如：
SELECT RAND(1) AS rd;
rd |
------------------|
0.7135919932129235|
SELECT RAND(1) AS rd;
rd |
------------------|
0.7135919932129235|
從結(jié)果可以看出，設(shè)置相同的種子之后，函數(shù)返回了相同的隨機數(shù)。
生成指定范圍內(nèi)的隨機數(shù)
基于 RAND() 函數(shù)和一些運算，就可以返回任意兩個數(shù)字之間的隨機數(shù)：
low + RAND() * (high - low)
以上表達式將會返回一個大于 low，小于 high 的隨機數(shù)。例如：
SELECT 10 + RAND() * 10 AS rd;
rd |
-----------------|
13.43443392823765|
以上示例返回了一個大于 10 且小于 20 的隨機數(shù)字。
如果想要生成某個范圍內(nèi)的隨機整數(shù)，可以加上 FLOOR 函數(shù)。例如：
SELECT FLOOR(10 + RAND() * 10) AS rd;
rd |
----|
17.0|
該語句返回了一個大于等于 10，小于（不等于）20的隨機整數(shù)。
生成 6 位數(shù)字手機驗證碼
我們已經(jīng)知道了如何獲取指定范圍內(nèi)的隨機數(shù)，再加上字符串格式化就可以生成由 6 位數(shù)字字符組成的手機驗證碼。例如：
SELECT RIGHT(CONCAT('000000',FLOOR(RAND()* 1000000)),6) AS captcha;
captcha|
-------|
925656 |
CONCAT 函數(shù)可以確保數(shù)據(jù)不夠 6 位時在前面補足 0，然后通過 RIGHT 函數(shù)返回右側(cè)的 6 個數(shù)字。
生成遵循正態(tài)分布的隨機數(shù)
RAND 函數(shù)生成的是一個遵循均勻分布的隨機數(shù)，Microsoft SQL Server沒有提供生成遵循正態(tài)分布（normal distribution）的隨機數(shù)。我們可以模擬生成遵循正態(tài)分布的隨機數(shù)：
SELECT (sqrt(-2 * log(rand())) * cos(2 * pi() * rand())) * @stdev + @mean;
以上查詢利用 Box-Muller 變換算法通過兩個平均分布的隨機數(shù)生成正態(tài)分布的隨機數(shù)，@stdev 是標準差，@mean 是均值。例如，以下查詢生成了一個均值為 0，標準差為 1 的正態(tài)分布隨機數(shù)：
SELECT (sqrt(-2 * log(rand())) * cos(2 * pi() * rand())) * 1 + 0 AS rd;
rd |
-------------------|
-0.4356079973405327|
我們可以驗證一下這種方式生成的隨機數(shù)是否遵循正態(tài)分布，例如：
CREATE TABLE #t(val float);
BEGIN
DECLARE @counter INT = 1
WHILE @counter <= 1000000
BEGIN
INSERT INTO #t
SELECT (sqrt(-2 * log(rand())) * cos(2 * pi() * rand())) * 1 + 0
SET @counter = @counter + 1
END
END;
SELECT avg(val) avg, stdev(val) stdev FROM #t;
avg |stdev |
------------------|------------------|
0.0009166176910995|1.0014773595001807|
通過運行 1000000 次計算，這些數(shù)據(jù)的平均值和標準差非常接近 0 和 1。
生成隨機字符串
生成固定長度的隨機字符串
除了隨機數(shù)字之外，有時候我們也需要生成一些隨機的字符串。Microsoft SQL Server 沒有提供專門生成隨機字符串的函數(shù)，但是可以通過其他函數(shù)進行模擬。例如：
SELECT CHAR(RAND() * 26 + 65) ch;
ch|
--|
D |
以上查詢返回了一個隨機的大寫字母，CHAR 函數(shù)用于將 ASCII 編碼轉(zhuǎn)換為對應(yīng)的字符。我們可以基于該查詢進一步創(chuàng)建一個存儲函數(shù)：
CREATE VIEW v_rand
AS
SELECT RAND() AS val;
CREATE OR ALTER FUNCTION rand_num(@n INT)
RETURNS INT
AS BEGIN
SELECT @n=@n * val FROM v_rand
RETURN @n
END;
CREATE OR ALTER FUNCTION random_string(
@num INT,
@chars VARCHAR(1024) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
) RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @res_str VARCHAR(1024) = ''
DECLARE @i INT=0
WHILE(@i<@num)BEGIN
SET @res_str = @res_str + SUBSTRING(@chars, FLOOR(dbo.rand_num(len(@chars))) + 1, 1)
SET @i = @i + 1
END
RETURN @res_str
END;
random_string 函數(shù)可以返回由指定字符（默認為所有數(shù)字、大小寫字母）組成的隨機字符串。例如：
SELECT dbo.random_string(10,DEFAULT) AS rs;
rs |
----------|
3CBFEPKYTd|
以上示例返回了一個由字母和數(shù)字組成、長度為 10 的隨機字符串。以下語句也可以用于返回一個 6 位隨機數(shù)字組成的手機驗證碼：
SELECT random_string(6, '0123456789');
random_string|
-------------|
082661 |
生成可變長度的隨機字符串
那么，怎么返回一個長度可變的隨機字符串呢？很簡單，為 random_string 函數(shù)指定一個隨機的長度參數(shù)即可。例如：
SELECT dbo.random_string(10 + rand() * 11, DEFAULT) AS rs;
rs |
---------------|
5ikNBTJLj1vKUKz|
以上示例返回了一個長度大于等于 10 且小于等于 20 的隨機字符串。
生成隨機日期和時間
將指定日期增加一個隨機的數(shù)字，就可以得到隨機的日期。例如：
SELECT DATEADD(DAY, rand() * 15, CAST(GETDATE()AS date)) AS rand_date;
rand_date |
----------|
2020-12-18|
DATEADD 函數(shù)用于日期的加減運算，CAST 函數(shù)用于類型轉(zhuǎn)換。以上示例返回了當(dāng)前日期加上 14 天之內(nèi)的某個隨機日期。
同樣，以下語句返回了一天中的某個隨機時間：
SELECT CAST(DATEADD(SECOND, rand() * 86400, GETDATE()) AS TIME) AS rand_time;
rand_time|
---------|
14:14:10|
生成 UUID
UUID（Universal Unique Identifier）或者 GUID（Globally Unique Identifier）是一個 128 比特的數(shù)字，可以用于唯一標識每個網(wǎng)絡(luò)對象或資源。由于它的生成機制，一個 UUID 可以保證幾乎不會與其他 UUID 重復(fù)，因此常常用于生成數(shù)據(jù)庫中的主鍵值。
Microsoft SQL Server 提供了一個系統(tǒng)函數(shù) NEWID，可以用于生成 UUID。例如：
SELECT NEWID() AS uuid;
uuid |
------------------------------------|
88E68504-7384-4AB8-A796-D13A8528AC21|
該函數(shù)返回的數(shù)據(jù)類型為 uniqueidentifier。如果想要生成沒有中劃線（-）的 UUID 字符串，可以使用 REPLACE 函數(shù)：
SELECT REPLACE(NEWID(),'-','') AS uuid;
uuid |
--------------------------------|
037C73E57F084E33AE026C949454AFF9|
獲取表中的隨機記錄
對于返回多行數(shù)據(jù)的查詢語句，RAND 函數(shù)每次都會返回相同的隨機值。例如：
SELECT RAND() AS rd FROM employee;
rd |
------------------|
0.9915256467818716|
0.9915256467818716|
0.9915256467818716|
...
因此，我們不能利用 RAND 函數(shù)從表中返回隨機的數(shù)據(jù)行，不過可以使用 NEWID 函數(shù)。例如：
SELECT TOP(5) emp_id, emp_name
FROM employee
ORDER BY NEWID();
emp_id|emp_name|
------|--------|
18|法正 |
8|孫丫鬟 |
24|簡雍 |
2|關(guān)羽 |
7|孫尚香 |
以上示例從 employee 表中返回了 5 個隨機記錄。該方法需要為表中的每行數(shù)據(jù)都生成一個隨機數(shù)，然后進行排序；所以會隨著表中的數(shù)據(jù)量增加而逐漸變慢。
另外，Microsoft SQL Server 中的查詢語句支持 TABLESAMPLE 子句，可以實現(xiàn)數(shù)據(jù)的近似抽樣，不過需要數(shù)據(jù)量大的時候才比較合適。
總結(jié)
本文介紹了在 Microsoft SQL Server 中生成隨機數(shù)據(jù)的方法，包括隨機數(shù)字、驗證碼、隨機字符串以及隨機日期和時間等，同時還介紹了如何生成 UUID，以及如何從表中返回隨機記錄。
分類導(dǎo)航