mysql自带的information_schema,可以看作一个信息库,可以查看各数据库的系统数据,show 操作时都是从这个库相应表获取数据的。
我平时常用的查看如下:
查看某库全部的表:select * from information_schema.tables where table_schema='testdb3' order by table_name
查看某个表的字段:select * from columns where table_schema='testdb3' and table_name='device' and COLUMN_NAME ='use_ip' ;
查看某个表的索引:select * from STATISTICS where table_schema='testdb3' and table_name='device';
注意,不区分大小写。
查看某个表的约束:select * from TABLE_CONSTRAINTS where constraint_schema='testdb3' and table_name='table_name';
查看某个表具有约束的列:select * from KEY_COLUMN_USAGE where constraint_schema='testdb3' and table_name='table_name';
TABLE_CONSTRAINTS 和KEY_COLUMN_USAGE 两个表关联起来就可以主外键详细信息:主键表、主键字段、外键表、外键字段
select c.constraint_schema,c.table_name,c.constraint_name,c.constraint_type, u.column_name, u.referenced_table_schema,u.referenced_table_name,u.referenced_column_name
from TABLE_CONSTRAINTS c,KEY_COLUMN_USAGE u
where c.constraint_schema=u.constraint_schema
and c.table_name=u.table_name
and c.constraint_name=u.constraint_name
and c.constraint_schema='testvpnms212' #某数据库
and c.table_name='tablename' ##某表
--and c.constraint_name='tablename_ibfk_1' #外键