MySQL三两奇巧

IT服务圈儿

有温度、有态度的IT自媒体平台

作者 | 玉面玲珑颜如玉

出品 | 脚本之家（ID：jb51net）

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

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

01列拼接问题

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

1. create table user (

2. id int primary key auto_increment,

3. username varchar(20),

4. age int

5. );

6. insert into user (username,age) values ('张三',20),("李四",25),("王五",26),("李四",20);

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

1. mysql> select group_concat(username) user from user;

查询结果为:

user

张三,李四,王五,李四

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

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

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

结果如下所示:

user

张三,李四,王五

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

02绘制简单直方图

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

1. 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语句:

1. 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个类似如下的关联表:

1. create table information (

2. id integer,

3. post varchar(20)

4. );

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

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

1. 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修改为:

1. 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这段的语句。

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

本文作者：本人笔名玉面玲珑颜如玉,1个多年滚打于Web开发的研发工程师。熟悉PHP、Java、C++等编程语言,以编程作为乐趣。

声明：本文为 脚本之家专栏作者 投稿，未经允许请勿转载。

*版权声明：转载文章和图片均来自公开网络，版权归作者本人所有，推送文章除非无法确认，我们都会注明作者和来源。如果出处有误或侵犯到原作者权益，请与我们联系删除或授权事宜。