使用系统表获取表结构,方便进一步处理。也可以直接使用 show create table db_name.table_name 查看建表信息。
select lower(col.table_schema) as table_schema --库名
,lower(col.table_name) as table_name --表名
,lower(col.column_name) as column_name --字段名
,case when lower(col.data_type) like '%varchar%'
then concat(lower(col.data_type),'(',col.character_maximum_length,')')
else lower(col.data_type) end as column_type --字段类型
,col.ordinal_position as column_order --字段顺序
,case when pk.column_name is not null then 1 else 0 end as primary_key --主键(不可为空)
,case when uk.column_name is not null then 1 else 0 end as unique_key --唯一键(可为空)
,col.column_default as default_value --默认值
,col.column_comment as column_comment --字段注释
from information_schema.columns col
left join information_schema.key_column_usage pk
on col.column_name = pk.column_name
and col.table_name = pk.table_name
and col.table_schema = pk.table_schema
and pk.constraint_name = 'PRIMARY'
left join information_schema.key_column_usage uk
on col.column_name = uk.column_name
and col.table_name = uk.table_name
and col.table_schema = uk.table_schema
and uk.constraint_name != 'PRIMARY'
where lower(col.table_schema) = lower('')
and lower(col.table_name) = lower('')
order by col.ordinal_position;
注: 部分版本数据库不支持添加注释执行,执行代码是需要将注释去掉