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

Mark Biek
– Mark Biek

2011-10-25 17:36:59 +00:00
Commented Oct 25, 2011 at 17:36 — Mark Biek
– Mark Biek, Commented Oct 25, 2011 at 17:36

Mark Biek · Accepted Answer · 2011-10-25 17:29:50Z

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, 2011 at 17:29

Mark Biek

152k54 gold badges160 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

John Over a year ago

By default uniquid gives 13 characters, I need 10. So if I chop off the last 3 doesnt that leave the chance of duplicates?

Mark Biek Over a year ago

Why does it have to be 10 characters?

Mark Biek Over a year ago

On another note, this question has some good answers. stackoverflow.com/questions/307486/short-unique-id-in-php

John Over a year ago

Its a requirement in the web app Im building.

Mark Biek Over a year ago

Then I think you're stuck rolling your own solution and manually checking for duplicates.

|

Salman Arshad · Accepted Answer · 2011-10-25 17:44:38Z

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, 2011 at 17:44

answered Oct 25, 2011 at 17:36

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

5 Comments

Michael Anderson Over a year ago

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.

Salman Arshad Over a year ago

@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).

Michael Anderson Over a year ago

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)).

tonix Over a year ago

@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?

Salman Arshad Over a year ago

I would put it in an infinite loop and throw an exception if the loop executed 1000 times... or even 3 times.

jackslash · Accepted Answer · 2015-01-06 17:12:40Z

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 6, 2015 at 17:12

jackslash

8,57047 silver badges56 bronze badges

answered Jan 6, 2015 at 17:06

rudyrockstar

312 bronze badges

Comments

Tim Trampedach · Accepted Answer · 2014-11-22 00:57:19Z

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.

Community · Accepted Answer · 2023-11-21 15:10:29Z

0

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.

edited Nov 21, 2023 at 15:10

CommunityBot

11 silver badge

answered Oct 25, 2011 at 17:33

gview

15.5k3 gold badges53 silver badges56 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:31:14Z

0

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?

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Jul 8, 2014 at 18:15

RJStanford

6296 silver badges5 bronze badges

Comments

Luca C. · Accepted Answer · 2016-07-12 09:03:43Z

0

I usually use:

SELECT LEFT(MD5(id), 8)

variants by needings:

SELECT LEFT(UUID(), 8)

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

edited Jul 12, 2016 at 9:03

answered Jul 12, 2016 at 7:59

Luca C.

12.7k2 gold badges89 silver badges83 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:34:39Z

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, 2017 at 12:34

CommunityBot

11 silver badge

answered Aug 25, 2016 at 14:10

Simon

3,7582 gold badges25 silver badges24 bronze badges

Comments

HARDIK · Accepted Answer · 2017-06-07 11:35:01Z

0

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();

edited Jun 7, 2017 at 11:35

answered Jun 7, 2017 at 10:22

HARDIK

728 bronze badges

1 Comment

Razvan Dumitru Over a year ago

meta.stackoverflow.com/questions/300837/…

Hariramprasath Nandhagopalan · Accepted Answer · 2018-04-13 06:57:04Z

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')

Collectives™ on Stack Overflow

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

10 Answers 10

6 Comments

5 Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

6 Comments

5 Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related