MySQL中一些非常規(guī)的函數(shù)使用總結(jié)

前言

兜兜轉(zhuǎn)轉(zhuǎn)，因為各種各樣的事情，已經(jīng)一年多沒有更新博客了，雖然一年過去了，但技術(shù)依舊很菜，菜就要多努力，所以還是那句話：腳踏實地，不急不躁，不以物喜，不以己悲，接著學(xué)習(xí)吧。

測試使用的mysql版本：5.7.20。

  • 本文所涉及到的函數(shù)包括concat，concat_ws，group_concat，find_in_set，left，right，field等。

1. concat(str1,str2,...)函數(shù)

相比大家應(yīng)該都使用過concat()函數(shù)吧，用來連接多個字符串，該方法比較簡單：

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

不過，需要注意的是，如果參數(shù)有一個為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...)函數(shù)

該方法和concat有些相似，用于連接多個字符串，不過該參數(shù)用于指定字符串連接參數(shù)之間的分隔符；也就是說，當(dāng)我們使用concat時，如果連接的參數(shù)比較多，并且連接的符號都是一樣的，可以使用該函數(shù)。

其中，第一個參數(shù)表示分隔符。

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值進(jìn)行連接到字符串中：

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函數(shù)

函數(shù)格式：

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

將查詢結(jié)果中的值連接起來，默認(rèn)使用逗號作為分隔符，然后返回一個字符串結(jié)果；如果有g(shù)roup by操作，就表示將同一個分組中的值連接起來，也就是分組后相同行組合。

備注：

  • 通過使用distinct可以排除重復(fù)值；如果希望對結(jié)果中的值進(jìn)行排序，可以使用order by子句；separator是一個字符串值，缺省為一個逗號；
  • 在有g(shù)roup by的查詢語句中，select指定的字段要么就包含在group by語句的后面，作為分組的依據(jù)，要么就包含在聚合函數(shù)中(max, min, avg, sum, count)；

以下簡單舉個例子，測試數(shù)據(jù)如下：

mysql> select * from student;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  1 | 劉備   | 語文   | 92    |
|  2 | 關(guān)羽   | 語文   | 89    |
|  3 | 張飛   | 語文   | 90    |
|  4 | 劉備   | 數(shù)學(xué)   | 82    |
|  5 | 關(guān)羽   | 數(shù)學(xué)   | 94    |
|  6 | 張飛   | 數(shù)學(xué)   | 71    |
|  7 | 劉備   | 英語   | 88    |
|  8 | 關(guān)羽   | 英語   | 90    |
|  9 | 張飛   | 英語   | 97    |
+----+--------+--------+-------+

我們以name分組，把name相同的打印在同一行，以逗號分隔；

mysql> select name,group_concat(score) from student group by name;
+--------+---------------------+
| name   | group_concat(score) |
+--------+---------------------+
| 關(guān)羽   | 89,94,90            |
| 劉備   | 92,82,88            |
| 張飛   | 90,71,97            |
+--------+---------------------+
3 rows in set (0.00 sec)

可以看到，根據(jù)name進(jìn)行分組后的score，已經(jīng)通過逗號連接在一起；如果我們不想使用逗號進(jìn)行分割，比如想使用分號進(jìn)行分割：

mysql> select name,group_concat(score separator ';') from student group by name;
+--------+-----------------------------------+
| name   | group_concat(score separator ';') |
+--------+-----------------------------------+
| 關(guān)羽   | 89;94;90                          |
| 劉備   | 92;82;88                          |
| 張飛   | 90;71;97                          |
+--------+-----------------------------------+
3 rows in set (0.00 sec)

接下來我們還可以對score進(jìn)行排序：

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 ';') |
+--------+-------------------------------------------------------+
| 關(guān)羽   | 94;90;89                                              |
| 劉備   | 92;88;82                                              |
| 張飛   | 97;90;71                                              |
+--------+-------------------------------------------------------+
3 rows in set (0.00 sec)

當(dāng)然，我們可以拼接多個字段：

mysql> select name,group_concat(course,score) from student group by name;
+--------+----------------------------+
| name   | group_concat(course,score) |
+--------+----------------------------+
| 關(guān)羽   | 語文89,數(shù)學(xué)94,英語90       |
| 劉備   | 語文92,數(shù)學(xué)82,英語88       |
| 張飛   | 語文90,數(shù)學(xué)71,英語97       |
+--------+----------------------------+
3 rows in set (0.00 sec)

當(dāng)然，group_concat同樣支持concat的基礎(chǔ)的拼接操作：

mysql> select name,group_concat(course,'-',score) from student group by name;
+--------+--------------------------------+
| name   | group_concat(course,'-',score) |
+--------+--------------------------------+
| 關(guān)羽   | 語文-89,數(shù)學(xué)-94,英語-90        |
| 劉備   | 語文-92,數(shù)學(xué)-82,英語-88        |
| 張飛   | 語文-90,數(shù)學(xué)-71,英語-97        |
+--------+--------------------------------+
3 rows in set (0.00 sec)

