The metadatabase of AnalyticDB MySQL is a information_schema database and is compatible with MySQL metadatabase. To query the metadatabase, you can use SQL statements in the JDBC connection. Query all tables in the test database. Example:

select * from TABLES where table_schema='test'

SCHEMATA

This table provides the information about databases.

FIELD TYPE ALLOW_NULL DEFAULT COMMENT
CATALOG_NAME varchar(16) YES NULL The name of the catalog.
SCHEMA_NAME varchar(64) NO NULL The name of the schema.
DEFAULT_CHARACTER_SET_NAME varchar(64) YES UTF-8 The default character set.
DEFAULT_COLLATION_NAME varchar(64) YES OFF The default collation.
SQL_PATH varchar(255) YES NULL The SQL path.

TABLES

This table provides the information about database tables. The data includes the metadata of the table and corresponding data in some tables such as partition information.

FIELD TYPE ALLOW_NULL DEFAULT COMMENT
TABLE_CATALOG varchar(512) NO NULL The fixed value is 'def'.
TABLE_SCHEMA varchar(64) NO NULL The name of the schema.
TABLE_NAME varchar(64) NO NULL The name of the table.
TABLE_TYPE varchar(64) YES NULL Tags: PARTITION_TABLE and DIMENSION_TABLE.
ENGINE varchar(64) YES NULL The engine type.
VERSION bigint(21) YES NULL tableId
ROW_FORMAT varchar(20) YES NULL The fixed value is 'Compact'.
TABLE_ROWS bigint(21) YES NULL The number of records.
AVG_ROW_LENGTH bigint(21) YES NULL The default value is null that is not used.
DATA_LENGTH bigint(21) YES NULL The data volume.
MAX_DATA_LENGTH bigint(21) YES NULL The default value is null that is not used.
INDEX_LENGTH bigint(21) YES NULL The index size.
DATA_FREE bigint(21) YES NULL The default value is null that is not used.
AUTO_INCREMENT bigint(21) YES NULL The auto-increment value of the table. The default value is null that is not used.
CREATE_TIME datetime YES NULL The creation time.
UPDATE_TIME datetime YES NULL The update time.
CHECK_TIME datetime YES NULL The default value is null that is not used.
TABLE_COLLATION varchar(32) YES NULL The fixed value is 'utf8_bin'.
CHECKSUM bigint(21) YES NULL The default value is null that is not used.
CREATE_OPTIONS varchar(255) YES NULL The default value is null that is not used.
TABLE_COMMENT varchar(255) YES NULL The table comments.
  • If you do not know the table name but want to know the number of different types of tables in a schema, you can query this table. Example:
    SELECT DISTINCT TABLE_GROUP, TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'xxx';
  • If you want to know which of the following views are in a SCHEMA, query this table. Example:
    SELECT TABLE_GROUP, TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'xxx' AND TABLE_TYPE = 'VIEW';

COLUMNS

The table stores all field details in the table.

FIELD TYPE ALLOW_NULL DEFAULT COMMENT
TABLE_CATALOG varchar(8) YES NULL The name of the catalog.
TABLE_SCHEMA varchar(64) NO NULL The schema to which the table belongs.
TABLE_NAME varchar(64) NO NULL The name of the table to which the table belongs.
COLUMN_NAME varchar(64) NO NULL The name of the column.
ORDINAL_POSITION bigint(21) YES NULL The position in the table.
COLUMN_DEFAULT varchar(255) YES NULL The default column.
IS_NULLABLE tinyint(1) YES 1 Indicates whether the column can be empty.
DATA_TYPE bigint(21) YES NULL The name of the data type.
CHARACTER_MAXIMUM_LENGTH bigint(21) YES NULL The maximum length of each string.
CHARACTER_OCTET_LENGTH bigint(21) YES NULL The octal length of characters.
NUMERIC_PRECISION int(11) YES NULL The numeric precision.
NUMERIC_SCALE bigint(21) YES NULL The range of the numeric values.
DATETIME_PRECISION bigint(21) YES NULL The time precision.
CHARACTER_SET_NAME varchar(32) YES NULL The name of the character set.
COLLATION_NAME varchar(32) YES NULL The name of the collation.
COLUMN_TYPE varchar(64) YES NULL The type of the column.
COLUMN_KEY varchar(3) NO NULL The type of the index.
EXTRA varchar(30) NO NULL Indicates whether the table is updated.
PRIVILEGES varchar(80) NO NULL The fixed values are select, insert, update, and references.
COLUMN_COMMENT varchar(1024) 1024 NULL The comments of the column.

If you want to know all the columns in a table, you can query the required columns based on TABLE_SCHEMA and TABLE_TABLENAME.