How to insert a random number which is not present in MySQL table?

Question

Main Query

$sql = "INSERT INTO foldertable ( folderid )
             SELECT (
                        SELECT random_num
                        FROM (
                                SELECT FLOOR(RAND() * 4 + 1) AS random_num
                        ) AS numbers_mst_plus_1
                        WHERE random_num NOT IN (
                            0,1
                        )
                                  )";

Problem

It inserts a Random number . But sometimes it simply inserting 0 .

What I figure ?

When The below SQL returns a number which is equal to 0 or 1 , It is inserting zero .

SELECT random_num
                        FROM (
                                SELECT FLOOR(RAND() * 4 + 1) AS random_num
                        ) AS numbers_mst_plus_1
                        WHERE random_num NOT IN (
                            0,1
                        )

What I want ?

select query to select a number Until it is unique for that column.

Try :-

I create a select Statement which selects 1,2,3,4,5

SELECT random_num
        FROM (
           SELECT 1 AS random_num
           UNION 
           SELECT 2 AS random_num
           UNION 
           SELECT 3 AS random_num
           UNION 
           SELECT 4 AS random_num
           UNION 
           SELECT 5 AS random_num
       ) AS numbers_mst_plus_1

Then try that to implement it in INSERT QUERY

$sql = "INSERT INTO foldertable ( folderid )
            SELECT (SELECT random_num FROM (SELECT 1 AS random_num UNION SELECT 2 AS random_num) AS temp_table WHERE random_num NOT IN (1) LIMIT 1
                    )

The above query always insert 2 to table and I am happy . Then I thought IF there is both 1 and 2 in NOT IN (Real e.g Not in Clause contains all the folder id which is present in Table )

The catch is There -----

$sql = "INSERT INTO foldertable ( folderid )
            SELECT (SELECT random_num FROM (SELECT 1 AS random_num UNION SELECT 2 AS random_num) AS temp_table WHERE random_num NOT IN (1 , 2) LIMIT 1
                    )
            ";

Now It is again entering 0 into folderid column

So I added an WHERE CLAUSE to it

$sql = "INSERT INTO foldertable ( folderid )
            SELECT (SELECT random_num FROM
                                        (SELECT 1 AS random_num UNION SELECT 2 AS random_num UNION SELECT 3 AS random_num )
                                        AS temp_table
                                        WHERE random_num
                                        NOT IN (
                                                SELECT folderid FROM foldertable
                                                )
                                        LIMIT 1
                    )
            WHERE ((SELECT COUNT(*) FROM foldertable) < 3)
            ";

So now it is not going to add an new row if all IDs are used .

But The real Pain in the ass is to write

           SELECT 1 AS random_num
           UNION 
           SELECT 2 AS random_num
           UNION 
           SELECT 3 AS random_num
           UNION 
           SELECT 4 AS random_num
           UNION 
           SELECT 5 AS random_num
           .... 100000000 times 
           UNION
           SELECCT 100000006 AS random_num

So That was not worth of Time

So here comes GOD FOR LOOP

$randomNoString = "";
    for ($i=0; $i < ($maxFolderId - $minFolderId + 1 ); $i++) { 
        if($i === 0){
            $randomNoString ="SELECT ".($minFolderId + $i) . " AS random_num" ;
        }
        else {
            $randomNoString =  $randomNoString ." UNION " . "SELECT ".($minFolderId + $i) . " AS random_num";
        }
    }

$sql = "INSERT INTO foldertable ( folderid )
        SELECT (SELECT random_num FROM
                                    ({$randomNoString})
                                    AS temp_table
                                    WHERE random_num
                                    NOT IN (
                                            SELECT folderid FROM foldertable
                                            )
                                    LIMIT 1
                )
        WHERE ((SELECT COUNT(*) FROM foldertable) < {$maxFolderCount})
        ";

What's Problem

1 . Now the folders Id are unique but not Random. 2. And my head is Saying that it's gonna create performance issue .

What I can't do

1. Can't set folderid to unique .

Another Thought If I wanted a string of 6 char. , How should I do that ?

Answer 1

regarding:

Another Thought If I wanted a string of 6 char. , How should I do that ?

select concat(
    cast(char(64+floor(rand()*26+1)) as char),
    cast(char(64+floor(rand()*26+1)) as char),
    cast(char(64+floor(rand()*26+1)) as char),
    cast(char(64+floor(rand()*26+1)) as char),
    cast(char(64+floor(rand()*26+1)) as char),
    cast(char(64+floor(rand()*26+1)) as char)) as char6;

or, if you want a longer statement:

with recursive cte as ( 
   select 'A' as a
   union all 
   select CHAR(ORD(a)+1)
   from cte
   where a<'Z') 
select concat(c1.a,c2.a,c3.a,c4.a,c5.a,c6.a) as char6
from cte c1
cross join (select a from cte order by rand()*26+1 limit 1) c2
cross join (select a from cte order by rand()*26+1 limit 1) c3
cross join (select a from cte order by rand()*26+1 limit 1) c4
cross join (select a from cte order by rand()*26+1 limit 1) c5
cross join (select a from cte order by rand()*26+1 limit 1) c6
order by rand()*26+1
limit 1;