Why Does `LAST_INSERT_ID()` Return 0 in MySQL?

LAST_INSERT_ID() Consistently Returns 0 in MySQL

Issue Overview:

Inserting data into a MySQL table using common methods (such as inserting NULL, 0, or empty parentheses) and subsequently querying for the last inserted ID (LAST_INSERT_ID()) returns 0 consistently. This issue affects both PHP's mysql_insert_id and PDO::lastInsertId().

Solution:

The problem lies in the MySQL phpmyadmin configuration file. When PersistentConnections is set to FALSE, a new CONNECTION_ID is established for each query. This invalidates the LAST_INSERT_ID() result because it is associated with the previous connection.

To resolve the issue, set PersistentConnections to TRUE in the phpmyadmin configuration file. This ensures that the same connection is maintained across multiple queries, allowing LAST_INSERT_ID() to accurately return the last inserted ID.

Additional Information:

For further insights into the impact of PersistentConnections, refer to the topic "Every query creates a new CONNECTION_ID()".

Acknowledgments:

Special thanks to dnagirl for valuable assistance in identifying this issue.

The above is the detailed content of Why Does `LAST_INSERT_ID()` Return 0 in MySQL?. For more information, please follow other related articles on the PHP Chinese website!