[SQL] Generating a random & unique 8 character string using MySQL

I'm working on a game which involves vehicles at some point. I have a MySQL table named "vehicles" containing the data about the vehicles, including the column "plate" which stores the License Plates for the vehicles.

Now here comes the part I'm having problems with. I need to find an unused license plate before creating a new vehicle - it should be an alphanumeric 8-char random string. How I achieved this was using a while loop in Lua, which is the language I'm programming in, to generate strings and query the DB to see if it is used. However, as the number of vehicles increases, I expect this to become even more inefficient it is right now. Therefore, I decided to try and solve this issue using a MySQL query.

The query I need should simply generate a 8-character alphanumeric string which is not already in the table. I thought of the generate&check loop approach again, but I'm not limiting this question to that just in case there's a more efficient one. I've been able to generate strings by defining a string containing all the allowed chars and randomly substringing it, and nothing more.

Any help is appreciated.

Continue reading...