MySQL concat、concat_ws、group_concat 用法

秒速五厘米 2023-10-06 22:18 28阅读 0赞

#### 阅读目录 ####

*  一、concat() 函数
 *   *  举例1
     *  举例2
 *  二、concat\_ws() 函数
 *   *  举例
 *  三、group\_concat() 函数
 *   *  举例1
     *  举例2
     *  示例
     *   *  测试数据
         *  1. 查询所有学生的选课信息
         *  2 使用 GROUP\_CONCAT() 函数将结果分组归集
         *  3 若要将第一条记录中 courseId 按照从小到大的方式归集，则可以在使用 GROUP\_CONCAT() 时加上 ORDER BY
         *  4 改变分隔符
 *  数据库
 *  案例
 *   *  1 MySQL 中某个字段前或后添加字符
     *  2 MySQL 中批量替换某个字段的值：replace
     *  3 substring(a,b,c) 截取字符串
     *  4 MySQL 删除最后一个字符
 *  项目案例

## 一、concat() 函数 ##

1、功能：将多个字符串连接成一个字符串。

2、语法：concat(str1, str2,…)

返回结果为连接参数产生的字符串，如果有任何一个参数为 null，则返回值为 null。

### 举例1 ###

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_20_color_FFFFFF_t_70_g_se_x_16]  
`select CONCAT(id,name,email) as userinfo from users;`

![在这里插入图片描述][7ec4916aab8b40f182208c264cff130c.png]  
上面有一行为 null 是因为 users 表中有一行的 email 值为 null。

### 举例2 ###

在例1的结果中三个字段 id，name，email 的组合没有分隔符，我们可以加一个逗号作为分隔符。

`select CONCAT(id,',',name,',',email) as userinfo from users;`

![在这里插入图片描述][7de19e2a87a34e55aaa9635ddc630e59.png]

## 二、concat\_ws() 函数 ##

1、功能：和 concat() 一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符～（concat\_ws 就是 concat with separator）

2、语法：concat\_ws(separator, str1, str2, …)

说明：第一个参数指定分隔符。需要注意的是分隔符不能为 null，如果为 null，则返回结果为 null。

### 举例 ###

使用 concat\_ws() 将 分隔符指定为逗号。

`select CONCAT_WS(',',id,name,email) as userinfo from users;`  
![在这里插入图片描述][63e84607a45f4cf998f1f374ac912645.png]  
把分隔符指定为 null，结果全部变成了 null。

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_17_color_FFFFFF_t_70_g_se_x_16]

## 三、group\_concat() 函数 ##

前言：

在有 group by 的查询语句中，select 指定的字段要么就包含在 group by 语句的后面，作为分组的依据，要么就包含在聚合函数中。

### 举例1 ###

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_14_color_FFFFFF_t_70_g_se_x_16]  
该例查询了name 相同的的人中最小的 id。如果我们要查询 name 相同的人的所有的id呢？

当然我们可以这样查询：

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_13_color_FFFFFF_t_70_g_se_x_16]  
但是这样同一个名字出现多次，看上去非常不直观。

有没有更直观的方法，既让每个名字都只出现一次，又能够显示所有的名字相同的人的 id呢？ ——使用 group\_concat()

1、功能：将 group by 产生的同一个分组中的值连接起来，返回一个字符串结果。

2、语法：

group_concat([DISTINCT] 要连接的字段 
    			 [Order BY ASC/DESC 排序字段] 
    			 [Separator '分隔符'])

说明：

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

### 举例2 ###

使用 group\_concat() 和 group by 显示相同名字的人的 id 号：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_17_color_FFFFFF_t_70_g_se_x_16 1]  
将上面的 id 号从大到小排序，且用 ‘\_’ 作为分隔符：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_20_color_FFFFFF_t_70_g_se_x_16 1]  
上面的查询中显示了以 name 分组的每组中所有的 id。接下来我们要查询以 name 分组的所有组的 id 和 email：

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_20_color_FFFFFF_t_70_g_se_x_16 2]

### 示例 ###

GROUP_CONCAT([DISTINCT] expr [,expr ...]
    
                 [ORDER BY {unsigned_integer | col_name | expr}
    
                     [ASC | DESC] [,col_name ...]]
    
                 [SEPARATOR str_val])

#### 测试数据 ####

![在这里插入图片描述][730a1fc17c6d48d0a864056a88b0000f.png]

