1

I thought I was witnessing some odd behavior. Upon inserting a record, LAST_INSERT_ID() is giving me incorrect results.

Then I read http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed value.

Is there some other way to get the autoincremented ID outside of the trigger? 

-- MySQL Script generated by MySQL Workbench
-- 07/08/16 05:58:42
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`a`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`a` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`, `a_id`),
  CONSTRAINT `fk_t1_a1`
    FOREIGN KEY (`a_id`)
    REFERENCES `mydb`.`a` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`inc`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`inc` (
  `a_id` INT NOT NULL,
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a_id`, `id`))
ENGINE = MyISAM;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
USE `mydb`;

DELIMITER $$
USE `mydb`$$
CREATE TRIGGER `t_BINS` BEFORE INSERT ON `t` FOR EACH ROW
BEGIN 
INSERT INTO inc(a_id) VALUES(NEW.a_id);
SET NEW.id=LAST_INSERT_ID();
END$$


DELIMITER ;

test

mysql> INSERT INTO a(id) VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t(a_id)VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t(a_id)VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t(a_id)VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM inc;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    1 |  3 |
+------+----+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    1 |  3 |
+------+----+
3 rows in set (0.00 sec)

mysql> INSERT INTO t(a_id)VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t(a_id)VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t(a_id)VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    1 |  3 |
|    1 |  4 |
|    2 |  1 |
|    2 |  2 |
+------+----+
6 rows in set (0.00 sec)

mysql> SELECT * FROM inc;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    1 |  3 |
|    1 |  4 |
|    2 |  1 |
|    2 |  2 |
+------+----+
6 rows in set (0.00 sec)

mysql> INSERT INTO inc(a_id)VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM inc;
+------+----+
| a_id | id |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    1 |  3 |
|    1 |  4 |
|    2 |  1 |
|    2 |  2 |
|    2 |  3 |
+------+----+
7 rows in set (0.00 sec)

mysql>
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • Interesting. Only way I can think of (which is a bit clunky) is to insert a row in to a table with a key of the previous LAST_INSERT_ID and a 2nd column of the new LAST_INSERT_ID (before it is reset at the end of the trigger). At least you could then get the value, but I would hope there is a better method. – Kickstart Jul 08 '16 at 13:31
  • my suggestion is make a tmp_insert_id_table and write insert query in trigger to insert last_insert_id() in it. So you don't have any kind of mismatch. This should be in after insert trigger. – Rakesh Kumar Jul 08 '16 at 13:33
  • @Kickstart Yea, a bit clunky :). By the way, my reason is described by http://stackoverflow.com/a/38266771/1032531 – user1032531 Jul 08 '16 at 13:34
  • @RakeshKumar But then how would you get it back? Query the temp table on what? – user1032531 Jul 08 '16 at 13:35
  • for example if you want to write a after insert trigger on table a then, insert a row in tmp table with last_insert_id+table_name – Rakesh Kumar Jul 08 '16 at 13:39
  • @RyanVincent I previously viewed both posts, and don't think it will help. I was hoping to explicitly set the last_insert_id value inside the trigger. – user1032531 Jul 08 '16 at 13:45
  • @RakeshKumar Ah, I understand. Unfortunately, I feel it would be better to get rid of the trigger and manually make two inserts getting last_insert_id after the first insert. – user1032531 Jul 08 '16 at 13:47
  • use afterinsert trigger – Rakesh Kumar Jul 08 '16 at 13:47
  • @RakeshKumar To what benefit? – user1032531 Jul 08 '16 at 13:49
  • Would a tmp table work for this? Not sure, but reading this suggests there might be issues - http://dba.stackexchange.com/questions/3479/store-result-set-in-temporary-table-variable-or-separate-variables-within-a-tri - with DDL not being allowed in a trigger. – Kickstart Jul 08 '16 at 13:56
  • @user1032531 benefiy is that, in afterinsert trigger you will get last insert id and also know table name. – Rakesh Kumar Jul 08 '16 at 15:10
  • By the bu knowing last id what you want to achieve, so i can suggest more if possible. – Rakesh Kumar Jul 08 '16 at 15:11

0 Answers0