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

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:

Duplicate your original table and name it "table_deleted". (All columns must be the same.)
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
;

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
    ;

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.

Solution

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:

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;