I was recently working on some MySQL 5.5 performance testing. I had to generate a lot of SQL queries I would then use for my testing. To make my tests repeatable I needed to hardcode the values for IDs. That is, I couldn’t simply use:

INSERT INTO TABLE_NAME SET column1 = 'value1';

because this query may generate a row with a different ID each time (depending on the value of auto_increment for the table_name). What I really needed is:

INSERT INTO TABLE_NAME SET column1 = 'value1', id=17;

To accomplish that I needed to know a value of the next auto increment ID for my table. Here is how can you retrieve that value in MySQL:

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='table_name';

This kind of “predicting” next ID will not work reliably and you should never use it in real application. It is safe in my case, as I’m only using it for generating data (queries) and I’m 100% that there are no concurrent connections to my database.