Why Does MySQL's LAST_INSERT_ID() Return 0, and How Can I Fix It?

MySQL: Troubleshooting LAST_INSERT_ID() Returning 0

In MySQL, the LAST_INSERT_ID() function returns the ID generated for the last inserted row in an AUTO_INCREMENT column. However, you have encountered an issue where it consistently returns 0 despite inserting new records into a table.

Upon investigation, it appears that the problem stems from your MySQL configuration. You mentioned that the PHP functions mysql_insert_id and PDO::lastInsertId() also yield no result. This suggests that the issue is not specific to the LAST_INSERT_ID() function but rather lies in the MySQL connection itself.

The solution to this problem is to set the PersistentConnections parameter to TRUE in your MySQL config file. This ensures that your PHP script maintains the same connection to the database for the duration of its operation. When PersistentConnections is set to FALSE, a new connection is created for each query, which causes the CONNECTION_ID to change. As a result, LAST_INSERT_ID() becomes ineffective because it cannot trace the connection with the last insert operation.

Setting PersistentConnections to TRUE solves this issue, allowing your script to reuse the same connection and thus correctly return the AUTO_INCREMENT ID of the last inserted record. Additionally, you can refer to the documentation on Every query creates a new CONNECTION_ID() for further information. Thanks to dnagirl for their assistance in resolving this problem.

The above is the detailed content of Why Does MySQL's LAST_INSERT_ID() Return 0, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!