MySQL中一些非常规的函数使用总结

前言

兜兜转转，因为各种各样的事情，已经一年多没有更新博客了，虽然一年过去了，但技术依旧很菜，菜就要多努力，所以还是那句话：脚踏实地，不急不躁，不以物喜，不以己悲，接着学习吧。

测试使用的mysql版本：5.7.20。

本文所涉及到的函数包括concat，concat_ws，group_concat，find_in_set，left，right，field等。

1. concat(str1,str2,...)函数

相比大家应该都使用过concat()函数吧，用来连接多个字符串，该方法比较简单：

mysql> select concat('a','-','b','-','c');
+-----------------------------+
| concat('a','-','b','-','c') |
+-----------------------------+
| a-b-c                       |
+-----------------------------+
1 row in set (0.00 sec)

不过，需要注意的是，如果参数有一个为null，则返回值直接为null。

mysql> select concat('a','-','b',null,'c');
+------------------------------+
| concat('a','-','b',null,'c') |
+------------------------------+
| NULL                         |
+------------------------------+
1 row in set (0.00 sec)

2. concat_ws(separator,str1,str2...)函数

该方法和concat有些相似，用于连接多个字符串，不过该参数用于指定字符串连接参数之间的分隔符；也就是说，当我们使用concat时，如果连接的参数比较多，并且连接的符号都是一样的，可以使用该函数。

其中，第一个参数表示分隔符。

mysql> select concat_ws('-', 'a', 'b', 'c', 'd');
+------------------------------------+
| concat_ws('-', 'a', 'b', 'c', 'd') |
+------------------------------------+
| a-b-c-d                            |
+------------------------------------+
1 row in set (0.00 sec)

如果要连接的字符串中有一个值是null的，那么不会把该null值进行连接到字符串中：

mysql> select concat_ws('-', 'a', 'b', 'c', null,'d');
+-----------------------------------------+
| concat_ws('-', 'a', 'b', 'c', null,'d') |
+-----------------------------------------+
| a-b-c-d                                 |
+-----------------------------------------+
1 row in set (0.00 sec)

分割符不能是null，而如果分隔符是null的话，那么返回值就直接为null：

mysql> select concat_ws(null, 'a', 'b', 'c', null,'d');
+------------------------------------------+
| concat_ws(null, 'a', 'b', 'c', null,'d') |
+------------------------------------------+
| NULL                                     |
+------------------------------------------+
1 row in set (0.00 sec)

3. group_concat函数

函数格式：

group_concat([DISTINCT] 字段1，字段2 [order by 排序字段] [separator '分隔符'])

将查询结果中的值连接起来，默认使用逗号作为分隔符，然后返回一个字符串结果；如果有group by操作，就表示将同一个分组中的值连接起来，也就是分组后相同行组合。

备注：

通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号；

在有group by的查询语句中，select指定的字段要么就包含在group by语句的后面，作为分组的依据，要么就包含在聚合函数中(max, min, avg, sum, count)；

以下简单举个例子，测试数据如下：

mysql> select * from student;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  1 | 刘备   | 语文   | 92    |
|  2 | 关羽   | 语文   | 89    |
|  3 | 张飞   | 语文   | 90    |
|  4 | 刘备   | 数学   | 82    |
|  5 | 关羽   | 数学   | 94    |
|  6 | 张飞   | 数学   | 71    |
|  7 | 刘备   | 英语   | 88    |
|  8 | 关羽   | 英语   | 90    |
|  9 | 张飞   | 英语   | 97    |
+----+--------+--------+-------+

我们以name分组，把name相同的打印在同一行，以逗号分隔；

mysql> select name,group_concat(score) from student group by name;
+--------+---------------------+
| name   | group_concat(score) |
+--------+---------------------+
| 关羽   | 89,94,90            |
| 刘备   | 92,82,88            |
| 张飞   | 90,71,97            |
+--------+---------------------+
3 rows in set (0.00 sec)

可以看到，根据name进行分组后的score，已经通过逗号连接在一起；如果我们不想使用逗号进行分割，比如想使用分号进行分割：

