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.
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.
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.
Try This: GUID
<?
echo str_replace ("}","",str_replace("{","",com_create_guid()));
?>
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?
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. :)
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
have mysql do it for you:
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
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.
