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

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?

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).

StackExchange Database Administrators Q#119183, answer score: 2