MySQL根据表名获取数据表所有字段信息（用于导出数据字典）

MySQL根据表名获取数据表所有字段信息（用于导出数据字典）

SELECT 
   col.TABLE_NAME AS '表名',
   (SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME = col.TABLE_NAME AND TABLE_SCHEMA = '数据库名') AS '表注释',
   col.COLUMN_NAME AS '字段名',
   col.COLUMN_TYPE AS '数据类型',
   col.CHARACTER_SET_NAME AS '字符集',
   col.COLUMN_KEY AS '键',
  CASE IFNULL(col.COLUMN_DEFAULT,'Null') 
        WHEN '' THEN '空字符串' 
        WHEN 'Null' THEN 'NULL' 
        ELSE col.COLUMN_DEFAULT END  AS '默认值',
    CASE col.IS_NULLABLE WHEN 'YES' THEN '是' ELSE '否' END AS '是否允许为空',
    col.COLUMN_COMMENT AS '字段注释'
 FROM 
  information_schema.COLUMNS col
 WHERE
  col.TABLE_NAME IN (
   SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '数据库名'
  ) AND col.TABLE_SCHEMA = '数据库名'
 ORDER BY col.TABLE_NAME