MySQL-三两奇巧

MySQL 作为国内中小企业最流行的关系型数据库, 在业务中经常会被用到。如果要说有什么奇巧, 真的不是一两篇文章能够概述的

在这里挑选了一些在实际开发中可能会经常用到的一些技巧, 简单地来说说。

01

列拼接问题

在实际项目中, 经常需要将某一列的内容拼接并返回, 特别是一些归集的报表导出的业务中。这里创建 1 张类似如下的数据表, 并插入相关的数据:

create table user (
   id int primary key auto_increment,
   username varchar(20),
   age int
);
insert into user (username,age) values ('张三',20),("李四",25),("王五",26),("李四",20);

现在我们想将 user 表中用户筛选出来进行拼接并返回。此时, 我们可以借助 MySQL 的 group_concat 函数进行拼接, 可以写出 1 条类似如下的语句:

mysql
>
 
select
 group_concat
(
username
)
 user 
from
 user
;

查询结果为:

user

张三, 李四, 王五, 李四

该语句查询出每行的用户名称, 之后将其进行拼接。然而, 由于数据表中用户李四存在 2 条记录, 因此导致最终结果出现了重复的记录。而实际开发需求中更多的是, 要求对最终的结果进行去重, 此时就用到了 1 个比较有意思的技巧。

我们稍作修改, 在 group_concat 函数的筛选的列之前添加 1 个 distinct 关键字, 此时可以得到:

mysql
>
 
select
 group_concat
(
distinct username
)
 user 
from
 user
;

结果如下所示:

user

张三, 李四, 王五

可以发现列值去重了, 得到我们期望的结果。

02

绘制简单直方图

一般业务中, 常常会遇到的 1 个需求就是对用户进行分组, 比如查询某个用户最近几个月的消费情况。这里进行简化处理, 假设只查询 user 表中用户出现的次数, 此时可以借助 group by 语句来操作:

select
    username,
    count (id) num
from
    user
group by
    username
order by
    num desc user

其结果类似如下:

username	num
李四	2
张三	1
王五	1

其中用户李四出现了 2 次, 而其他的用户只出现了 1 次。有些时候, 为了能方便的查看出差别, 一般会采用图形化的方式进行处理。此时, 可以借助 MySQL 中 repeat 函数对某个字符串重复多次的方式, 进行简单的 ASCII 图形化展示。

此时可以写出 1 个类似如下的 SQL 语句:

mysql
>
select
    b.username,
    repeat ("*", b.num) star
from
    (
        select
            username,
            count (id) num
        from
            user
        group by
            username
        order by
            num desc
    ) b

结果如下所示:

username	star
李四	**
张三	*
王五	*

这里涉及到 1 个子查询的知识点, 就不展开说明了。简单的说, 我们可以让我们的数据来自另 1 个 select 子语句, 但是需要给该子语句 1 个别名, 否则 MySQL 会抛出异常。

我们将得到的统计次数传递给 repeat 函数, 从而让其根据结果进行渲染, 从而得到 1 个简单的柱状图。如果数据量更多的话, 效果会更加明显。

03

简化表关联

在关系型数据库中, 常常会通过 2 个及多个表之间的关联来完成复杂的业务需求。对于之前的逻辑, 我们新增 1 个类似如下的关联表:

create table information (
   id integer,
   post varchar(20)
);
insert into information (id,post) values (1,'大学教授'),(2,"IT架构师"),(3,"CEO"),(4,"操盘手");

通过用户的 id 进行 2 个表之间数据的关联。现在, 我们想查询每个用户的信息, 包括其名字、年龄及职位。于是有如下的 SQL 语句:

mysql
>
 
select
    a.id,
    a.username,
    a.age,
    b.post
from
    user a
    join information b on a.id = b.id

此时可以得到如下的结果:

id	username	age	post
1	张三	20	大学教授
2	李四	25	IT 架构师
3	王五	26	CEO
4	李四	20	操盘手

4 rows in set

在 MySQL 中在关联查询时如果 2 个表中的关联字段是相同的话, 可以通过 using 子句进行简化, 我们可以将上述的 SQL 修改为:

mysql
>
 
select
    a.id,
    a.username,
    a.age,
    b.post
from
    user a
    join information b using (id);

其结果为:

id	username	age	post
1	张三	20	大学教授
2	李四	25	IT 架构师
3	王五	26	CEO
4	李四	20	操盘手

通过 using 子句我们简化了 SQL 中 on a.id=b.id这段的语句。

当然, 实际开发中还有其他一些技巧, 有时间再分享。