-- 查询数据库中所有列
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;
![image](https://img2020.cnblogs.com/blog/1751422/202102/1751422-20210206115148900-455431808.png)
-- 查询数据库中索引列
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
![image](https://img2020.cnblogs.com/blog/1751422/202102/1751422-20210206115109119-300650966.png)