增加新行的值

Question

鉴于 Web 应用程序中的此表设计：

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

插入新发票修订并取回分配的修订号的最方便、最可靠的方法是什么？

第一个明显的方法让我觉得在共享环境中不可靠：

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

这种替代方案看起来稍微更好（我不知道它是否实际上更好）：

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

...但我不知道修订号，除非我运行一个新查询：

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

有推荐的方法吗？

应用程序在 PHP 上运行，具有良好的旧 mysql 扩展 -mysql_query() 等。

Answer 1

Mysql 有一个名为 last_insert_id() 的函数，它返回最后一个自动生成的 ID。因此，您可以在插入数据后直接SELECT last_insert_id()。

PHP 有一个内置函数来执行此操作，称为 mysql_insert_id() 。

更多信息请参见： http://www.php.net /manual/en/function.mysql-insert-id.php

虽然这都是正确的并且通常很有用，但实际上并不是这里要寻找的内容。我的做法是生成表格。第一个称为带有自动增量字段的发票，第二个称为invoice_revisions。这应该与添加了视野字段的发票表具有相同的格式。

然后，当您更新发票表时，首先执行以下操作：

INSERT INTOinvoice_revision SELECT i.*,IFNULL(max(ir.revision),0)+1 AS revision FROMinvoice i LEFT JOINinvoice_revision ir on ir.invoice_nr = i .invoice_nr WHERE i.invoice_nr = ?

然后照常更新您的发票表。这样，您就可以在发票表中获得最新数据，并在invoice_revisions 表中获得所有先前版本的列表。

请注意，如果您使用 Myisam 表，请在该表中的 auto_increment 处设置修订版：

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

Answer 2

如果您在插入和选择周围放置一个适当隔离的事务，则没有人可以在两个语句之间更改这些表。

另一种方法是使用存储过程来执行这两个命令并返回结果。

Answer 3

我从 MySQL 手册中收集了一些技术。我想我应该在这里分享它们以供记录。

1.表锁定

如果在表上放置锁，则其他并发进程将排队。然后，您不需要任何特殊技巧来避免欺骗：

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)

LAST_INSERT_ID() 函数接受一个可选参数。如果设置，它会返回这样的值，并且还会将其存储在当前会话中，因此下次调用 LAST_INSERT_ID() 将返回该值。这是模拟序列的便捷方法：

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;