Information Schema

为了和 MySQL 保持兼容,TiDB 支持很多 INFORMATION_SCHEMA 表,其中有不少表都支持相应的 SHOW 命令。查询 INFORMATION_SCHEMA 表也为表的连接操作提供了可能。

CHARACTER_SETS Table

CHARACTER_SETS 表提供字符集相关的信息。TiDB 目前仅支持部分字符集。

mysql> SELECT * FROM character_sets; +--------------------+----------------------+---------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+---------------+--------+ | utf8 | utf8_bin | UTF-8 Unicode | 3 | | utf8mb4 | utf8mb4_bin | UTF-8 Unicode | 4 | | ascii | ascii_bin | US ASCII | 1 | | latin1 | latin1_bin | Latin1 | 1 | | binary | binary | binary | 1 | +--------------------+----------------------+---------------+--------+ 5 rows in set (0.00 sec)

COLLATIONS Table

COLLATIONS 表提供了 CHARACTER_SETS 表中字符集对应的排序规则列表。TiDB 当前仅支持二进制排序规则,包含该表仅为兼容 MySQL。

mysql> SELECT * FROM collations WHERE character_set_name='utf8mb4'; +------------------------+--------------------+------+------------+-------------+---------+ | COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | +------------------------+--------------------+------+------------+-------------+---------+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 1 | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 1 | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 1 | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 1 | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 1 | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 1 | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 1 | | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 1 | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 1 | | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 1 | | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 1 | | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 1 | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 1 | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 1 | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 1 | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 1 | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 1 | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 1 | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 1 | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 1 | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 1 | | utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 1 | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 1 | | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 1 | +------------------------+--------------------+------+------------+-------------+---------+ 26 rows in set (0.00 sec)

COLLATION_CHARACTER_SET_APPLICABILITY Table

COLLATION_CHARACTER_SET_APPLICABILITY 表将排序规则映射至适用的字符集名称。和 COLLATIONS 表一样,包含此表也是为了兼容 MySQL。

mysql> SELECT * FROM collation_character_set_applicability WHERE character_set_name='utf8mb4'; +------------------------+--------------------+ | COLLATION_NAME | CHARACTER_SET_NAME | +------------------------+--------------------+ | utf8mb4_general_ci | utf8mb4 | | utf8mb4_bin | utf8mb4 | | utf8mb4_unicode_ci | utf8mb4 | | utf8mb4_icelandic_ci | utf8mb4 | | utf8mb4_latvian_ci | utf8mb4 | | utf8mb4_romanian_ci | utf8mb4 | | utf8mb4_slovenian_ci | utf8mb4 | | utf8mb4_polish_ci | utf8mb4 | | utf8mb4_estonian_ci | utf8mb4 | | utf8mb4_spanish_ci | utf8mb4 | | utf8mb4_swedish_ci | utf8mb4 | | utf8mb4_turkish_ci | utf8mb4 | | utf8mb4_czech_ci | utf8mb4 | | utf8mb4_danish_ci | utf8mb4 | | utf8mb4_lithuanian_ci | utf8mb4 | | utf8mb4_slovak_ci | utf8mb4 | | utf8mb4_spanish2_ci | utf8mb4 | | utf8mb4_roman_ci | utf8mb4 | | utf8mb4_persian_ci | utf8mb4 | | utf8mb4_esperanto_ci | utf8mb4 | | utf8mb4_hungarian_ci | utf8mb4 | | utf8mb4_sinhala_ci | utf8mb4 | | utf8mb4_german2_ci | utf8mb4 | | utf8mb4_croatian_ci | utf8mb4 | | utf8mb4_unicode_520_ci | utf8mb4 | | utf8mb4_vietnamese_ci | utf8mb4 | +------------------------+--------------------+ 26 rows in set (0.00 sec)

COLUMNS Table

COLUMNS 表提供了表的所有列的信息。

mysql> CREATE TABLE test.t1 (a int); 1 row in set (0.01 sec) mysql> SELECT * FROM information_schema.columns WHERE table_schema='test' AND TABLE_NAME='t1'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: t1 COLUMN_NAME: a ORDINAL_POSITION: 1 COLUMN_DEFAULT: NULL IS_NULLABLE: YES DATA_TYPE: int CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: 11 NUMERIC_SCALE: 0 DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: int(11) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: GENERATION_EXPRESSION: 1 row in set (0.01 sec)

对应的 SHOW 语句如下:

mysql> SHOW COLUMNS FROM t1 FROM test; +-------+---------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+------+---------+-------+ | a | int(11) | YES | | NULL | | +-------+---------+------+------+---------+-------+ 1 row in set (0.00 sec)

ENGINES Table

ENGINES 表提供了关于存储引擎的信息。从和 MySQL 兼容性上考虑,TiDB 会一直将 InnoDB 描述为唯一支持的引擎。

mysql> SELECT * FROM engines\G *************************** 1. row *************************** ENGINE: InnoDB SUPPORT: DEFAULT COMMENT: Supports transactions, row-level locking, and foreign keys TRANSACTIONS: YES XA: YES SAVEPOINTS: YES 1 row in set (0.00 sec)