#### 1. 查询所有学生的选课信息 ####

SELECT
        s.stu_id         AS  studentId,
        s.stu_name       AS  studentName,
        c.course_id      AS  courseId,
        c.course_name    AS  studentCourse
    FROM
        student s
    LEFT JOIN
        stu_course sc
    ON
        s.stu_id = sc.stu_id
    LEFT JOIN
        course c
    ON
        sc.course_id = c.course_id

![在这里插入图片描述][87e6184316a34b1892d8471b8405ef23.png]

#### 2 使用 GROUP\_CONCAT() 函数将结果分组归集 ####

SELECT
        s.stu_id       AS  studentId,
        s.stu_name     AS  studentName,
        GROUP_CONCAT(c.course_id)    AS  courseId,
        GROUP_CONCAT(c.course_name)  AS  studentCourse
    FROM
        student s
    LEFT JOIN
        stu_course sc
    ON
        s.stu_id = sc.stu_id
    LEFT JOIN
        course c
    ON
        sc.course_id = c.course_id
    GROUP BY
        studentId

![在这里插入图片描述][8db1fdf2724c42d8aa57c2e640ad2517.png]  
使用 GROUP\_CONCAT 时会将数值类型的数据转化成二进制 BLOB类 型，可以用`CAST(expr AS type)` 函数或 `CONVERT(expr, type)` 函数将数值类型的数据转化成字符串:

SELECT
        s.stu_id          AS  studentId,
        s.stu_name     AS  studentName,
        GROUP_CONCAT(CAST(c.course_id AS CHAR))     AS  courseId,
        GROUP_CONCAT(c.course_name)  AS  studentCourse
    FROM
        student s
    LEFT JOIN
        stu_course sc
    ON
        s.stu_id = sc.stu_id
    LEFT JOIN
        course c
    ON
        sc.course_id = c.course_id
    GROUP BY
        studentId

![在这里插入图片描述][ed78075d87f740efbed090d6f9a6667b.png]

#### 3 若要将第一条记录中 courseId 按照从小到大的方式归集，则可以在使用 GROUP\_CONCAT() 时加上 ORDER BY ####

SELECT
        s.stu_id          AS  studentId,
        s.stu_name     AS  studentName,
        GROUP_CONCAT(CAST(c.course_id AS CHAR) ORDER BY c.course_id) AS courseId,
        GROUP_CONCAT(c.course_name)  AS  studentCourse
    FROM
        student s
    LEFT JOIN
        stu_course sc
    ON
        s.stu_id = sc.stu_id
    LEFT JOIN
        course c
    ON
        sc.course_id = c.course_id
    GROUP BY
        studentId

![在这里插入图片描述][6744cffb813d4011b2b026bdbe34ef2e.png]  
但我们发现，虽然 courseId 这一列的数据按照从小到大的顺序排序了，但另一列studentCourse 对应的数据却并没有跟着变，这也算是 GROUP\_CONCAT 的一个缺点了。若有人知晓如何让另一列也跟着变，还请不吝告知。

#### 4 改变分隔符 ####

GROUP\_CONCAT 默认的分隔符是逗号（”,”），若想换成其他分隔符，可以用 SEPARATOR 关键字：

SELECT
        s.stu_id       AS  studentId,
        s.stu_name     AS  studentName,
        GROUP_CONCAT(CAST(c.course_id AS CHAR) ORDER BY c.course_id SEPARATOR '/')    AS  courseId,
        GROUP_CONCAT(c.course_name SEPARATOR '|')   AS  studentCourse
    FROM
        student s
    LEFT JOIN
        stu_course sc
    ON
        s.stu_id = sc.stu_id
    LEFT JOIN
        course c
    ON
        sc.course_id = c.course_id
    GROUP BY
        studentId

![在这里插入图片描述][b81aa39d665b48d79ac91194675b794b.png]

## 数据库 ##

