LAST_INSERT_ID () is not equal to $ db-> insert_id?

Question

I have the following 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;

echo '|';

$sql = 'SELECT LAST_INSERT_ID() as number';
$result = $db->query($sql);
$row = $result->fetch_assoc();
echo $row['number'];
echo '<br>';

The table officeRechNrhas a unique primary index ['jahr','monat'], and zahl- indexwith autoincrement.

If the table is officeRechNrempty and I execute the code 3 times, then the output

1 | 0

2 | 2

3 | 3 ...

Why is LAST_INSERT_ID()zero after insertion, but correct after update? How do I change my query so that both functions print the same number (1) after insertion?

---

Edit: The purpose of the code is what I need for each account created in a particular year and month, the third unique increasing number. So, for example, if we have 7 invoices in 2015 and the month of May (3), then I will have the following numbers

2015-3-1
2015-3-2
2015-3-3
2015-3-4
2015-3-5
2015-3-6
2015-3-7

, SQL, . , zahl autoincrement, , insert_id (. https://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html). insert_id, -.

Answer 1

, LAST_INSERT_ID(...); , "" LAST_INSERT_ID() . , ( ) LAST_INSERT_ID() return 0. next+1 LAST_INSERT_ID(), . MySQL 12.14 :

expr LAST_INSERT_ID(), , LAST_INSERT_ID().

LAST_INSERT_ID() .

INSERT INTO
    officeRechNr (jahr,monat,zahl)
VALUES
    (?,?,1)
ON DUPLICATE KEY UPDATE zahl = zahl+1

( ) .

, SELECT. , , , SELECT.