Why there is a difference between PDO::lastInsertId and Mysql's LAST_INSERT_ID() behavior?

My current development environment is PHP 7.2. This is MariaDB 10.3.11.

When using PDO's lastInsertId function, if you run another query after the insert query, the lastInsertId result value will always be 0.

example table is

create table test
(
    id    int unsigned auto_increment comment 'PK' primary key,
    title varchar(128) charset utf8mb4 not null comment 'title'
)
comment 'test';

create table test2
(
    id    int unsigned auto_increment comment 'PK' primary key,
    title varchar(128) charset utf8mb4 not null comment 'title'
)
comment 'test2';

An example code is

public function pdoTest()
{
    $title = "test";
    $id = 1;

    $db = new PDO('mysql:host=<your-host>;port=<your-port>;dbname=<your-dbname>;charset=utf8', '<your-username>', '<your-password>');

    $db->beginTransaction();

    $query = "INSERT INTO test (title) VALUES (:title)";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':title', $title);
    $stmt->execute();

    $updateQuery = "UPDATE test2 SET title = :title WHERE id = :id";
    $stmt = $db->prepare($updateQuery);
    $stmt->bindParam(':id', $id);
    $stmt->bindParam(':title', $title);
    $stmt->execute();

    echo $db->lastInsertId();
    $db->commit();
}

In the above situation, the return value of lastInsertId was 0. I searched the PHP site. Does anyone know why lastInsertId is not working properly?

I am wondering if only the insert query must be performed before executing the lastInsertId function.

Solution

When working with MySQL, PHP is using Mysql C API.

In order to retrieve the auto-increment value, PHP is calling mysql_insert_id() function from this API. And, according to MySQL documentation,

The reason for the differences between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide more exact information about what happens to the AUTO_INCREMENT column.

So, it seems that things are the other way round: it's LAST_INSERT_ID()'s behavior is closer to "incorrect", as it's still returning a value after executing queries that didn't change it.

While PDO::lastInsertId() gives you more exact result, returning a value only if the last query affected the AUTO_INCREMENT column.