MYSQL查询表信息

SELECT
a.table_name 表名,
a.ordinal_position 字段序号,
a.COLUMN_NAME 字段名,
a.DATA_TYPE 数据类型,
case a.column_key when 'pri' then 1 else 0 end as 主键,
case extra when 'auto_increment' then 1 else 0 end as 自增长,

case when character_maximum_length is NULL then numeric_precision else character_maximum_length end as 长度,
case a.is_nullable when 'yes' then 0 else 1 end as 允许空,
column_default 默认值,
column_comment AS 字段说明
FROM information_schema.COLUMNS a