查询数据库中索引列

-- 查询数据库中所有列
select  
    tab.TABLE_SCHEMA 数据库名,
    tab.TABLE_NAME 表名,
    tab.TABLE_COMMENT 表说明,
    col.COLUMN_NAME 字段名,
    col.COLUMN_TYPE 字段类型,
    col.IS_NULLABLE 是否可为空,
    col.COLUMN_DEFAULT 默认值,
    col.COLUMN_COMMENT 字段说明
FROM information_schema.`COLUMNS` col 
LEFT JOIN information_schema.`TABLES` tab on (tab.TABLE_SCHEMA = col.TABLE_SCHEMA and tab.TABLE_NAME = col.TABLE_NAME)
where 1 = 1 
and col.TABLE_SCHEMA = 'user_db' 
and col.TABLE_NAME = 'user' 
order by col.TABLE_SCHEMA,col.TABLE_NAME;

运行结果

-- 查询数据库中索引列
select DISTINCT 
    col.TABLE_SCHEMA 数据库名,
    col.TABLE_NAME 表名,
    col.COLUMN_NAME 字段名,
    IF(sta.INDEX_NAME = 'PRIMARY','YES','') 是否为主键索引,
    IF(sta.INDEX_NAME != 'PRIMARY','YES','') 是否为索引
FROM information_schema.`COLUMNS` col 
INNER JOIN information_schema.STATISTICS sta on 
    (sta.TABLE_SCHEMA = col.TABLE_SCHEMA and sta.TABLE_NAME = col.TABLE_NAME and sta.COLUMN_NAME = col.COLUMN_NAME) 
where 1 = 1 
and col.TABLE_SCHEMA = 'user_db' 
and col.TABLE_NAME = 'user' 
order by col.TABLE_SCHEMA,col.TABLE_NAME

运行结果