schema_auto_increment_columns

Overview

In 10.6 ES, 10.6 CS:

All the existing AUTO_INCREMENT columns.

In 10.5 ES, 10.5 CS, 10.4 ES, 10.4 CS, 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS:

Not present

See also: Sys Database Tables for MariaDB Enterprise Server 10.6 and in 10.6 CS

DETAILS

SCHEMA

VIEW `schema_auto_increment_columns` AS SELECT
  `information_schema`.`COLUMNS`.`TABLE_SCHEMA` AS `table_schema`,
  `information_schema`.`COLUMNS`.`TABLE_NAME` AS `table_name`,
  `information_schema`.`COLUMNS`.`COLUMN_NAME` AS `column_name`,
  `information_schema`.`COLUMNS`.`DATA_TYPE` AS `data_type`,
  `information_schema`.`COLUMNS`.`COLUMN_TYPE` AS `column_type`,
  locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) = 0 AS `is_signed`,
  locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0 AS `is_unsigned`,
  case `information_schema`.`COLUMNS`.`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',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0,0,1) AS `max_value`,
  `information_schema`.`TABLES`.`AUTO_INCREMENT` AS `auto_increment`,
  `information_schema`.`TABLES`.`AUTO_INCREMENT` / (case `information_schema`.`COLUMNS`.`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',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0,0,1)) AS `auto_increment_ratio`
FROM (`INFORMATION_SCHEMA`.`COLUMNS` join `INFORMATION_SCHEMA`.`TABLES` on(`information_schema`.`COLUMNS`.`TABLE_SCHEMA` = `information_schema`.`TABLES`.`TABLE_SCHEMA` and `information_schema`.`COLUMNS`.`TABLE_NAME` = `information_schema`.`TABLES`.`TABLE_NAME`)) where `information_schema`.`COLUMNS`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','performance_schema') and `information_schema`.`TABLES`.`TABLE_TYPE` = 'BASE TABLE' and `information_schema`.`COLUMNS`.`EXTRA` = 'auto_increment' order by `information_schema`.`TABLES`.`AUTO_INCREMENT` / (case `information_schema`.`COLUMNS`.`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',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0,0,1)) desc,case `information_schema`.`COLUMNS`.`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',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0,0,1)

CHANGE HISTORY

Release Series

History

10.6 Enterprise

  • Added in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Added in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Not present.

10.5 Community

  • Not present.

10.4 Enterprise

  • Not present.

10.4 Community

  • Not present.

10.3 Enterprise

  • Not present.

10.3 Community

  • Not present.

10.2 Enterprise

  • Not present.

10.2 Community

  • Not present.

EXTERNAL REFERENCES