Why Does MySQL's LAST_INSERT_ID() Return Zero?

MySQL: LAST_INSERT_ID() Returns Zero

When attempting to retrieve the last inserted ID using LAST_INSERT_ID() after inserting data into a table, users may encounter a persistent issue where the function returns 0. This can occur even when using alternate insert syntaxes such as inserting NULL or 0 or omitting values entirely.

Investigation and Solution:

The primary cause of this issue often lies in MySQL's PersistentConnections setting within the phpmyadmin configuration. When this setting is set to FALSE, a new connection is established for each query, effectively resetting the connection context.

In this specific instance, the issue stemmed from the PersistentConnections flag in the phpmyadmin config being set to FALSE. Each query initiated a new connection, resulting in a new CONNECTION_ID. Consequently, LAST_INSERT_ID() became ineffective, as it relies on the connection context to retrieve the last inserted ID.

Conclusion:

By setting PersistentConnections to TRUE in the phpmyadmin configuration file, the connection persistence issue can be resolved. This allows the LAST_INSERT_ID() function to correctly retrieve the last inserted ID, as the connection context is maintained across multiple queries.

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