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

一、函数: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 set语句拼接 mysql拼接字段值_字段

不好意思,上面是我弟弟写的,正确的如下:

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)

一、函数:concat_ws(separator,str1,str2,...)

        CONCAT_WS() 函数全称:CONCAT With Separator ,是CONCAT()的特殊形式。第一个参数(separator)是其它参数的分隔符。分隔符的位置在要连接的两个字符串之间。`分隔符可以是一个字符串,也可以是其它字段参数。`

需要注意的是:

        如果分隔符为 NULL,则结果为 NULL;但如果分隔符后面的参数为NULL,只会被直接忽略掉,而不会导致结果为NULL。

        好了,我们依旧用上面的LOL表,连接各字段,以逗号分隔: 

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)

三、函数:group_concat(expr)

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

        group_concat函数通常用于有group by的查询语句,group_concat一般包含在查询返回结果字段中。

        是不是group_concat函数的公式看着还挺复杂的?我们一起看看,上方公式中 [] 括号是可选项,表示可用可不用;

  • [DISTINCT]:对拼接的参数支持去重功能;
  • [Order by]:拼接的参数支持排序功能;
  • [Separator]:这个你很熟悉了,支持自定义'分隔符',如不设置默认为无分隔符;

        好了,下面让我们来进入开心的测试环节吧~ 还是用这张LOL表,别问为什么。或许这就是青春吧!还记的那年通宵五排,大龙团的时候网吧停电了...

mysql set语句拼接 mysql拼接字段值_mysql 两个字段拼接_02

又跑题了。。不好意思。

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)

        举个场景,我们要区分出各个价格段的英雄来,如果不用group_concat的话,或许只能用order by区分了。

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)

        那么GROUP_CONCAT函数中的order by 排序怎么用?是用在了拼接字段的排序上,如根据hero_title进行排序拼接,如下:

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渣渣,让我们一起每天进步一点点。