Metadatabase data dictionary
The metadatabase of AnalyticDB for MySQL is INFORMATION_SCHEMA, which is compatible with the MySQL metadatabase. To query it, execute SQL statements over a Java Database Connectivity (JDBC) connection to your cluster.
For example, to list all tables in the test database:
SELECT * FROM TABLES WHERE table_schema = 'test';SCHEMATA
INFORMATION_SCHEMA.SCHEMATA provides information about databases in your cluster. Use it to list all databases or check their default character set and collation settings.
| 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. |
Example queries
List all databases in your cluster:
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.SCHEMATA;TABLES
INFORMATION_SCHEMA.TABLES provides information about database tables, including metadata and partition details. Use it when you don't know a table name, need to list tables of a specific type, or want to check table sizes and record counts.
| Field | Type | Nullable | Default | Description |
|---|---|---|---|---|
| TABLE_CATALOG | varchar(512) | No | def | 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 (partition table), DIMENSION_TABLE (replicated table). |
| ENGINE | varchar(64) | Yes | NULL | The engine type. |
| VERSION | bigint(21) | Yes | NULL | The table ID. |
| ROW_FORMAT | varchar(20) | Yes | NULL | Fixed as Compact. |
| TABLE_ROWS | bigint(21) | Yes | NULL | The number of records. |
| AVG_ROW_LENGTH | bigint(21) | Yes | NULL | Not applicable for AnalyticDB for MySQL. |
| DATA_LENGTH | bigint(21) | Yes | NULL | The data size. |
| MAX_DATA_LENGTH | bigint(21) | Yes | NULL | Not applicable for AnalyticDB for MySQL. |
| INDEX_LENGTH | bigint(21) | Yes | NULL | The index size. |
| DATA_FREE | bigint(21) | Yes | NULL | Not applicable for AnalyticDB for MySQL. |
| AUTO_INCREMENT | bigint(21) | Yes | NULL | Not applicable for AnalyticDB for MySQL. |
| CREATE_TIME | datetime | Yes | NULL | The time the table was created. |
| UPDATE_TIME | datetime | Yes | NULL | The time the table schema was last modified. |
| CHECK_TIME | datetime | Yes | NULL | Not applicable for AnalyticDB for MySQL. |
| TABLE_COLLATION | varchar(32) | Yes | NULL | Fixed as utf8_bin. |
| CHECKSUM | bigint(21) | Yes | NULL | Not applicable for AnalyticDB for MySQL. |
| CREATE_OPTIONS | varchar(255) | Yes | NULL | Not applicable for AnalyticDB for MySQL. |
| TABLE_COMMENT | varchar(255) | Yes | NULL | The table comments. |
Usage notes
UPDATE_TIME reflects the last time the table schema was modified. AnalyticDB for MySQL updates this field only after you execute the ALTER TABLE statement to modify the schema of a table.
Example queries
List all tables in a specific schema:
SELECT DISTINCT TABLE_CATALOG, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_SCHEMA = '<schema-name>';List all views in a specific schema:
SELECT TABLE_CATALOG, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_SCHEMA = '<schema-name>' AND TABLE_TYPE = 'VIEW';COLUMNS
INFORMATION_SCHEMA.COLUMNS stores details about every column in every table. Use it to inspect column definitions for multiple tables at once — faster than running DESCRIBE TABLE for each table individually.
| Field | Type | Nullable | Default | Description |
|---|---|---|---|---|
| TABLE_CATALOG | varchar(8) | Yes | NULL | The catalog to which the table belongs. |
| TABLE_SCHEMA | varchar(64) | No | NULL | The schema (database) to which the table belongs. |
| TABLE_NAME | varchar(64) | No | NULL | The name of the table. |
| 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 | Whether the column accepts NULL values. |
| DATA_TYPE | bigint(21) | Yes | NULL | The data type of the column. |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) | Yes | NULL | The maximum length in characters. Applies to string columns only. |
| CHARACTER_OCTET_LENGTH | bigint(21) | Yes | NULL | The maximum length in bytes. Applies to string columns only. |
| NUMERIC_PRECISION | int(11) | Yes | NULL | The numeric precision. Applies to numeric columns only. |
| NUMERIC_SCALE | bigint(21) | Yes | NULL | The numeric scale. Applies to numeric columns only. |
| DATETIME_PRECISION | bigint(21) | Yes | NULL | The time precision. Applies to temporal columns only. |
| CHARACTER_SET_NAME | varchar(32) | Yes | NULL | The character set. Applies to character string columns only. |
| COLLATION_NAME | varchar(32) | Yes | NULL | The collation. Applies to character string columns only. |
| COLUMN_TYPE | varchar(64) | Yes | NULL | The full data type definition of the column. |
| COLUMN_KEY | varchar(3) | No | NULL | The index type. |
| EXTRA | varchar(30) | No | NULL | Additional column information. For example, on update CURRENT_TIMESTAMP. |
| PRIVILEGES | varchar(80) | No | NULL | Fixed as: select, insert, update, references. |
| COLUMN_COMMENT | varchar(1024) | Yes | 1024 | The column comments. |
Example queries
List all columns in a specific table:
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.columns
WHERE TABLE_SCHEMA = '<schema-name>' AND TABLE_NAME = '<table-name>';FAQ
Why doesn't UPDATE_TIME update after I modify a table?
UPDATE_TIME only reflects schema changes made by ALTER TABLE. It does not update after DML operations such as row insertions, updates, or deletions.