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.