Skip to content

Instantly share code, notes, and snippets.

@aantonw

aantonw/auto_increment_capacity.sql

Forked from gplessis/auto_increment_capacity.sql
Last active August 29, 2015 14:26
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save aantonw/9f0a0c7c07cff2e0c19a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save aantonw/9f0a0c7c07cff2e0c19a to your computer and use it in GitHub Desktop.
Download ZIP
Detect AUTO_INCREMENT capacity in MySQL
Raw
auto_increment_capacity.sql
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