How to generating unique token in MYSQL

user_5767018 0
Viewed 1908

I am new to MySQL and I am having an issue with unique identifier token. Here is my employee table.

Employee Table (MySQL)

CREATE TABLE employees (
    emp_no      INT             NOT NULL,  --  AUTO_INCREMENT    
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,     
    hire_date   DATE            NOT NULL,
    token varchar(100) not null,
    PRIMARY KEY (emp_no)                   
);

I have employee table in MySQL. emp_no is the primary key with auto-increment. I want to have a token for each employee and the token should be unique for each employee.

The token should look something like:

32f41e07b9e8393ae250ad617801a155

How can I achieve this?

My Approach

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 16) AS myrandomstring

But I don't think that the above query will guarantee uniqueness for each employee.

mysql sql
3 Answers

You can use UUID Mysql function to generate an id.

From what you research, it seems that the function UUID_SHORT should be a better option.

mysql> SELECT UUID_SHORT();
    -> 92395783831158784
user_103743410

You can rely on external libraries for that for example ramsey/uuid, leave the table with regular unsigned integer ID and make another column uuid which is of typ VARCHAR(), I wouldnt count on mysql generating my UUID I'd rather do it programatically.

user_129389380

UUID is a good way to generate a unique String id and will not duplicated for each time you generated.

The only concern for UUID is length, which can make index not efficient as primary key(auto-increment int). But it depends on your data size and logic need.

I would recommend to generate UUID from your client code since it much clear and easy to maintain or update if you need.

user_90126490
Related