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

Revisions

  1. @gplessis gplessis created this gist Jun 24, 2014.
    25 changes: 25 additions & 0 deletions auto_increment_capacity.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,25 @@
    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'