How to generate unique id in MySQL?

user_177653 0
Viewed 143386

I'm programming a script using PHP and MySQL and I want to get a unique id (consisting of a string: capitals and small letters with numbers) like: gHYtUUi5b. I found many functions in PHP that can generate such numbers but I'm afraid about how to ensure the id is unique!

UPDATE: uuid is long, I mean such id like: (P5Dc) an 11 alphanumeric char.

mysql sql php
16 Answers

To get unique and random looking tokens you could just encrypt your primary key i.e.:

SELECT HEX(AES_ENCRYPT(your_pk,'your_password')) AS 'token' FROM your_table;

This is good enough plus its reversable so you'd not have to store that token in your table but to generate it instead.

Another advantage is once you decode your PK from that token you do not have to do heavy full text searches over your table but simple and quick PK search.

Theres one small problem though. MySql supports different block encryption modes which if changed will completely change your token space making old tokens useless...

To overcome this one could set that variable before token generated i.e.:

SET block_encryption_mode = 'aes-256-cbc';

However that a bit waste... The solution for this is to attach an encryption mode used marker to the token:

SELECT CONCAT(CONV(CRC32(@@GLOBAL.block_encryption_mode),10,35),'Z',HEX(AES_ENCRYPT(your_pk,'your_password'))) AS 'token' FROM your_table;

Another problem may come up if you wish to persist that token in your table on INSERT because to generate it you need to know primary_key for the record which was not inserted yet... Ofcourse you might just INSERT and then UPDATE with LAST_INSERT_ID() but again - theres a better solution:

INSERT INTO your_table ( token )
SELECT CONCAT(CONV(CRC32(@@GLOBAL.block_encryption_mode),10,35),'Z',HEX(AES_ENCRYPT(your_pk,'your_password'))) AS 'token'
FROM information_schema.TABLES 
WHERE  TABLE_SCHEMA = DATABASE() AND TABLE_NAME = "your_table";

One last but not least advantage of this solution is you can easily replicate it in php, python, js or any other language you might use.

user_39098740

USE IT

    $info = random_bytes(16);
    $info[6] = chr(ord($info[6]) & 0x0f | 0x40); 
    $info[8] = chr(ord($info[8]) & 0x3f | 0x80); 
    $result =vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($info), 4));
    return $result;
user_99566180

You could use Twitter's snowflake.

In short, it generates a unique id based on time, server id and a sequence. It generates a 64-bit value so it is pretty small and it fits in an INT64. It also allows for sorting values correctly.

https://developer.twitter.com/en/docs/basics/twitter-ids

In sum, it allows multiple servers, highly concurrency, sorting value and all of them in 64 bits.

Here it is the implementation for MySQL

https://github.com/EFTEC/snowflake-mysql

It consists of a function and a table.

user_2027050
Related