MySQL method of generating random numbers, strings, dates, verification codes and UUIDs

The previous article introduced how to generate random numbers, strings, dates, verification codes, and UUIDs in Oracle. Today we will continue to discuss how to generate various random data in MySQL.

📝All numbers generated by computers are pseudo-random numbers, not real physical random numbers.

Generate random numbers

Generate a random number between 0 and 1

The RAND function in MySQL can be used to generate a random number greater than or equal to 0 and less than 1. For example:

SELECT rand();
rand() |
------------------|
0.7245639057127423|

SELECT rand();
rand() |
-------------------|
0.01697599982561171|

The function returns a double value with 16 decimal places; each call returns different data.

If you want to reproduce certain scenarios, you need to make sure you generate the same random numbers each time you run it. In this case, you can pass an input parameter to the RAND function to set a random number seed. For example:

SELECT rand(1);
rand(1) |
-------------------|
0.40540353712197724|

SELECT rand(1);
rand(1) |
-------------------|
0.40540353712197724|

From the results, we can see that the same seed returns the same random number.

Generates a random number in the specified range

Based on the RAND function and mathematical operations, you can return a random number between any two numbers:

low + RAND() * (high − low)

The above expression will return a random number greater than or equal to low and less than high. For example:

SELECT 10 + rand(1) * 10;
10 + rand(1) * 10 |
------------------|
14.054035371219772|

The above example returns a random number greater than or equal to 10 and less than 20.

If you want to generate random integers within a certain range, you can add the FLOOR function. For example:

SELECT floor(10 + rand(1) * (10)) AS rd;
rd |
----|
14.0|

This statement returns a random integer greater than or equal to 10 and less than or equal to 19 (not 20).

Generate a 6-digit mobile verification code

We have obtained a random integer within the specified range, and together with the LPAD function, we can generate a mobile phone verification code consisting of 6 digits. For example:

SELECT lpad(floor(rand(999) * 1000000), 6, '0') AS captcha;
captcha|
-------|
088146 |

The lpad function can ensure that 0 is added to the front when the data is less than 6 bits.

Generates random numbers following a normal distribution

The RAND function generates a random number that follows a uniform distribution. MySQL does not provide a function for generating random numbers that follow a normal distribution. We can create a stored function to simulate normally distributed random numbers:

delimiter //
create function normal_distrib(mean double, stdev double) 
returns double no sql
begin
set @x = rand(), @y = rand();
set @nd = (sqrt(-2 * log(@x)) * cos(2 * pi() * @y)) * stdev + mean;
return @nd;
end
//
delimiter ;

The above function generates normally distributed random numbers from two evenly distributed random numbers using the Box-Muller transformation algorithm.

The following statement generates a normally distributed random number with an expected value of 0 and a standard deviation of 1 using the normal_distrib function:

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

The following statement can be used to verify whether the normal_distrib function follows a normal distribution:

with recursive temp(val) as (
 select normal_distrib(0,1)
 union all
 select normal_distrib(0,1)
 from temp
 limit 1000000
)
select /*+ set_var(cte_max_recursion_depth = 1m) */avg(val),std(val)
from temp;
avg(val) |std(val) |
---------------------|------------------|
-0.002340136192616743|0.9994844557755181|

By running this data 1,000,000 times, calculating the mean and standard deviation, the results returned are very close to 0 and 1.

Generate a random string

Generate a random string of fixed length

In addition to random numbers, sometimes we also need to generate some random strings. MySQL does not provide a function specifically for generating random strings, but it can be simulated through other functions. For example:

SELECT char(floor(rand() * 26)+65) as rand_char;
rand_char|
---------|
T |

The above query returns a random uppercase letter. The char function is used to convert the ASCII code to the corresponding character. We can further create a stored function based on this query:

delimiter //
create function rand_string(len integer) 
returns text no sql
begin
 declare counter int default 1;
 declare str text default '';
 
 if len < 1 then
 return null;
 end if;
 
 while counter <= len do
 set str = concat(str, char(floor(rand() * 94) + 33));
 set counter = counter + 1;
 end while;
 
 return str;
end
//
delimiter ;

The rand_string function returns a random string consisting of any printable characters (ASCII codes from 33 to 126). For example:

