SELECT
a.TABLE_SCHEMA 数据库,
a.TABLE_NAME 表名称,
b.TABLE_COMMENT 表说明,
a.ORDINAL_POSITION 序号,
a.COLUMN_NAME 字段名称,
CASE
WHEN a.COLUMN_KEY = 'PRI' THEN
'√'
ELSE
''
END 主键,
CASE
WHEN a.EXTRA = 'auto_increment' THEN
'√'
ELSE
''
END 标识,
a.COLUMN_TYPE 类型,
IFNULL(
a.NUMERIC_PRECISION,
a.CHARACTER_MAXIMUM_LENGTH
) 长度,
IFNULL(a.NUMERIC_SCALE, 0) 精度,
CASE
WHEN a.IS_NULLABLE = 'NO' THEN
''
ELSE
'√'
END 允许空,
a.COLUMN_DEFAULT 默认值,
a.COLUMN_COMMENT 字段说明,
CASE
WHEN a.COLUMN_KEY = 'PRI' THEN
'主键'
WHEN a.COLUMN_KEY = 'UNI' THEN
'唯一'
WHEN a.COLUMN_KEY = 'MUL' THEN
'一般'
ELSE
''
END 索引类型,
c.INDEX_NAME 索引名称
FROM
information_schema. COLUMNS a
LEFT JOIN information_schema. TABLES b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
LEFT JOIN information_schema.STATISTICS AS c ON a.TABLE_SCHEMA = c.TABLE_SCHEMA
AND a.TABLE_NAME = c.TABLE_NAME
AND a.COLUMN_NAME = c.COLUMN_NAME
WHERE
a.TABLE_SCHEMA = '数据库名称'
AND a.TABLE_NAME = '表名称'
ORDER BY
a.TABLE_NAME,
a.ORDINAL_POSITION
注意:对于多索引的未解决。如果谁解决了 ,请联系我。谢谢。