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

Microsoft SQL Server 生成隨機(jī)數(shù)字、字符串、日期、驗(yàn)證碼以及 UUID

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

文章目錄

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

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。

隨機(jī)數(shù)對(duì)于我們而言并不陌生，例如手機(jī)短信驗(yàn)證碼就是一個(gè)隨機(jī)的數(shù)字字符串；對(duì)于統(tǒng)計(jì)分析、機(jī)器學(xué)習(xí)等領(lǐng)域而言，通常也需要生成大量的隨機(jī)數(shù)據(jù)用于測試、數(shù)據(jù)抽樣、算法驗(yàn)證等。那么今天我們就來介紹一下如何在 Microsoft SQL Server 中生成隨機(jī)數(shù)據(jù)，包括隨機(jī)數(shù)字、驗(yàn)證碼、隨機(jī)字符串以及隨機(jī)日期和時(shí)間等。

如果覺得文章有用，歡迎評(píng)論??、點(diǎn)贊??、推薦??

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

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

Microsoft SQL Server 提供了內(nèi)置的RAND()函數(shù)，可以用于生成一個(gè)大于 0 小于 1 的隨機(jī)數(shù)字。例如：

SELECT RAND() AS rd;
rd                |
------------------|
0.8186902226508435|

SELECT RAND() AS rd;
rd                |
------------------|
0.7467745597058656|



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

如果想要重現(xiàn)某些場景，需要確保每次運(yùn)行時(shí)生成相同的隨機(jī)數(shù)。這種情況下可以為 RAND 函數(shù)傳遞一個(gè)輸入?yún)?shù)，設(shè)置一個(gè)隨機(jī)數(shù)種子。例如：

SELECT RAND(1) AS rd;
rd                |
------------------|
0.7135919932129235|

SELECT RAND(1) AS rd;
rd                |
------------------|
0.7135919932129235|


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

基于 RAND() 函數(shù)和一些運(yùn)算，就可以返回任意兩個(gè)數(shù)字之間的隨機(jī)數(shù)：

low + RAND() * (high - low)



以上表達(dá)式將會(huì)返回一個(gè)大于 low，小于 high 的隨機(jī)數(shù)。例如：

SELECT 10 + RAND() * 10 AS rd;
rd               |
-----------------|
13.43443392823765|



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

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

SELECT FLOOR(10 + RAND() * 10) AS rd;
rd  |
----|
17.0|



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

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

SELECT RIGHT(CONCAT('000000',FLOOR(RAND()* 1000000)),6) AS captcha;
captcha|
-------|
925656 |



CONCAT 函數(shù)可以確保數(shù)據(jù)不夠 6 位時(shí)在前面補(bǔ)足 0，然后通過 RIGHT 函數(shù)返回右側(cè)的 6 個(gè)數(shù)字。
生成遵循正態(tài)分布的隨機(jī)數(shù)

RAND 函數(shù)生成的是一個(gè)遵循均勻分布的隨機(jī)數(shù)，Microsoft SQL Server沒有提供生成遵循正態(tài)分布（normal distribution）的隨機(jī)數(shù)。我們可以模擬生成遵循正態(tài)分布的隨機(jī)數(shù)：

SELECT (sqrt(-2 * log(rand())) * cos(2 * pi() * rand())) * @stdev + @mean;



以上查詢利用 Box-Muller 變換算法通過兩個(gè)平均分布的隨機(jī)數(shù)生成正態(tài)分布的隨機(jī)數(shù)，@stdev 是標(biāo)準(zhǔn)差，@mean 是均值。例如，以下查詢生成了一個(gè)均值為 0，標(biāo)準(zhǔn)差為 1 的正態(tài)分布隨機(jī)數(shù)：

SELECT (sqrt(-2 * log(rand())) * cos(2 * pi() * rand())) * 1 + 0 AS rd;
rd                 |
-------------------|
-0.4356079973405327|



我們可以驗(yàn)證一下這種方式生成的隨機(jī)數(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|



通過運(yùn)行 1000000 次計(jì)算，這些數(shù)據(jù)的平均值和標(biāo)準(zhǔn)差非常接近 0 和 1。
生成隨機(jī)字符串
生成固定長度的隨機(jī)字符串

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

SELECT CHAR(RAND() * 26 + 65) ch;
ch|
--|
D |



以上查詢返回了一個(gè)隨機(jī)的大寫字母，CHAR 函數(shù)用于將 ASCII 編碼轉(zhuǎn)換為對(duì)應(yīng)的字符。我們可以基于該查詢進(jìn)一步創(chuàng)建一個(gè)存儲(chǔ)函數(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ù)可以返回由指定字符（默認(rèn)為所有數(shù)字、大小寫字母）組成的隨機(jī)字符串。例如：

SELECT dbo.random_string(10,DEFAULT) AS rs;
rs        |
----------|
3CBFEPKYTd|



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

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



生成可變長度的隨機(jī)字符串

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

SELECT dbo.random_string(10 + rand() * 11, DEFAULT) AS rs;
rs             |
---------------|
5ikNBTJLj1vKUKz|



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

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

SELECT DATEADD(DAY, rand() * 15, CAST(GETDATE()AS date)) AS rand_date;
rand_date |
----------|
2020-12-18|



DATEADD 函數(shù)用于日期的加減運(yùn)算，CAST 函數(shù)用于類型轉(zhuǎn)換。以上示例返回了當(dāng)前日期加上 14 天之內(nèi)的某個(gè)隨機(jī)日期。

同樣，以下語句返回了一天中的某個(gè)隨機(jī)時(shí)間：

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）是一個(gè) 128 比特的數(shù)字，可以用于唯一標(biāo)識(shí)每個(gè)網(wǎng)絡(luò)對(duì)象或資源。由于它的生成機(jī)制，一個(gè) UUID 可以保證幾乎不會(huì)與其他 UUID 重復(fù)，因此常常用于生成數(shù)據(jù)庫中的主鍵值。

Microsoft SQL Server 提供了一個(gè)系統(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|



獲取表中的隨機(jī)記錄

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

SELECT RAND() AS rd FROM employee;
rd                |
------------------|
0.9915256467818716|
0.9915256467818716|
0.9915256467818716|
...



因此，我們不能利用 RAND 函數(shù)從表中返回隨機(jī)的數(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 個(gè)隨機(jī)記錄。該方法需要為表中的每行數(shù)據(jù)都生成一個(gè)隨機(jī)數(shù)，然后進(jìn)行排序；所以會(huì)隨著表中的數(shù)據(jù)量增加而逐漸變慢。

另外，Microsoft SQL Server 中的查詢語句支持 TABLESAMPLE 子句，可以實(shí)現(xiàn)數(shù)據(jù)的近似抽樣，不過需要數(shù)據(jù)量大的時(shí)候才比較合適。
總結(jié)

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