Created
July 15, 2022 04:48
-
-
Save srimaln91/25b10375e532cd6180bdfa11721efd1c to your computer and use it in GitHub Desktop.
Find out the usage of MySQL auto increment fields
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
TABLE_SCHEMA, | |
TABLE_NAME, | |
COLUMN_NAME, | |
DATA_TYPE, | |
COLUMN_TYPE, | |
IF( | |
LOCATE('unsigned', COLUMN_TYPE) > 0, | |
1, | |
0 | |
) AS IS_UNSIGNED, | |
( | |
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 / ( | |
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 AUTO_INCREMENT_RATIO | |
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
Copied from : https://code.openark.org/blog/mysql/checking-for-auto_increment-capacity-with-single-query