MySQL可直接使用的查询表的列信息(实现方案)_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 `下划线第一个位置的字母`,
    case
        when
            locate('_', column_name, 1) != 0
        then
            concat(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_name
    end as `驼峰展示`,
    data_type as `数据类型`,
    case
        when is_nullable = 'yes' then '必填'
        else '非必填'
    end as `是否必填`,
    column_comment as `描述`
from
    information_schema.columns
where
    table_name = 'tb_order';

如何查询列

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

select 
    column_name  as `数据元素`,
    data_type as `数据类型`,
    case
        when is_nullable = 'yes' then '必填'
        else '非必填'
    end as `是否必填`,
    column_comment as `描述`
from
    information_schema.columns
where
    table_name = 'tb_order';

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

如何转大写

mysql中字符转大写，可使用 ucase函数，字符转小写可使用lower函数。

select 
    column_name as `数据元素`,
    ucase(data_type) as `数据类型-大写`,
		lower(data_type) as `数据类型-小写`,
    case
        when is_nullable = 'yes' then '必填'
        else '非必填'
    end as `是否必填`,
    column_comment as `描述`
from
    information_schema.columns
where
    table_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 `数据类型`,
    case
        when is_nullable = 'yes' then '必填'
        else '非必填'
    end as `是否必填`,
    column_comment as `描述`
from
    information_schema.columns
where
    table_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 `下划线第一个位置的字母`,
    case
        when
            locate('_', column_name, 1) != 0
        then
            concat(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_name
    end as `驼峰展示`,
    data_type as `数据类型`,
    case
        when is_nullable = 'yes' then '必填'
        else '非必填'
    end as `是否必填`,
    column_comment as `描述`
from
    information_schema.columns
where
    table_name = 'tb_order';

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