详解MySQL拼接函数CONCAT的使用心得

数据库 2024/11/23 佚名

3 2 1

前几篇文章给大家介绍了MySQL中的替换函数（Replace）、切分函数（SubString），今天我们一起来看看MySQL专业拼接“字符串”的函数：concat。老规矩，有好的建议和想法，记得写到评论中，等我上班摸鱼时，跟大家一起吃瓜~

一、concat函数相关的几种用法

1-1、函数：concat(str1,str2,…)

concat 函数一般用在SELECT 查询语法中，用于修改返回字段内容，例如有张LOL英雄信息表如下

mysql> select * from `LOL`;
+----+---------------+--------------+-------+
| id | hero_title  | hero_name  | price |
+----+---------------+--------------+-------+
| 1 | D刀锋之影   | 泰隆     | 6300 |
| 2 | X迅捷斥候   | 提莫     | 6300 |
| 3 | G光辉女郎   | 拉克丝    | 1350 |
| 4 | F发条魔灵   | 奥莉安娜   | 6300 |
| 5 | Z至高之拳   | 李青     | 6300 |
| 6 | W无极剑圣   | 易      |  450 |
| 7 | J疾风剑豪   | 亚索     |  450 |
+----+---------------+--------------+-------+
7 rows in set (0.00 sec)

我需要返回一列：英雄称号 - 英雄名称的数据，这是就用到了concat函数，如下：

SELECT CONCAT(hero_title,' - ',hero_name) as full_name, price from `LOL`;

mysql> SELECT CONCAT(hero_title,' - ',hero_name) as full_name, price from `LOL`;
+------------------------------+-------+
| full_name          | price |
+------------------------------+-------+
| D刀锋之影 - 泰隆       | 6300 |
| X迅捷斥候 - 提莫       | 6300 |
| G光辉女郎 - 拉克丝      | 1350 |
| F发条魔灵 - 奥莉安娜     | 6300 |
| Z至高之拳 - 李青       | 6300 |
| W无极剑圣 - 易        |  450 |
| J疾风剑豪 - 亚索       |  450 |
+------------------------------+-------+
7 rows in set (0.00 sec)

如果拼接的参数中有NULL，则返回NULL；如下：

SELECT CONCAT(hero_title,NULL,hero_name) as full_name, price from `LOL`;

mysql> SELECT CONCAT(hero_title,'NULL',hero_name) as full_name, price from `LOL`;
+-------------------------------+-------+
| full_name           | price |
+-------------------------------+-------+
| D刀锋之影NULL泰隆       | 6300 |
| X迅捷斥候NULL提莫       | 6300 |
| G光辉女郎NULL拉克丝      | 1350 |
| F发条魔灵NULL奥莉安娜     | 6300 |
| Z至高之拳NULL李青       | 6300 |
| W无极剑圣NULL易        |  450 |
| J疾风剑豪NULL亚索       |  450 |
+-------------------------------+-------+
7 rows in set (0.00 sec)

不好意思，上面是我弟弟写的，正确的如下：

mysql> SELECT CONCAT(hero_title,NULL,hero_name) as full_name, price from `LOL`;
+-----------+-------+
| full_name | price |
+-----------+-------+
| NULL   | 6300 |
| NULL   | 6300 |
| NULL   | 1350 |
| NULL   | 6300 |
| NULL   | 6300 |
| NULL   |  450 |
| NULL   |  450 |
+-----------+-------+
7 rows in set (0.00 sec)

1-2、函数：concat_ws(separator,str1,str2,…)

"htmlcode">

select concat_ws(',',hero_title,hero_name,price) as full_name, price from `LOL`;

mysql> select concat_ws(',',hero_title,hero_name,price) as full_name, price from `LOL`;
+---------------------------------+-------+
| full_name            | price |
+---------------------------------+-------+
| D刀锋之影,泰隆,6300       | 6300 |
| X迅捷斥候,提莫,6300       | 6300 |
| G光辉女郎,拉克丝,1350      | 1350 |
| F发条魔灵,奥莉安娜,6300     | 6300 |
| Z至高之拳,李青,6300       | 6300 |
| W无极剑圣,易,450        |  450 |
| J疾风剑豪,亚索,450       |  450 |
+---------------------------------+-------+
7 rows in set (0.00 sec)

分隔符后的拼接参数为NULL时，直接忽略，不会影响整体结果，如下：

select concat_ws(',',hero_title,NULL,hero_name) as full_name, price from `LOL`;

mysql> select concat_ws(',',hero_title,NULL,hero_name) as full_name, price from `LOL`;
+----------------------------+-------+
| full_name         | price |
+----------------------------+-------+
| D刀锋之影,泰隆       | 6300 |
| X迅捷斥候,提莫       | 6300 |
| G光辉女郎,拉克丝      | 1350 |
| F发条魔灵,奥莉安娜     | 6300 |
| Z至高之拳,李青       | 6300 |
| W无极剑圣,易        |  450 |
| J疾风剑豪,亚索       |  450 |
+----------------------------+-------+
7 rows in set (0.00 sec)

分隔符为NULL时，结果返回NULL，如下：

select concat_ws(NULL,hero_title,hero_name,price) as full_name, price from `LOL`;

mysql> select concat_ws(NULL,hero_title,hero_name,price) as full_name, price from `LOL`;
+-----------+-------+
| full_name | price |
+-----------+-------+
| NULL   | 6300 |
| NULL   | 6300 |
| NULL   | 1350 |
| NULL   | 6300 |
| NULL   | 6300 |
| NULL   |  450 |
| NULL   |  450 |
+-----------+-------+
7 rows in set (0.00 sec)

