Text size:
How to retrieve and set the Auto_increment value of a MySQL table
The MySQL database management system has a mechanism whereby you can insert rows of data into a table and it'll keep track automatically of the row number. This means you don't have to waste time trying to find unique numbers to identify each row, and you have a self-managing index. Cool. But what if you need to manage this feature yourself? It so happens that I'm in a situation with a job for a client where I need to find out what the auto_increment value is for a table, keep it to one side, empty a table (which has the effect of resetting the auto_increment value) and then restore the old auto_increment value.
I knew how to set this value manually but had not yet found out how to retrieve it. Now, if you run a search on the Internet asking how to retrieve that elusive parameter, you'll find gazillions of pages telling you that the LAST_INSERT_ID() function returns the auto_incremented value assigned to the last line inserted:
mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 8 | +------------------+ 1 row in set (0.02 sec)That's all very well if you want to insert data before finding the last auto_increment value, but what if you don't want to alter the content of the table? After much searching and turning up the previous answer, and even being told on some pages that what I wanted to do was simply impossible, I decided to dig into the source code of phpMyAdmin.
phpMyAdmin does display the next auto_increment value associated with a table, so that information must be available somewhere. But where? A clue is given to us in lines 88 and then 463 (in phpMyAdmin-2.8.2.4) of tbl_printview.php. Don't you just love open source software? :o)
This query spits out all kinds of information about all the tables in the currently selected database:
mysql> SHOW TABLE STATUS; +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | admins | MyISAM | 10 | Dynamic | 2 | 94 | 188 | 281474976710655 | 6144 | 0 | 8 | 2008-04-21 06:19:13 | 2008-04-21 06:24:40 | NULL | utf8_general_ci | NULL | | | | archives | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2008-04-28 08:57:20 | 2008-04-28 08:57:20 | NULL | utf8_general_ci | NULL | | | | closures | MyISAM | 10 | Dynamic | 3 | 22 | 68 | 281474976710655 | 3072 | 0 | 4 | 2008-04-26 11:05:15 | 2008-04-26 15:53:07 | NULL | utf8_general_ci | NULL | | | | countries | MyISAM | 10 | Dynamic | 2 | 20 | 40 | 281474976710655 | 3072 | 0 | 3 | 2008-04-26 11:05:15 | 2008-04-26 15:51:17 | NULL | utf8_general_ci | NULL | | | | events | MyISAM | 10 | Dynamic | 1 | 68 | 68 | 281474976710655 | 5120 | 0 | 2 | 2008-04-26 11:05:15 | 2008-04-26 15:03:29 | NULL | utf8_general_ci | NULL | | | | index | MyISAM | 10 | Dynamic | 60 | 47 | 2864 | 281474976710655 | 3072 | 0 | 61 | 2008-04-26 11:09:57 | 2008-04-26 15:56:55 | NULL | utf8_general_ci | NULL | | | | notes | MyISAM | 10 | Dynamic | 5 | 182 | 912 | 281474976710655 | 3072 | 0 | 6 | 2008-04-26 15:42:34 | 2008-04-26 15:56:55 | NULL | utf8_general_ci | NULL | | | | producers | MyISAM | 10 | Dynamic | 3 | 28 | 84 | 281474976710655 | 3072 | 0 | 4 | 2008-04-26 11:05:15 | 2008-04-26 15:51:59 | NULL | utf8_general_ci | NULL | | | | regions | MyISAM | 10 | Dynamic | 2 | 22 | 44 | 281474976710655 | 3072 | 0 | 3 | 2008-04-26 11:05:15 | 2008-04-26 15:51:38 | NULL | utf8_general_ci | NULL | | | | retailers | MyISAM | 10 | Dynamic | 3 | 20 | 60 | 281474976710655 | 6144 | 0 | 4 | 2008-04-26 11:05:15 | 2008-04-26 15:52:44 | NULL | utf8_general_ci | NULL | | | | semaphore | MyISAM | 10 | Dynamic | 0 | 0 | 20 | 281474976710655 | 2048 | 20 | NULL | 2008-04-26 11:36:33 | 2008-04-26 15:56:55 | 2008-04-26 15:23:47 | utf8_general_ci | NULL | | | | styles | MyISAM | 10 | Dynamic | 2 | 22 | 44 | 281474976710655 | 3072 | 0 | 3 | 2008-04-26 11:05:15 | 2008-04-26 15:52:36 | NULL | utf8_general_ci | NULL | | | | subregions | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2008-04-26 11:05:15 | 2008-04-26 11:05:14 | NULL | utf8_general_ci | NULL | | | | winenames | MyISAM | 10 | Dynamic | 4 | 24 | 96 | 281474976710655 | 3072 | 0 | 5 | 2008-04-26 11:05:15 | 2008-04-26 15:52:19 | NULL | utf8_general_ci | NULL | | | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ 14 rows in set (0.00 sec)This is a lot of information. We can narrow it down to a single table or a few tables by using a LIKE clause in the query. Something lile this:
mysql> SHOW TABLE STATUS LIKE 'notes'; +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | notes | MyISAM | 10 | Dynamic | 5 | 182 | 912 | 281474976710655 | 3072 | 0 | 6 | 2008-04-26 15:42:34 | 2008-04-26 15:56:55 | NULL | utf8_general_ci | NULL | | | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec) mysql> SHOW TABLE STATUS LIKE '%es'; +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | archives | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2008-04-28 08:57:20 | 2008-04-28 08:57:20 | NULL | utf8_general_ci | NULL | | | | closures | MyISAM | 10 | Dynamic | 3 | 22 | 68 | 281474976710655 | 3072 | 0 | 4 | 2008-04-26 11:05:15 | 2008-04-26 15:53:07 | NULL | utf8_general_ci | NULL | | | | countries | MyISAM | 10 | Dynamic | 2 | 20 | 40 | 281474976710655 | 3072 | 0 | 3 | 2008-04-26 11:05:15 | 2008-04-26 15:51:17 | NULL | utf8_general_ci | NULL | | | | notes | MyISAM | 10 | Dynamic | 5 | 182 | 912 | 281474976710655 | 3072 | 0 | 6 | 2008-04-26 15:42:34 | 2008-04-26 15:56:55 | NULL | utf8_general_ci | NULL | | | | styles | MyISAM | 10 | Dynamic | 2 | 22 | 44 | 281474976710655 | 3072 | 0 | 3 | 2008-04-26 11:05:15 | 2008-04-26 15:52:36 | NULL | utf8_general_ci | NULL | | | | winenames | MyISAM | 10 | Dynamic | 4 | 24 | 96 | 281474976710655 | 3072 | 0 | 5 | 2008-04-26 11:05:15 | 2008-04-26 15:52:19 | NULL | utf8_general_ci | NULL | | | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ 6 rows in set (0.00 sec)The 11th column in the above printouts is headed "Auto_increment". The value in this column is the value that will be assigned to the next auto_incremented index. If the table doesn't have an auto_incremented field then this column contains the value "NULL". So, this simple PHP code will retrieve that value programatically. It will find the next auto_increment value for the table 'notes' and echo it followed by a carriage return:
$res = mysql_query("SHOW TABLE STATUS LIKE 'notes'"); $row = mysql_fetch_assoc($res); mysql_free_result($res); echo $row['Auto_increment'] . "\n";Setting the auto_increment value on a table is dead easy. If I wanted to change the auto_increment value on the 'notes' table above to 20, the query would be:
mysql> ALTER TABLE `notes` AUTO_INCREMENT=20;If you try and alter the auto_increment on a table that doesn't have one in the first place, the query will appear to have succeeded but nothing wil actually have been changed.
Last update: 28-APR-2008
This page has been served 21021
times since 28-APR-2008