Auto Increment a column that is not unique...?

Question

I've been thinking on this for a few hours, and I can't work out a viable solution - I can think of solutions, but they are clunky.

Using MySQL, I want to create an invoice table using auto increment for the invoice number. Pretty standard stuff. HOWEVER, my challenge is that I wish to have multiple companies generating invoices in this table, but all still with the unique invoice number that is auto incremented from the previous one.

For example:

• Company A's previous invoice is 3010
• Company B's previous invoice is 2144
• Company C's previous invoice is 7889877

When Company A creates their next invoice I want it to be 3011. B to be 2145 etc.

I had the idea of doing a query WHERE company = 'B' ORDER BY id DESC then using PHP to increase that value++, then insert a new record with the invoice number inserted from PHP. In this case the 'id' field would be irrelevant to the actual invoice number, and just be a unique identifier.

Is there a better way?

Answer 1

That seems possible in MyISAM but afaik not using InnoDB:

CREATE TABLE `invoice_sequences` (
  `company_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`company_id`,`invoice_id`)
) ENGINE=MyISAM;

http://sqlfiddle.com/#!9/d135c/1 (if the fiddle returns an error, try again; it seems a bit slow sometimes).

Answer 2

Probably not. Auto incrementing attributes are typically based on a sequence object that just gives an incremental higher number each time it is called. That you're trying to do requires knowledge of the current state of the table, so there's unlikely to be a way other than querying the database first. You should of course wrap this in a transaction to keep it consistent.