MySql数据库设计表、字段、索引输出
在数据治理过程中,时常需要输出资源目录表格。
SQL代码
# 导出数据库结构
SELECT
b.TABLE_COMMENT 表中文名,
a.table_name 表名,
a.COLUMN_NAME AS 列名,
-- DATA_TYPE AS 数据类型,
a.COLUMN_TYPE AS 数据类型,
-- CHARACTER_MAXIMUM_LENGTH AS 长度,
( CASE WHEN is_nullable = 'NO' THEN '否' ELSE '是' END ) AS 是否可空,
( CASE WHEN column_key = 'PRI' THEN '是' ELSE '否' END ) AS 是否主键,
if(c.NON_UNIQUE=0,'唯一键',if(c.NON_UNIQUE=1,'普通索引',null)) as 索引,
right(c.INDEX_NAME,13) as 索引名称,
a.COLUMN_COMMENT AS 说明
FROM
INFORMATION_SCHEMA.COLUMNS a
left join INFORMATION_SCHEMA.TABLES b
on a.table_name=b.table_name and a.table_schema=b.table_schema
left join information_schema.STATISTICS c
on a.table_name=c.table_name and a.table_schema=c.table_schema and a.COLUMN_NAME=c.COLUMN_NAME
WHERE
-- 库名
a.table_schema = 'well_logging_data' and a.table_name not like '%_DH_YS_%' ORDER BY a.TABLE_NAME ;