mysql> select name,group_concat(score separator ';') from student group by name;
+--------+-----------------------------------+
| name   | group_concat(score separator ';') |
+--------+-----------------------------------+
| 关羽   | 89;94;90                          |
| 刘备   | 92;82;88                          |
| 张飞   | 90;71;97                          |
+--------+-----------------------------------+
3 rows in set (0.00 sec)

接下来我们还可以对score进行排序：

mysql> select name,group_concat(score order by score desc separator ';') from student group by name;
+--------+-------------------------------------------------------+
| name   | group_concat(score order by score desc separator ';') |
+--------+-------------------------------------------------------+
| 关羽   | 94;90;89                                              |
| 刘备   | 92;88;82                                              |
| 张飞   | 97;90;71                                              |
+--------+-------------------------------------------------------+
3 rows in set (0.00 sec)

当然，我们可以拼接多个字段：

mysql> select name,group_concat(course,score) from student group by name;
+--------+----------------------------+
| name   | group_concat(course,score) |
+--------+----------------------------+
| 关羽   | 语文89,数学94,英语90       |
| 刘备   | 语文92,数学82,英语88       |
| 张飞   | 语文90,数学71,英语97       |
+--------+----------------------------+
3 rows in set (0.00 sec)

当然，group_concat同样支持concat的基础的拼接操作：

mysql> select name,group_concat(course,'-',score) from student group by name;
+--------+--------------------------------+
| name   | group_concat(course,'-',score) |
+--------+--------------------------------+
| 关羽   | 语文-89,数学-94,英语-90        |
| 刘备   | 语文-92,数学-82,英语-88        |
| 张飞   | 语文-90,数学-71,英语-97        |
+--------+--------------------------------+
3 rows in set (0.00 sec)

注意：

group_concat函数返回的字符串默认有长度限制（1024），超过最大长度就会被截断。

查看默认设置的两种方式：

mysql> select @@global.group_concat_max_len;
+-------------------------------+
| @@global.group_concat_max_len |
+-------------------------------+
|                          1024 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> show variables like "group_concat_max_len";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 1024  |
+----------------------+-------+
1 row in set (0.00 sec)

如果要修改的话，修改对应的配置项即可，不过一般作为开发，我们是没权限修改数据库的配置的。

4. find_in_set函数

函数格式：

FIND_IN_SET(str,strlist)

比如说我们有个字符串类型的变量，在表里保存的形式是1,2,3,4，然后我们要判断我们的某个参数是否在这个字符串中，就可以使用该函数。

str：要查询的字符串；

strlist：参数以逗号进行分割，比如1,2,3,4；

可以通俗的理解为将对字符串的筛选查询，转换为in的形式。

select * from table where FIND_IN_SET(id, '1,2,3,4,5');

等价于：

select * from table where id in ('1','2','3','4','5');

举个简单的例子，假如我们有一个用户表，包含角色：

mysql> select * from user_role;
+----+-----------+---------+
| id | user_name | role    |
+----+-----------+---------+
|  1 | 小红      | 1,2,3   |
|  2 | 小华      | 1,2     |
|  3 | 小明      | 2,3     |
|  4 | 小军      | 1,2,3,4 |
+----+-----------+---------+
4 rows in set (0.00 sec)

假如role字段中的1,2,3,4分别表示一种角色，那么如果我们想筛选包含某种角色的数据，那么可以通过：

mysql> select * from user_role where find_in_set('1', role);
+----+-----------+---------+
| id | user_name | role    |
+----+-----------+---------+
|  1 | 小红      | 1,2,3   |
|  2 | 小华      | 1,2     |
|  4 | 小军      | 1,2,3,4 |
+----+-----------+---------+
3 rows in set (0.00 sec)

mysql> select * from user_role where find_in_set('4', role);
+----+-----------+---------+
| id | user_name | role    |
+----+-----------+---------+
|  4 | 小军      | 1,2,3,4 |
+----+-----------+---------+
1 row in set (0.00 sec)

5. left(str, length)函数

该函数用于截取字符串指定长度的左侧部分：

