在LAST_INSERT_ID中从触发器中更改MySQL ()

Question

我有一个BEFORE INSERT TRIGGER，用于计算列(id_2)的AUTO_INCREMENT值。

id_1 | id_2 | data
1    | 1    | 'a'
1    | 2    | 'b'
1    | 3    | 'c'
2    | 1    | 'a'
2    | 2    | 'b'
2    | 3    | 'c'
2    | 4    | 'a'
3    | 1    | 'b'
3    | 2    | 'c'

我有主(id_1，id_2)，我正在使用InnoDB。以前，该表使用MyISAM，而我没有遇到任何问题：id_2被设置为AUTO_INCREMENT，因此id_1的每个新条目都会自己生成新的id_2。现在，在切换到InnoDB之后，我有了这个触发器来做同样的事情：

SET @id = NULL;
SELECT COALESCE(MAX(id_2) + 1, 1) INTO @id FROM tbl WHERE id_1 = NEW.id_1;
SET NEW.id_2= @id;

它工作得很好，除了现在LAST_INSERT_ID()有错误的值(它返回0)。很多代码取决于LAST_INSERT_ID()是否正确。但是，自MySQL 5.0.12以来，触发器中对LAST_INSERT_ID所做的任何更改都不会影响全局值。有什么办法绕过这件事吗？我可以很容易地设置AFTER UPDATE TRIGGER，它通过调用LAST_INSERT_ID(NEW.id_2)来更改LAST_INSERT_ID，但是任何客户端都会将LAST_INSERT_ID设置为0。

是否有任何工作可以迫使MySQL维护在触发器中更改的LAST_INSERT_ID状态？除了切换回支持这一点的MyISAM或运行另一个SELECT max(id_2) FROM tbl WHERE id_1 = :id作为事务的一部分以确保找到的行将是前面插入的行之外，还有其他选择吗？

> SHOW CREATE TABLE tbl;

CREATE TABLE `tbl` (
   `id_1` int(11) NOT NULL,
   `id_2` int(11) NOT NULL,
   `data` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id_1`,`id_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

示例：

INSERT INTO tbl (id_1, id_2, data) VALUES (1, NULL, 'd');
SELECT LAST_INSERT_ID();

第一个语句将将行1 | 4 | 'd'插入到表中。第二个语句将返回0，但我需要它返回4。

正如Ravinder Reddy所要求的，添加关于系统的简短说明：

我有一个包含篮的表，还有另一个包含items的表(tbl)。购物篮是由应用程序创建的，并被分配给篮子表上的AUTO_INCREMENT中的ID。任务是用id =将插入到tbl中，在该篮子的作用域中为它们分配一个唯一的ID。每个项都有一些与其相关联的data，这可能在相同的篮中重复。因此，在实践中，我试图将所有data条目存储在一个单一的购物篮中，然后能够通过它们的id_1-id_2对引用(并检索)这些单独的条目。

Answer 1

使用表结构描述，很明显，它没有可以自动生成值的主键字段。MySQL的information_schema.tables不包含auto_increment值，而是包含未定义auto_increment的字段的null。

触发器问题：

触发器正文中使用的代码块似乎取决于id字段的显式计算和输入。它没有使用auto_increment字段的默认行为。

根据https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id：

LAST_INSERT_ID()返回一个BIGINT无符号(64位)值 表示第一个自动生成的值。 成功插入AUTO_INCREMENT列 作为最近执行INSERT语句的结果。

很明显，它只适用于auto_increment字段。

没有一个字段id_1和id_2被归为auto_increment。

由于这个原因，虽然在插入时将null作为这些字段的输入传递，但是不会自动生成和分配值。

更改表以将auto_increment设置为这些id_x字段之一，然后开始插入值。一个警告是，在插入过程中显式地将值传递给auto_increment字段将导致last_insert_id返回zero或最近自动生成的值，而不是NEW.id。在插入过程中传递一个null或不选择auto_increment字段将触发该字段的新值的生成，last_insert_id可以选择并返回该字段。

下面的示例演示了上面的行为：

mysql> drop table if exists so_q27476005;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table so_q27476005( i int primary key );
Query OK, 0 rows affected (0.33 sec)

下面的语句显示字段的下一个适用的auto_increment值。

mysql> select auto_increment
    ->   from information_schema.tables
    ->  where table_name='so_q27476005';
+----------------+
| auto_increment |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

让我们尝试在字段中插入一个null值。

mysql> insert into so_q27476005 values( null );
ERROR 1048 (23000): Column 'i' cannot be null

上面的语句失败了，因为输入进入了not null primary key字段，但没有归因于auto_increment。仅对于auto_increment字段，可以传递null输入。

现在让我们看看last_insert_id的行为：

mysql> insert into so_q27476005 values( 1 );
Query OK, 1 row affected (0.04 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

由于输入是显式的，而且字段也不是auto_increment的属性，

对last_insert_id的调用导致了一个0。请注意，如果在同一数据库连接会话中对另一个表的任何其他auto_increment字段进行了另一个auto_increment调用，则这也可以是一些其他值。

让我们看看表中的记录。

mysql> select * from so_q27476005;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

现在，让我们将auto_increment应用于字段i。

mysql> alter table so_q27476005 change column i i int auto_increment;
Query OK, 1 row affected (0.66 sec)
Records: 1  Duplicates: 0  Warnings: 0

下面的语句显示字段i的下一个适用的i值。

mysql> select auto_increment
    ->   from information_schema.tables
    ->  where table_name='so_q27476005';
+----------------+
| auto_increment |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

您可以反复检查last_insert_id是否仍然是相同的。

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

让我们将一个null值插入到字段i中。

mysql> insert into so_q27476005 values( null );
Query OK, 1 row affected (0.03 sec)

通过将一个null传递给一个primary key字段，它成功了，因为该字段的属性为auto_increment。

让我们看看生成和插入了哪个值。

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

字段i的下一个适用的i值是：

mysql> select auto_increment
    ->   from information_schema.tables
    ->  where table_name='so_q27476005';
+----------------+
| auto_increment |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from so_q27476005;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

现在，让我们观察当为字段提供显式输入时，last_insert_id是如何产生的。

mysql> insert into so_q27476005 values( 3 );
Query OK, 1 row affected (0.07 sec)

mysql> select * from so_q27476005;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)


mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

您可以看到，由于显式输入，last_insert_id没有捕获该值。

但是，信息模式会注册下一个可应用的值。

mysql> select auto_increment
    ->   from information_schema.tables
    ->  where table_name='so_q27476005';
+----------------+
| auto_increment |
+----------------+
|              4 |
+----------------+
1 row in set (0.08 sec)

现在，让我们观察当字段的输入是自动/隐式时，last_insert_id是如何产生的。

mysql> insert into so_q27476005 values( null );
Query OK, 1 row affected (0.10 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                4 |
+------------------+
1 row in set (0.00 sec)

希望这些细节对你有帮助。