如何快速制作数据词典

其实制作数据词典是一件非常麻烦费力的事情，如果有一条SQL能够帮你全都查询出来，那无疑会省力许多，今天呢我就给大家带来一条这样的SQL，源自大佬小梦想的亲笔之作。

USE information_schema;

SELECT
字段,
字段说明,
PK,
数据类型,
允许为空,
默认值
FROM
(
SELECT
CONCAT('数据表:', MAX(C.TABLE_NAME)) AS '字段',
MAX(C.TABLE_NAME) AS '表名',
MAX(T.TABLE_COMMENT) AS '字段说明',
'' AS 'PK',
'' AS '数据类型',
'' AS '允许为空',
'' AS '默认值'
FROM
information_schema. COLUMNS AS C
INNER JOIN information_schema. TABLES AS T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE
C.TABLE_SCHEMA = '替换成数据库名字'
GROUP BY
T.TABLE_NAME
UNION ALL
SELECT
MAX(C.COLUMN_NAME) AS '字段',
MAX(C.TABLE_NAME) AS '表名',
MAX(C.COLUMN_COMMENT) AS '字段说明',
MAX(C.EXTRA) AS 'PK',
MAX(C.COLUMN_TYPE) AS '数据类型',
MAX(C.IS_NULLABLE) AS '允许为空',
MAX(C.COLUMN_DEFAULT) AS '默认值'
FROM
information_schema. COLUMNS AS C
WHERE
C.TABLE_SCHEMA = '替换成数据库名字'
GROUP BY
C.TABLE_NAME ASC,
C.ORDINAL_POSITION ASC
UNION ALL
SELECT
'' AS '字段',
MAX(C.TABLE_NAME) AS '表名',
'' AS '字段说明',
'' AS 'PK',
'' AS '数据类型',
'' AS '允许为空',
'' AS '默认值'
FROM
information_schema. COLUMNS AS C
WHERE
C.TABLE_SCHEMA = '替换成数据库名字'
GROUP BY
C.TABLE_NAME
) S
ORDER BY
表名 ASC

效果图如下
转载请注明出处，侵权必究