MySQL查看所有表自增主键AUTO_INCREMENT使用情况（与最大值比率）

SQL语句

SELECT
	t.TABLE_SCHEMA AS '库名',
	t.TABLE_NAME AS '表名',
	c.COLUMN_TYPE AS '主键字段类型',
	t.AUTO_INCREMENT AS '自增值',
	CONCAT(
		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 ratio 
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' 
ORDER BY
	ratio DESC

结果示例