schema_auto_increment_columns Sys Schema View
MariaDB starting with 10.6
The Sys Schema 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:
| Column | Description |
|---|---|
| table_schema | Schema name containing the table with the auto_increment attribute. |
| table_name | Table containing the auto_increment attribute. |
| column_name | Name of the column containing the auto_increment attribute. |
| data_type | Data type of the auto_increment column, for example tinyint. |
| column_type | Data type of the auto_increment column, plus additional information for example tinyint(3) unsigned. |
| is_signed | 1 if the column is signed,0 if not. |
| is_unsigned | 1 if the column is unsigned,0 if it is. |
| max_value | Maximum possible value for the column, for example 255 for an unsigned tinyint. |
| auto_increment | Current auto_increment value for the column. |
| auto_increment_ratio | Ratio 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
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.
