11.1.1 查看数据库中是否使用了外键
mysql> select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA='employees' and REFERENCED_TABLE_SCHEMA is not null\G
......
6 rows in set (0.01 sec)
11.1.2 查看InnoDB表空间文件信息
mysql> select * from information_schema.FILES where file_name='./employees/dept_ emp.ibd'\G
......
1 row in set (0.00 sec)
11.1.3 查看索引的统计信息
mysql> select * from information_schema.STATISTICS where TABLE_SCHEMA='employees' and TABLE_NAME='dept_emp' and INDEX_NAME='primary'\G
......
2 rows in set (0.00 sec)
11.1.4 查看表的字段相关信息
mysql> select TABLE_SCHEMA, TABLE_NAME,COLUMN_NAME, ORDINAL_POSITION,COLUMN_DEFAULT, IS_NULLABLE,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT from information_schema.COLUMNS where TABLE_SCHEMA='employees' and TABLE_NAME= 'dept_emp';
......
4 rows in set (0.00 sec)
11.1.5 查看数据库中是否使用了存储程序
mysql> select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,CHARACTER_SET_NAME,COLLATION_ NAME,DTD_IDENTIFIER,CREATED,LAST_ALTERED from information_schema.ROUTINES where ROUTINE_SCHEMA= 'employees';
......
5 rows in set (0.01 sec)
11.1.6 查看数据库中的分区表信息
mysql> select TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME,PARTITION_METHOD,PARTITION_ EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,DATA_FREE from information_schema.PARTITIONS where TABLE_SCHEMA='employees' and TABLE_NAME= 'salaries' and PARTITION_NAME is not null;
......
11.1.7 查看数据库中的触发器
mysql> select TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_ ORIENTATION,ACTION_TIMING,CREATED from information_schema.TRIGGERS where TRIGGER_SCHEMA= 'sys'\G
......
2 rows in set (0.01 sec)
11.1.8 查看数据库中的计划任务
mysql> select * from information_schema.events where EVENT_SCHEMA='sbtest'\G
......
1 row in set (0.00 sec)
11.1.9 查看客户端会话的状态信息
mysql> select * from information_schema.PROCESSLIST\G
......
1 row in set (0.00 sec)
11.2.1 查看索引列的信息
mysql> select t.name as d_t_name, i.name as i_name,i.type as i_type,i.N_FIELDS as i_column_numbers,f.name as i_column_name,f.pos as i_position from INNODB_SYS_TABLES as t join INNODB_SYS_INDEXES as i on t.TABLE_ID=i.TABLE_ID left join INNODB_SYS_FIELDS as f on i.INDEX_ID=f.INDEX_ID where t.name='employees/dept_emp';
......
4 rows in set (0.01 sec)
11.2.2 查看表的字段相关信息
mysql> select t.name as db_table_name,c.name as column_name,c.pos as column_position, c.mtype as column_type,c.len as column_len from INNODB_SYS_TABLES as t,INNODB_SYS_COLUMNS as c where t.TABLE_ID=c.TABLE_ID and t.name='employees/dept_emp';
......
4 rows in set (0.00 sec)
- 温馨提示：
- 关于文中提到的参数的详细解释，可参考本书下载资源中的“附录C”。
- 关于information_schema系统库更详细的内容，可参阅微信公众号“沃趣技术”，其中我们用11个章节对其进行了全方位的介绍。
- 温馨提示：关于information_schema系统库更详细的内容，可参阅微信公众号“沃趣技术”。或详见开源文档：information_schema详解
上一篇：第10章 information_schema初相识 | 下一篇：第12章 mysql系统库之权限系统表