MYSQL generate a unique random number that's different from all existing values-CodePudding

I'm trying to find an efficent way to generate a unique number that's different from all existing values in MYSQL database. I tried to do that in PHP, but it's not efficent because it has to deal with lots of loops. Recently, I tried to do this with MYSQL and I found this sqlfiddle solution. But It doesn't work. Sometimes it generates the same number as in the table and it doesn't check every value in the table. I tried this this but didn't help. They generally give that query:

SELECT *, FLOOR(RAND() * 9) AS random_number FROM Table1 
WHERE "random_number" NOT IN (SELECT tracker FROM Table1)

I will work with 6-digit numbers in the future, so I need that to be efficent and fast. I can use different methods such as pre-generating the numbers to be more efficent but I don't know how to handle that. I would be glad if you help.

CodePudding user response：

If you can move away from the 6 digit (numeric) requirement, I would as it would allow you to create true random strings with some sort of uuid() function.

However, if this needs to be done outside of PHP and has to be 6 digit numbers, I would use an auto-increment column in MySQL.

If there needs to be some randomness, you can adjust the auto-increment column by a random increase:

alter table tableName auto_increment = [insert new starting number here];

This of course may find you in 7 digit numbers rather quickly.

Alternatively, I'd see the solution being PHP picking a random number and checking that against the DB (or pull in the rows of the DB first to check against without a DB query every time).

CodePudding user response：

Random numbers and non-repeatable numbers are basically 2 different things that are mutually exclusive. Can it be that a sequence of numbers that only looks like random numbers is enough?

If yes, then I have a solution for it:

Use auto increment of your database.
Multiply the Id by a prime number. Other manipulations like bit rotations are possible too.

About prime number: It is important, the the value range (in your case 1000000) and the multiplicand have no common prime divisors. Otherwise the sequence of numbers is much shorter.

Here is an example for 6 digits:

MYSQL_INSERT_INSTRUCTION;
$id = $mysql_conn->insert_id;
$random_id = $id * 683567 % 1000000;

With this you get:

After 1000000 records the whole sequence is repeated. I recommend the usage of the full range of 32 bits. So the sequence have 4 294 967 296 different numbers. In this case use a a much larger prime number, e.g. about 2.7e9.

Alternatives

Instead of $random_id = $id * 683567 % 1000000; you can user other calculations to disguise your algorithm. Some examples:

# add a value
$random_id = ( $id * 683567   12345 ) % 1000000;

# add a value and swap higher and lower part
$temp = ( $id * 683567   12345 ) % 1000000;
$random_id = intdiv($temp/54321)   ($tempT321)*54321;