Laravel：插入后LAST_INSERT_ID（）为0？

Question

I have the following working query:

$year         = 2019;
$month        = 6;
$stmt         = $db->prepare('INSERT INTO officeRechNr (jahr,monat,zahl) VALUES (?,?,1) ON DUPLICATE KEY UPDATE zahl = LAST_INSERT_ID(zahl+1)');
$stmt->bind_param('ii', $year, $month);
$stmt->execute();
echo $db->insert_id;

The table officeRechNr has the unique primary index ['jahr','monat'] and zahl is an index with autoincrement.

So if the table officeRechNr is empty, and I execute the code 5 times, then the output is

1, 2, 3, 4, 5

I tried to translate this in Laravel 5 with

\DB::select(DB::raw('INSERT INTO officeRechNr (jahr,monat,zahl) VALUES (?,?,1) ON DUPLICATE KEY UPDATE zahl = LAST_INSERT_ID(zahl+1)'),[2019,6]);

return DB::select('SELECT LAST_INSERT_ID()');

However, if I execute this code 5 times I get

0, 2, 3, 4, 5

Although it returns 0 after the first insert, there is a 1 in the zahl column.

Why does my Laravel Code not return 1 after the first insert?

Answer 1

Short Answer: SELECT_LAST_INSERT_ID() will not work on first insert(see example below). Instead use \DB::getPdo()->lastInsertId();

---

Why SELECT_LAST_INSERT_ID() will not work on first insert

Its stated here: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.

With automatically generated is meant that the auto column increased itself and is not set by me.

Example:

Image a table test like this:

CREATE TABLE test (
       id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
       name VARCHAR(10) NOT NULL
       );

Now

$sql = 'INSERT INTO test ( id, name ) VALUES (33, "ADAM")';
$db->query($sql);
echo $db->insert_id;
$result =  $db->query('SELECT LAST_INSERT_ID()');
$row = $result->fetch_assoc();
print_r($row);

returns

33

array('SELECT_LAST_INSERT_ID()' => 0);

Doing the same with

$sql = 'INSERT INTO test (  name ) VALUES ("Eba")';

returns

1

array('SELECT_LAST_INSERT_ID()' => 1);

So in the first statement:

$stmt         = $db->prepare('INSERT INTO officeRechNr (jahr,monat,zahl) VALUES (?,?,1) ON DUPLICATE KEY UPDATE zahl = LAST_INSERT_ID(zahl+1)');

SELECT_LAST_INSERT_ID() is 0 because the AI key was not automatically generated, although the id is 1 in the table. The reason why it works for the sequent numbers is

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().