Last auto_increment value
We use some sequences for generating unique identifiers in tables. It is used different techniques for generating UIDs. In Oracle or PgSQL we create a sequence and use next value of it every time when we insert into the particular table. In mysql there is an attribute for the column to be a autoincrement field.
Sometimes we need to take its last value. The first idea will be using LAST_INSERT_ID() function in MySQL. But it will not work for your every scenario. It is useful when you have just inserted a row.
What about to see AI value for any table in any schema when you want? Googling did not give an adequate result for me. So I used different approach. As you know mysql keeps all information about db in schema named information_schema .
You can fetch any data related to table structures using informaton_schema. The table called “TABLES” keeps list of tables and attributes for all tables in database.
mysql> show columns from tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ 21 rows in set (0.00 sec)
The column AUTO_INCREMENT is what we need. The following query will be useful for our purpose:
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA='db_name' AND TABLE_NAME='table_name'
To make it more user-friendly I have created a function that returns that value for any table:
CREATE FUNCTION `current_ai_value`(p_schema_name VARCHAR(255), p_table_name VARCHAR(255)) RETURNS int(11) BEGIN DECLARE current_value INT; select AUTO_INCREMENT INTO current_value FROM information_schema.TABLES WHERE TABLE_NAME=p_table_name and table_schema=p_schema_name; RETURN current_value; END
Source available at https://gist.github.com/adilek/77847022c68762e49b7c