mysql 拼接字符串(concat函数&&concat_ws函数)
MySQL的查询结果行字段拼接,可以用下面两个函数实现:
--------------------------------------------------------------------------------------
MySQL的查询结果行字段拼接,可以用下面两个函数实现:
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
1. concat函数
--------------------------------------------------------------------------------------
[select concat('你','好','吗?','朋','友') as content from test ;
02.+---------------------+
03.| content |
04.+---------------------+
05.| 你好吗?朋友 |
06.+---------------------+
如果连接串中存在NULL,则返回结果为NULL:
select concat('你','好',NULL,'吗?','朋','友') as content from test ;
02.+--------------------------+
03.| content |
04.+--------------------------+
05.| NULL |
06.+--------------------------+
--------------------------------------------------------------------------------------
2. concat_ws函数
--------------------------------------------------------------------------------------
concat(separator,str1,str2,...) 代表 concat with separator ,是concat()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。
select concat_ws(':','你','好','吗?','朋','友') as content from test ;
02.+----------------------------+
03.| content |
04.+----------------------------+
05.| 你:好:吗?:朋:友 |
06.+----------------------------+
分隔符为NULL,则返回结果为NULL:
select concat_ws(NULL,'你','好','吗?','朋','友') as content from test;
02.+-----------------------------+
03.| content |
04.+-----------------------------+
05.| NULL |
06.+-----------------------------+
如果参数中存在NULL,则会被忽略:
select concat_ws(':','你','好',NULL,NULL,NULL,'吗?','朋','友') as content from test ;
02.+-------------------------------------------+
03.| content |
04.+-------------------------------------------+
05.| 你:好:吗?:朋:友 |
06.+-------------------------------------------+
可以对NULL进行判断,并用其它值进行替换:
select concat_ws(':','你','好',ifNULL(NULL,'0'),'吗?','朋','友') as content from bank limit 1;
02.+---------------------------------------------+
03.| content |
04.+---------------------------------------------+
05.| 你:好:0:吗?:朋:友 |
06.+---------------------------------------------+