基于Mysql源数据的数仓表结构整理

博客围绕MySQL数仓表结构整理展开,介绍了简单的表结构信息查看方法,结合数仓规范进行整理。最终给出可用脚本,考虑了MySQL源表分表、数仓目标表规范、字段中文名截取、字段描述特殊字符对拷贝到Excel的影响等情况。

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

1、简单的表结构信息查看

SELECT
    a1.table_name 表名,
    a1.TABLE_ROWS 记录条数,
    a1.TABLE_COMMENT 表注释,
    a2.COLUMN_NAME 列名,
    a2.COLUMN_TYPE 数据类型,
    a2.IS_NULLABLE 是否为空,
    a2.COLUMN_DEFAULT 默认值,
    a2.COLUMN_COMMENT 列备注,
    a1.CREATE_TIME 建表时间 
FROM
    information_schema.TABLES a1
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS a2 ON a1.TABLE_SCHEMA = a2.TABLE_SCHEMA 
    AND a1.TABLE_NAME = a2.TABLE_NAME 
WHERE
    a1.table_schema = 'jellyfish_user' 
    AND a1.table_name = 'user_profile_0' 
ORDER BY
    a1.TABLE_NAME ASC,
    a2.ORDINAL_POSITION ASC;

2、结合数仓规范整理

SELECT
    replace(a1.TABLE_COMMENT,' ','-') 中文表名,
    case when substring_index(a1.table_name,'_',-1)+0=substring_index(a1.table_name,'_',-1) then concat('oss_bi_all_',replace(a1.table_name,concat('_',substring_index(a1.table_name,'_',-1)),''))
       when substring_index(a1.table_name,'_',-1)+0=0 then concat('oss_bi_all_',a1.table_name)
    else null end 英文表名,
    a2.COLUMN_NAME 中文字段名,
    a2.COLUMN_NAME 英文字段名,
    a2.COLUMN_TYPE 字段类型,
    replace(replace(a2.COLUMN_COMMENT,' ',''),',',';') 字段描述,
    '' 字段算法 
FROM
    information_schema.TABLES a1
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS a2 ON a1.TABLE_SCHEMA = a2.TABLE_SCHEMA 
    AND a1.TABLE_NAME = a2.TABLE_NAME 
WHERE
    a1.table_schema = 'jellyfish_user'
    AND a1.table_name = 'user_phone_num' 
ORDER BY
    a1.TABLE_NAME ASC,
    a2.ORDINAL_POSITION ASC;


3、最终可用的脚本
最终可用考虑到如下几方面的情况:
mysql源表 分表的情况;
数仓目标表规范情况;
字段中文名从注释中截取情况;
字段描述中空格及逗号对结果数据拷贝到excel的影响情况等。

SELECT replace(a1.TABLE_COMMENT,' ','-') 中文表名,
	case when length(substring_index(a1.table_name,'_',-1)+0)=length(substring_index(a1.table_name,'_',-1)) then concat('oss_bi_all_',replace(a1.table_name,concat('_',substring_index(a1.table_name,'_',-1)),''))
       when length(substring_index(a1.table_name,'_',-1)+0)=1 then concat('oss_bi_all_',a1.table_name)
	else null end 英文表名,
	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(a2.COLUMN_COMMENT,' ',1),',',1),',',1),':',1),':',1) 中文字段名,
	a2.COLUMN_NAME 英文字段名,
	a2.COLUMN_TYPE 字段类型,
	replace(replace(a2.COLUMN_COMMENT,' ',''),',',';') 字段描述,
	'' 字段算法 
FROM
	information_schema.TABLES a1
	LEFT JOIN INFORMATION_SCHEMA.COLUMNS a2 ON a1.TABLE_SCHEMA = a2.TABLE_SCHEMA 
	AND a1.TABLE_NAME = a2.TABLE_NAME 
WHERE
	a1.table_schema = 'jellyfish_server'
	AND a1.table_name = 'pay_info' 
ORDER BY
	a1.TABLE_NAME ASC,
	a2.ORDINAL_POSITION ASC;

最终excel展示:

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值