mysql information_schema

mysql表信息查询

-- mysql表信息查询
select table_schema,
       table_name,
       table_comment
from information_schema.tables
where table_schema = 'table_schema';

mysql表字段查询

-- mysql表字段查询
select table_schema   as '实例',
       table_name     as '表名',
       column_name    as '字段名',
       case data_type
           when 'varchar' then concat(data_type, '(', character_maximum_length, ')')
           when 'decimal' then concat(data_type, '(', numeric_precision, ',', numeric_scale, ')')
           else data_type
           end        as '数据类型',
       column_default as '默认值',
       is_nullable    as '是否可为空',
       column_key     as '约束',
       column_comment as '备注'
from information_schema.columns
where columns.table_schema = 'table_schema'
  and table_name = 'table_name'
order by ordinal_position;

-- mysql表字段查询
select table_schema,
       table_name,
       column_name,
       case data_type
           when 'varchar' then concat(data_type, '(', character_maximum_length, ')')
           when 'decimal' then concat(data_type, '(', numeric_precision, ',', numeric_scale, ')')
           else data_type
           end as data_type,
       column_default,
       is_nullable,
       column_key,
       column_comment
from information_schema.columns
where columns.table_schema = 'table_schema'
  and table_name = 'table_name'
order by ordinal_position;