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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
-- Copyright (c) 2015, 2025, Oracle and/or its affiliates.
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; version 2 of the License.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
--
-- View: schema_auto_increment_columns
--
-- Present current auto_increment usage/capacity in all tables.
--
-- mysql> select * from schema_auto_increment_columns limit 5;
-- +-------------------+-------------------+-------------+-----------+-------------+-----------+-------------+---------------------+----------------+----------------------+
-- | table_schema | table_name | column_name | data_type | column_type | is_signed | is_unsigned | max_value | auto_increment | auto_increment_ratio |
-- +-------------------+-------------------+-------------+-----------+-------------+-----------+-------------+---------------------+----------------+----------------------+
-- | test | t1 | i | tinyint | tinyint | 1 | 0 | 127 | 34 | 0.2677 |
-- | mem__advisor_text | template_meta | hib_id | int | int | 1 | 0 | 2147483647 | 516 | 0.0000 |
-- | mem__advisors | advisor_schedules | schedule_id | int | int | 1 | 0 | 2147483647 | 249 | 0.0000 |
-- | mem__advisors | app_identity_path | hib_id | int | int | 1 | 0 | 2147483647 | 251 | 0.0000 |
-- | mem__bean_config | plists | id | bigint | bigint | 1 | 0 | 9223372036854775807 | 1 | 0.0000 |
-- +-------------------+-------------------+-------------+-----------+-------------+-----------+-------------+---------------------+----------------+----------------------+
--
CREATE OR REPLACE
ALGORITHM = MERGE
DEFINER = 'mysql.sys'@'localhost'
SQL SECURITY INVOKER
VIEW schema_auto_increment_columns (
table_schema,
table_name,
column_name,
data_type,
column_type,
is_signed,
is_unsigned,
max_value,
auto_increment,
auto_increment_ratio
) AS
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
COLUMN_TYPE,
(LOCATE('unsigned', COLUMN_TYPE) = 0) AS is_signed,
(LOCATE('unsigned', COLUMN_TYPE) > 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', 'sys', 'INFORMATION_SCHEMA', 'performance_schema')
AND TABLE_TYPE='BASE TABLE'
AND EXTRA='auto_increment'
ORDER BY auto_increment_ratio DESC, max_value;
|