KEY_COLUMN_USAGE Table

KEY_COLUMN_USAGE 表描述了列的键约束,比如主键约束。

mysql> SELECT * FROM key_column_usage WHERE table_schema='mysql' and table_name='user'\G *************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: mysql CONSTRAINT_NAME: PRIMARY TABLE_CATALOG: def TABLE_SCHEMA: mysql TABLE_NAME: user COLUMN_NAME: Host ORDINAL_POSITION: 1 POSITION_IN_UNIQUE_CONSTRAINT: NULL REFERENCED_TABLE_SCHEMA: NULL REFERENCED_TABLE_NAME: NULL REFERENCED_COLUMN_NAME: NULL *************************** 2. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: mysql CONSTRAINT_NAME: PRIMARY TABLE_CATALOG: def TABLE_SCHEMA: mysql TABLE_NAME: user COLUMN_NAME: User ORDINAL_POSITION: 2 POSITION_IN_UNIQUE_CONSTRAINT: NULL REFERENCED_TABLE_SCHEMA: NULL REFERENCED_TABLE_NAME: NULL REFERENCED_COLUMN_NAME: NULL 2 rows in set (0.00 sec)

SCHEMATA Table

SCHEMATA 表提供了关于数据库的信息。表中的数据与 SHOW DATABASES 语句的执行结果等价。

mysql> SELECT * FROM schemata; +--------------+--------------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+--------------------+----------------------------+------------------------+----------+ | def | INFORMATION_SCHEMA | utf8mb4 | utf8mb4_bin | NULL | | def | mynewdb | utf8mb4 | utf8mb4_bin | NULL | | def | mysql | utf8mb4 | utf8mb4_bin | NULL | | def | PERFORMANCE_SCHEMA | utf8mb4 | utf8mb4_bin | NULL | | def | test | utf8mb4 | utf8mb4_bin | NULL | +--------------+--------------------+----------------------------+------------------------+----------+ 5 rows in set (0.00 sec)

SESSION_VARIABLES Table

SESSION\_VARIABLES 表提供了关于 session 变量的信息。表中的数据跟 SHOW SESSION VARIABLES 语句执行结果类似。

mysql> SELECT * FROM session_variables LIMIT 10; +----------------------------------+----------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +----------------------------------+----------------------+ | max_write_lock_count | 18446744073709551615 | | server_id_bits | 32 | | net_read_timeout | 30 | | innodb_online_alter_log_max_size | 134217728 | | innodb_optimize_fulltext_only | OFF | | max_join_size | 18446744073709551615 | | innodb_read_io_threads | 4 | | session_track_gtids | OFF | | have_ssl | DISABLED | | max_binlog_cache_size | 18446744073709547520 | +----------------------------------+----------------------+ 10 rows in set (0.00 sec)

SLOW_QUERY 表

SLOW_QUERY 表中提供了慢查询相关的信息,其内容通过解析 TiDB 慢查询日志而来,列名和慢日志中的字段名是一一对应。关于如何使用该表调查和改善慢查询请参考慢查询日志文档

mysql> desc information_schema.slow_query; +---------------+---------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+------+---------+-------+ | Time | timestamp unsigned | YES | | NULL | | | Txn_start_ts | bigint(20) unsigned | YES | | NULL | | | User | varchar(64) | YES | | NULL | | | Host | varchar(64) | YES | | NULL | | | Conn_ID | bigint(20) unsigned | YES | | NULL | | | Query_time | double unsigned | YES | | NULL | | | Process_time | double unsigned | YES | | NULL | | | Wait_time | double unsigned | YES | | NULL | | | Backoff_time | double unsigned | YES | | NULL | | | Request_count | bigint(20) unsigned | YES | | NULL | | | Total_keys | bigint(20) unsigned | YES | | NULL | | | Process_keys | bigint(20) unsigned | YES | | NULL | | | DB | varchar(64) | YES | | NULL | | | Index_ids | varchar(100) | YES | | NULL | | | Is_internal | tinyint(1) unsigned | YES | | NULL | | | Digest | varchar(64) | YES | | NULL | | | Stats | varchar(512) | YES | | NULL | | | Cop_proc_avg | double unsigned | YES | | NULL | | | Cop_proc_p90 | double unsigned | YES | | NULL | | | Cop_proc_max | double unsigned | YES | | NULL | | | Cop_proc_addr | varchar(64) | YES | | NULL | | | Cop_wait_avg | double unsigned | YES | | NULL | | | Cop_wait_p90 | double unsigned | YES | | NULL | | | Cop_wait_max | double unsigned | YES | | NULL | | | Cop_wait_addr | varchar(64) | YES | | NULL | | | Mem_max | bigint(20) unsigned | YES | | NULL | | | Succ | tinyint(1) unsigned | YES | | NULL | | | Query | longblob unsigned | YES | | NULL | | +---------------+---------------------+------+------+---------+-------+

STATISTICS Table

STATISTICS 表提供了关于表索引的信息。

