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

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'