查询MySql的表结构和元数据

MySql的表结构和元数据存储在系统的表information_schema.columns表中。primaryKey、uniqueKey存储在于information_schema.key_column_usage表中。并且primaryKey的CONSTRAINT_NAME固定为PRIMARY。其余存在的数据就是uniqueKey。当取消unique约束的时候，该条记录从key_column_usage中移除。

话不多说，直接上代码

select col.table_schema as tableSchema, col.table_name as tableName, col.column_name as name,col.data_type as type,
 (case 
 when col.data_type='varchar' or col.data_type='longtext' then col.CHARACTER_MAXIMUM_LENGTH 
 when col.data_type='timestamp' then col.datetime_precision
 else col.numeric_precision end ) as length,
 col.numeric_scale as scale,
 (case when k.CONSTRAINT_NAME='PRIMARY' then '1' else '0' end ) as primaryKey,
 (case when k.CONSTRAINT_NAME!='PRIMARY' then '1' else '0'  end ) as uniqueKey,
 (case when col.IS_NULLABLE='YES' then '1' else '0'  end ) AS nullable, 
 col.column_default AS defaultValue,
 col.column_comment as comment
from information_schema.columns col left join information_schema.key_column_usage k on  k.column_name = col.column_name and   k.table_schema = col.table_schema and  k.table_name = col.table_name  
where  LOWER(col.table_schema)=LOWER('DBname') and LOWER(col.table_name)=LOWER('TableName');

上边的代码用的left join。会出现重复的行。比如一个字段既是primarykey，也是uniqueKey。
可以用下边的代码

select col.table_schema as tableSchema, col.table_name as tableName, col.column_name as name,col.data_type as type,
                 (case when col.data_type='varchar' or col.data_type='longtext' then col.CHARACTER_MAXIMUM_LENGTH 
                when col.data_type='timestamp' then col.datetime_precision
                else col.numeric_precision end ) as length,
                col.numeric_scale as scale,
                (case when 
                              (Select COUNT(*) From information_schema.key_column_usage k where col.column_name =  k.column_name and k.CONSTRAINT_NAME ='PRIMARY')>0   
                               then '1' else '0' end ) as primaryKey,
                (case when 
                                (Select COUNT(*) From information_schema.key_column_usage k where col.column_name =  k.column_name  and k.CONSTRAINT_NAME !='PRIMARY')>0
                                then '1' else '0'  end ) as uniqueKey,
                (case when col.IS_NULLABLE='YES' then '1' else '0'  end ) AS nullable, 
                col.column_default AS defaultValue,
                col.column_comment as comment
               from information_schema.columns col 
                               where  LOWER(col.table_schema)=LOWER('ApolloPortalDB') and LOWER(col.table_name)=LOWER('TZZ') ;

image.png