检索InnoDB组合密钥表的最后插入ID

I have the following table:

我有下表：

CREATE  TABLE `my_table` (
  composite_pk1 INT NOT NULL ,
  composite_pk2 INT NOT NULL ,
  data VARCHAR(255) NOT NULL ,
   primary key (composite_pk1, composite_pk2)
) ENGINE=InnoDB;

For a given composite_pk1, I wish composite_pk2 to act as an autoincrement primary key. I don't wish to lock the table, and as such plan on using a trigger such as the following:

对于给定的composite_pk1，我希望composite_pk2充当自动增量主键。我不想锁定表格，因此计划使用如下的触发器：

DELIMITER $$

CREATE TRIGGER my_trigger BEFORE INSERT ON my_table
FOR EACH ROW BEGIN
    SET NEW.composite_pk2 = (
       SELECT IFNULL(MAX(composite_pk2), 0) + 1
       FROM issue_log
       WHERE composite_pk1  = NEW.composite_pk1
 );

END $$

I can now insert a record:

我现在可以插入一条记录：

$stmt=$myDB->prepare('INSERT INTO my_table(composite_pk1, data) VALUES (?,?)');
$stmt->execute([123,'hello']);

How do I get the last inserted composite_pk2? PDO::lastInsertId only works with native autoincrement tables (i.e. not the trigger approach). I "could" later do a SELECT query to get the max value, however, there is no guarantee that another record has snuck in.

如何获取最后插入的composite_pk2？ PDO :: lastInsertId仅适用于本机自动增量表（即不是触发器方法）。我“可以”稍后执行SELECT查询以获取最大值，但是，无法保证其他记录已经进入。

1 个解决方案

#1

You can make composite_pk2 an unique key with auto_increment:

您可以使用auto_increment使composite_pk2成为唯一键：

CREATE  TABLE `my_table` (
  composite_pk1 INT NOT NULL ,
  composite_pk2 INT NOT NULL unique auto_increment,
  data VARCHAR(255) NOT NULL ,
   primary key (composite_pk1, composite_pk2)
) ENGINE=InnoDB;

Now last_insert_id() will return the recently created id for composite_pk2.

现在last_insert_id（）将返回最近为composite_pk2创建的id。

1 个解决方案

#1

更多相关文章

随机推荐