The Ultimate Guide to High Availability with MariaDB

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.
column_type
is_signed
is_unsigned
max_value
auto_increment
auto_increment_ratio

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.