SELECT
Column_Name AS '字段名称',
data_type AS '数据类型',
(
CASE
WHEN data_type = 'float'
OR data_type = 'double'
OR data_type = 'TINYINT'
OR data_type = 'SMALLINT'
OR data_type = 'MEDIUMINT'
OR data_type = 'INT'
OR data_type = 'INTEGER'
OR data_type = 'decimal'
OR data_type = 'bigint' THEN
NUMERIC_PRECISION ELSE CHARACTER_MAXIMUM_LENGTH
END
) AS '长度(整数)',
NUMERIC_SCALE AS '长度(小数)',
( CASE WHEN EXTRA = 'auto_increment' THEN '是' ELSE '否' END ) AS '是否自增',
( CASE WHEN IS_NULLABLE = 'NO' THEN '否' ELSE '是' END ) AS '能否为空',
( CASE WHEN COLUMN_KEY = 'PRI' THEN '是' ELSE '否' END ) AS '是否是主键',# extra,
COLUMN_DEFAULT AS '默认值' ,
COLUMN_COMMENT AS '约束条件与说明'
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = '数据库名称'
AND TABLE_NAME = '表名称'