查看MySQL中自增ID的使用情况

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

SELECT     t.TABLE_SCHEMA AS `schema`,     t.TABLE_NAME AS `table`,     t.AUTO_INCREMENT AS `auto_increment`,     c.DATA_TYPE AS `pk_type`,     (         t.AUTO_INCREMENT /         (CASE DATA_TYPE             WHEN 'tinyint'                 THEN IF(COLUMN_TYPE LIKE '%unsigned',                     255,                     127                 )             WHEN 'smallint'                 THEN IF(COLUMN_TYPE LIKE '%unsigned',                     65535,                     32767                 )             WHEN 'mediumint'                 THEN IF(COLUMN_TYPE LIKE '%unsigned',                     16777215,                     8388607                 )             WHEN 'int'                 THEN IF(COLUMN_TYPE LIKE '%unsigned',                     4294967295,                     2147483647                 )             WHEN 'bigint'                 THEN IF(COLUMN_TYPE LIKE '%unsigned',                     18446744073709551615,                     9223372036854775807                 )         END / 100)     ) AS `max_value`     FROM information_schema.TABLES t     INNER JOIN information_schema.COLUMNS c         ON t.TABLE_SCHEMA = c.TABLE_SCHEMA         AND t.TABLE_NAME = c.TABLE_NAME     WHERE         t.AUTO_INCREMENT IS NOT NULL         AND c.COLUMN_KEY = 'PRI'         AND c.DATA_TYPE LIKE '%int' ;