auto_increment_columns
NAME
auto_increment_columns: List AUTO_INCREMENT columns and their capacityTYPE
ViewDESCRIPTION
auto_increment_columns presents current capacity and limits for AUTO_INCREMENT columns.
For each AUTO_INCREMENT column, it observes its column type and its signed/unsigned status, and calculates the maximum possible value expressed by this column. It cross references this with table's current AUTO_INCREMENT value, to present with current usage or capacity.
This view answers the questions: "Am I running out of AUTO_INCREMENT values?", "Should I modify to BIGINT?"
STRUCTURE
mysql> DESC common_schema.auto_increment_columns; +----------------------+------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+------------------------+------+-----+---------+-------+ | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | DATA_TYPE | varchar(64) | NO | | | | | COLUMN_TYPE | longtext | NO | | NULL | | | is_signed | int(1) | NO | | 0 | | | is_unsigned | int(1) | NO | | 0 | | | max_value | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | auto_increment_ratio | decimal(24,4) unsigned | YES | | NULL | | +----------------------+------------------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- TABLE_SCHEMA: schema of table with AUTO_INCREMENT columns
- TABLE_NAME: name of table with AUTO_INCREMENT columns
- COLUMN_NAME: AUTO_INCREMENT column name
- DATA_TYPE: type of column: this is always an integer type: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT.
- COLUMN_TYPE: full description of column type
- is_signed: 1 if type is SIGNED, 0 if UNSIGNED
- is_unsigned: 1 if type is UNSIGNED, 0 if SIGNED. This is just the opposite of is_signed and is provided for convenience
- max_value: maximum value which can be expressed by this column
- AUTO_INCREMENT: current AUTO_INCREMENT value for table
- auto_increment_ratio: ratio between max_value and table's AUTO_INCREMENT. Ranges [0..1]. Expresses capacity
Upper case columns are directly derived from underlying INFORMATION_SCHEMA tables, whereas lower case columns are computed.
EXAMPLES
Show AUTO_INCREMENT capacity for 'sakila' database:
mysql> SELECT * FROM common_schema.auto_increment_columns WHERE TABLE_SCHEMA='sakila'; +--------------+------------+--------------+-----------+-----------------------+-----------+-------------+------------+----------------+----------------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE | COLUMN_TYPE | is_signed | is_unsigned | max_value | AUTO_INCREMENT | auto_increment_ratio | +--------------+------------+--------------+-----------+-----------------------+-----------+-------------+------------+----------------+----------------------+ | sakila | actor | actor_id | smallint | smallint(5) unsigned | 0 | 1 | 65535 | 201 | 0.0031 | | sakila | address | address_id | smallint | smallint(5) unsigned | 0 | 1 | 65535 | 606 | 0.0092 | | sakila | category | category_id | tinyint | tinyint(3) unsigned | 0 | 1 | 255 | 17 | 0.0667 | | sakila | city | city_id | smallint | smallint(5) unsigned | 0 | 1 | 65535 | 601 | 0.0092 | | sakila | country | country_id | smallint | smallint(5) unsigned | 0 | 1 | 65535 | 110 | 0.0017 | | sakila | customer | customer_id | smallint | smallint(5) unsigned | 0 | 1 | 65535 | 600 | 0.0092 | | sakila | film | film_id | smallint | smallint(5) unsigned | 0 | 1 | 65535 | 1001 | 0.0153 | | sakila | inventory | inventory_id | mediumint | mediumint(8) unsigned | 0 | 1 | 16777215 | 4582 | 0.0003 | | sakila | language | language_id | tinyint | tinyint(3) unsigned | 0 | 1 | 255 | 7 | 0.0275 | | sakila | payment | payment_id | smallint | smallint(5) unsigned | 0 | 1 | 65535 | 16050 | 0.2449 | | sakila | rental | rental_id | int | int(11) | 1 | 0 | 2147483647 | 16050 | 0.0000 | | sakila | staff | staff_id | tinyint | tinyint(3) unsigned | 0 | 1 | 255 | 3 | 0.0118 | | sakila | store | store_id | tinyint | tinyint(3) unsigned | 0 | 1 | 255 | 3 | 0.0118 | +--------------+------------+--------------+-----------+-----------------------+-----------+-------------+------------+----------------+----------------------+