Generating a random unique 8 character string using MySQL

Question

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 amp 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

User · Answer

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

User · 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 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

User · Answer

What about calculating the MD5  or other  hash of sequential integers  then taking the first 8 characters   i e   MD5 1    c4ca4238a0b923820dcc509a6f75849b   gt  c4ca4238 MD5 2    c81e728d9d4c2f636f067f89cc14862c   gt  c81e728d MD5 3    eccbc87e4b5ce2fe28308fd9f2a7baf3   gt  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

User · Answer

If you re OK with  random  but entirely predictable license plates  you can use a linear-feedback shift register to choose the next plate number - it s guaranteed to go through every number before repeating  However  without some complex math  you won t be able to go through every 8 character alphanumeric string  you ll get 2 41 out of the 36 8  78   possible plates   To make this fill your space better  you could exclude a letter from the plates  maybe O   giving you 97

User · Answer

This function generates a Random string based on your input length and allowed characters like this   SELECT str rand 8   23456789abcdefghijkmnpqrstuvwxyz      function code       DROP FUNCTION IF EXISTS str rand   DELIMITER     CREATE FUNCTION str rand      u count INT UNSIGNED      v chars TEXT   RETURNS TEXT NOT DETERMINISTIC NO SQL SQL SECURITY INVOKER COMMENT    BEGIN     DECLARE v retval TEXT DEFAULT         DECLARE u pos    INT UNSIGNED      DECLARE u        INT UNSIGNED       SET u   LENGTH v chars       WHILE u count  gt  0     DO       SET u pos   1   FLOOR RAND     u         SET v retval   CONCAT v retval  MID v chars  u pos  1          SET u count   u count - 1      END WHILE       RETURN v retval  END     DELIMITER     This code is based on shuffle string function sends by  Ross Smith II

User · Answer

For a String consisting of 8 random numbers and upper- and lowercase letters  this is my solution   LPAD LEFT REPLACE REPLACE REPLACE TO BASE64 UNHEX MD5 RAND                                     8   8  0    Explained from inside out    RAND generates a random number between 0 and 1 MD5 calculates the MD5 sum of  1   32 characters from a-f and 0-9 UNHEX translates  2  into 16 bytes with values from 00 to FF TO BASE64 encodes  3  as base64  22 characters from a-z and A-Z and 0-9 plus     and      followed by two     the three REPLACEs remove the          and     characters from  4  LEFT takes the first 8 characters from  5   change 8 to something else if you need more or less characters in your random string LPAD inserts zeroes at the beginning of  6  if it is less than 8 characters long  again  change 8 to something else if needed

User · Answer

8 letters from the alphabet - All caps   UPDATE  tablename  SET  tablename   randomstring   concat CHAR FLOOR 65    RAND     25    CHAR FLOOR 65    RAND     25    CHAR FLOOR 65    RAND     25    CHAR FLOOR 65    RAND     25   CHAR FLOOR 65    RAND     25    CHAR FLOOR 65    RAND     25    CHAR FLOOR 65    RAND     25    CHAR FLOOR 65    RAND     25

User · Answer

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

User · Answer

For generate random string  you can use   SUBSTRING MD5 RAND    FROM 1 FOR 8   You recieve smth like that   353E50CC

User · Answer

DELIMITER     USE  temp      DROP PROCEDURE IF EXISTS  GenerateUniqueValue     CREATE PROCEDURE  GenerateUniqueValue  IN tableName VARCHAR 255  IN columnName VARCHAR 255    BEGIN     DECLARE uniqueValue VARCHAR 8  DEFAULT         WHILE LENGTH uniqueValue    0 DO         SELECT CONCAT SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                   SUBSTRING  ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789   RAND   34 1  1                    INTO  newUniqueValue          SET  rcount   -1          SET  query CONCAT  SELECT COUNT    INTO  rcount FROM    tableName   WHERE   columnName    like      newUniqueValue                PREPARE stmt FROM   query          EXECUTE stmt          DEALLOCATE PREPARE stmt      IF  rcount   0 THEN             SET uniqueValue    newUniqueValue           END IF       END WHILE       SELECT uniqueValue      END    DELIMITER     Use this stored procedure and use it everytime like  Call GenerateUniqueValue  tableName   columnName

