mysql语句里生成字典_通过sql语句生成MySQl数据字典

生成整个数据库的数据字典

通过一天的学习，下载了好多的数据字典生成工具都用不成，最后只得通过sql语句来实现了，现在分享出来，希望大家多多指点。

通过下面sql语句生成的数据字典包含主键、外键、索引。

select distinct a.TABLE_SCHEMA as '数据库' ,

a.TABLE_NAME as '表名',

a.COLUMN_NAME as '字段名',

a.COLUMN_TYPE as '类型长度',

case when a.IS_NULLABLE='yes' then '√'else '' end as '允许空值',

case when a.COLUMN_DEFAULT='' then '""' else a.COLUMN_DEFAULT end  as '默认值',

a.CHARACTER_SET_NAME as '字符集',

a.COLLATION_NAME as '整理',

a.EXTRA as '自动递增',

case when b.CONSTRAINT_NAME='PRIMARY'  then 'PRIMARY' else '' end  as '主键',

c.CONSTRAINT_NAME  as '外键名',

c.REFERENCED_TABLE_NAME as '关联父表',

c.REFERENCED_COLUMN_NAME as '父表字段',

d. CONSTRAINT_NAME as '索引名称'

from INFORMATION_SCHEMA.COLUMNS as a

left join (select CONSTRAINT_NAME,TABLE_NAME table_name2,COLUMN_NAME col_name2 from INFORMATION_SCHEMA .KEY_COLUMN_USAGE where  CONSTRAINT_NAME='PRIMARY' and table_name like 'idsm%') as b

on a.TABLE_NAME=b.table_name2 and a.COLUMN_NAME=b.col_name2

left join (select CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,TABLE_NAME table_name3,COLUMN_NAME col_name3 from INFORMATION_SCHEMA .KEY_COLUMN_USAGE where REFERENCED_COLUMN_NAME!='' and table_name like 'idsm%') as c

on a.TABLE_NAME=c.table_name3 and a.COLUMN_NAME=c.col_name3

left join (select CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,TABLE_NAME table_name4,COLUMN_NAME col_name4 from INFORMATION_SCHEMA .KEY_COLUMN_USAGE where REFERENCED_COLUMN_NAME is null and CONSTRAINT_NAME!='PRIMARY' and table_name like 'idsm%') as d

on a.TABLE_NAME=d.table_name4 and a.COLUMN_NAME=d.col_name4

where table_name like 'idsm%'

注意：

(一)表中的字段名有可能重复

原因如下：

1、表中有的字段它既是主键又是外键

2、表中有的字段与其它的字段联合创建了唯一索引

3、表中有的字段与其它的字段联合创建了外键

(二)关于索引

表中的索引指的是唯一索引，normal类型的索引不能查出

生成单张表的数据字典

select distinct

a.COLUMN_NAME as '字段名称',

a.COLUMN_COMMENT as '字段含义',

a.COLUMN_TYPE as '类型长度',

a.IS_NULLABLE as '允许空值',

case when b.CONSTRAINT_NAME='PRIMARY' then '主键' else '' end as '注'

from INFORMATION_SCHEMA.COLUMNS as a

left join INFORMATION_SCHEMA .KEY_COLUMN_USAGE as b

on a.COLUMN_NAME = b.COLUMN_NAME

and a.table_name = b.table_name

and b.CONSTRAINT_NAME='PRIMARY'

where a.table_name like ‘xxxxxx’

注：

将上述sql语句，最后一句的“xxxxxx”换成需要生成数据字典的表的名称即可。