Generate unique 10 chars alphanumeric hashes in MySQL

Question

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?

Blaney

MySQL only has support for auto incrementing ints and selects on the varchar are also always slower vs an int column. i assume you have a very good reason why you want to do this? – Underhill

@RaymondNijland yes , to avoid to create a script from the application side :) – Blaney

Answer 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.

Russianize

thanks, nice, i'm testing it out but it returns error: [ERROR in query 2] Every derived table must have its own alias – Blaney

You can wrap that in a mysql function for easier results. – Gambia

@sbaaaang . . . I tested the logic, but in a select statement, not an update. I just added const to the assigment of @i and moved chars into the same subquery. This eliminates the separate set command. – Russianize

@Gambia uh? i'm new on mysql functions.. :( – Blaney

@GordonLinoff now no errors, but i can't get the hashes inserted, the table is empty :P – Blaney

@sbaaaang . . . Maybe I misunderstood the question. Are you trying to update an existing table or are you trying to insert new rows into a new table? – Russianize

@GordonLinoff table is empty i need to insert them not to update ;) – Blaney

@sbaaaang . . . Modify your question giving the table format and the number of values you want created. – Russianize

@GordonLinoff better now? :P – Blaney

create function hash10() returns varchar(10) begin declare chars varchar(34); 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) ); end| delimiter ; – Gambia

Sorry, was trying to post code... basically just wrap Gordon's call above in a mysql function ... create function hash10(). That way you can simplify your call to insert into table X values (hash10()),(hash10()) etc. – Gambia

Answer 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

Multiangular

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

Gambia

Should be: declare chars varchar(36); – Biquadrate

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

Nunuance

indeed you should handle that from the application but it will be much harder to pull it off. – Underhill

@Manu i agree just i'm lazy i would like to know if is here some way to avoid writing an application script – Blaney

Answer 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.

Biquadrate