mysql导出数据字典汇总

最新推荐文章于 2025-06-13 11:50:59 发布

逻辑喵师傅逻辑不通

最新推荐文章于 2025-06-13 11:50:59 发布

阅读量425

点赞数

CC 4.0 BY-SA版权

分类专栏： Mysql 文章标签： mysql 导出数据字典

本文链接：https://blog.csdn.net/lin252552/article/details/109072436

Mysql 专栏收录该内容

14 篇文章

订阅专栏

该查询用于获取数据库'xxx'中所有表的详细字段信息，包括表名、字段名、数据类型、长度、是否允许空值、默认值及字段注释等，帮助数据库管理和开发人员了解表结构。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
TABLE_CATALOG,
TABLE_SCHEMA as 数据库名,
TABLE_NAME as 表名,
COLUMN_NAME as 字段名,
ORDINAL_POSITION as 字段位置,
COLUMN_DEFAULT as 字段默认值,
IS_NULLABLE 是否允许空值,
DATA_TYPE as 数据类型,
CHARACTER_MAXIMUM_LENGTH as 以字符为单位的最大长度,
CHARACTER_OCTET_LENGTH as 以字节为单位的最大长度,
NUMERIC_PRECISION as 数值类型的长度,
NUMERIC_SCALE as 小数点精度,
DATETIME_PRECISION as 回的日期时间值的精度,
CHARACTER_SET_NAME as 编码方式,
COLLATION_NAME as 校对集,
COLUMN_TYPE as 字段类型,
COLUMN_KEY as 键,
EXTRA as 额外的信息例如自动递增在这里显示,
PRIVILEGES as 权限,
COLUMN_COMMENT as 字段注释,
GENERATION_EXPRESSION
FROM
    information_schema. COLUMNS
WHERE
    TABLE_SCHEMA = 'xxx'

SELECT
b.`表注释`,
    INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AS '表名',
    information_schema.`COLUMNS`.COLUMN_NAME AS '字段名',
    information_schema.`COLUMNS`.COLUMN_TYPE AS '数据类型',
		information_schema.`COLUMNS`.COLUMN_DEFAULT as 字段默认值,
		information_schema.`COLUMNS`.CHARACTER_MAXIMUM_LENGTH as 以字符为单位的最大长度,
		information_schema.`COLUMNS`.CHARACTER_OCTET_LENGTH as 以字节为单位的最大长度,
		information_schema.`COLUMNS`.NUMERIC_PRECISION as 数值类型的长度,
		information_schema.`COLUMNS`.NUMERIC_SCALE as 小数点位数,
    information_schema.`COLUMNS`.IS_NULLABLE AS '允许为空',
    information_schema.`COLUMNS`.COLUMN_COMMENT AS '字段说明' 
FROM
    information_schema.`COLUMNS` LEFT JOIN 
		(SELECT
		INFORMATION_SCHEMA.Tables.TABLE_COMMENT AS '表注释',
    INFORMATION_SCHEMA.Tables.TABLE_NAME AS '表名'
FROM
    INFORMATION_SCHEMA.Tables 
WHERE
    TABLE_SCHEMA = 'sub_test') b
		on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = b.`表名`
WHERE
    TABLE_SCHEMA = 'sub_test'