Generating a random & unique 8 character string using MySQL

Question

I woudn't bother with the likelihood of collision. Just generate a random string and check if it exists. If it does, try again and you shouldn't need to do it more that a couple of times unless you have a huge number of plates already assigned.

Another solution for generating an 8-character long pseudo-random string in pure (My)SQL:

SELECT LEFT(UUID(), 8);

You can try the following (pseudo-code):

DO 
    SELECT LEFT(UUID(), 8) INTO @plate;
    INSERT INTO plates (@plate);
WHILE there_is_a_unique_constraint_violation
-- @plate is your newly assigned plate number

---

Since this post has received a unexpected level of attention, let me highlight ADTC's comment : the above piece of code is quite dumb and produces sequential digits.

For slightly less stupid randomness try something like this instead :

SELECT LEFT(MD5(RAND()), 8)

And for true (cryptograpically secure) randomness, use RANDOM_BYTES() rather than RAND() (but then I would consider moving this logic up to the application layer).

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;

What about calculating the MD5 (or other) hash of sequential integers, then taking the first 8 characters.

i.e

MD5(1) = c4ca4238a0b923820dcc509a6f75849b => c4ca4238
MD5(2) = c81e728d9d4c2f636f067f89cc14862c => c81e728d
MD5(3) = eccbc87e4b5ce2fe28308fd9f2a7baf3 => eccbc87e

etc.

caveat: I have no idea how many you could allocate before a collision (but it would be a known and constant value).

edit: This is now an old answer, but I saw it again with time on my hands, so, from observation...

Chance of all numbers = 2.35%

Chance of all letters = 0.05%

First collision when MD5(82945) = "7b763dcb..." (same result as MD5(25302))

Create a random string

Here's a MySQL function to create a random string of a given length.

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `RandString`(length SMALLINT(3)) RETURNS varchar(100) CHARSET utf8
begin
    SET @returnStr = '';
    SET @allowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    SET @i = 0;

    WHILE (@i < length) DO
        SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
        SET @i = @i + 1;
    END WHILE;

    RETURN @returnStr;
END

Usage SELECT RANDSTRING(8) to return an 8 character string.

You can customize the @allowedChars.

Uniqueness isn't guaranteed - as you'll see in the comments to other solutions, this just isn't possible. Instead you'll need to generate a string, check if it's already in use, and try again if it is.

---

Check if the random string is already in use

If we want to keep the collision checking code out of the app, we can create a trigger:

DELIMITER $$

CREATE TRIGGER Vehicle_beforeInsert
  BEFORE INSERT ON `Vehicle`
  FOR EACH ROW
  BEGIN
    SET @vehicleId = 1;
    WHILE (@vehicleId IS NOT NULL) DO 
      SET NEW.plate = RANDSTRING(8);
      SET @vehicleId = (SELECT id FROM `Vehicle` WHERE `plate` = NEW.plate);
    END WHILE;
  END;$$
DELIMITER ;

Here is one way, using alpha numerics as valid characters:

select concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1)
             ) as LicensePlaceNumber;

Note there is no guarantee of uniqueness. You'll have to check for that separately.

Here's another method for generating a random string:

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 8) AS myrandomstring

You may use MySQL's rand() and char() function:

select concat( 
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97)
) as name;