注意：

group_concat函數(shù)返回的字符串默認(rèn)有長度限制（1024），超過最大長度就會被截斷。

查看默認(rèn)設(shè)置的兩種方式：

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)

如果要修改的話，修改對應(yīng)的配置項即可，不過一般作為開發(fā)，我們是沒權(quán)限修改數(shù)據(jù)庫的配置的。

4. find_in_set函數(shù)

函數(shù)格式：

FIND_IN_SET(str,strlist)

比如說我們有個字符串類型的變量，在表里保存的形式是1,2,3,4，然后我們要判斷我們的某個參數(shù)是否在這個字符串中，就可以使用該函數(shù)。

str：要查詢的字符串；

strlist：參數(shù)以逗號進(jìn)行分割，比如1,2,3,4；

可以通俗的理解為將對字符串的篩選查詢，轉(zhuǎn)換為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分別表示一種角色，那么如果我們想篩選包含某種角色的數(shù)據(jù)，那么可以通過：

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)函數(shù)

該函數(shù)用于截取字符串指定長度的左側(cè)部分：

  • str，要截取的字符串；
  • length，一個正整數(shù)，指定從左邊返回的字符數(shù)；
  • 如果str或者length參數(shù)為null，則直接返回null；如果length為0或者負(fù)數(shù)，返回一個空字符串；如果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)函數(shù)

和left方法相反，該方法用于截取字符串指定長度的右側(cè)部分。規(guī)則和left方法差不多。

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

7. field函數(shù)

格式：FIELD(str, str1, str2, str3,...)，用于對sql結(jié)果進(jìn)行指定順序排序：

字段str按照給定字符串str1，str2，str3進(jìn)行排序返回；如果str中字段值不在給定字符串中，則這部分?jǐn)?shù)據(jù)將排在結(jié)果集最前面；如果這部分str值相同，則按照主鍵進(jìn)行升序排序；

還是以student表為例：

mysql> select * from student;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  1 | 劉備   | 語文   | 92    |
|  2 | 關(guān)羽   | 語文   | 89    |
|  3 | 張飛   | 語文   | 90    |
|  4 | 劉備   | 數(shù)學(xué)   | 82    |
|  5 | 關(guān)羽   | 數(shù)學(xué)   | 94    |
|  6 | 張飛   | 數(shù)學(xué)   | 71    |
|  7 | 劉備   | 英語   | 88    |
|  8 | 關(guān)羽   | 英語   | 90    |
|  9 | 張飛   | 英語   | 97    |
+----+--------+--------+-------+
9 rows in set (0.00 sec)

進(jìn)行排序：

mysql> select * from student order by field(course, '數(shù)學(xué)','語文') asc;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  7 | 劉備   | 英語   | 88    |
|  8 | 關(guān)羽   | 英語   | 90    |
|  9 | 張飛   | 英語   | 97    |
|  4 | 劉備   | 數(shù)學(xué)   | 82    |
|  5 | 關(guān)羽   | 數(shù)學(xué)   | 94    |
|  6 | 張飛   | 數(shù)學(xué)   | 71    |
|  1 | 劉備   | 語文   | 92    |
|  2 | 關(guān)羽   | 語文   | 89    |
|  3 | 張飛   | 語文   | 90    |
+----+--------+--------+-------+
9 rows in set (0.01 sec)

其他case：

mysql> select * from student order by field(course, '數(shù)學(xué)','語文','英語') asc;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  4 | 劉備   | 數(shù)學(xué)   | 82    |
|  5 | 關(guān)羽   | 數(shù)學(xué)   | 94    |
|  6 | 張飛   | 數(shù)學(xué)   | 71    |
|  1 | 劉備   | 語文   | 92    |
|  2 | 關(guān)羽   | 語文   | 89    |
|  3 | 張飛   | 語文   | 90    |
|  7 | 劉備   | 英語   | 88    |
|  8 | 關(guān)羽   | 英語   | 90    |
|  9 | 張飛   | 英語   | 97    |
+----+--------+--------+-------+
9 rows in set (0.00 sec)

降序時需要注意下：

降序時，按照str3，str2，str1的順序逆向排序，不在給定字符串列表中的數(shù)據(jù)排到最后；相同的默認(rèn)按照主鍵進(jìn)行升序排序；

mysql> select * from student order by field(course, '語文','英語') desc;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  7 | 劉備   | 英語   | 88    |
|  8 | 關(guān)羽   | 英語   | 90    |
|  9 | 張飛   | 英語   | 97    |
|  1 | 劉備   | 語文   | 92    |
|  2 | 關(guān)羽   | 語文   | 89    |
|  3 | 張飛   | 語文   | 90    |
|  4 | 劉備   | 數(shù)學(xué)   | 82    |
|  5 | 關(guān)羽   | 數(shù)學(xué)   | 94    |
|  6 | 張飛   | 數(shù)學(xué)   | 71    |
+----+--------+--------+-------+
9 rows in set (0.00 sec)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容