Menu
I was wondering if anybody knew a good way to create a unique random integer id for a primary key for a table. I'm using MySQL. The value has to be integer.
578
UUID() function in MySQL This function in MySQL is used to return a Universal Unique Identifier (UUID) generated according to RFC 4122, “A Universally Unique Identifier (UUID) URN Namespace”. It is designed as a number that is universally unique.
SQL RAND() example SELECT RAND(); SELECT RAND(5); As you can see, in the first example, the RAND() function generates a random number in decimals between 0 to 1. Each time you execute the statement, it returns different random values.
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
Random primary keys are unique key ranges used by each server on Replication (both One-Way Replication and Daisy Chain Replication). They are an older method and not recommended. The recommended method is to instead use auto-increment variables to avoid data collision.
You can use an AUTO_INCREMENT for your table, but give the users the encrypted version:
encrypted_id: SELECT HEX(AES_ENCRYPT(id, 'my-private-key'));
id: SELECT AES_DECRYPT(UNHEX(encrypted_id), 'my-private-key');
179
If your're open to suggestions and you can implement it, use UUIDs.
MySQL's UUID() function will return a 36 chars value which can be used for ID.
If you want to use integer, still, I think you need to create a function getRandID() that you will use in the INSERT statement. This function needs to use random + check of existing ids to return one that is not used before.
Check RAND() function for MySQL.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
