Generating a Random & Unique 8 Character String Using MySQL

Generating a random & unique 8 character string using MySQL

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;

How do I generate a unique, random string for one of my MySql table columns?

BEFORE UPDATE trigger solution:

You can create a 6 character random alphanumeric uppercase string with:

lpad(conv(floor(rand()*pow(36,6)), 10, 36), 6, 0);

In order to not create an already existing string you can use a BEFORE UPDATE trigger.

DELIMITER //
CREATE TRIGGER `unique_codes_before_update`
BEFORE UPDATE ON `unique_codes` FOR EACH ROW 
BEGIN
    declare ready int default 0;
    declare rnd_str text;
    if new.CODE is null then
        while not ready do
            set rnd_str := lpad(conv(floor(rand()*pow(36,6)), 10, 36), 6, 0);
            if not exists (select * from unique_codes where CODE = rnd_str) then
                set new.CODE = rnd_str;
                set ready := 1;
            end if;
        end while;
    end if;
END//
DELIMITER ;

Every time you set your CODE column to NULL in an UPDATE statement, the trigger will create a new random string in a loop until no match has been found in the table.

Now you can replace all NULL values with:

update unique_codes set CODE = NULL where code is NULL;

In the SQLFiddle demo here i use a one character random string to demonstrate that no value is duplicated.

You can also use the same code in a BEFORE INSERT trigger. This way you can just insert new rows with CODE=NULL and the trigger will set it to a new unique random string. And you will never need to update it again.

Original answer (32 character strings):

select lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0) as rnd_str_8;

-- output example: 3AHX44TF

will generate an 8-character alphanumeric uppercase random string. Concatenate four of them to get 32 characters:

select concat(
    lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0),
    lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0),
    lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0),
    lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0)
) as rnd_str_32;

-- output example: KGC8A8EGKE7E4MGD4M09U9YWXVF6VDDS

http://sqlfiddle.com/#!9/9eecb7d/76933

So what about uniqness? Well - try to generate duplicates ;-)

MySQL - inserting 70000 random unique strings efficiently

Create your table structure:

CREATE TABLE t (code CHAR(8) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL UNIQUE);

Define a PHP function to generate a random string:

function random_string(integer $length = 8): string {
    return bin2hex(mcrypt_create_iv(ceil($length/2), MCRYPT_DEV_URANDOM));
}

Use PHP to build a multi-value INSERT statement, ram that into the database, count how many were inserted, and repeat until the required number are inserted:

function insert_records(\PDO $pdo, integer $need = 70000): void {
    $have = 0;
    while ($have < $need) {
        // generate multi value INSERT
        $sql = 'INSERT IGNORE INTO t VALUES ';
        for ($i = 1; $i < $need; $i++) {
            $sql .= sprintf('("%s"),', random_string());
        }
        $sql .= sprintf('("%s");', random_string());

        // pass to database and ask how many records were inserted
        $result = $pdo->query($sql);
        $count  = $result->rowCount();

        // adjust bookkeeping values so we know how many we have and how many
        // we need
        $need -= $count;
        $have += $count;
    }
}

On my machine (Amazon Linux c2.small), the run time for 70k records is about 2 seconds:

real    0m2.136s
user    0m1.256s
sys     0m0.212s

The relevant tricks in this code, to make it fast, are:

• Sending the minimum number of SQL statements necessary to generate the needed number of records. Using a multi-value insert - INSERT INTO ... VALUES (), (), ... (); - really helps this as it minimizes the total amount of statement processing MySQL has to do and it tells us how many records were inserted without having to do another query.
• Using INSERT IGNORE to avoid having to check for the existence of every single code we insert, which is really really expensive.
• Using the fastest possible string generating function we can for our needs. In my experience, mcrypt_create_iv is a fast generator that is cryptographically secure, so it provides an ideal balance of security and performance.
• Using the ASCII character set and fixed width CHAR to remove unnecessary byte overhead and UNIQUE to enforce de-duplication.

Generate a random string in MySQL

It's solved by using the Delimiter, i don't know for sure how, but it works
Thanks

DELIMITER $$
CREATE FUNCTION randomPassword()
RETURNS varchar(128)
BEGIN

SET @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
SET @charLen = length(@chars);

SET @randomPassword = '';

WHILE length(@randomPassword) < 12
    DO
    SET @randomPassword = concat(@randomPassword, substring(@chars,CEILING(RAND() * @charLen),1));
END WHILE;

RETURN @randomPassword ;
END$$
DELIMITER ; 

Huge MySQL Random Alphanumeric String

One option would be to just chain together the output of multiple calls to uuid(), i.e.

DELIMITER ;;
CREATE TRIGGER testdata
BEFORE INSERT ON testdata
FOR EACH ROW
BEGIN
    IF new.token IS NULL THEN
        SET new.token = CONCAT(REPLACE(uuid(), '-', ''), REPLACE(uuid(), '-', ''), ...)
    END IF;
END
;;

Note here that I remove the hyphen from the UUIDs because they are superfluous and do not do much in the way of making the strings more random.

PHP/MySQL - Best way to create unique random string?

Assuming 10 characters from the character set a-z, A-Z, 0-9 mean there are (26 + 26 + 10)¹⁰ = 8.39299366 × 10¹⁷ possible combinations. To calculate the odds of a collision... just 1/x the afore-mentioned number. So I would not be worrying about getting the same string twice. Even if do get the same string again I'll just run the function again in a loop, the only exit condition being that a unique string is found.

---

Related Topics