数据管控 mysql数据字典

获取数据库信息：

select * from information_schema.SCHEMATA

通过schema_name获取表信息

select t.*,p.PARTITION_NAME FROM information_schema.`TABLES` t 
LEFT JOIN information_schema.`PARTITIONS` p on t.TABLE_CATALOG=p.TABLESPACE_NAME
and t.TABLE_SCHEMA=p.TABLE_SCHEMA
and t.TABLE_NAME=p.TABLE_NAME
where t.TABLE_TYPE='BASE TABLE' AND t.TABLE_SCHEMA='schema_name'
ORDER BY t.TABLE_NAME

 

获取字段信息

select * from information_schema.`COLUMNS` where TABLE_SCHEMA='schema_name'

获取主键

select * from information_schema.`TABLES` t ,information_schema.KEY_COLUMN_USAGE c
where t.TABLE_NAME=c.TABLE_NAME and t.TABLE_SCHEMA=c.TABLE_SCHEMA
and c.CONSTRAINT_NAME='PRIMARY' and t.TABLE_SCHEMA='schema_name'