CREATE TABLE `users` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `email` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
      `email_verified_at` timestamp NULL DEFAULT NULL,
      `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      `deleted_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=50078 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
    INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES ('77', '', NULL, NULL, '', NULL, NULL, NULL, NULL);
    INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES ('5577', '', '55@qq.com', NULL, '', NULL, NULL, NULL, NULL);
    INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES ('50010', 'willem', '11@qq.com', NULL, '', NULL, NULL, NULL, '2022-04-07 14:03:11');
    INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES ('50012', 'wgchen', '22@qq.com', NULL, '', NULL, NULL, NULL, '2022-04-07 14:03:16');
    INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES ('50018', 'ycc', '33@qq.com', NULL, '', NULL, NULL, NULL, '2022-04-07 14:03:23');
    INSERT INTO `users` (`id`, `name`, `email`, `email_verified_at`, `password`, `remember_token`, `created_at`, `updated_at`, `deleted_at`) VALUES ('50077', '77', '', NULL, '', NULL, NULL, NULL, '2022-04-07 14:06:39');

## 案例 ##

### 1 MySQL 中某个字段前或后添加字符 ###

UPDATE optional_product
    SET cate_path = concat('|',cate_path,'|')

![在这里插入图片描述][91719328c92548899a53ff9495a8def9.png]

### 2 MySQL 中批量替换某个字段的值：replace ###

例如我们有一个表：`mxd_goods` 中字段 `goods_pic` 的值要全部去掉 `goods/`。

![在这里插入图片描述][d1886844ed3b4dfba84170500e1b7054.png]  
现在要将该表中的所有 `goods_pic` 字段的值去掉 `goods/` 。

update  mxd_goods set goods_pic= replace(goods_pic, 'goods/', '');

注：`goods_pic` 是表字段值，`goods/` 是被替换的值，即搜索的值，‘’是替换后的值，此处替换为空，意思是去掉了 `goods/`。

一定要加 `where id =数字` ，否则将是全表修改哦。

![在这里插入图片描述][11c89c786392430f91a6a664e93ffcf3.png]  
怎么撤销呢？

UPDATE mxd_goods
    SET goods_pic = REPLACE (goods_pic, 'g_', 'goods/g_')
    WHERE
    	goods_id = 1849;

### 3 substring(a,b,c) 截取字符串 ###

`substring(a,b,c):` 表示截取，其中第一个参数a表示被截取的参数对象，第二个参数b表示从哪个位置开始截取，第三个参数c表示要截取的长度

举例如下：

SELECT  SUBSTRING("hello",2,2);

表示从字符串 ’hello’ 中的 ’e’ 字符开始截取两个字符

运行结果如下:

![在这里插入图片描述][069d1133101944b9b98fe522291cdc08.png]

### 4 MySQL 删除最后一个字符 ###

使用 SUBSTRING 的任何解决方案只会通过删除最后一个字符来显示字段的内容.它实际上不会更新列的内容.如果这是你想要的(即使用 SELECT), 那么 SUBSTRING 就足够了.

但是,如果要实际更新列的值,可以尝试以下操作:

UPDATE <table_name>
    SET <column_name> = CONCAT(LEFT(<column_name>, CHAR_LENGTH(<column_name>) -1), '')
    WHERE <condition>;

SELECT
    	SUBSTR(
    		title,
    		1,
    		CHAR_LENGTH(title) - 1
    	)
    FROM
    	hhir_crm_email_send_content
    WHERE
    	id = 14;

![在这里插入图片描述][eaf26d937673431288006d119640e168.png]

## 项目案例 ##

SELECT
    	`record`.`id`,
    	`record`.`fund_name`,
    	`record`.`report_type`,
    	`record`.`number`,
    	`record`.`year`,
    	`record`.`quarter`,
    	`record`.`notice_time`,
    	`record`.`customer_sub_name`,
    	`record`.`legal_subject_name`,
    	`record`.`send_status`,
    	`record`.`created_at`,
    	`report_send`.`report_name`,
    	`send_content`.`title` AS `email_title`,
    	GROUP_CONCAT(
    			`contact_link`.`contact_name`,
    			concat('(', `contact_link`.`contact_email`, ');') SEPARATOR ''
    		) AS contacts
    FROM
    	`hhir_crm_email_record` AS `record`
    LEFT JOIN `hhir_crm_email_report_send` AS `report_send` ON `report_send`.`email_record_id` = `record`.`id`
    AND `report_send`.`valid` = 1
    LEFT JOIN `hhir_crm_email_send_content` AS `send_content` ON `send_content`.`email_record_id` = `record`.`id`
    AND `send_content`.`valid` = 1
    LEFT JOIN `hhir_crm_email_contact_link` AS `contact_link` ON `contact_link`.`email_record_id` = `record`.`id`
    AND `contact_link`.`valid` = 1
    WHERE
    	`record`.`valid` = 1
    GROUP BY `record`.`id`

