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

Question

I’m using MySql 5.5.37. I have a table with the following columns

+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| ID               | varchar(32)      | NO   | PRI | NULL    |       |
| CODE             | varchar(6)       | NO   | UNI | NULL    |       |

The code column is unique and my ID column is a GUID. I have a number of rows that I would like to update, subject to some criteria in the above table (e.g. WHERE COLUMN1 = 0). How do I generate random, unique 6-character codes (ideally letters and numbers) for my CODE column such that they don’t violate the unique constraint in my table? Note that the columns in the table that do not meet the criteria (e.g. Where COLUMN1 <> 0) already have unique values for the CODE column.

Edit: This is different than this question -- Generating a random & unique 8 character string using MySQL because that link deals with IDs taht are numeric. My IDs are 32-character strings. Also their solution does not take into account the fact that there may values in the table prior to running the statements I want to run that will generate a unique values for the column in question.

Answer 1

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 ;-)

Answer 2

This one is tricky but I think I've reached a nice solution:

DROP FUNCTION IF EXISTS getRandomAlphaNumeric;

DELIMITER $$

CREATE FUNCTION getRandomAlphaNumeric() RETURNS CHAR(6)
DETERMINISTIC
BEGIN

    SELECT 
    CONCAT (
        CHAR(FLOOR(RAND()*10)+48), CHAR(FLOOR(RAND()*26)+65), CHAR(FLOOR(RAND()*26)+97),
        CHAR(FLOOR(RAND()*10)+48), CHAR(FLOOR(RAND()*26)+65), CHAR(FLOOR(RAND()*26)+97)
    ) INTO @code
    ;

    RETURN @code;
END
$$

DELIMITER ;


DROP PROCEDURE IF EXISTS generateCodes;

DELIMITER $$
CREATE PROCEDURE generateCodes()
BEGIN

    SET @count = 0;
    SELECT COUNT(1) INTO @count FROM demo.codes;

    SET @i = 0;
    WHILE @i < @count DO

        PREPARE stmt FROM "SELECT @id := id, @itemCode := code FROM demo.codes p LIMIT ?, 1;";
        EXECUTE stmt USING @i;

        SET @code = getRandomAlphaNumeric();

        SELECT COUNT(1) INTO @countRowsWithCode FROM demo.codes WHERE code = @code;

        IF @countRowsWithCode = 0 AND @itemCode IS NULL THEN
            UPDATE demo.codes SET code = @code WHERE id = @id;
        END IF;

        SET @i := @i + 1;   
    END WHILE;
END
$$

DELIMITER ;


CALL generateCodes();

First, I created a function that returns a random string of 6 chars that it's used following to generates the desired codes:

DROP FUNCTION IF EXISTS getRandomAlphaNumeric;

DELIMITER $$

CREATE FUNCTION getRandomAlphaNumeric() RETURNS CHAR(6)
DETERMINISTIC
BEGIN

    SELECT 
    CONCAT (
        CHAR(FLOOR(RAND()*10)+48), CHAR(FLOOR(RAND()*26)+65), CHAR(FLOOR(RAND()*26)+97),
        CHAR(FLOOR(RAND()*10)+48), CHAR(FLOOR(RAND()*26)+65), CHAR(FLOOR(RAND()*26)+97)
    ) INTO @code
    ;

    RETURN @code;
END
$$

Then I created a procedure that is responsible to update the table with random unique codes. The procedure consists in:

• Count all the records that will be updated with a fresh and random code of 6 characters.

  SELECT COUNT(1) INTO @count FROM demo.codes;

• Then, foreach row (using WHILE loop):

  • Get the id of the next record to be uptdated

    PREPARE stmt FROM "SELECT @id := id, @itemCode := code FROM demo.codes p LIMIT ?, 1;"; EXECUTE stmt USING @i;

  • Get a new code for the record:

    SET @code = getRandomAlphaNumeric();

  • For last, verify if the new code do not already exists on table and if currently the field column has no value (is NULL), if it's not, update the current record with the random code:

    SELECT COUNT(1) INTO @countRowsWithCode FROM demo.codes WHERE code = @code; IF @countRowsWithCode = 0 AND @itemCode IS NULL THEN UPDATE demo.codes SET code = @code WHERE id = @id; END IF;

  • Finally, CALL the created PROCEDURE in order to populate the fields from code column that are NULL.

    CALL generateCodes();

Answer 3

CONV(CONV(( SELECT MAX(CODE) FROM tbl ), 36, 10) + 1, 10, 36)

will get you the next 'number' encoded in base-36 (digits & capital letters).

For example:

SELECT CONV(CONV(( 'A1B2C' ), 36, 10) + 1, 10, 36); --> 'A1B2D'

Answer 4

Try this for code

SELECT LEFT(MD5(NOW()), 6) AS CODE;

LEFT(MD5(NOW()), 6) this will return unique code with 6 characters.

Try another way like this

SELECT LEFT(UUID(), 6);

LEFT(UUID(), 6) This will also return unique code

Answer 5

DELIMITER $$

USE `db` $$

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 ;

Call this stored procedure like

Call GenerateUniqueValue('tableName','columnName')

This will give you a unique 8 character string everytime.

Answer 6

Simple and efficient solution to get a random 10 characters string with uppercase and lowercase letters and digits, without custom function (works with MySQL >= 5.6) :

select substring(base64_encode(md5(rand())) from 1+rand()*4 for 10);