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
column:
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;