Best way for Unique Random String for MySQL Long table

An option:

Put all you possible characters in a table with only one column.

val------01...9ab...z

Use this query

SELECT CONCAT(a.val,b.val,c.val,d.val)FROM chars AS aJOIN chars AS bJOIN chars AS cJOIN chars AS dORDER BY RAND()LIMIT 10000

On the other hand if you need to get one ID at a time I see two approaches.

A. If you have a lot of unassigned IDs available.

In this case you just generate an ID and see if it's free. If not try another one.

B. If you want to keep you assigned IDs and the available IDs in the same magnitude level.

In this case it would be best to pre-generate all your IDs, shuffle them, and when you need one just pick the next available one. Say put them all in a table, and when you assign one from that table, you remove it so it can't be picked again.

If your allowed characters are 0-9a-z this means the table will occupy 36⁴. That's just a couple of MB.

As those strings need to be unique, why not use a numeric auto-increment value and then convert that to a character based value similar to the conversion of decimal to hex.

If you choose the e.g. all characters and digits you simply need to create a routine that will convert an integer to a "base 62" number.

You can make use of the DISTINCT keyword.

For example, the following query will only return unique rows by which you can validate that your 4 char random string remains unique:

mysql> SELECT DISTINCT random_strings FROM chars;