Generate unique 10 chars alphanumeric hashes in MySQL

17,161

Solution 1

If you want to create unique values for this field, you can use an auto-incrementing approach, just base 36. Here is an example going up to several hundred million distinct values:

update t cross join (select @i := 0, @chars = '0123456789abcdefghijklmnopqrstuvwxyz') const
    set hash = concat(substring(@chars, ((@i := @i + 1) %36)+1, 1),
                      substring(@chars, floor(@i/pow(36, 1))%36 + 1, 1),
                      substring(@chars, floor(@i/pow(36, 2))%36 + 1, 1),
                      substring(@chars, floor(@i/pow(36, 3))%36 + 1, 1),
                      substring(@chars, floor(@i/pow(36, 4))%36 + 1, 1),
                      substring(@chars, floor(@i/pow(36, 5))%36 + 1, 1),
                      '0000'
                     );

EDIT: (based on revised question)

Your table has a unique constraint on it. I would just do the following:

insert into vouchers(hash)
    select concat(substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1),
                  substring(@chars, floor(rand()*36) + 1, 1)
                 );

Just do this a bunch of times in a loop (or as necessary) to populate the table. It is highly unlikely that you will get duplicates. If you do, that particular insert will fail.

Solution 2

-- most elegant, has adjustable length 1-32 and probably has best performance
SELECT SUBSTR(REPLACE(UUID(),'-',''),1,10) as randomStringUUID
;

-- generate 10 character [a-z0-9] string, has adjustable letter/nr ratio
SELECT CONCAT(
  CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ,CASE WHEN RAND()>=0.5 THEN char(round(RAND()*9+48)) ELSE char(round(RAND()*25+97)) END
  ) as randomString
;

-- as bonus: generate a variable size letter only string, best for emulating names/words
SELECT SUBSTR(CONCAT(char(RAND()*25+55),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97),char(RAND()*25+97)),1,RAND()*9+4) as RandomName

Test at http://sqlfiddle.com/#!8/d41d8/586

Solution 3

Here's the code to wrap Gordon's answer above into a function (credit to Gordon) -

delimiter |
create function hash10() returns varchar(10)
begin
declare chars varchar(36);
set chars = '0123456789abcdefghijklmnopqrstuvwxyz';
return concat(substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1),
              substring(chars, floor(rand()*36) + 1, 1)
             );
end|
delimiter ;

Then you can use...

insert into x (hash) values (hash10()),(hash10()),(hash10());

Solution 4

I think it is better to handle this from application logic.

If you want to handle it sql way, try using mysql function UUID() ( but uuid generated is 36 characters long)

Solution 5

Just use a loop:

DROP FUNCTION hash10;
DELIMITER |
CREATE FUNCTION hash10() RETURNS VARCHAR(10)
BEGIN
  DECLARE chars VARCHAR(36);
  DECLARE result VARCHAR(10);
  DECLARE i INT;
  SET chars = '0123456789abcdefghijklmnopqrstuvwxyz';
  SET result = '';
  SET i = 0;
  label: LOOP
    SET result = CONCAT(result, SUBSTRING(chars, FLOOR(RAND()*36) + 1, 1));
    SET i = i + 1;
    IF i = 10 THEN
      LEAVE label;
    END IF;
  END LOOP label;
  RETURN result;
END|
DELIMITER ;

To generate a different length just replace all the 10's to different number.

Share:
17,161
itsme
Author by

itsme

JS

Updated on June 04, 2022

Comments

  • itsme
    itsme about 2 years

    I have a simple table with field called "hash" VARCHAR 10 UNIQUE FIELD

    Now I would like to run a query and generate automatically the hashes inside the field.

    The problem is that the hashes has to be alpha-numeric and has to be long 10 chars and UNIQUE.

    table structure:

    CREATE TABLE `vouchers` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `hash` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `hash` (`hash`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    

    So I need to INSERT hashes into hash field, they should look like random alphanumeric random hashes, I mean users shouldn't be able to catch the next or previous hash just looking at one hash, also they must be 10 chars long and unique.

    Has anyone any clue for this?