two auto incrementing fields in one MySQL table

Question

I'm setting up a table that needs two auto-incrementing fields, 'id' and 'member#'.

I'll use AUTO_INCREMENT = 1001 on the latter for new data, as there is old data with member numbers less than 1000.

I'll use 'MAX(id)+1' on the 'id' field to auto-increment it.

But I'm not sure if this will do the job whenever there's an INSERT, or even where to put that bit of code. All I'm trying to do here is auto-increment the field, not SELECTing anything.

And out of curiosity, why is there only one AUTO_INCREMENTing field per table?

Surely, it can't be difficult to code AUTO_INCREMENT_2, AUTO_INCREMENT_3 etc.

All answers and assistance appreciated.

================================

ADDITIONAL INFORMATION AND LINKS

Sorry for the delay in my response, I've been doing additional research.

Ok so to explain further, we have people joining our group via the net. As such we need to assign a unique membership number to each person. Two John Does? Two different membership numbers. For this I've set the member# column as AUTO_INCREMENT, and then AUTO_INCREMENT = 1001 as a table option. Old membership numbers have three digits, new memberships have four. So each time someone registers as a new member on the web, there's an insert command that automatically assigns the next four digit membership number in the series to the new member.

member# INT(6) UNSIGNED NOT NULL UNIQUE KEY AUTO_INCREMENT And as a table option AUTO_INCREMENT = 1001

I hope this is clear. Other situations where someone might want to use a similar strategy could be assigning consecutive invoice numbers, receipt numbers, account numbers, etc. So how does one guarantee a +1 result, ie consecutive numbers?

Now we also need a table id column. Lots of tables need a table id. It too needs to be assigned an AUTO_INCREMENT value, in our case, beginning with 1, and incrementing by 1 (the default), to identify and distinguish one row from another. But unfortunately there can be only one AUTO_INCREMENT column per table in MySQL. :-/

So this situation belongs to a class of problems known as MAX+1 problems. (It may also be related to ROW_COUNT and LAST_INSERT_ID solutions.) The limit of a single AUTO_INCREMENT field per table requires a MAX+1 workaround and I am looking for advice on the best way to implement this. For example, is there a way to set this up inside the CREATE TABLE command itself, when defining the id field? Or something else of an equally simple nature, such as writing a function. It is indeed preferable to optimize for efficiency and use only needed features rather than implement a series of commands. Typically a suggested work around might be:

Lock tables membership write;
$max = SELECT MAX(id) FROM membership;
INSERT INTO membership ( id, firstname, lastname )
VALUES ($max+1 , 'jane', 'smith')
unlock tables;

Is there something better?

As whether AUTO_INCREMENT_2 /_3... features should exist. Well, I'd have to point out that there are a lot of features in MySQL that I'll never use, but obviously someone needs them. Nevertheless, it would be convenient to have this for those (rare) occasions when you might need it. Perhaps there is a distinction to be drawn between having a feature available and using it on any given table. I doubt an unused feature requires much in the way of additional memory or clicks (which are pretty cheap these days anyways).

Some links that may prove useful in understanding this situation:

https://duckduckgo.com/?q=mysql+max%2B1+problems&t=ffab&atb=v1-1&ia=web

Insert and set value with max()+1 problems

Problem with MySql INSERT MAX()+1

https://bugs.mysql.com/bug.php?id=3575

All answers, advice and assistance appreciated.

Answer 1

Each InnoDB table has at most one counter for its auto-increment. This is part of the implementation. If you could define N auto-increment columns, in the same table, it would need more storage space to store N counters. It would require the auto-increment lock to last longer while you incremented N counters.

As for why is there only one per table, sure, it is possible that they could implement it to support more than one, but why?

It would make the implementation a lot more complex, and hinder performance, for cases that 99.99% of apps don't need.

They were trying to solve the needs for the majority of cases. In nearly every case of a table with an auto-increment, one per table is sufficient.

In nearly every case where someone like you thinks they need more than one per table, you'd be wise to step back and reconsider your design.

Answer 2

1. In MySQL the table structure cannot contain more than one auto_increment field. When you try to create a table with 2 autoincremented fields or alter the table in attempt to create second autoincrement, the query fails.

2. Autoincrement guarantees that each next value generated in the field will be greater than previous one in current connection. But it do NOT guarantee, that each next value generated in the field will be greater than previous value by 1. The "delta" may be 2 or even 1000... it cannot be negative or zero only.