funstein funstein - 3 months ago 22
MySQL Question

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.

Answer

This problem consists of two very different sub-problems:

  • the string must be seemingly random
  • the string must be unique

While randomness is quite easily achieved, the uniqueness without a retry loop is not. This brings us to concentrate on the uniqueness first. Non-random uniqueness can trivially be achieved with AUTO_INCREMENT. So using a uniqueness-preserving, pseudo-random transformation would be fine:

  • Hash has been suggested by @paul
  • AES-encrypt fits also
  • But there is a nice one: RAND(N) itself!

A sequence of random numbers created by the same seed is guaranteed to be

  • reproducible
  • different for the first 8 iterations
  • if the seed is an INT32

So we use @AndreyVolk's or @GordonLinoff's approach, but with a seeded RAND:

e.g. Assumin id is an AUTO_INCREMENT col:

INSERT INTO vehicles VALUES (blah); -- leaving out the number plate
SELECT @lid:=LAST_INSERT_ID();
UPDATE vehicles SET numberplate=concat(
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1)
             )
     WHERE id=@lid;
Comments