str，要截取的字符串；

length，一个正整数，指定从左边返回的字符数；

如果str或者length参数为null，则直接返回null；如果length为0或者负数，返回一个空字符串；如果length大于str字符串长度，返回整个str字符串；

mysql> select left('hello world', 5);
+------------------------+
| left('hello world', 5) |
+------------------------+
| hello                  |
+------------------------+
1 row in set (0.01 sec)

mysql> select left('hello world', null);
+---------------------------+
| left('hello world', null) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> select left('hello world', -10);
+--------------------------+
| left('hello world', -10) |
+--------------------------+
|                          |
+--------------------------+
1 row in set (0.00 sec)

mysql> select left('hello world', 100);
+--------------------------+
| left('hello world', 100) |
+--------------------------+
| hello world              |
+--------------------------+
1 row in set (0.00 sec)

6. right(str, length)函数

和left方法相反，该方法用于截取字符串指定长度的右侧部分。规则和left方法差不多。

mysql> select right('hello world', 5);
+-------------------------+
| right('hello world', 5) |
+-------------------------+
| world                   |
+-------------------------+
1 row in set (0.00 sec)

7. field函数

格式：FIELD(str, str1, str2, str3,...)，用于对sql结果进行指定顺序排序：

字段str按照给定字符串str1，str2，str3进行排序返回；如果str中字段值不在给定字符串中，则这部分数据将排在结果集最前面；如果这部分str值相同，则按照主键进行升序排序；

还是以student表为例：

mysql> select * from student;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  1 | 刘备   | 语文   | 92    |
|  2 | 关羽   | 语文   | 89    |
|  3 | 张飞   | 语文   | 90    |
|  4 | 刘备   | 数学   | 82    |
|  5 | 关羽   | 数学   | 94    |
|  6 | 张飞   | 数学   | 71    |
|  7 | 刘备   | 英语   | 88    |
|  8 | 关羽   | 英语   | 90    |
|  9 | 张飞   | 英语   | 97    |
+----+--------+--------+-------+
9 rows in set (0.00 sec)

进行排序：

mysql> select * from student order by field(course, '数学','语文') asc;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  7 | 刘备   | 英语   | 88    |
|  8 | 关羽   | 英语   | 90    |
|  9 | 张飞   | 英语   | 97    |
|  4 | 刘备   | 数学   | 82    |
|  5 | 关羽   | 数学   | 94    |
|  6 | 张飞   | 数学   | 71    |
|  1 | 刘备   | 语文   | 92    |
|  2 | 关羽   | 语文   | 89    |
|  3 | 张飞   | 语文   | 90    |
+----+--------+--------+-------+
9 rows in set (0.01 sec)

其他case：

mysql> select * from student order by field(course, '数学','语文','英语') asc;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  4 | 刘备   | 数学   | 82    |
|  5 | 关羽   | 数学   | 94    |
|  6 | 张飞   | 数学   | 71    |
|  1 | 刘备   | 语文   | 92    |
|  2 | 关羽   | 语文   | 89    |
|  3 | 张飞   | 语文   | 90    |
|  7 | 刘备   | 英语   | 88    |
|  8 | 关羽   | 英语   | 90    |
|  9 | 张飞   | 英语   | 97    |
+----+--------+--------+-------+
9 rows in set (0.00 sec)

降序时需要注意下：

降序时，按照str3，str2，str1的顺序逆向排序，不在给定字符串列表中的数据排到最后；相同的默认按照主键进行升序排序；

mysql> select * from student order by field(course, '语文','英语') desc;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  7 | 刘备   | 英语   | 88    |
|  8 | 关羽   | 英语   | 90    |
|  9 | 张飞   | 英语   | 97    |
|  1 | 刘备   | 语文   | 92    |
|  2 | 关羽   | 语文   | 89    |
|  3 | 张飞   | 语文   | 90    |
|  4 | 刘备   | 数学   | 82    |
|  5 | 关羽   | 数学   | 94    |
|  6 | 张飞   | 数学   | 71    |
+----+--------+--------+-------+
9 rows in set (0.00 sec)