PHP笔记网

第一步：通过SQL查询出数据字典数据

PostgreSQL版

select b.表名,b.表描述,b.列名,b.类型,b.长度,b.主键约束,b.唯一约束,b.外键约束,b.可否为空,(case when c.column_name is not null then 'Y' else 'N' end) as 索引字段,b.描述
from
(select
(select relname from pg_class where oid=a.attrelid) as 表名,
(select relname ||'–'||(select description from pg_description where objoid = oid and objsubid = 0) from pg_class where oid=a.attrelid) as 表描述,
a.attname as 列名,
format_type(a.atttypid,a.atttypmod) as 类型,
(case when atttypmod-4>0 then atttypmod-4 else 0 end) as 长度,
(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else 'N' end) as 主键约束,
(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else 'N' end) as 唯一约束,
(case when (select count(*) from pg_constraint where conrelid=a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else 'N' end) as 外键约束,
(case when a.attnotnull=true then 'Y' else 'N' end) as 可否为空,
col_description(a.attrelid,a.attnum) as 描述
from pg_attribute a where attstattarget=-1 and attrelid in(select oid from pg_class where relname in (select relname as table_name from pg_class where relkind='r' and relname like '%' order by relname))
order by 表描述,attnum) b
left join
(select
	distinct
    t.relname as table_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
)c
on b.表名 = c.table_name and b.列名 = c.column_name;

MySQL版本

SELECT
	t.TABLE_SCHEMA AS '数据库名',
	t.TABLE_NAME AS '表名',
	t.COLUMN_NAME AS '字段名',
	t.COLUMN_TYPE AS '数据类型',
	CASE IFNULL(t.COLUMN_DEFAULT,'Null') 
        WHEN '' THEN '空字符串' 
        WHEN 'Null' THEN 'NULL' 
        ELSE t.COLUMN_DEFAULT END  AS '默认值',
    CASE t.IS_NULLABLE WHEN 'YES' THEN '是' ELSE '否' END AS '是否允许为空',
    t.COLUMN_COMMENT AS '字段说明'
FROM
	information_schema. COLUMNS t
WHERE
	t.TABLE_SCHEMA = 'chuangke_course'
AND t.TABLE_NAME = 'ckc_course';

第二步：导出查询结果数据到Excel文档

参考：

1. https://blog.csdn.net/xiaokui6/article/details/130825393
2. https://blog.csdn.net/qq_35144624/article/details/101560854