MySQL之information_schema数据库详细讲解
程序员文章站
2022-06-25 11:06:06
1. 概述information_schema 数据库跟 performance_schema 一样，都是 mysql 自带的信息数据库。其中 performance_schema 用于性能分析，而...
1. 概述
information_schema 数据库跟 performance_schema 一样，都是 mysql 自带的信息数据库。其中 performance_schema 用于性能分析，而 information_schema 用于存储数据库元数据(关于数据的数据)，例如数据库名、表名、列的数据类型、访问权限等。
information_schema 中的表实际上是视图，而不是基本表，因此，文件系统上没有与之相关的文件。
mysql> use information_schema; reading table information for completion of table and column names you can turn off this feature to get a quicker startup with -a database changed mysql> show tables; +---------------------------------------+ | tables_in_information_schema | +---------------------------------------+ | character_sets | | collations | | collation_character_set_applicability | | columns | | column_privileges | | engines | | events | | files | | global_status | | global_variables | | key_column_usage | | optimizer_trace | | parameters | | partitions | | plugins | | processlist | | profiling | | referential_constraints | | routines | | schemata | | schema_privileges | | session_status | | session_variables | | statistics | | tables | | tablespaces | | table_constraints | | table_privileges | | triggers | | user_privileges | | views | | innodb_locks | | innodb_trx | | innodb_sys_datafiles | | innodb_ft_config | | innodb_sys_virtual | | innodb_cmp | | innodb_ft_being_deleted | | innodb_cmp_reset | | innodb_cmp_per_index | | innodb_cmpmem_reset | | innodb_ft_deleted | | innodb_buffer_page_lru | | innodb_lock_waits | | innodb_temp_table_info | | innodb_sys_indexes | | innodb_sys_tables | | innodb_sys_fields | | innodb_cmp_per_index_reset | | innodb_buffer_page | | innodb_ft_default_stopword | | innodb_ft_index_table | | innodb_ft_index_cache | | innodb_sys_tablespaces | | innodb_metrics | | innodb_sys_foreign_cols | | innodb_cmpmem | | innodb_buffer_pool_stats | | innodb_sys_columns | | innodb_sys_foreign | | innodb_sys_tablestats | +---------------------------------------+ 61 rows in set (0.00 sec)
2. information_schema 库中常用的表
character_sets 表
提供了 mysql 可用字符集的信息。show character set; 命令从这个表获取结果。
mysql> show character set; +----------+---------------------------------+---------------------+--------+ | charset | description | default collation | maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | big5 traditional chinese | big5_chinese_ci | 2 | | dec8 | dec west european | dec8_swedish_ci | 1 | | cp850 | dos west european | cp850_general_ci | 1 | ... | eucjpms | ujis for windows japanese | eucjpms_japanese_ci | 3 | | gb18030 | china national standard gb18030 | gb18030_chinese_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.07 sec) mysql> select * from character_sets; +--------------------+----------------------+---------------------------------+--------+ | character_set_name | default_collate_name | description | maxlen | +--------------------+----------------------+---------------------------------+--------+ | big5 | big5_chinese_ci | big5 traditional chinese | 2 | | dec8 | dec8_swedish_ci | dec west european | 1 | | cp850 | cp850_general_ci | dos west european | 1 | ... | eucjpms | eucjpms_japanese_ci | ujis for windows japanese | 3 | | gb18030 | gb18030_chinese_ci | china national standard gb18030 | 4 | +--------------------+----------------------+---------------------------------+--------+ 41 rows in set (0.00 sec)
schemata 表
当前 mysql 实例中所有数据库的信息。show databases; 命令从这个表获取数据。
mysql> select * from schemata; +--------------+--------------------+----------------------------+------------------------+----------+ | catalog_name | schema_name | default_character_set_name | default_collation_name | sql_path | +--------------+--------------------+----------------------------+------------------------+----------+ | def | information_schema | utf8 | utf8_general_ci | null | | def | mysql | latin1 | latin1_swedish_ci | null | | def | performance_schema | utf8 | utf8_general_ci | null | | def | sys | utf8 | utf8_general_ci | null | | def | test | utf8 | utf8_unicode_ci | null | +--------------+--------------------+----------------------------+------------------------+----------+ 10 rows in set (0.00 sec) mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 10 rows in set (0.00 sec)
tables 表
存储数据库中的表信息（包括视图），包括表属于哪个数据库，表的类型、存储引擎、创建时间等信息。show tables from xx; 命令从这个表获取结果。
mysql> select * from tables; +---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+--------------------------------------------+ | table_catalog | table_schema | table_name | table_type | engine | version | row_format | table_rows | avg_row_length | data_length | max_data_length | index_length | data_free | auto_increment | create_time | update_time | check_time | table_collation | checksum | create_options | table_comment | +---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+--------------------------------------------+ | def | information_schema | character_sets | system view | memory | 10 | fixed | null | 384 | 0 | 16434816 | 0 | 0 | null | 2018-04-23 11:51:32 | null | null | utf8_general_ci | null | max_rows=43690 | | | def | information_schema | collations | system view | memory | 10 | fixed | null | 231 | 0 | 16704765 | 0 | 0 | null | 2018-04-23 11:51:32 | null | null | utf8_general_ci | null | max_rows=72628 | | ... | def | zentao | zt_usertpl | base table | myisam | 10 | dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2017-08-16 16:36:45 | 2017-08-16 16:36:45 | null | utf8_general_ci | null | | | +---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+--------------------------------------------+ 525 rows in set (3.03 sec) mysql> show tables from zentao; +-------------------+ | tables_in_zentao | +-------------------+ | zt_action | | zt_block | | zt_branch | ... | zt_usertpl | +-------------------+ 48 rows in set (0.00 sec)
columns 表
存储表中的列信息，包括表有多少列、每个列的类型等。show columns from schemaname.tablename 命令从这个表获取结果。
mysql> select * from columns limit 2,5; +---------------+--------------------+----------------+--------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-------------+------------+-------+------------+----------------+-----------------------+ | table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_scale | datetime_precision | character_set_name | collation_name | column_type | column_key | extra | privileges | column_comment | generation_expression | +---------------+--------------------+----------------+--------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-------------+------------+-------+------------+----------------+-----------------------+ | def | information_schema | character_sets | description | 3 | | no | varchar | 60 | 180 | null | null | null | utf8 | utf8_general_ci | varchar(60) | | | select | | | | def | information_schema | character_sets | maxlen | 4 | 0 | no | bigint | null | null | 19 | 0 | null | null | null | bigint(3) | | | select | | | | def | information_schema | collations | collation_name | 1 | | no | varchar | 32 | 96 | null | null | null | utf8 | utf8_general_ci | varchar(32) | | | select | | | | def | information_schema | collations | character_set_name | 2 | | no | varchar | 32 | 96 | null | null | null | utf8 | utf8_general_ci | varchar(32) | | | select | | | | def | information_schema | collations | id | 3 | 0 | no | bigint | null | null | 19 | 0 | null | null | null | bigint(11) | | | select | | | +---------------+--------------------+----------------+--------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-------------+------------+-------+------------+----------------+-----------------------+ 5 rows in set (0.08 sec)
statistics 表
表索引的信息。show index from schemaname.tablename; 命令从这个表获取结果。
mysql> show index from szhuizhong.users; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | users | 0 | primary | 1 | userid | a | 1460 | null | null | | btree | | | | users | 0 | account_index | 1 | account | a | 1460 | null | null | | btree | | | | users | 1 | corpid | 1 | fromid | a | 2 | null | null | yes | btree | | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
user_privileges 表
用户权限表。内容源自 mysql.user 授权表。是非标准表。
mysql> select * from user_privileges; +-------------------------+---------------+-------------------------+--------------+ | grantee | table_catalog | privilege_type | is_grantable | +-------------------------+---------------+-------------------------+--------------+ | 'mysql.sys'@'localhost' | def | usage | no | | 'root'@'%' | def | select | yes | | 'root'@'%' | def | insert | yes | | 'root'@'%' | def | update | yes | | 'root'@'%' | def | delete | yes | | 'root'@'%' | def | create | yes | | 'root'@'%' | def | drop | yes | | 'root'@'%' | def | reload | yes | | 'root'@'%' | def | shutdown | yes | | 'root'@'%' | def | process | yes | | 'root'@'%' | def | file | yes | | 'root'@'%' | def | references | yes | | 'root'@'%' | def | index | yes | | 'root'@'%' | def | alter | yes | | 'root'@'%' | def | show databases | yes | | 'root'@'%' | def | super | yes | | 'root'@'%' | def | create temporary tables | yes | | 'root'@'%' | def | lock tables | yes | | 'root'@'%' | def | execute | yes | | 'root'@'%' | def | replication slave | yes | | 'root'@'%' | def | replication client | yes | | 'root'@'%' | def | create view | yes | | 'root'@'%' | def | show view | yes | | 'root'@'%' | def | create routine | yes | | 'root'@'%' | def | alter routine | yes | | 'root'@'%' | def | create user | yes | | 'root'@'%' | def | event | yes | | 'root'@'%' | def | trigger | yes | | 'root'@'%' | def | create tablespace | yes | +-------------------------+---------------+-------------------------+--------------+ 29 rows in set (0.00 sec)
schema_privileges 表
方案权限表。给出了关于方案（数据库）权限的信息。内容来自 mysql.db 授权表。是非标准表。
mysql> select * from schema_privileges; +-------------------------+---------------+--------------+-------------------------+--------------+ | grantee | table_catalog | table_schema | privilege_type | is_grantable | +-------------------------+---------------+--------------+-------------------------+--------------+ | 'mysql.sys'@'localhost' | def | sys | trigger | no | | 'root'@'%' | def | mysql | select | yes | | 'root'@'%' | def | mysql | insert | yes | | 'root'@'%' | def | mysql | update | yes | | 'root'@'%' | def | mysql | delete | yes | | 'root'@'%' | def | mysql | create | yes | | 'root'@'%' | def | mysql | drop | yes | | 'root'@'%' | def | mysql | references | yes | | 'root'@'%' | def | mysql | index | yes | | 'root'@'%' | def | mysql | alter | yes | | 'root'@'%' | def | mysql | create temporary tables | yes | | 'root'@'%' | def | mysql | lock tables | yes | | 'root'@'%' | def | mysql | execute | yes | | 'root'@'%' | def | mysql | create view | yes | | 'root'@'%' | def | mysql | show view | yes | | 'root'@'%' | def | mysql | create routine | yes | | 'root'@'%' | def | mysql | alter routine | yes | | 'root'@'%' | def | mysql | event | yes | | 'root'@'%' | def | mysql | trigger | yes | +-------------------------+---------------+--------------+-------------------------+--------------+ 19 rows in set (0.00 sec)
table_privileges 表
表权限表。给出了关于表权限的信息。内容源自 mysql.tables_priv 授权表。是非标准表。
mysql> select * from table_privileges; +-------------------------+---------------+--------------+------------+----------------+--------------+ | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | +-------------------------+---------------+--------------+------------+----------------+--------------+ | 'mysql.sys'@'localhost' | def | sys | sys_config | select | no | +-------------------------+---------------+--------------+------------+----------------+--------------+ 1 row in set (0.00 sec)
column_privileges 表
列权限表。给出了关于列权限的信息。内容源自 mysql.columns_priv 授权表。是非标准表。
mysql> select * from column_privileges; empty set (0.00 sec)
collations 表
提供了关于各字符集的对照信息。show collation; 命令从这个表获取结果。
mysql> select * from collations; +--------------------------+--------------------+-----+------------+-------------+---------+ | collation_name | character_set_name | id | is_default | is_compiled | sortlen | +--------------------------+--------------------+-----+------------+-------------+---------+ | big5_chinese_ci | big5 | 1 | yes | yes | 1 | | big5_bin | big5 | 84 | | yes | 1 | | dec8_swedish_ci | dec8 | 3 | yes | yes | 1 | | dec8_bin | dec8 | 69 | | yes | 1 | ... | gb18030_bin | gb18030 | 249 | | yes | 1 | | gb18030_unicode_520_ci | gb18030 | 250 | | yes | 8 | +--------------------------+--------------------+-----+------------+-------------+---------+ 222 rows in set (0.03 sec)
collation_character_set_applicability 表
指明了可用于校对的字符集。相当于 show collation 命令结果的前两个字段。
mysql> select * from collation_character_set_applicability; +--------------------------+--------------------+ | collation_name | character_set_name | +--------------------------+--------------------+ | big5_chinese_ci | big5 | | big5_bin | big5 | | dec8_swedish_ci | dec8 | ... | gb18030_bin | gb18030 | | gb18030_unicode_520_ci | gb18030 | +--------------------------+--------------------+ 222 rows in set (0.00 sec)
table_constraints 表
描述了存在约束的表。以及表的约束类型。
mysql> select * from table_constraints; +--------------------+-------------------+--------------------+--------------+---------------------------+-----------------+ | constraint_catalog | constraint_schema | constraint_name | table_schema | table_name | constraint_type | +--------------------+-------------------+--------------------+--------------+---------------------------+-----------------+ | def | mysql | primary | mysql | columns_priv | primary key | | def | mysql | primary | mysql | db | primary key | | def | mysql | primary | mysql | engine_cost | primary key | | def | mysql | primary | mysql | event | primary key | | def | mysql | primary | mysql | func | primary key | | def | mysql | primary | mysql | gtid_executed | primary key | | def | mysql | primary | mysql | help_category | primary key | | def | mysql | name | mysql | help_category | unique | | def | mysql | primary | mysql | help_keyword | primary key | | def | mysql | name | mysql | help_keyword | unique | | def | mysql | primary | mysql | help_relation | primary key | | def | mysql | primary | mysql | help_topic | primary key | | def | mysql | name | mysql | help_topic | unique | | def | mysql | primary | mysql | innodb_index_stats | primary key | | def | mysql | primary | mysql | innodb_table_stats | primary key | | def | mysql | primary | mysql | ndb_binlog_index | primary key | | def | mysql | primary | mysql | plugin | primary key | | def | mysql | primary | mysql | proc | primary key | | def | mysql | primary | mysql | procs_priv | primary key | | def | mysql | primary | mysql | proxies_priv | primary key | | def | mysql | primary | mysql | server_cost | primary key | | def | mysql | primary | mysql | servers | primary key | | def | mysql | primary | mysql | slave_master_info | primary key | | def | mysql | primary | mysql | slave_relay_log_info | primary key | | def | mysql | primary | mysql | slave_worker_info | primary key | | def | mysql | primary | mysql | tables_priv | primary key | | def | mysql | primary | mysql | time_zone | primary key | | def | mysql | primary | mysql | time_zone_leap_second | primary key | | def | mysql | primary | mysql | time_zone_name | primary key | | def | mysql | primary | mysql | time_zone_transition | primary key | | def | mysql | primary | mysql | time_zone_transition_type | primary key | | def | mysql | primary | mysql | user | primary key | | def | sys | primary | sys | sys_config | primary key | | def | zentao | primary | zentao | zt_action | primary key | ... | def | zentao | account | zentao | zt_usergroup | unique | | def | zentao | primary | zentao | zt_userquery | primary key | | def | zentao | primary | zentao | zt_usertpl | primary key | +--------------------+-------------------+--------------------+--------------+---------------------------+-----------------+ 213 rows in set (0.37 sec)
key_column_usage 表
描述了具有约束的键列。
mysql> select * from key_column_usage; +--------------------+-------------------+--------------------+---------------+--------------+---------------------------+--------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | column_name | ordinal_position | position_in_unique_constraint | referenced_table_schema | referenced_table_name | referenced_column_name | +--------------------+-------------------+--------------------+---------------+--------------+---------------------------+--------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def | mysql | primary | def | mysql | columns_priv | host | 1 | null | null | null | null | | def | mysql | primary | def | mysql | columns_priv | db | 2 | null | null | null | null | | def | mysql | primary | def | mysql | columns_priv | user | 3 | null | null | null | null | | def | mysql | primary | def | mysql | columns_priv | table_name | 4 | null | null | null | null | | def | mysql | primary | def | mysql | columns_priv | column_name | 5 | null | null | null | null | ... | def | mysql | primary | def | mysql | time_zone_leap_second | transition_time | 1 | null | null | null | null | | def | mysql | primary | def | mysql | time_zone_name | name | 1 | null | null | null | null | | def | mysql | primary | def | mysql | time_zone_transition | time_zone_id | 1 | null | null | null | null | | def | mysql | primary | def | mysql | time_zone_transition | transition_time | 2 | null | null | null | null | | def | mysql | primary | def | mysql | time_zone_transition_type | time_zone_id | 1 | null | null | null | null | | def | mysql | primary | def | mysql | time_zone_transition_type | transition_type_id | 2 | null | null | null | null | | def | mysql | primary | def | mysql | user | host | 1 | null | null | null | null | | def | mysql | primary | def | mysql | user | user | 2 | null | null | null | null | | def | sys | primary | def | sys | sys_config | variable | 1 | null | null | null | null | +--------------------+-------------------+--------------------+---------------+--------------+---------------------------+--------------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ 278 rows in set (0.03 sec)
routines 表
提供了关于存储子程序（存储程序和函数）的信息。此时，routines 表不包含自定义函数（udf）。名为“mysql.proc name”的列指明了对应于 information_schema.routines 表的 mysql.proc 列。
views 表
给出了关于数据库中的视图的信息。需要有 show views 权限，否则无法查看视图信息。
mysql> select * from views limit 1\g
*************************** 1. row ***************************
table_catalog: def
table_schema: sys
table_name: host_summary
view_definition: select if(isnull(`performance_schema`.`accounts`.`host`),'background',`performance_schema`.`accounts`.`host`) as `host`,sum(`stmt`.`total`) as `statements`,`sys`.`format_time`(sum(`stmt`.`total_latency`)) as `statement_latency`,`sys`.`format_time`(ifnull((sum(`stmt`.`total_latency`) / nullif(sum(`stmt`.`total`),0)),0)) as `statement_avg_latency`,sum(`stmt`.`full_scans`) as `table_scans`,sum(`io`.`ios`) as `file_ios`,`sys`.`format_time`(sum(`io`.`io_latency`)) as `file_io_latency`,sum(`performance_schema`.`accounts`.`current_connections`) as `current_connections`,sum(`performance_schema`.`accounts`.`total_connections`) as `total_connections`,count(distinct `performance_schema`.`accounts`.`user`) as `unique_users`,`sys`.`format_bytes`(sum(`mem`.`current_allocated`)) as `current_memory`,`sys`.`format_bytes`(sum(`mem`.`total_allocated`)) as `total_memory_allocated` from (((`performance_schema`.`accounts` join `sys`.`x$host_summary_by_statement_latency` `stmt` on((`performance_schema`.`accounts`.`host` = `stmt`.`host`))) join `sys`.`x$host_summary_by_file_io` `io` on((`performance_schema`.`accounts`.`host` = `io`.`host`))) join `sys`.`x$memory_by_host_by_current_bytes` `mem` on((`performance_schema`.`accounts`.`host` = `mem`.`host`))) group by if(isnull(`performance_schema`.`accounts`.`host`),'background',`performance_schema`.`accounts`.`host`)
check_option: none
is_updatable: no
definer: mysql.sys@localhost
security_type: invoker
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.03 sec)
triggers 表
提供了关于触发程序的信息。必须有 super 权限才能查看该表。
mysql> select * from triggers limit 1\g
*************************** 1. row ***************************
trigger_catalog: def
trigger_schema: sys
trigger_name: sys_config_insert_set_user
event_manipulation: insert
event_object_catalog: def
event_object_schema: sys
event_object_table: sys_config
action_order: 1
action_condition: null
action_statement: begin if @sys.ignore_sys_config_triggers != true and new.set_by is null then set new.set_by = user(); end if; end
action_orientation: row
action_timing: before
action_reference_old_table: null
action_reference_new_table: null
action_reference_old_row: old
action_reference_new_row: new
created: 2017-05-27 11:18:43.60
sql_mode:
definer: mysql.sys@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
database_collation: utf8_general_ci
1 row in set (0.00 sec)
到此这篇关于mysql之information_schema数据库详细讲解的文章就介绍到这了,更多相关mysql之information_schema数据库内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！