Does the MySQL Auto Increment Column Need to Be Unique or a Primary Key

Question

I was developing a database in SQL Server where I was using an identity column as a seed for a primary key field. The intention was to reset the identity to 1 at the beginning of every year. This would allow us to create a PK of the Year - Identity Column.

Create Table Issues (
IssueID  AS RIGHT(CONVERT(VARCHAR, Year(getdate()), 4),2) + '-' + RIGHT(REPLICATE('0', 2) + 
CONVERT(VARCHAR, RecordID),3) NOT NULL PRIMARY KEY,
RecordID int Identity (1,1),.........)

The result would be

     
IssueID    RecordID
20-001        1
20-002        2
20-003        3
21-001        1
etc....

Now I've been told we are going to use a MySQL database instead.

Can an Auto-Increment field in MySQL contain duplicate values like it can in SQL Server?
If Not, how can I do what I need to do in MySQL?

You can have duplicate values in an `AUTO_INCREMENT` column, but the `AUTO_INCREMENT` feature itself will not help you here in any way you hope. — Progman, Jul 14 '20 at 20:22

score 1 · Answer 1 · answered Jul 14 '20 at 22:46

In MySQL, you can't use the default auto-increment feature for what you describe, a incrementing value that starts over per year.

This was a feature of the MyISAM storage engine years ago. An auto-increment that was the second column of a multi-column primary key would start counting from one for each distinct value in the first column of the PK. See the example under "MyISAM Notes" on this page: https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

But it's considered not a good idea to use MyISAM because it does not support ACID. In general, I would find another way of solving this task. I would not use MyISAM.

In InnoDB, there's no way the table will generate a value that is a duplicate of a value currently in the table, or even a value less than the maximum value previously generated for that table. In other words, there's no way to "fill in the gaps" using auto-increment.

You can use ALTER TABLE mytable AUTO_INCREMENT=1 to reset the counter, but the value you set it will automatically advance to the max value currently in the table + 1.

So you'll have to generate it using either another table, or else something other than the MySQL database. For example, I've seen some people use memcached, which supports an atomic "increment and return counter" operation.

Another thing to consider: If you need a row counter per year, this is actually different from using MySQL's auto-increment feature. It's not easy to use the latter as a row counter. Besides, what happens if you roll back a transaction or delete a row? You'd end up with non-consecutive RecordId values, with unexplained "gaps." It's also a fact about the auto-increment feature that it guarantees that subsequent id's will be greater, but it does not guarantee to generate all values consecutively. So you'll get gaps eventually anyway.

score 0 · Answer 2 · answered Jul 14 '20 at 21:33

In MySQL a table can have only one auto_increment column and this column must be a part of the primary key. See details here.

Technical workaround for your task would be creating of a table with a single auto_increment column, and you can obtain auto_increment value by inserting a record into this table and immediately calling standard MySQL function last_inser_id(). When time comes you should truncate the table - in this case the auto_increment count will be reset.

Does the MySQL Auto Increment Column Need to Be Unique or a Primary Key

2 Answers2