MySQL 表信息查询，便于补数据库结构设计文档

MySQL 表信息查询，便于补数据库结构设计文档

select c.table_name                                          表名,
       t.TABLE_COMMENT                                       表说明,
       c.COLUMN_NAME                                         列名,
       c.COLUMN_TYPE                                         数据类型,
       case c.COLUMN_KEY when 'PRI' then '是' else '' end    主键,
       case c.is_nullable when 'YES' then '是' else '否' end 是否非空,
       c.COLUMN_COMMENT                                      注释
from information_schema.`COLUMNS` c,
     information_schema.TABLES t
where c.TABLE_SCHEMA = '数据库名'
  and c.TABLE_SCHEMA = t.TABLE_SCHEMA
  and c.TABLE_NAME = t.TABLE_NAME
  and c.TABLE_NAME in ('表名')
order by c.table_name asc, (case c.COLUMN_KEY when 'PRI' then 1 else 0 end) desc, c.COLUMN_NAME asc;