Increment value on new row

Question

Given this table design in a web application:

CREATE TABLE `invoice` (
  `invoice_nr` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `revision` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`invoice_nr`,`revision`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

What's the most handy and reliable way to insert a new invoice revision and get back the assigned revision number?

The first obvious approach strikes me as unreliable in a shared environment:

SELECT MAX(revision)+1 AS next_revision -- E.g. 2
FROM invoice
WHERE invoice_nr = 31416;

INSERT INTO invoice (invoice_nr, revision)
VALUES (31416, 2);

This alternative looks slightly better (I don't know if it's actually better):

INSERT INTO invoice (invoice_nr, revision)
SELECT 31416, MAX(revision)+1
FROM invoice
WHERE invoice_nr = 31416;

... but I can't know the revision number unless I run a new query:

SELECT MAX(revision) AS last_revision
FROM invoice
WHERE invoice_nr = 31416;

Is there a recommended method?

App runs on PHP with good old mysql extension--mysql_query() et al.

Answer 1

Mysql has a funtion called last_insert_id() that returns the last auto generated ID. So you can just SELECT last_insert_id() straight after inserting your data.

PHP has a built in function for doing this called mysql_insert_id().

More on that here: http://www.php.net/manual/en/function.mysql-insert-id.php

Whilst that's all true and generally useful it's not actually what's being looked for here. How I'd do this is generate to tables. One called invoice with you auto increment field and a second called invoice_revisions. This should have the same format as the invoice table with the added vision field.

Then when you update your invoice table you first do:

INSERT INTO invoice_revision SELECT i.*,IFNULL(max(ir.revision),0)+1 AS revision FROM invoice i LEFT JOIN invoice_revision ir on ir.invoice_nr = i.invoice_nr WHERE i.invoice_nr = ?

Then update your invoice table as normal. This way you have your up to date data in the invoice table and the list of all the previous versions in the invoice_revisions table.

Note if you are using Myisam tables you and set the revision at the auto_increment in that table:

http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html

Answer 2

if you put a properly isolated transaction around your insert and select nobody may alter these tables between both statements.

another approach is to use a stored procedure to execute both commands and return the result.

Answer 3

I've gathered a couple of techniques from the MySQL manual. I thought I should share them here for the records.

1. Table locking

If you place a lock on the table, other simultaneous processes will be queued. Then, you don't need any special trick to avoid dupes:

LOCK TABLES invoice WRITE;

SELECT MAX(revision)+1 AS next_revision -- E.g. 2
FROM invoice
WHERE invoice_nr = 31416;

INSERT INTO invoice (invoice_nr, revision)
VALUES (31416, 2);

-- Or INSERT INTO ... SELECT

UNLOCK TABLES;

2. LAST_INSERT_ID(expr)

The LAST_INSERT_ID() function accepts an optional parameter. If set, it returns such value and it also stores it for the current session so next call to LAST_INSERT_ID() will return that value. This is a handy way to emulate sequences:

CREATE TABLE `sequence` (
    `last_value` INT(50) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Last value used'
);

START TRANSACTION;

UPDATE sequence
SET last_value=LAST_INSERT_ID(last_value+1);

INSERT INTO invoice (invoice_nr, revision)
VALUES (31416, LAST_INSERT_ID());

COMMIT;