Save unique random order number in MySQL database

Question

I have this PHP code, that will generate a random 6 letter ordernumber:

<?php 
$random = mt_rand(100000,999999); 
echo $random;
?>

I can insert this number in my database, but how do I make sure that there is no dublicates, and the ordernumber is unique?

ORDER BY RAND is a heavy statement, its not recommend to do this on large databases — S.Visser, Apr 17 '13 at 09:05
Consider using [UUID v4 generator](http://stackoverflow.com/questions/2040240/php-function-to-generate-v4-uuid/15875555#15875555) for the order number to reduce collisions. — Ja͢ck, Apr 17 '13 at 09:07
@Svetlio does not mean we cant give him the correct information about such functions right? — S.Visser, Apr 17 '13 at 09:11

score 0 · Answer 1 · answered Apr 17 '13 at 09:03

0

You can make your database column as Unique. Also for double check before inserting check if that column does already have that value.

answered Apr 17 '13 at 09:03

chandresh_cool

11,753
3
30
45

score 0 · Answer 2 · answered Apr 17 '13 at 09:04

You should simply create UNIQUE INDEX over this field in database. Generate number, check if it exists in db, if yes, try again until it does not.

The only catch that you should make sure that your address space for random keys is not too populated, otherwise generating new key without collision will become too slow.

As long as number of rows is less than say 10% of the possible address space, this method should work well. But with 50% or more it will be rather bad.

You can also create some inventive schemes that try to keep list of unused keys so far, but this is probably too complicated.

Kickstart · Answer 3 · 2013-04-17T09:37:37.290

Set the number as a unique index and catch an error where a duplicate is found. Or just use the MySQL function RAND() to generate the number instead, then retrieve the value.

You could possibly do it in a single INSERT statement. Something like this (although this particular example slows down dramatically as the number of rows increases)

INSERT INTO numbers (SomeNumber)
SELECT aNum
FROM (SELECT a.i+b.i*10+c.i*100+d.i*1000+e.i*10000 AS aNum
FROM integers a, integers b, integers c, integers d, integers e) Sub1
LEFT JOIN Numbers ON Sub1.aNum = Numbers.SomeNumber
WHERE Numbers.SomeNumber IS NULL
ORDER BY RAND()
LIMIT 1

Or a lot more efficient, but leaves the possibility that no row will be inserted (it generates 10 random numbers and picks one that is unused to insert), hence you would need to check the rows affected and if 0 try again:-

INSERT INTO numbers (SomeNumber)
SELECT aNum
FROM (SELECT ROUND(RAND() * 899999) + 100000 AS aNum
UNION SELECT ROUND(RAND() * 899999) + 100000 AS aNum
UNION SELECT ROUND(RAND() * 899999) + 100000 AS aNum
UNION SELECT ROUND(RAND() * 899999) + 100000 AS aNum
UNION SELECT ROUND(RAND() * 899999) + 100000 AS aNum
UNION SELECT ROUND(RAND() * 899999) + 100000 AS aNum
UNION SELECT ROUND(RAND() * 899999) + 100000 AS aNum
UNION SELECT ROUND(RAND() * 899999) + 100000 AS aNum
UNION SELECT ROUND(RAND() * 899999) + 100000 AS aNum
UNION SELECT ROUND(RAND() * 899999) + 100000 AS aNum) Sub1
LEFT JOIN Numbers ON Sub1.aNum = Numbers.SomeNumber
WHERE Numbers.SomeNumber IS NULL
LIMIT 1

With both these examples you could retrieve the id of the inserted row and then from that select the number inserted.

Praveen kalal · Answer 4 · 2013-04-17T09:16:48.987

0

<?php 
$random = mt_rand(100000,999999); 
echo $random;
$sql ="SELECT FROM table where ordernumber=$random";
$result = mysqli_query($conn,$sql);
if(mysqli_num_rows($result)>0){
// regenerate random number 
}
else{
//insert here
}
?>

but this is not good approach rather use auto increment

edited Apr 17 '13 at 09:16

answered Apr 17 '13 at 09:08

Praveen kalal

2,148
4
19
33

Please don't provide answers with deprecated PHP functions - mysql_* – Svetoslav Apr 17 '13 at 09:14

score -1 · Answer 5 · answered Apr 17 '13 at 09:05

-1

Before inserting check whether the number exist in databse. If yes generate another random number and do the checking again and so on.But i recomend you to use the auto increment feature to do the same

answered Apr 17 '13 at 09:05

웃웃웃웃웃

11,829
15
59
91

This is a bad idea because the number might be inserted right between checking if it exists and the insert. If you want to take this approach it *must* be done by setting a constraint in your database. – Jasper Sep 27 '18 at 10:11

Save unique random order number in MySQL database

5 Answers5