MySQL generates non-repeating random numbers (unique number generation)

Transferred from: http://blog.csdn.net/dreamer2020/article/details/52049629

Source of the problem

There are times in the business where you want to generate non-repeating random numbers, such as a new product number, room number, or other item number. You do not want to use the self-index ID of the table, and hope that the newly generated number is not duplicated.

This will require a test of MySQL's random number function.

Solution

The RAND function of MySQL generates a random number between 0 and 1, and a random number can be obtained by a certain amplification. The conditional query then restricts the new random number from appearing in the table. As shown below:

SELECT FLOOR(RAND() * 99999) AS random_numFROM numbers WHERE "random_num" NOT IN (SELECT my_number FROM numbers)LIMIT 1

However, after measurement, the method returns to empty when the table is empty . Consider the case of an empty table, plus a collection, as follows:

SELECT random_numFROM (  SELECT FLOOR(RAND() * 99999) AS random_num FROM numbers UNION SELECT FLOOR(RAND() * 99999) AS random_num) AS ssWHERE "random_num" NOT IN (SELECT my_number FROM numbers)LIMIT 1

By the Union field, when the table is empty, the direct fetch of a random number is returned.

Extension: MySQL random fetch record

Basic idea: random sorting.

select * from numbers order by rand() limit 1

This line of command allows you to randomly select a record from the table. How to select multiple bars randomly, as follows

select * from numbers order by rand() limit 10

It is said that the order by rand () method is inefficient, and I do not have in the record more than the table measured. Under the premise of small scale, this method should be the simplest. If you want to consider efficiency, please refer to the link at the end of this article.

Attached link

How to Generate Random number without repeat in database using PHP?

MySQL Unique number generation

MySQL Select ten random rows from 600K rows fast

MySQL generates non-repeating random numbers (unique number generation)