数据库设计文档-快速输出字段

前言

我们在写数据库文档时候，经常需要需要将表结构通过图标方式展示出来，每次定义了表结构、域字段如何快速将表结构贴出来。

postgres

1、需要展示表的字段

SELECT
	a.attname AS 字段名称,
	d.description AS 说明,
		concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as 字段类型,
	(case when a.attlen > 0 then a.attlen else a.atttypmod - 4 end) as 长度,
	ad.adsrc                               as 默认值,
	 (case when CAST(a.attnotnull as TEXT)='true' then '否' else '是' end)    as 是否为空
	  
FROM
	pg_class C  
	inner join  pg_attribute A on A .attnum > 0 AND A .attrelid = C .oid
	inner join pg_type T on A .atttypid = T .oid
	inner join pg_description d on d.objoid = A .attrelid and d.objsubid = A .attnum
  left join pg_attrdef ad    on  ad.adrelid = c.oid and ad.adnum = a.attnum
WHERE
	C .relname in ('parking_park_relation')
ORDER BY
	C .relname DESC

mysql

SELECT
    column_name AS '字段名称',
    column_comment AS '说明',
		column_type AS '字段类型',
		CHARACTER_MAXIMUM_LENGTH as '长度',
		COLUMN_DEFAULT as '默认值',
  IF
	( IS_NULLABLE = 'YES', '是', '否' ) 是否可为空,
IF
	( COLUMN_KEY = 'PRI', '是', '否' ) 是否主键
FROM
    information_schema. COLUMNS
WHERE
    TABLE_SCHEMA = 'test_mybatis'
-- 数据表名，可以不填写，查询全部表结构
AND table_name = 'sys_region';

导出到excel 并贴到网页上