Random Number Generation in Mysql PHP

Question

Hi,

I have 16 million records and need a 8 digit UNIQUE random number for each record, also I want all the numbers to start with 01, and store them in a seperate table, so I can assign the UNIQUE numbers to each record.

I am unsure of how to do this in PHP in order to get 16 million UNIQUE numbers?

Please help?!

J.

Answer 1

With what you specified, this does not seem possible.

You require 16,000,000 unique numbers from a given 8 digit number, of which the leading 2 is 01.

That leaves you with only 6 numbers, so your range will be 1,000,000 numbers

from 01000000 to 01999999.

This does not seem correct at all.

Answer 2

Try This: GUID

<? 
    echo str_replace ("}","",str_replace("{","",com_create_guid()));
?>

Answer 3

The usual way of generating a unique number for each record is to have an auto_incrementing value — so they will be unique, but not random.

.. and they would be generated by MySQL, not PHP. I think it would be a significant overhead to generate 16 million random but distinct numbers in PHP.

Do the numbers have to be random? Perhaps uniqueness is a more important requirement?

Answer 4

John, regarding your comment to pavium, if you need all the numbers to have exactly the same length but not necessarily random you can make use of the ZEROFILL MySQL property along with the auto_increment index.

Answer 5

Instead of generating the number at random and struggling with keeping track numbers that have been picked, how about generating the number in sequence but update the records in random order?

So, here is a totally MySQL solution. No PHP, no loop, just a batch update statement:

UPDATE 
   your_table, 
   (SELECT @rownum:=@rownum+1 rownum, t.id 
    FROM 
      (SELECT @rownum:=0) r, 
      (SELECT id FROM your_table ORDER BY RAND()) t
   ) p 
SET unique_num = 100000000 + p.rownum 
WHERE p.id = your_table.id

Replace your_table with table name, unique_num with the column where the unique number will be stored and id with the primary key column of your table.

p/s: tested working at least on MySQL 5.1.37

Answer 6

have mysql do it for you:

1. create an auto_increment column
2. add 100000000 using UPDATE
3. done!

if you really need the leading zero, then remove the auto_increment from the column, change it to varchar(10) and prepend a 0 using UPDATE

Answer 7

I like to use a 16-character base-60 varchar for my random primary keys. Here's a tiny table:

mysql> create table foo (k varchar(16), v varchar(255), primary key(k));

... and here's some rudimentary PHP, which will hit function k() until it finds an unused key.

<?php

require_once 'connect.php';

$v = 'A value that needs a unique random key.';

while (!mysql_query("INSERT INTO foo VALUES ('" . k() . "', '$v')")) {};

function k() {
   $t = "0123456789ABCDEFGHIJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz";
   $r = "";
   for ($i = 0; $i < 16; $i++) {
      $r .= substr($t, rand(0, 59), 1);
   }
   return $r;
}

?>

The chances of a collision are small--60 to the 16th--so k() almost never gets called twice. Bonus: if I ever need to make that key larger it's a varchar, so I can add bytes without scragging everything that's already in the table.