essentially i understand how to produce random chars both at php and mysql but now you ask , that i must produce a 4 char random string for any table of 10 1000 approximately rows what way is the greatest to make certain it'll remain unique !?

PS. i'm able to make use of a longer string if i have to although not longer then 12.

Just to really make it simple, table is available i have to add an additional column and grow it having a 4 char random string and secrets must remain unique.

A choice:

Invest you possible figures inside a table with just one column.

val
------
0
1
...
9
a
b
...
z

Make use of this query

SELECT CONCAT(a.val,b.val,c.val,d.val)
FROM chars AS a
JOIN chars AS b
JOIN chars AS c
JOIN chars AS d
ORDER BY RAND()
LIMIT 10000

However if you want to acquire one ID at any given time I see two approaches.

A. For those who have lots of unassigned IDs available.

Within this situation you simply generate an ID and find out whether it's free. Otherwise try a different one.

B. If you wish to help you stay designated IDs and also the available IDs within the same magnitude level.

Within this situation it might be better to pre-generate all of your IDs, shuffle them, and when you really need one just select the next available one. Say place them all inside a table, so when you assign one from that table, you take it off therefore it can not be selected again.

In case your permitted figures are 0-9a-z what this means is the table will occupy 364. That's just a few Megabytes.

As individuals strings have to be unique, why don't you make use of a number auto-increment value after which convert that to some character based value like the conversion of decimal to hex.

When you purchase the e.g. all figures and numbers you just need to produce a routine which will convert an integer to some "base 62" number.

This might be extended, but would permit you to create the thing you need:

 CREATE FUNCTION gen_alphanum () RETURNS CHAR(4)
    RETURN 
    ELT(FLOOR(1 + (RAND() * (50-1))), 'a','b','c','d','e','f','g','h','i','j','k','l','m  ','n','o','p','q','r','s','t','u','v','w','x','y', 'z',
    'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y', 'Z',);
    )

It may sound like you have the code in MySQL for creating these random valued strings.

Think about this option:

  • produce a User Defined Function in MySQL. Have this function run the SQL claims to create and return this new random string. Make sure that you use NOT EXISTS(SELECT MyRandomString FROM MyTable) within that creation statement to check on the random string does not already appear in the table.

  • When placing new rows, make use of this function's return value to assign towards the MyRandomString column.

  • to update the information existing, simply:

 UPDATE MyTable
 SET    MyRandomString = fn_CreateSomeRandomString()
  • when placing:
 INSERT INTO MyTable (foo, bar, MyRandomString) 
 VALUES ('','', fn_CreateSomeRandomString());

Here is a sample of that UDF on PasteBin.

You are able to take advantage from the DISTINCT keyword.

For instance, the next query is only going to return unique rows by which you'll validate that the 4 char random string remains unique:

mysql> SELECT DISTINCT random_strings FROM chars;