Databases
StackOverflow2013
Postsdbo
POAdd field with random values and unique key, duplicate entries
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAdd field with random values and unique key, duplicate entries
    primarykey
    data
    text
    <p>I want to extend a mysql database <code>users</code> with a new <code>unique</code> field that gets filled with a random value on creation.</p> <p>Right now I have these commands, which create the field, add a random string and add a <code>unique</code> 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.</p> <p><em>(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?)</em></p> <pre><code>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` ); </code></pre> <p>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).</p> <p>(The accepted answer in <a href="https://stackoverflow.com/questions/4661144/mysql-alter-table-add-column-with-unique-random-value">this similar SO topic</a> does not really provide a solution.)</p> <p>[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]</p> <p>[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.</p> <p>Otherwise this works:</p> <pre><code>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` ); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USkontur
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COIt could be helpful to know **why** you want to do this. What are you trying to achieve?
      singulars
      1. POAdd field with random values and unique key, duplicate entries
      1. USMatteo Tassinari
    2. COUpdated the question with more background information on the problem.
      singulars
      1. POAdd field with random values and unique key, duplicate entries
      1. USkontur
    3. COYou 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.
      singulars
      1. POAdd field with random values and unique key, duplicate entries
      1. USMatteo Tassinari
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload