How to generate random numbers with specified digits in MySQL and how to generate random numbers in batches

1. First introduce several commonly used MySQL functions

RAND() randomly generates a decimal between 0 and 1 (0<1)

CEILING Round up
FLOOR Round down

2. Generate random numbers

-- Generate a 3-digit random number SELECT CEILING(RAND()*900+100);
-- Generate a 4-digit random number SELECT CEILING(RAND()*9000+1000);
-- Generate a 5-digit random number SELECT CEILING(RAND()*90000+10000);
...

Example:

To explain:

-- Generate a random decimal between 0 and 1 0 < RAND() < 1

-- Multiply by 9000 to get a random decimal between 0 and 9000 0 < RAND*9000 < 9000

-- Add 1000 to get a random decimal between 0 and 10000 0 < RAND*9000+1000 < 10000

-- Use the CEILING function to round up, remove the trailing decimals, and get an integer -- or use the FLOOR function to round down, both are CEILING(RAND()*9000+1000)
-- or FLOOR(RAND()*9000+1000)

3. Note

This method of generating a specified number of random numbers is not particularly good because there is a possibility of duplication. The fewer digits a random number has, the greater the probability of repetition. Therefore, this method is only useful in special occasions.

mysql batch generate random numbers

Fake data:

update exercise_data set star_num=FLOOR(1 + (RAND() * 5));

The rand function randomly generates a random number between 0 and 1. Multiplying by 5 will get a random number between 1 and 5, and floor is the previous integer.

The above is the method that I introduced to you to generate a random number of specified digits in MySQL. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!