PHP/MySQL - Best way to create unique random string?

Question

How do I create a random unique string in MySQL?

when I need to create a random string in PHP I use this function:

public function generateString($length)
{   
    $charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";

    for($i=0; $i<$length; $i++) 
        $key .= $charset[(mt_rand(0,(strlen($charset)-1)))]; 

    return $key;
}

Then I would take the generated string and store it in a MySQL database.

What is the best way to make sure the generated random string is unique to all the other random strings created for other entries in the database?

Maybe something like this?

while(++$i < 100)
{
  //query db with random key to see if there is a match

  //if no match found break out of loop
  break;

}

This seems messy and long, and I could potentially hit the database multiple times. How can I quickly be sure my new random string is unique?

possible duplicate of [Short unique id in php](http://stackoverflow.com/questions/307486/short-unique-id-in-php) — Mark Biek, Oct 25 '11 at 17:36

score 17 · Answer 1 · answered Oct 25 '11 at 17:29

17

Why not just use the built-in functions for generating unique identifiers? You wouldn't have to worry about duplicates that way.

Both PHP and MySQL have their own.

PHP: uniqid()

MySQL: UUID()

answered Oct 25 '11 at 17:29

Mark Biek

146,731
54
156
201

By default uniquid gives 13 characters, I need 10. So if I chop off the last 3 doesnt that leave the chance of duplicates? – John Oct 25 '11 at 17:33
Why does it have to be 10 characters? – Mark Biek Oct 25 '11 at 17:36
On another note, this question has some good answers. http://stackoverflow.com/questions/307486/short-unique-id-in-php – Mark Biek Oct 25 '11 at 17:36
Its a requirement in the web app Im building. – John Oct 25 '11 at 17:39
1

Then I think you're stuck rolling your own solution and manually checking for duplicates. – Mark Biek Oct 25 '11 at 17:55
Although these IDs are unique, they're not very random. – Barmar Apr 16 '15 at 15:02

Salman A · Accepted Answer · 2011-10-25T17:44:38.307

9

Assuming 10 characters from the character set a-z, A-Z, 0-9 mean there are (26 + 26 + 10)¹⁰ = 8.39299366 × 10¹⁷ possible combinations. To calculate the odds of a collision... just 1/x the afore-mentioned number. So I would not be worrying about getting the same string twice. Even if do get the same string again I'll just run the function again in a loop, the only exit condition being that a unique string is found.

edited Oct 25 '11 at 17:44

answered Oct 25 '11 at 17:36

Salman A

262,204
82
430
521

3

Your collision probability is not strictly correct - you are calculating a 1-1 collision, not for a collection. For small collections your chance of a collision is roughly `n(n-1)/N` where `n` is the size of the collection, and `N` is your 8.329x10^17. The conclusion from this is that your chance of collision becomes non-negligible at about `sqrt(N)` - which is 10^8, So if you have a lot (but not completely unreasonably large amount) of data you may get a collision. Best solution in this case is to just increase your key length slightly, or do a check before using the value. – Michael Anderson Apr 04 '12 at 12:58
@Michael: I think, given that I already have one million unique strings, the probability of a collision is still one in a trillion (1000000/62^10). – Salman A Apr 05 '12 at 08:57
1

I agree that the chance of collision when you add one string given that you already have 1M unique strings is 1M/(10^17). But you've had to do this 1M times. This means your overall chance of collision is (roughly) `1M x (1M/(10^17))`. – Michael Anderson Apr 05 '12 at 09:08
@SalmanA Would you put a max retries parameter? E.g. to try at most `1000` times generating the number? Or would you put your code in an infinite loop? – tonix May 04 '20 at 14:24
I would put it in an infinite loop and throw an exception if the loop executed 1000 times... or even 3 times. – Salman A May 19 '20 at 17:47

score 3 · Answer 3 · edited Jan 06 '15 at 17:12

3

SET rand_str = SUBSTRING(MD5(NOW()),1,$LENGTH); -- Where LENGTH is 1 to 32 as per MD5

Some examples are below:

SET rand_str = SUBSTRING(MD5(NOW()),1,5); -- 5 character string

SET rand_str = SUBSTRING(MD5(NOW()),1,15); -- 15 character string

edited Jan 06 '15 at 17:12

jackslash

8,550
45
56

answered Jan 06 '15 at 17:06

rudyrockstar

31
2

score 1 · Answer 4 · answered Nov 22 '14 at 00:57

I would make the column of this id unique in your DB. Then you can do something like this to safeguard against collisions:

    $row_count = 0;
    while ($row_count == 0) {
        error_reporting(0);
        $id_string = substr(uniqid(), 0, 10);

        $sql = "UPDATE <table> SET unique_id = :unique_id WHERE <something true>";
        $query = $this->db->prepare($sql);
        $query->execute(array(':unique_id' => $unique_id));
        $row_count = $query->rowCount();
    }

Sure, it may need to try the query more than once, but this way you know it's guaranteed to be unique in your DB. The error_reporting(0) line is in there to suppress any warnings which might be turned on. PHP's uniqid() also isn't the most unique generate there is, but you can easily swap that out for your own or just take the hit of potential collisions here and there.

score 0 · Answer 5 · edited May 23 '17 at 10:31

Luckily databases already have the ability to create unique IDs (numeric) - I suggest the approach that we took, which is to create a two-way conversion between a gently increasing numeric ID and an alpha-numeric ID. Having it be two-way assures that the alpha-numeric "random" versions are also unique without having to explicitly test them. Indeed, I only ever store the numeric version in the database (since you get it for free with a SERIAL column) and only ever print the alpha version.

This example generates seven-byte IDs but the approach can be trivially tweaked to fit almost any set of circumstances.

See: How to generate unique id in MySQL?

Luca C. · Answer 6 · 2016-07-12T09:03:43.267

0

I usually use:

SELECT LEFT(MD5(id), 8)

variants by needings:

SELECT LEFT(UUID(), 8)

SELECT LEFT(MD5(RAND()), 8)

edited Jul 12 '16 at 09:03

answered Jul 12 '16 at 07:59

Luca C.

11,714
1
86
77

score 0 · Answer 7 · edited May 23 '17 at 12:34

0

If you want to use these strings for security purpose, you should use openssl_random_pseudo_bytes which will indicate you if PHP was able to use a strong algorithm to generate it:

Ouput needs some cleaning though. Have a look at this question for more info.

edited May 23 '17 at 12:34

Community

1
1

answered Aug 25 '16 at 14:10

Simon

3,580
2
23
24

HARDIK · Answer 8 · 2017-06-07T11:35:01.787

Unique random strings can be used as character keys or tokens to identify database records and check to database table and provides Unique key with store $refer_by variable.

define('DB_SERVER', "localhost");
define('DB_USER', "root");
define('DB_PASS', "");
define('DB_DATABASE', "student");
$con = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);

