There is a table with 50,000 entries, I want to give everyone a unique random digital id, not in order. Let it be in the range of 10 000 000 - 99 999 999. Is it possible to make such a request?

  • one
    use a timestamp - the number of milliseconds starting from a certain point. - Mikhail Vaysman
  • one
    You can write a function that will generate a number (id) in the specified range, check if there is an entry in the database with such an id, if not, update the first non-updated id entry, if there is, then start this function again - Maxim Kravets
  • Michael, I wanted a completely random random. Maxim, is there a single request? 50,000 selektov and updates will have to do? - Vadim
  • one
    50,000 selektov and updates will take you well, let it be two minutes. You wrote this question longer. - sanmai
  • one
    You can take the id of the 1st entry equal to 10,000,000 and add to each subsequent id a random value in the range from 1 to 1799 - get an increasing sequence of unique numbers. 1799 = (99 999 999 - 10 000 000) / 50 000 - this value guarantees that the maximum id value does not exceed 99 999 999 (although this unnecessary caution - rand (X) will not return the maximum value 50,000 times :-) in a row. On oracle this can be done exactly with 1 request. As in mysql - I do not know. - Nikolay

1 answer 1

update randTest K join ( select A.id,B.RND from ( select id, @m:=@m+1 M from (select @m:=0) N, randTest ) A, ( select RND,@n:=@n+1 N from ( select distinct RND RND from ( select 10000000+ceil(rand()*100000000)%90000000 RND from randTest R, (select 1 union all select 2) S ) X ) Y, (select @n:=0) N ) B where BN=AM ) L on K.id=L.id set id=L.RND

Prior to the start of update, it is assumed that there is already some unique value in the ID field, or there is another unique field on the basis of which you can perform join LK. If the initial id is strictly from 1 in a row without breaks, then subquery A can be removed and used as id BN .

General algorithm: Since initially random numbers can be repeated, by gluing with S we create 2 times more random numbers than records in the table. We select from them unique ( Y ). We number ( A ) and glue together the initial IDs ( B ) numbered in order. At the output in L we get a list of all current IDs with a unique random value for each of them. It remains to glue these values ​​by ID to the table being changed.