Is there a way to emulate auto_increment on a second column in MySQL?

Question

I think this is more clear than my last version:

I have a table that already has a PK field starting with 1 that is already set for auto-increment. I would like to add a new field that does not start with 1, instead starting with an arbitrary number (think invoice number) and also automatically increments. Apparently, you can only have one col per table that has that attribute. Is there a way, in one query operation, to force MySQL to increment the last (or maximum) value of that field by 1 as the value for that col in the new row?

Update: I just ended up using the id column as a base for the new value I needed, and deleted the non-essential column. My question never got answered, really, but I did take that as a hint to rethink the approach so thanks!

Answer 1

This isn't really an answer to your specific question (since you ask how one can achieve your goal within one SQL statment), but you could define a trigger on the table that populates the second column by calling a sequence-generating function. This will ensure that the value of this column is truly unique (which isn't the case if you are doing max(value) or similar) as the calls to the function will be queued.

Or if you really need two columns that show the same value, create a view that references the same column twice with different aliases.

Answer 2

if it's in the same table you can set the column you want to your unique column but they would have the same value which seems pointless> Are you sure you need this?

Answer 3

It seems silly to have two columns with the same value, but here you go.

create trigger foo_before_insert 
  before insert on my_table
  for each row set
  new.second_auto_column = first_auto_column
;

Answer 4

This would be easier to answer if you can tell us what the field is for. Normally there's a better way to achieve the same business requirement. Whatever you would do would be a hack, and you'd end up spending a lot of time dealing with the unintended consequences.