The metadatabase of AnalyticDB for MySQL is a NIFORMATION_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_CATALOG, 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_CATALOG, 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_NAME.