CODEWITHSUNDEEP
phpmysqlrandomkey
John Jones
John Jones

Reputation: 2265

Random Number Generation in Mysql PHP

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.

Upvotes: 0

Views: 2216

Answers (8)

Kent Brewster
Kent Brewster

Reputation: 2520

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.

Upvotes: 0

longneck
longneck

Reputation: 12226

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

Upvotes: 0

Lukman
Lukman

Reputation: 19164

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

Upvotes: 0

Darkerstar
Darkerstar

Reputation: 932

The easiest way is to first generate a list of numbers 16M numbers and random sort them and then map each to your record, and it be done in pretty much in PHP native functions.

I have added a stepping loop, so that it doesn't generate a too large array. But it also mean the id will be kinda of look like increamenting, but still random numbers.

Maybe someone can come up with a better solution using rotating string? I am off work now. :)

Upvotes: 0

Alix Axel
Alix Axel

Reputation: 154543

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.

Upvotes: 1

pavium
pavium

Reputation: 15118

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?

Upvotes: 0

Treby
Treby

Reputation: 1320

Try This: GUID

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

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

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.

Upvotes: 4

Related Questions