![在这里插入图片描述][3b4b6f3d050a467b85648369a5f7ffc8.png]

SELECT
    	id,
    	GROUP_CONCAT(
    		title,
    		concat('(', cc_emails, ');') SEPARATOR ''
    	) AS email_title
    FROM
    	hhir_crm_email_send_content
    WHERE
    	id IN (12, 13)

![在这里插入图片描述][800e99d5fabc4cf3b4e7c7c287428be2.png]

![在这里插入图片描述][4677e09bfcf1402d93163df76d98ffcf.png]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/4e75522159d04a42a747a79f26e0cfbc.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAd2lsbGVtX3dn,size_20,color_FFFFFF,t_70,g_se,x_16
[7ec4916aab8b40f182208c264cff130c.png]: https://img-blog.csdnimg.cn/7ec4916aab8b40f182208c264cff130c.png
[7de19e2a87a34e55aaa9635ddc630e59.png]: https://img-blog.csdnimg.cn/7de19e2a87a34e55aaa9635ddc630e59.png
[63e84607a45f4cf998f1f374ac912645.png]: https://img-blog.csdnimg.cn/63e84607a45f4cf998f1f374ac912645.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_17_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/3b84f9c71c7e4935a426388d44a59b2b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAd2lsbGVtX3dn,size_17,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_14_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/e997902f46e24347b274bc05e0687618.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAd2lsbGVtX3dn,size_14,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_13_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/47d0ab3f35644962ba005e497fa283b9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAd2lsbGVtX3dn,size_13,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_17_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/79c78ce136ea4cb38e1131c1ac735b0b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAd2lsbGVtX3dn,size_17,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/2be22c7298c94016885bbaea4b5c740e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAd2lsbGVtX3dn,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAd2lsbGVtX3dn_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/10de14d548b1426dab2f17c80ba0c0de.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAd2lsbGVtX3dn,size_20,color_FFFFFF,t_70,g_se,x_16
[730a1fc17c6d48d0a864056a88b0000f.png]: https://img-blog.csdnimg.cn/730a1fc17c6d48d0a864056a88b0000f.png
[87e6184316a34b1892d8471b8405ef23.png]: https://img-blog.csdnimg.cn/87e6184316a34b1892d8471b8405ef23.png
[8db1fdf2724c42d8aa57c2e640ad2517.png]: https://img-blog.csdnimg.cn/8db1fdf2724c42d8aa57c2e640ad2517.png
[ed78075d87f740efbed090d6f9a6667b.png]: https://img-blog.csdnimg.cn/ed78075d87f740efbed090d6f9a6667b.png
[6744cffb813d4011b2b026bdbe34ef2e.png]: https://img-blog.csdnimg.cn/6744cffb813d4011b2b026bdbe34ef2e.png
[b81aa39d665b48d79ac91194675b794b.png]: https://img-blog.csdnimg.cn/b81aa39d665b48d79ac91194675b794b.png
[91719328c92548899a53ff9495a8def9.png]: https://img-blog.csdnimg.cn/91719328c92548899a53ff9495a8def9.png
[d1886844ed3b4dfba84170500e1b7054.png]: https://img-blog.csdnimg.cn/d1886844ed3b4dfba84170500e1b7054.png
[11c89c786392430f91a6a664e93ffcf3.png]: https://img-blog.csdnimg.cn/11c89c786392430f91a6a664e93ffcf3.png
[069d1133101944b9b98fe522291cdc08.png]: https://img-blog.csdnimg.cn/069d1133101944b9b98fe522291cdc08.png
[eaf26d937673431288006d119640e168.png]: https://img-blog.csdnimg.cn/eaf26d937673431288006d119640e168.png
[3b4b6f3d050a467b85648369a5f7ffc8.png]: https://img-blog.csdnimg.cn/3b4b6f3d050a467b85648369a5f7ffc8.png
[800e99d5fabc4cf3b4e7c7c287428be2.png]: https://img-blog.csdnimg.cn/800e99d5fabc4cf3b4e7c7c287428be2.png
[4677e09bfcf1402d93163df76d98ffcf.png]: https://img-blog.csdnimg.cn/4677e09bfcf1402d93163df76d98ffcf.png