The metadatabase of AnalyticDB for MySQL is the NIFORMATION_SCHEMA database and is compatible with MySQL metadatabase. To query the metadatabase, you can execute SQL statements after you establish a Java Database Connectivity (JDBC) connection to an AnalyticDB for MySQL cluster. For example, to query all tables in the test database (schema), execute the following SQL statement:
select * from TABLES where table_schema='test'SCHEMATA
The INFORMATION_SCHEMA.SCHEMATA table provides information about databases.
Field | Type | Nullable | Default | Description |
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
The INFORMATION_SCHEMA.TABLES table provides information about database tables. The data includes the metadata of the tables and corresponding data in specific tables such as partition information.
Field | Type | Nullable | Default | Description |
TABLE_CATALOG | varchar(512) | No | def | The value is fixed as 'def'. |
TABLE_SCHEMA | varchar(64) | No | None | The name of the schema. |
TABLE_NAME | varchar(64) | No | None | The name of the table. |
TABLE_TYPE | varchar(64) | Yes | NULL | The type of the table. Valid values: PARTITION_TABLE that indicates a partition table and DIMENSION_TABLE that indicates a replicated table. |
ENGINE | varchar(64) | Yes | NULL | The engine type. |
VERSION | bigint(21) | Yes | NULL | The table ID. |
ROW_FORMAT | varchar(20) | Yes | NULL | The value is fixed as 'Compact'. |
TABLE_ROWS | bigint(21) | Yes | NULL | The number of records. |
AVG_ROW_LENGTH | bigint(21) | Yes | NULL | This field is not used and the default value is NULL. |
DATA_LENGTH | bigint(21) | Yes | NULL | The data size. |
MAX_DATA_LENGTH | bigint(21) | Yes | NULL | This field is not used and the default value is NULL. |
INDEX_LENGTH | bigint(21) | Yes | NULL | The index size. |
DATA_FREE | bigint(21) | Yes | NULL | This field is not used and the default value is NULL. |
AUTO_INCREMENT | bigint(21) | Yes | NULL | The auto-increment value of the table. This field is not used and the default value is NULL. |
CREATE_TIME | datetime | Yes | NULL | The creation time. |
UPDATE_TIME | datetime | Yes | NULL | The update time. |
CHECK_TIME | datetime | Yes | NULL | This field is not used and the default value is NULL. |
TABLE_COLLATION | varchar(32) | Yes | NULL | The value is fixed as 'utf8_bin'. |
CHECKSUM | bigint(21) | Yes | NULL | This field is not used and the default value is NULL. |
CREATE_OPTIONS | varchar(255) | Yes | NULL | This field is not used and the default value is NULL. |
TABLE_COMMENT | varchar(255) | Yes | NULL | The table comments. |
If you do not know the name of a table but want to know different types of tables in a specific schema, you can query the INFORMATION_SCHEMA.TABLES table. Syntax:
SELECT DISTINCT TABLE_CATALOG, TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'xxx';If you want to know the views in a specific schema, you can query the INFORMATION_SCHEMA.TABLES table. Syntax:
SELECT TABLE_CATALOG, TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'xxx' AND TABLE_TYPE = 'VIEW';
COLUMNS
The INFORMATION_SCHEMA.COLUMNS table stores all field details in tables.
Field | Type | Nullable | Default | Description |
TABLE_CATALOG | varchar(8) | Yes | NULL | The name of the catalog to which the table containing the column belongs. |
TABLE_SCHEMA | varchar(64) | No | NULL | The name of the schema (database) to which the table that contains the column belongs. |
TABLE_NAME | varchar(64) | No | NULL | The name of the table that contains the column. |
COLUMN_NAME | varchar(64) | No | NULL | The name of the column. |
ORDINAL_POSITION | bigint(21) | Yes | NULL | The position of the column in the table. |
COLUMN_DEFAULT | varchar(255) | Yes | NULL | The default value of the column. |
IS_NULLABLE | tinyint(1) | Yes | 1 | Indicates whether the column can be empty. |
DATA_TYPE | bigint(21) | Yes | NULL | The data type of the column. |
CHARACTER_MAXIMUM_LENGTH | bigint(21) | Yes | NULL | The maximum length in characters if the column is a string column. |
CHARACTER_OCTET_LENGTH | bigint(21) | Yes | NULL | The maximum length in bytes if the column is a string column. |
NUMERIC_PRECISION | int(11) | Yes | NULL | The numeric precision if the column is a numeric column. |
NUMERIC_SCALE | bigint(21) | Yes | NULL | The numeric scale if the column is a numeric column. |
DATETIME_PRECISION | bigint(21) | Yes | NULL | The time precision if the column is a temporal column. |
CHARACTER_SET_NAME | varchar(32) | Yes | NULL | The name of the character set if the column is a character string column. |
COLLATION_NAME | varchar(32) | Yes | NULL | The name of the collation if the column is a character string column. |
COLUMN_TYPE | varchar(64) | Yes | NULL | The data type of the column. |
COLUMN_KEY | varchar(3) | No | NULL | The index type. |
EXTRA | varchar(30) | No | NULL | The additional information about the column. The value can be on update CURRENT_TIMESTAMP in specific cases. |
PRIVILEGES | varchar(80) | No | NULL | The privileges you have on the column. The values are fixed as select, insert, update, and references. |
COLUMN_COMMENT | varchar(1024) | Yes | 1024 | The comments on 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_NAME.
FAQ
Why isn't the UPDATE_TIME field in the INFORMATION_SCHEMA.TABLES table updated immediately after I modify a table?
AnalyticDB for MySQL updates the UPDATE_TIME field only after you execute the ALTER TABLE statement to modify the schema of a table.