mysql> desc statistics; +---------------|---------------------|------|------|---------|-------+ | Field | Type | Null | Key | Default | Extra | +---------------|---------------------|------|------|---------|-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | YES | | NULL | | | TABLE_NAME | varchar(64) | YES | | NULL | | | NON_UNIQUE | varchar(1) | YES | | NULL | | | INDEX_SCHEMA | varchar(64) | YES | | NULL | | | INDEX_NAME | varchar(64) | YES | | NULL | | | SEQ_IN_INDEX | bigint(2) UNSIGNED | YES | | NULL | | | COLUMN_NAME | varchar(21) | YES | | NULL | | | COLLATION | varchar(1) | YES | | NULL | | | CARDINALITY | bigint(21) UNSIGNED | YES | | NULL | | | SUB_PART | bigint(3) UNSIGNED | YES | | NULL | | | PACKED | varchar(10) | YES | | NULL | | | NULLABLE | varchar(3) | YES | | NULL | | | INDEX_TYPE | varchar(16) | YES | | NULL | | | COMMENT | varchar(16) | YES | | NULL | | | INDEX_COMMENT | varchar(1024) | YES | | NULL | | +---------------|---------------------|------|------|---------|-------+

下列语句是等价的:

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name = 'tbl_name' AND table_schema = 'db_name' SHOW INDEX FROM tbl_name FROM db_name

TABLES Table

TABLES 表提供了数据库里面关于表的信息。

mysql> SELECT * FROM tables WHERE table_schema='mysql' AND table_name='user'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: mysql TABLE_NAME: user TABLE_TYPE: BASE TABLE ENGINE: InnoDB VERSION: 10 ROW_FORMAT: Compact TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 0 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: 0 CREATE_TIME: 2019-03-29 09:17:27 UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8mb4_bin CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: TIDB_TABLE_ID: 5 1 row in set (0.00 sec)

以下操作是等价的:

SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' [AND table_name LIKE 'wild'] SHOW TABLES FROM db_name [LIKE 'wild']

TABLE_CONSTRAINTS Table

TABLE\_CONSTRAINTS 表记录了表的约束信息。

mysql> SELECT * FROM table_constraints WHERE constraint_type='UNIQUE'\G *************************** 1. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: mysql CONSTRAINT_NAME: name TABLE_SCHEMA: mysql TABLE_NAME: help_topic CONSTRAINT_TYPE: UNIQUE *************************** 2. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: mysql CONSTRAINT_NAME: tbl TABLE_SCHEMA: mysql TABLE_NAME: stats_meta CONSTRAINT_TYPE: UNIQUE *************************** 3. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: mysql CONSTRAINT_NAME: tbl TABLE_SCHEMA: mysql TABLE_NAME: stats_histograms CONSTRAINT_TYPE: UNIQUE *************************** 4. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: mysql CONSTRAINT_NAME: tbl TABLE_SCHEMA: mysql TABLE_NAME: stats_buckets CONSTRAINT_TYPE: UNIQUE *************************** 5. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: mysql CONSTRAINT_NAME: delete_range_index TABLE_SCHEMA: mysql TABLE_NAME: gc_delete_range CONSTRAINT_TYPE: UNIQUE *************************** 6. row *************************** CONSTRAINT_CATALOG: def CONSTRAINT_SCHEMA: mysql CONSTRAINT_NAME: delete_range_done_index TABLE_SCHEMA: mysql TABLE_NAME: gc_delete_range_done CONSTRAINT_TYPE: UNIQUE 6 rows in set (0.00 sec)

其中:

  • CONSTRAINT_TYPE 的取值可以是 UNIQUEPRIMARY KEY,或者 FOREIGN KEY
  • UNIQUEPRIMARY KEY 信息与 SHOW INDEX 语句的执行结果类似。

USER_PRIVILEGES Table

USER_PRIVILEGES 表提供了关于全局权限的信息。该表的数据根据 mysql.user 系统表生成。

mysql> desc USER_PRIVILEGES; +----------------|--------------|------|------|---------|-------+ | Field | Type | Null | Key | Default | Extra | +----------------|--------------|------|------|---------|-------+ | GRANTEE | varchar(81) | YES | | NULL | | | TABLE_CATALOG | varchar(512) | YES | | NULL | | | PRIVILEGE_TYPE | varchar(64) | YES | | NULL | | | IS_GRANTABLE | varchar(3) | YES | | NULL | | +----------------|--------------|------|------|---------|-------+ 4 rows in set (0.00 sec)

不支持的 Information Schema 表

TiDB 包含以下 INFORMATION_SCHEMA 表,但仅会返回空行:

  • COLUMN_PRIVILEGES
  • EVENTS
  • FILES
  • GLOBAL_STATUS
  • GLOBAL_VARIABLES
  • OPTIMIZER_TRACE
  • PARAMETERS
  • PARTITIONS
  • PLUGINS
  • PROFILING
  • REFERENTIAL_CONSTRAINTS
  • ROUTINES
  • SCHEMA_PRIVILEGES
  • SESSION_STATUS
  • TABLESPACES
  • TABLE_PRIVILEGES
  • TRIGGERS
  • VIEWS