rand_string(8)|
--------------|
7j5dz[58 |

The above example returns a random string of length 8 consisting of printable characters.

In addition, the elt function in MySQL can also be used to return the element in the specified position. For example:

SELECT elt(1 + floor(rand() * 36),
   0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 
   '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') as val;
val|
---|
B |

The above statement returns a random number or uppercase letter. Substituting it into the rand_string function can return a random string consisting of numbers and uppercase letters.

Generates a random string of variable length

So, how do you return a random string of variable length? It's very simple, just specify a random length parameter for the rand_string function. For example:

SELECT rand_string(floor(10 + rand() * 11));
rand_string(floor(10 + rand() * 11))|
------------------------------------|
4U13MjO+($}k"cO@5%[ |

The above example returns a random string of printable characters with a length greater than or equal to 10 and less than or equal to 20.

Generate a random date and time

By adding a random number to the specified date, you can get a random date. For example:

SELECT date_add('2020-01-01', interval rand() * 31 day) rand_date;
rand_date |
----------|
2020-01-19|

The above example returns a random date in January 2020. The following statement returns a random time of day:

SELECT sec_to_time(rand() * 3600) rand_time;
rand_time |
------------------|
00:05:29.546878000|

Among them, the sec_to_time function is used to convert seconds to time data.

Get a random record from a table

For a query statement that returns multiple rows of data, the RAND function returns different random data each time. For example:

SELECT rand(1) FROM employee;
rand(1) |
--------------------|
 0.40540353712197724|
 0.8716141803857071|
 0.1418603212962489|
...

Using this feature, we can return random rows of data from a table. For example:

SELECT emp_id,emp_name
FROM employee
ORDER BY rand(1)
LIMIT 5;
emp_id|emp_name |
------|----------|
  6|Wei Yan|
 14|Zhang Bao|
 16|Zhou Cang|
 15|Zhao Tong|
  1|Liu Bei|

The above example returns 5 random rows from the employee table. This method needs to generate a random number for each row of data in the table and then sort it; so it will gradually become slower as the amount of data in the table increases.

If there is an auto-increment primary key in the table, you can also generate random data based on the primary key. For example:

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

Then return a random record based on this random number:

SELECT e.emp_id, e.emp_name
FROM employee
INNER JOIN (SELECT round(rand() * (SELECT max(emp_id) FROM employee)) AS id
  ) AS t
WHERE e.emp_id >= t.id
LIMIT 1;
emp_id|emp_name|
------|--------|
  9|Zhao Yun|

This method can only return one random record at a time, and will only return uniformly distributed random records when there are no gaps in the values ​​of the auto-increment field.

Generating UUID

UUID (Universal Unique Identifier) ​​or GUID (Globally Unique Identifier) ​​is a 128-bit number that can be used to uniquely identify each network object or resource. Due to its generation mechanism, a UUID is guaranteed to be almost never repeated with other UUIDs, so it is often used to generate primary key values ​​in databases.

MySQL provides a system function UUID that can be used to generate UUID. For example:

SELECT uuid();
uuid() |
------------------------------------|
35f67fde-e0e9-11ea-9d25-0800272142b1|

If you want to generate a UUID without hyphens (-), you can use the REPLACE function:

SELECT replace(uuid(),'-','');
replace(uuid(),'-','') |
--------------------------------|
8505290be0ea11ea9d250800272142b1|

In addition, MySQL also provides a UUID_SHORT function, which can return a 64-bit unsigned integer. For example:

SELECT uuid_short();
uuid_short() |
-----------------|
98862025337208832|

This function returns a "short" unique identifier that is unique only if the following conditions are met:

• The server_id of the current server is between 0 and 255 and is unique in the replication structure;
• The system time of the server host was not adjusted back before and after restarting mysqld;
• The average number of calls per second is less than 16 million.

Summarize

This article introduces how to generate random data in a MySQL database, including random numbers, verification codes, random strings, and random dates and times. It also introduces how to return random records from a table and how to generate UUIDs.

This is the end of this article about how to generate random numbers, strings, dates, verification codes and UUIDs in MySQL. For more information about how to generate random numbers and UUIDs in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!