- Available only for those tables where one column uses AUTO_INCREMENT. See a very clear example for a table that uses AUTO_INCREMENT in MySQL documentation here.
- To find what the current AUTO_INCREMENT value of some table is, you should NOT rely on the rows count, as some threads in StackOverflow suggest. Instead,
SHOW TABLE STATUS LIKE `yourtablename`;will reveal some info on your table. One of the columns will be “AUTO_INCREMENT”, with the value you’re looking for: The next time a row is inserted, the field with the AUTO_INCREMENT property will get this value. For example, this value is initially 1 when creating the table, and also after a TRUNCATE.
- To get only the AUTO_INCREMENT value:
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name=`yourtablename` AND table_schema = DATABASE();
- To compare with the number of rows (say, to verify that the AUTO_INCREMENT value equals to the number of rows plus 1), consider:
SELECT TABLE_ROWS, AUTO_INCREMENT FROM information_schema.tables WHERE table_name=`yourtablename` AND table_schema = DATABASE();
- To modify the AUTO_INCREMENT value yourself (say, to the number 123), you need:
ALTER TABLE `yourtablename` AUTO_INCREMENT = 123;
- A hack: To “reset” the AUTO_INCREMENT value yourself to the lowest possible number, try:
ALTER TABLE `yourtablename` AUTO_INCREMENT = 1;
- Last notes about ‘SHOW TABLE STATUS’: Yes, it has a weird syntax. Also, don’t get startled if the
TABLE_ROWScolumn shows a different number of rows than you actually have in that table (i.e. when calling
SELECT COUNT(1) FROM `yourtablename`, for example).
Some useful notes about MySQL’s AUTO_INCREMENT value: