How to Get the Next Auto-Increment ID in MySQL?

Obtaining the Next Auto-Increment ID in MySQL

Inserting new rows into a MySQL table with an auto-incrementing ID column requires obtaining the next available ID value. Here's how to achieve this:

Using the Information Schema:
For the following query to return accurate results, you must ensure that information_schema_stats_expiry is set to 0 (only required once per session):

SET information_schema_stats_expiry = 0;

SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'table_name'
AND table_schema = DATABASE( );

Using `SHOW TABLE STATUS:
This query returns the next auto-increment ID value along with additional table information:

SHOW TABLE STATUS LIKE 'table_name';

Once you have the next available ID value, you can insert a new row into the payments table using the following query:

INSERT INTO payments (date, item, method, payment_code)
VALUES (NOW(), '1 Month', 'paypal', CONCAT("sahf4d2fdd45", id))

The above is the detailed content of How to Get the Next Auto-Increment ID in MySQL?. For more information, please follow other related articles on the PHP Chinese website!