function refercode()
{
    $string = '';
    $characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
    $max = strlen($characters) - 1;
    for ($i = 0; $i < 6; $i++) {
        $string .= $characters[mt_rand(0, $max)];
    }
    $refer = "select * from user_detail where refer_code = '".$string."' ";
    $coderefertest = mysqli_query($con,$refer);

    if(mysqli_num_rows($coderefertest)>0)
    {
        return refercode();
    }
    else
    {
        return $string;
    }
}
$refer_by = refercode();

https://meta.stackoverflow.com/questions/300837/what-comment-should-i-add-to-code-only-answers — Razvan Dumitru, Jun 07 '17 at 10:48

score 0 · Answer 9 · answered Apr 13 '18 at 06:57

DELIMITER $$

USE `temp` $$

DROP PROCEDURE IF EXISTS `GenerateUniqueValue`$$

CREATE PROCEDURE `GenerateUniqueValue`(IN tableName VARCHAR(255),IN columnName VARCHAR(255)) 
BEGIN
    DECLARE uniqueValue VARCHAR(8) DEFAULT "";
    WHILE LENGTH(uniqueValue) = 0 DO
        SELECT CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1)
                ) INTO @newUniqueValue;
        SET @rcount = -1;
        SET @query=CONCAT('SELECT COUNT(*) INTO @rcount FROM  ',tableName,' WHERE ',columnName,'  like ''',@newUniqueValue,'''');
        PREPARE stmt FROM  @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    IF @rcount = 0 THEN
            SET uniqueValue = @newUniqueValue ;
        END IF ;
    END WHILE ;
    SELECT uniqueValue;
    END$$

DELIMITER ;

Use this stored procedure and call this stored procedure as

Call GenerateUniqueValue('tableName','columnName')

score 0 · Answer 10 · answered Oct 25 '11 at 17:33

Take a look at the uniqid function and the pecl uuid extension. Either one can be used as the basis for generating guids, although if you plan to have a cluster, you will want to insure that you have something extra that insures that two servers don't generate the same id. Having a per server configuration that adds as prefix or suffix to the id is sufficient to address that issue.

PHP/MySQL - Best way to create unique random string?

10 Answers10

Linked