User · Answer

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

User · Answer

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  lt  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

User · Answer

If you dont have a id or seed  like its its for a values list in insert   REPLACE RAND

User · Answer

You can generate a random alphanumeric string with   lpad conv floor rand   pow 36 8    10  36   8  0     You can use it in a BEFORE INSERT trigger and check for a duplicate in a while loop   CREATE TABLE  vehicles         plate  CHAR 8  NULL DEFAULT NULL       data  VARCHAR 50  NOT NULL      UNIQUE INDEX  plate    plate       DELIMITER    CREATE TRIGGER  vehicles before insert  BEFORE INSERT ON  vehicles  FOR EACH ROW BEGIN      declare str len int default 8      declare ready int default 0      declare rnd str text      while not ready do         set rnd str    lpad conv floor rand   pow 36 str len    10  36   str len  0           if not exists  select   from vehicles where plate   rnd str  then             set new plate   rnd str              set ready    1          end if      end while   END   DELIMITER     Now just insert your data like  insert into vehicles col1  col2  values   value1    value2      And the trigger will generate a value for the plate column    sqlfiddle demo   That works this way if the column allows NULLs  If you want it to be NOT NULL you would need to define a default value   plate  CHAR 8  NOT NULL DEFAULT  default     You can also use any other random string generating algorithm in the trigger if uppercase alphanumerics isn t what you want  But the trigger will take care of uniqueness

User · Answer

Taking into account the total number of characters that you require  you would have a very small chance of generating two exactly similar number plates  Thus you could probably get away with generating the numbers in LUA   You have 36 8 different unique numberplates  2 821 109 907 456  that s a lot   even if you already had a million numberplates already  you d have a very small chance of generating one you already have  about 0 000035   Of course  it all depends on how many numberplates you will end up creating

User · Answer

To create a random 10 digit alphanumeric  excluding lookalike chars 01oOlI   LPAD LEFT REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE TO BASE64 UNHEX MD5 RAND                                      O         l         I         1         0         o        10   10  0    This is exactly what I needed to create a voucher code  Confusing characters are removed to reduce errors when typing it into a voucher code form   Hopes this helps somebody  based on Jan Uhlig s brilliant answer    Please see Jan s answer for a breakdown on how this code works

User · Answer

Simple and efficient solution to get a random 10 characters string with uppercase and lowercase letters and digits    select substring base64 encode md5 rand     from 1 rand   4 for 10

User · Answer

An easy way that generate a unique number   set  i   0  update vehicles set plate   CONCAT  i   i 1  ROUND RAND     1000    order by rand

User · Answer

I was looking for something similar and I decided to make my own version where you can also specify a different seed if wanted  list of characters  as parameter   CREATE FUNCTION  random string  length SMALLINT 3   seed VARCHAR 255   RETURNS varchar 255  CHARSET utf8     NO SQL BEGIN     SET  output            IF seed IS NULL OR seed      THEN SET seed    ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789   END IF       SET  rnd multiplier   LENGTH seed        WHILE LENGTH  output   lt  length DO           Select random character and add to output         SET  output   CONCAT  output  SUBSTRING seed  RAND       rnd multiplier   1   1        END WHILE       RETURN  output  END   Can be used as   SELECT random string 10        Which would use the built-in seed of upper- and lowercase characters   digits  NULL would also be value instead of      But one could specify a custom seed while calling   SELECT random string 10   1234

User · Answer

I Use data from another column to generate a  hash  or unique string  UPDATE table name SET column name   Right  MD5 another column with data   8

User · Answer

Generate 8 characters key   lpad conv floor rand   pow 36 6    10  36   8  0         How do I generate a unique  random string for one of my MySql table columns

User · Answer

Here s another method for generating a random string   SELECT SUBSTRING MD5 RAND    FROM 1 FOR 8  AS myrandomstring

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

Examples related to mysql

Examples related to sql