MySQL/GreatDB 获取表元数据信息、采集表信息、获取表列表、列信息、索引信息、主键信息、分区信息

本文主要是介绍MySQL/GreatDB 获取表元数据信息、采集表信息、获取表列表、列信息、索引信息、主键信息、分区信息，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！


-- 获取表列表
select   TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT from information_schema.TABLES where TABLE_SCHEMA ='dev'
and TABLE_NAME ='two_primary';
select * from information_schema.TABLES where TABLE_SCHEMA ='dev';
-- 获取列信息
selectTABLE_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
from information_schema.COLUMNS where  TABLE_SCHEMA='dev' and TABLE_NAME = 'two_primary';-- index信息
select * from information_schema.STATISTICS where  TABLE_SCHEMA='dev' and TABLE_NAME = 'par_list';-- 获取索引信息 不使用全文索引，仅区分 唯一索引和其他索引
select INDEX_NAME,SEQ_IN_INDEX,COLUMN_NAME as col_ids,INDEX_COMMENT,IF(t2.CONSTRAINT_TYPE = 'UNIQUE', 'Unique', 'Normal') as index_type
from information_schema.STATISTICS t1
left join  (select CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE
from information_schema.TABLE_CONSTRAINTS ) t2 on t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
and t1.TABLE_NAME = t2.TABLE_NAME and t2.CONSTRAINT_NAME = t1.INDEX_NAME
where  t1.TABLE_SCHEMA='dev' and t1.TABLE_NAME = 'two_primary'
order by INDEX_NAME, SEQ_IN_INDEX;-- 约束select CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE
from information_schema.TABLE_CONSTRAINTS where  TABLE_SCHEMA='dev' and TABLE_NAME = 'two_primary';;-- 主键
selectINDEX_NAME,COLUMN_NAME
from information_schema.STATISTICS where index_name = 'PRIMARY' and  TABLE_SCHEMA='dev' and TABLE_NAME = 'par_list';select INDEX_NAME as  key_name,COLUMN_NAME as col_id from information_schema.STATISTICS  where  index_name = 'PRIMARY'  and lower(table_name) = lower('two_primary')  and lower(TABLE_SCHEMA) = lower('dev')  order by SEQ_IN_INDEX ;
---- 分区信息select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,PARTITION_ORDINAL_POSITION,SUBPARTITION_ORDINAL_POSITION,PARTITION_METHOD,SUBPARTITION_METHOD,replace( PARTITION_EXPRESSION,'`',''),SUBPARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH,MAX_DATA_LENGTH,INDEX_LENGTH,DATA_FREE,CREATE_TIME,UPDATE_TIME,CHECK_TIME,CHECKSUM,PARTITION_COMMENT,NODEGROUP,TABLESPACE_NAME
from information_schema.PARTITIONS where   TABLE_SCHEMA='dev' and TABLE_NAME = 'two_primary';;