schema_auto_increment_columns

• 查询排除系统字典库(mysql，sys，INFORMATION_SCHEMA，performance_schema)之外所有库中基表具有自增属性的自增列相关属性及其表相关的信息，默认按照自增值使用率和自增列类型最大值进行降序排序。数据来源：INFORMATION_SCHEMA的COLUMNS、TABLES

  • 此视图在MySQL 5.7.9中新增

• schema_auto_increment_columns视图字段含义如下：

  • TABLE_SCHEMA：包含自增值的表的schema名称
  • TABLE_NAME：包含AUTO_INCREMENT值的表名
  • column_name：AUTO_INCREMENT值的列名称
  • data_type：自增列的数据类型
  • COLUMN_TYPE：自增列的列类型，即在数据类型基础上加上一些其他信息。例如，对于列类型为bigint(20) unsigned的列类型，数据类型只是指的bigint
  • is_signed：列类型是否是有符号的
  • is_unsigned：列类型是否是无符号的
  • MAX_VALUE：自增列的最大自增值
  • auto_increment：自增列的当前AUTO_INCREMENT值
  • auto_increment_ratio：自增列当前使用的自增值与自增列最大自增值的比例，表示当前自增列的使用率

• 视图定义语句

CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'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;

• 视图查询信息示例

admin@localhost : sys 11:11:58> 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 |
+--------------+------------+-------------+-----------+------------------+-----------+-------------+------------+----------------+----------------------+
| sbtest      | sbtest1    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10713891 |              0.0025 |
| sbtest      | sbtest2    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10710865 |              0.0025 |
| sbtest      | sbtest3    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10714919 |              0.0025 |
| sbtest      | sbtest4    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10714039 |              0.0025 |
| sbtest      | sbtest5    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10713075 |              0.0025 |
+--------------+------------+-------------+-----------+------------------+-----------+-------------+------------+----------------+----------------------+
5 rows in set (1.50 sec)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-schema-auto-increment-columns.html

---

上一篇： ps_check_lost_instrumentation视图 |

下一篇： schema_index_statistics,x$schema_index_statistics视图