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数据库内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！