Add field with random values and unique key, duplicate entries

Question

I want to extend a mysql database users with a new unique field that gets filled with a random value on creation.

Right now I have these commands, which create the field, add a random string and add a unique key, but obviously when the last command executes, there are fields that have equal random strings which throw an error when trying to add the key.

(I have about 20k rows, so with a "100k random number string" there still is quite a chance of duplicates - but even with longer strings there always is the chance, so how to I handle the basic problem of possible duplicates?)

ALTER TABLE `users` ADD `nick` VARCHAR( 55 ) NOT NULL AFTER `id`;

UPDATE `users` SET `nick` = CONCAT('user_', FLOOR(RAND() * 100000));

ALTER TABLE `users` ADD UNIQUE ( `nick` );

My question is how to either make sure all the random strings are distinct, or how else to achieve this upgrade and generation of random strings on one swipe (and preferably with sql only).

(The accepted answer in this similar SO topic does not really provide a solution.)

[EDIT: So far the table has not had any user nicknames. An update under development will add functionality for users to set their user nickname once, but until users do so, I want them to be assigned a temporary, unique and random user nickname - and for security reasons I do not want to use the primary id in that user nickname]

[EDIT: I tried the suggested hashing of the user id and found that with 8 signs of the hash it performs without duplicates (whereas 6 still has duplicates fairly often, for 20k rows), but I still wonder if there is no other way to approach this than by pushing the odds of a duplicate smaller.

Otherwise this works:

ALTER TABLE `users` ADD `nick` VARCHAR( 55 ) NOT NULL AFTER `id`;

UPDATE `users` SET `nick` = CONCAT('user_', SUBSTR(MD5(`users`.`id`), 1, 8));

ALTER TABLE `users` ADD UNIQUE ( `nick` );

It could be helpful to know **why** you want to do this. What are you trying to achieve? — Matteo Tassinari, Dec 04 '12 at 09:23
Updated the question with more background information on the problem. — kontur, Dec 04 '12 at 09:37
You could, for example, do an hash of the user name, and than take the first 8-10 characters as temporary nicknames. Collisions are possible but unlikely. Or, use the UUID as @BartFriederichs suggested and keep only the last part. — Matteo Tassinari, Dec 04 '12 at 09:42
The hash is a possiblity, but basically it's only the increased number of possible letters, that reduces the chance of duplicates. As such, the problem stays the same, doesn't it? — kontur, Dec 04 '12 at 10:18
Yes, I guess it does, but I do think that the chance of duplicates is much lower than your first approach. — Matteo Tassinari, Dec 04 '12 at 10:31

score 2 · Answer 1 · answered Dec 04 '12 at 09:28

2

MySQL has a UUID() function that gives a universally unique number.

answered Dec 04 '12 at 09:28

Bart Friederichs

33,050
15
95
195

Potentially sounds good, but as the generated string would serve as a temporary user nickname, it does not quite suit my actual implementation. – kontur Dec 04 '12 at 09:35
Perhaps you could use MySQL variables then, to just add a sequential number? – Bart Friederichs Dec 04 '12 at 11:44

kontur · Accepted Answer · 2012-12-06T21:39:41.633

There does not seem to be a real way to catch or avoid the possibility of generated duplicates before adding the unique key. Thus, increasing the complexity of the random string seems to be the most viable solution, like so:

ALTER TABLE `users` ADD `nick` VARCHAR( 55 ) NOT NULL AFTER `id`;

UPDATE `users` SET `nick` = CONCAT('user_', SUBSTR(MD5(`users`.`id`), 1, 8));

ALTER TABLE `users` ADD UNIQUE ( `nick` );

EDIT: I mark this as the correct answer as long as there are no new suggestions beside from increasing the complexity of the random string appended.

Add field with random values and unique key, duplicate entries

2 Answers2