schema_auto_increment_columns

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.6

The Sys Schema view was introduced in MariaDB 10.6

Description

Information about AUTO_INCREMENT columns, sorted by descending usage ratio and maximum column value. Does not include columns in the mysql, sys, information_schema and performance_schema schemas.

Contains the following columns:

ColumnDescription
table_schemaSchema name containing the table with the auto_increment attribute.
table_nameTable containing the auto_increment attribute.
column_nameName of the column containing the auto_increment attribute.
data_typeData type of the auto_increment column, for example tinyint.
column_typeData type of the auto_increment column, plus additional information for example tinyint(3) unsigned.
is_signed1 if the column is signed,0 if not.
is_unsigned1 if the column is unsigned,0 if it is.
max_valueMaximum possible value for the column, for example 255 for an unsigned tinyint.
auto_incrementCurrent auto_increment value for the column.
auto_increment_ratioRatio of used to maximum value for the auto_increment column.

Example

CREATE OR REPLACE TABLE animals (
    id TINYINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

 INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('fox'),('whale'),('ostrich');

SELECT * FROM sys.schema_auto_increment_columns\G
*************************** 1. row ***************************
        table_schema: test
          table_name: animals
         column_name: id
           data_type: tinyint
         column_type: tinyint(4)
           is_signed: 1
         is_unsigned: 0
           max_value: 127
      auto_increment: 7
auto_increment_ratio: 0.0551

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.