MySQL可直接使用的查询表的列信息

背景

最近产品找来，想让帮忙出下表的信息，字段驼峰展示，每张表信息show create table全部展示，再逐个粘贴，有点太耗费时间，本篇记录快速方法，方便备查。

实现方案

第一步、确定下划线【_】的位置，可使用LOCATE函数；
第二步、找到下划线后第一个字符；
第三步、各个区域截取【下划线前的字符+下划线后的第一位要大写+剩余字符串小写】，使用CONCAT拼接。
实现效果如下所示。

模板SQL

可直接使用的模板SQL，只需要将【tb_order】改成所要统计的表即可，如下所示

SELECT COLUMN_NAME AS `数据元素`,LOCATE('_', COLUMN_NAME, 1) + 1 AS `下划线第一个位置`,UCASE(SUBSTRING(COLUMN_NAME,LOCATE('_', COLUMN_NAME, 1) + 1,1)) AS `下划线第一个位置的字母`,CASEWHENLOCATE('_', COLUMN_NAME, 1) != 0THENCONCAT(LOWER(SUBSTRING(COLUMN_NAME,1,LOCATE('_', COLUMN_NAME, 1) - 1)),UCASE(SUBSTRING(COLUMN_NAME,LOCATE('_', COLUMN_NAME, 1) + 1,1)),LOWER(SUBSTRING(COLUMN_NAME,LOCATE('_', COLUMN_NAME, 1) + 2,LENGTH(COLUMN_NAME))))ELSE COLUMN_NAMEEND AS `驼峰展示`,data_type AS `数据类型`,CASEWHEN IS_NULLABLE = 'YES' THEN '必填'ELSE '非必填'END AS `是否必填`,column_comment AS `描述`
FROMinformation_schema.COLUMNS
WHERETABLE_NAME = 'tb_order';

如何查询列

可从MySQL的系统表information_schema.COLUMNS，直接查询目标表的信息，主要如下所示。

SELECT COLUMN_NAME  AS `数据元素`,data_type AS `数据类型`,CASEWHEN IS_NULLABLE = 'YES' THEN '必填'ELSE '非必填'END AS `是否必填`,column_comment AS `描述`
FROMinformation_schema.COLUMNS
WHERETABLE_NAME = 'tb_order';

执行SQL，控制台如下所示。

如何转大写

MySQL中字符转大写，可使用 UCASE函数，字符转小写 可使用LOWER函数。

SELECT COLUMN_NAME AS `数据元素`,UCASE(data_type) AS `数据类型-大写`,LOWER(data_type) AS `数据类型-小写`,CASEWHEN IS_NULLABLE = 'YES' THEN '必填'ELSE '非必填'END AS `是否必填`,column_comment AS `描述`
FROMinformation_schema.COLUMNS
WHERETABLE_NAME = 'tb_order';

执行SQL，控制台如下所示。

如何获取字符位置

获取字符串中指定字符位置，可使用LOCATE函数。

LOCATE(sub_str, str,length)
sub_str 表示待匹配的子串
str 表示匹配的目标字符串
length 表示匹配的长度

SELECT COLUMN_NAME AS `数据元素`,LOCATE('_',COLUMN_NAME,1)+1 as `下划线第一个位置`,UCASE(SUBSTRING(COLUMN_NAME,LOCATE('_',COLUMN_NAME,1)+1,1)) AS `下划线第一个位置的字母`,data_type AS `数据类型`,CASEWHEN IS_NULLABLE = 'YES' THEN '必填'ELSE '非必填'END AS `是否必填`,column_comment AS `描述`
FROMinformation_schema.COLUMNS
WHERETABLE_NAME = 'tb_order';

执行SQL，控制台如下所示。

如何拼接字段

使用CONCAT函数连接各部分子字符串。

SELECT COLUMN_NAME AS `数据元素`,LOCATE('_', COLUMN_NAME, 1) + 1 AS `下划线第一个位置`,UCASE(SUBSTRING(COLUMN_NAME,LOCATE('_', COLUMN_NAME, 1) + 1,1)) AS `下划线第一个位置的字母`,CASEWHENLOCATE('_', COLUMN_NAME, 1) != 0THENCONCAT(LOWER(SUBSTRING(COLUMN_NAME,1,LOCATE('_', COLUMN_NAME, 1) - 1)),UCASE(SUBSTRING(COLUMN_NAME,LOCATE('_', COLUMN_NAME, 1) + 1,1)),LOWER(SUBSTRING(COLUMN_NAME,LOCATE('_', COLUMN_NAME, 1) + 2,LENGTH(COLUMN_NAME))))ELSE COLUMN_NAMEEND AS `驼峰展示`,data_type AS `数据类型`,CASEWHEN IS_NULLABLE = 'YES' THEN '必填'ELSE '非必填'END AS `是否必填`,column_comment AS `描述`
FROMinformation_schema.COLUMNS
WHERETABLE_NAME = 'tb_order';

执行SQL，控制台如下所示。

以上即SQL方式统计列信息的实现方案。

SQL适用场景

1、最多两个字符，通过下划线作为连接符，对于多个下划线的情况，case when 单独处理