How do I generate a unique, random string for one of my MySql table columns?
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'
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 ;-)