MYSQL generate a unique random-looking number without using PHP loops

Question

Without using PHP loops, 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 in the future. 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.

EDIT: Based on @Wiimm's solution, I can fill the table with 999.999 different 'random' unique numbers without using PHP loop functions. Then I developed a method where ID's of the deleted rows can be reused. This is how I managed it:

1. Duplicate your original table and name it "table_deleted". (All columns must be the same.)
2. Create a trigger in MYSQL. To do that, enter "SQL" in MYSQL and run this code (It simply moves your row to the "table_deleted"):

MYSQL Code

DELIMITER
        $$
        CREATE TRIGGER `table_before_delete` BEFORE DELETE
        ON
            `your_table` FOR EACH ROW
        BEGIN
            INSERT INTO table_deleted
            select * from your_table where id = old.id;
        END ; $$
DELIMITER
;

3. Create another trigger. This code will move the row back to original table when it's updated.

MYSQL Code

DELIMITER
        $$
        CREATE TRIGGER `table_after_update` AFTER UPDATE
        ON
                `your_table` FOR EACH ROW
        BEGIN
            INSERT INTO your_table
            select * from table_deleted where id = old.id;
        END ; $$
DELIMITER
    ;

4. The PHP code that I use (The number column must be "NULL" to work this code):

PHP Code

//CHECK IF TABLE_DELETED HAS ROWS
$deleted = $db->query('SELECT COUNT(*) AS num_rows FROM table_deleted');
$deletedcount= $temp->fetchColumn();
//IF TABLE_DELETED HAS ROWS, RUN THIS
if($tempcount > 0) {
   //UPDATE THE VALUE THAT HAS MINIMUM ID
   $update = $db->prepare("UPDATE table_deleted SET value1= ?, value2= ?, value3= ?, value4= ? ORDER BY id LIMIT 1");
   $update->execute(array("$value1","$value2","$value3","$value4"));
   //AFTER UPDATE, DELETE THAT ROW
   $delete=$db->prepare("DELETE from table_deleted ORDER BY id LIMIT 1");
   $delete->execute();
}
else {
   //IF TABLE_DELETED IS EMPTY, ADD YOUR VAULES (EXCEPT RANDOM NUMBER)
   $query=$db->prepare("insert into your_table set value1= ?, value2= ?, value3= ?, value4= ?");
   $query->execute(array("$value1","$value2","$value3","$value4"));
   //USING @Wiimm's SOLUTION FOR GENERATING A RANDOM-LOOKING UNIQUE NUMBER
   $last_id = $db->lastInsertId();
   $number= str_pad($last_id * 683567 % 1000000, 6, '0', STR_PAD_LEFT);
   //INSERT THAT RANDOM NUMBER TO THE CURRENT ROW
   $insertnumber= $db->prepare("UPDATE your_table SET number= :number where id = :id");
   $insertnumber->execute(array("number" => "$number", "id" => "$last_id"));
}

MYSQL triggers do the rest for you.

Answer 1

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).

Answer 2

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 for you?

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, that 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:

 1: 683567
 2: 367134
 3:  50701
 4: 734268
 5: 417835
 6: 101402
 7: 784969
 8: 468536
 9: 152103
10: 835670
11: 519237
12: 202804
13: 886371
14: 569938
15: 253505
16: 937072
17: 620639
18: 304206
19: 987773
20: 671340

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 much larger prime number, e.g. about 2.8e9. I use always a prime ~ 0.86*RANGE for this.

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) + ($temp%54321)*54321;