mysql查询数据库的表结构，导出excel

最近需要把表结构导出给业务方；

查询整个数据库的表结构

SELECT
 TABLE_SCHEMA 数据库,
TABLE_NAME 表名,
COLUMN_NAME 字段名,
COLUMN_TYPE 字段类型,
DATA_TYPE 字段类型,
CONCAT( case   WHEN  NUMERIC_PRECISION is null THEN "" else NUMERIC_PRECISION  end   , case    WHEN CHARACTER_OCTET_LENGTH is null  then "" else CHARACTER_OCTET_LENGTH   end ) 字段长度,
IS_NULLABLE 是否允许为空,
COLUMN_COMMENT 字段说明,
COLUMN_DEFAULT 备注
FROM 
INFORMATION_SCHEMA.COLUMNS where
table_schema="databasename";

查询单个表的结构

SELECT
TABLE_NAME 表名,
COLUMN_NAME 字段名,
COLUMN_TYPE 字段类型,
DATA_TYPE 字段类型,
CONCAT( case   WHEN  NUMERIC_PRECISION is null THEN "" else NUMERIC_PRECISION  end   , case    WHEN CHARACTER_OCTET_LENGTH is null  then "" else CHARACTER_OCTET_LENGTH   end ) 字段长度,
IS_NULLABLE 是否允许为空,
COLUMN_COMMENT 字段说明,
COLUMN_DEFAULT 备注
FROM 
INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME='ss_role';