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

野性酷女 2022-11-12 02:40 120阅读 0赞

# 前言 #

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

# 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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70]

# 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';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 1]

# 导出到excel 并贴到网页上 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 4]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70]: /images/20221022/8348a6a1a2014880b0e138598e4fb898.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210322135159231.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2021032212433779.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210322124530362.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20210322124545195.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZhamluZ19mZWl5dWU=,size_16,color_FFFFFF,t_70