Generate unique primary key automatically in MySQL

How to generate unique primary key automatically for every record in MySQL?

Note: This question was asked by one anonymous visitor as a comment. Sharing the response as a separate article to make it more visible to our visitors.

I've not used MySQL for quite a while now. When I used MySQL some 3-4 years back I probably couldn't find any direct support for Sequences in MySQL (unlike Oracle) and hence I used the AUTO_INCREMENT feature which served my purpose of automatic unique ID generation.

What I could interpret from your question that you simply want a mechanism to generate unique numbers which will serve as primary keys of the records being inserted in the table. If this is the case then it's very easy to implement using the AUTO_INCREMENT feature only. You just need to append the 'AUTO_INCREMENT' clause to the primary key column of the table. For example:-

CREATE TABLE table_name(

id INT PRIMARY KEY AUTO_INCREMENT,

column_name1 ...,

...);

Once the table is created you can set the initial value of the AUTO_INCREMENT counter by using the following statement:-

ALTER TABLE table_name AUTO_INCREMENT = value;

You can of course not specify a value less than or equal to any value that has already been used for that table. In such a case MyISAM resets the AUTO_INCREMENT counter to the current maximum value plus one. InnoDB might also take care of it accordingly. But it's always better to avoid setting such a value and the start value is normally set in the very beginning before any insertion. From there on MySQL engine automatically increments the current maximum value by one to get the new unique number. The default value of the AUTO_INCREMENT counter is 1.

You can use LAST_INSERT_ID() to get the unique number generated by the previous INSERT opration executed on a table which had an AUTO_INCREMENT column. You can even pass an expression as an argument to the LAST_INSERT_ID() function. In this case the value of the argument will be returned by the function and the same value is remembered to be returned by the next invocation of the LAST_INSERT_ID() function. You may use this to create a table serving as a Sequence:-

CREATE TABLE sequence_table(

seq INT NOT NULL

);

Initialize the sequence counter by inserting a record:-

INSERT INTO sequence_table VALUES(100);

Updating the sequence and reading the value:-

UPDATE sequence_table SET seq = LAST_INSERT_ID(seq + 1);

SELECT LAST_INSERT_ID();

These are probably the two most commonly used mechanisms of implementing sequences. I guess the former suits your requirement better in most of the cases.