MySQL之information_schema数据库详细讲解
来源：脚本之家
2023-01-14 13:10:51
0浏览
收藏
怎么入门数据库编程？需要学习哪些知识点？这是新手们刚接触编程时常见的问题；下面golang学习网就来给大家整理分享一些知识点，希望能够给初学者一些帮助。本篇文章就来介绍《MySQL之information_schema数据库详细讲解》，涉及到MySQLinformation 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)
以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。
版本声明
本文转载于：脚本之家 如有侵犯，请联系study_golang@163.com删除
MySQL/MariaDB中如何支持全部的Unicode
- 上一篇
- MySQL/MariaDB中如何支持全部的Unicode
- 下一篇
- SQL insert into语句写法讲解
评论列表
-
- 沉默的老鼠
- 这篇技术贴出现的刚刚好，太全面了，感谢大佬分享，已收藏，关注师傅了！希望师傅能多写数据库相关的文章。
- 2023-02-11 16:40:00
-
- 贪玩的花卷
- 细节满满，已收藏，感谢楼主的这篇技术文章，我会继续支持！
- 2023-02-07 12:24:26
-
- 粗心的小天鹅
- 写的不错，一直没懂这个问题，但其实工作中常常有遇到...不过今天到这，帮助很大，总算是懂了，感谢老哥分享技术文章！
- 2023-01-22 01:08:47
-
- 无聊的画笔
- 这篇技术贴太及时了，老哥加油！
- 2023-01-15 07:14:10
查看更多
最新文章
-
- 数据库 · MySQL | 11小时前 | 数据类型 扩展性 存储引擎 CREATETABLE 约束条件
- MySQL建表攻略：详解数据表创建方法
- 265浏览 收藏
-
- 数据库 · MySQL | 14小时前 | mysql 字符集 中文乱码 utf8mb4 utf8mb4_unicode_ci
- MySQL中文乱码解决方案与字符集修改命令大全
- 339浏览 收藏
查看更多
课程推荐
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案，代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景，打造一站式知识长龙服务，适合有JS基础的同学学习。
- 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例，全面具体可落地，从理论到实践，一步一步将GO核心编程技术、编程思想、底层实现融会贯通，使学习者贴近时代脉搏，做IT互联网时代的弄潮儿。
- 508次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信：Le-studyg；在课程中，我们将首先介绍MySQL8的新特性，包括性能优化、安全增强、新数据类型等，帮助学生快速熟悉MySQL8的最新功能。接着，我们将深入解析MySQL的网络通信机制，包括协议、连接管理、数据传输等，让
- 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网，分为导航，轮播，关于我们，成功案例，服务流程，团队介绍，数据部分，公司动态，底部信息等内容区块。网站整体采用CSSGrid布局，支持响应式，有流畅过渡和展现动画。
- 484次学习
查看更多
AI推荐
-
- 笔灵AI生成答辩PPT
- 探索笔灵AI生成答辩PPT的强大功能，快速制作高质量答辩PPT。精准内容提取、多样模板匹配、数据可视化、配套自述稿生成，让您的学术和职场展示更加专业与高效。
- 23次使用
-
- 知网AIGC检测服务系统
- 知网AIGC检测服务系统，专注于检测学术文本中的疑似AI生成内容。依托知网海量高质量文献资源，结合先进的“知识增强AIGC检测技术”，系统能够从语言模式和语义逻辑两方面精准识别AI生成内容，适用于学术研究、教育和企业领域，确保文本的真实性和原创性。
- 35次使用
-
- AIGC检测-Aibiye
- AIbiye官网推出的AIGC检测服务，专注于检测ChatGPT、Gemini、Claude等AIGC工具生成的文本，帮助用户确保论文的原创性和学术规范。支持txt和doc(x)格式，检测范围为论文正文，提供高准确性和便捷的用户体验。
- 37次使用
-
- 易笔AI论文
- 易笔AI论文平台提供自动写作、格式校对、查重检测等功能，支持多种学术领域的论文生成。价格优惠，界面友好，操作简便，适用于学术研究者、学生及论文辅导机构。
- 47次使用
-
- 笔启AI论文写作平台
- 笔启AI论文写作平台提供多类型论文生成服务，支持多语言写作，满足学术研究者、学生和职场人士的需求。平台采用AI 4.0版本，确保论文质量和原创性，并提供查重保障和隐私保护。
- 40次使用
查看更多
相关文章
-
- MySQL主从切换的超详细步骤
- 2023-01-01 501浏览
-
- Mysql-普通索引的 change buffer
- 2023-01-25 501浏览
-
- MySQL高级进阶sql语句总结大全
- 2022-12-31 501浏览
-
- Mysql报错：message from server: * is blocked because of many
- 2023-02-24 501浏览
-
- 腾讯云大佬亲码“redis深度笔记”，不讲一句废话，全是精华
- 2023-02-22 501浏览
