Generate unique 10 chars alphanumeric hashes in MySQL

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?

Solution

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.