MySQL information_schema 数据库

information_schema CHARACTER_SETS可用字符集 COLLATIONS字符集的排序规则
COLLATION_CHARACTER_SET_APPLICABILITY字符集和排序规则的可设置信息SHOW COLLATION;COLUMNS数据库中所有列及属性 COLUMN_PRIVILEGES数据库中所有列DML权限 ENGINES存储引擎描述 EVENTS调度事件 FILES  GLOBAL_STATUS服务器状态变量SHOW GLOBAL STATUS;GLOBAL_VARIABLES服务器状态变量SHOW GLOBAL VARIABLES; KEY_COLUMN_USAGE有约束的键列
PARAMETERS存储过程和函数的参数或返回值 PARTITIONS分区表分区信息 PLUGINS服务器插件 PROCESSLIST当前正在运行的线程 PROFILING跟踪信息SHOW PROFILES;
SHOW PROFILE;REFERENTIAL_CONSTRAINTS外键约束 ROUTINES存储过程或函数信息 SCHEMATA数据库部分信息 SCHEMA_PRIVILEGES方案（数据库）权限
SESSION_STATUS会话状态变量SHOW SESSION STATUS;SESSION_VARIABLES会话状态变量SHOW SESSION VARIABLES;STATISTICS索引信息SHOW INDEX FROM mysql.db;
SHOW INDEX FROM mysql;TABLES表信息SHOW TABLES FROM mysql;
SHOW TABLES LIKE 'user';TABLESPACES表空间信息（非 InnoDB） TABLE_CONSTRAINTS表约束信息 TABLE_PRIVILEGES表权限信息 TRIGGERS触发器信息 USER_PRIVILEGES用户权限 VIEWS视图信息 INNODB_CMPINNODB 压缩信息 INNODB_CMP_RESETINNODB 压缩信息 INNODB_TRXINNODB 当前未提交的事务 INNODB_CMPMEMINNODB 缓冲池中压缩信息 INNODB_CMPMEM_RESETINNODB 缓冲池中压缩信息 INNODB_LOCK_WAITSINNODB 锁等待信息 INNODB_LOCKSINNODB 锁等待信息 

--服务器信息status;--当前连接情况show processlist;--当前正在执行语句的连接select * from information_schema.processlist where info is not null;

--查看数据库中所有外键  select referenced_table_name,referenced_column_name,constraint_name,table_name,column_namefrom information_schema.key_column_usagewhere constraint_name <> 'PRIMARY';SELECT CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,UPDATE_RULE,DELETE_RULEFROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='MYSQL'; --查看数据库中表大小及行数select table_name,table_rows,round((data_length / 1024 / 1024), 3) "data_length_MB" ,round((index_length / 1024 / 1024), 3) "index_length_MB" ,create_time,engine,table_collation from information_schema.tableswhere table_schema = 'MYSQL'order by table_rows desc;

--存储引擎show engines;show variables like '%storage_engine%';show create table <table_name>;show table status from <db_name> where name='<table_name>';--数据库字符集&排序规则select * from information_schema.schemata;--查看表存储引擎&字符集select TABLE_SCHEMA,ENGINE,VERSION,ROW_FORMAT,TABLE_COLLATION,CREATE_TIME from information_schema.tables where table_schema='mysql';

--查看存储过程/函数/触发器/视图 定义 select name from mysql.proc where type='PROCEDURE';select name from mysql.proc where type='FUNCTION';select * from information_schema.triggers where trigger_name='';select * from information_schema.views where table_name='';select * from information_schema.tables where table_name='';show create procedure proc_name;show create function func_name;show create view view_name;