How to create a unique random integer identifier for a primary key for a table?

Question

I was wondering if anyone knows a good way to create a unique random integer identifier for the primary key for the table. I am using MySQL. The value must be an integer.

Answer 1

If you open offers and can implement it, use UUID. The MySQL UUID() function returns a value of 36 characters, which can be used for ID .

If you want to use an integer, I think you need to create the getRandID() function that you will use in the INSERT . This function should use random + verification of existing identifiers in order to return one that has not been previously used.

Check out the RAND() function for MySQL.

Answer 2

In response to: "Because I want to use this value to encode on Base62, and then use it for the identifier in the URL. If I automatically increment, it may be obvious to the user how the URL identifier is generated."

If security is your goal, then using Base62, even with a “random” generated number, will not help.

The best option:

• Do not reinvent the wheel - use AUTO_INCREMENT
• Then use the cryptographic hash function + randomly generated string (hidden in db for that particular URL) to create the final "unique identifier for that URL"

Answer 3

How you create unique_id is a useful question, but you seem to make a counter fruitful assumption about when you create them!

My point is that you do not need to generate this unique identifier during the creation of your rows, since they are essentially independent of the inserted data.

What I am doing is pre-generating a unique identifier for future use, so I can take my own sweet time and absolutely guarantee that they are unique and there is no processing that needs to be done during insertion.

For example, I have an order table with order_id. This identifier is generated on the fly when the user enters an order, gradually 1,2,3, etc. Forever and ever. The user should not see this internal identifier.

Then I have another table - unique_ids with (order_id, unique_id). I have a regular program that runs every night that preloads this table with enough unique_id rows to cover orders that can be inserted in the next 24 hours. (If I ever receive 10,000 orders in one day, I will have a problem - but it will be a good problem!)

This approach guarantees uniqueness and distracts any processing from the insert transaction and into the batch procedure, where it does not affect the user.

Answer 4

How about this approach ( PHP and MySQL ):

---

Short

• Generate random number for user_id (UNIQUE)
• Insert the generated number string as user_id
• If the inserted row count is 0, go to step 1

Does it look hard? Keep reading.

---

Long

Table:

 users (user_id int UNIQUE) 

the code:

 <?php // values stored in configuration $min = 1; $max = 1000000; $numberOfLoops = 0; do { $randomNumber = rand($min, $max); // the very insert $insertedRows = insert_to_table( 'INSERT INTO foo_table (user_id) VALUES (:number)', array( ':number' => $randomNumber )); $numberOfLoops++; // the magic if (!isset($reported) && $numberOfLoops / 10 > 0.5) { /** * We can assume that at least 50% of numbers * are already in use, so increment values of * $min and $max in configuration. */ report_this_fact(); $reported = true; } while ($insertedRows < 1); 

---

• All values ​​( $min , $max , 0.5 ) are for explanation only and do not have a statistical value.
• The functions insert_to_table and report_this_fact not built in PHP. They are also numbers for explaining the purpose of the explanation.

Answer 5

There is an AUTO_INCREMENT function. I would use this.

See here for more details.

Answer 6

AUTO_INCREMENT will be your best bet for this.

Here are some examples.

If you need, you can configure where the growth value starts (by default it is 1).

Answer 7

my way, for both 32-bit and 64-bit platforms. 64 bit result

 function hexstr2decstr($hexstr){ $bigint = gmp_init($hexstr, 16); $bigint_string = gmp_strval($bigint); return $bigint_string; } function generate_64bitid(){ return substr(md5(uniqid(rand(), true)), 16, 16); } function dbGetUniqueXXXId(){ for($i = 0; $i < 10; $i++){ $decstr = hexstr2decstr(generate_64bitid()); //check duplicate for mysql.tablexxx if($dup == false){ return $decstr; } } return false; } 

Answer 8

You can use AUTO_INCREMENT for your table, but provide users with an encrypted version:

encrypted_id: SELECT HEX(AES_ENCRYPT(id, 'my-private-key'));

id: SELECT AES_DECRYPT(UNHEX(encrypted_id), 'my-private-key');