1-3、函数：group_concat(expr)

group_concat ( [DISTINCT] 字段名 [order by 排序字段 ASC/DESC] [Separator ‘分隔符'] )

"text-align: center">

又跑题了。。不好意思。

mysql> select * from `LOL`;
+----+---------------+--------------+-------+
| id | hero_title  | hero_name  | price |
+----+---------------+--------------+-------+
| 1 | D刀锋之影   | 泰隆     | 6300 |
| 2 | X迅捷斥候   | 提莫     | 6300 |
| 3 | G光辉女郎   | 拉克丝    | 1350 |
| 4 | F发条魔灵   | 奥莉安娜   | 6300 |
| 5 | Z至高之拳   | 李青     | 6300 |
| 6 | W无极剑圣   | 易      |  450 |
| 7 | J疾风剑豪   | 亚索     |  450 |
+----+---------------+--------------+-------+
7 rows in set (0.00 sec)

"htmlcode">

select * from `LOL` order by price desc;

mysql> select * from `LOL` order by price desc;
+----+---------------+--------------+-------+
| id | hero_title  | hero_name  | price |
+----+---------------+--------------+-------+
| 1 | D刀锋之影   | 泰隆     | 6300 |
| 2 | X迅捷斥候   | 提莫     | 6300 |
| 4 | F发条魔灵   | 奥莉安娜   | 6300 |
| 5 | Z至高之拳   | 李青     | 6300 |
| 3 | G光辉女郎   | 拉克丝    | 1350 |
| 6 | W无极剑圣   | 易      |  450 |
| 7 | J疾风剑豪   | 亚索     |  450 |
+----+---------------+--------------+-------+
7 rows in set (0.00 sec)

但是这样很不直观啊，我想一行都看到，怎么办？

SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price 
 from `LOL` GROUP BY price ORDER BY price desc;

这时GROUP_CONCAT函数就轻松的帮你解决了这个问题。看，舒服不~

注释：这里我是拼接了（hero_title,' - ‘,hero_name）这三个参数，分隔符设为：','，根据价格来分组，根据价格来排的序。效果如下

mysql> SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price desc;
+------------------------------------------------------------------------+-------+
| full_name                               | price |
+------------------------------------------------------------------------+-------+
| D刀锋之影 - 泰隆,X迅捷斥候 - 提莫,F发条魔灵 - 奥莉安娜,Z至高之拳 - 李青     | 6300 |
| G光辉女郎 - 拉克丝                            | 1350 |
| W无极剑圣 - 易,J疾风剑豪 - 亚索                      |  450 |
+------------------------------------------------------------------------+-------+
3 rows in set (0.00 sec)

如果按价格（price）从小到大排序，只需控制外层ORDER BY即可，如下：

SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price 
  from `LOL` GROUP BY price ORDER BY price asc;

mysql> SELECT GROUP_CONCAT(hero_title,' - ',hero_name Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc;
+-------------------------------------------------------------------------+-------+
| full_name                                | price |
+-------------------------------------------------------------------------+-------+
| W无极剑圣 - 易,J疾风剑豪 - 亚索                      |  450 |
| G光辉女郎 - 拉克丝                            | 1350 |
| D刀锋之影 - 泰隆,X迅捷斥候 - 提莫,F发条魔灵 - 奥莉安娜,Z至高之拳 - 李青     | 6300 |
+-------------------------------------------------------------------------+-------+
3 rows in set (0.00 sec)

"htmlcode">

SELECT GROUP_CONCAT(hero_title,' - ',hero_name order by hero_title Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc;

mysql> SELECT GROUP_CONCAT(hero_title,' - ',hero_name order by hero_title Separator ',' ) as full_name, price from `LOL` GROUP BY price ORDER BY price asc;
+-------------------------------------------------------------------------+-------+
| full_name                                | price |
+-------------------------------------------------------------------------+-------+
| J疾风剑豪 - 亚索,W无极剑圣 - 易                       |  450 |
| G光辉女郎 - 拉克丝                            | 1350 |
| D刀锋之影 - 泰隆,F发条魔灵 - 奥莉安娜,X迅捷斥候 - 提莫,Z至高之拳 - 李青     | 6300 |
+-------------------------------------------------------------------------+-------+
3 rows in set (0.00 sec)

好了，可以看出，MySQL中的concat拼接函数还是很好用的，希望你在有类似的需求或困惑时，想到它！或者能找到这篇博客，帮到你！

MySQL拼接函数CONCAT,MySQL,CONCAT

标签：

MySQL拼接函数CONCAT,MySQL,CONCAT

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“详解MySQL拼接函数CONCAT的使用心得”

详解MySQL拼接函数CONCAT的使用心得

暂无“详解MySQL拼接函数CONCAT的使用心得”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

详解MySQL拼接函数CONCAT的使用心得

MySQL拼接函数CONCAT,MySQL,CONCAT

详解mysql持久化统计信息

MySql主从复制实现原理及配置

评论“详解MySQL拼接函数CONCAT的使用心得”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

详解MySQL拼接函数CONCAT的使用心得

MySQL拼接函数CONCAT,MySQL,CONCAT

详解mysql持久化统计信息

MySql主从复制实现原理及配置

评论“详解MySQL拼接函数CONCAT的使用心得”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存