Skip to content

Instantly share code, notes, and snippets.

@gplessis
Created June 24, 2014 20:17
Show Gist options
  • Save gplessis/fc2d021feee9757bc2d7 to your computer and use it in GitHub Desktop.
Save gplessis/fc2d021feee9757bc2d7 to your computer and use it in GitHub Desktop.
Detect AUTO_INCREMENT capacity in MySQL
SELECT table_schema,
table_name,
data_type,
( CASE data_type
WHEN 'tinyint' THEN 255
WHEN 'smallint' THEN 65535
WHEN 'mediumint' THEN 16777215
WHEN 'int' THEN 4294967295
WHEN 'bigint' THEN 18446744073709551615
end >> IF(Locate('unsigned', column_type) > 0, 0, 1) ) AS MAX_VALUE,
AUTO_INCREMENT,
AUTO_INCREMENT*100/( CASE data_type
WHEN 'tinyint' THEN 255
WHEN 'smallint' THEN 65535
WHEN 'mediumint' THEN 16777215
WHEN 'int' THEN 4294967295
WHEN 'bigint' THEN 18446744073709551615
end >> IF(Locate('unsigned', column_type) > 0, 0, 1) ) AS percent_capacity
FROM
information_schema.columns
INNER JOIN
information_schema.tables USING (table_schema, table_name)
WHERE
table_schema NOT IN ( 'MYSQL', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA')
AND extra = 'auto_increment'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment