MySQL concat、concat_ws、group_concat 用法
阅读目录
一、concat() 函数
1、功能:将多个字符串连接成一个字符串。
2、语法:concat(str1, str2,…)
返回结果为连接参数产生的字符串,如果有任何一个参数为 null,则返回值为 null。
举例1
select CONCAT(id,name,email) as userinfo from users;
上面有一行为 null 是因为 users 表中有一行的 email 值为 null。
举例2
在例1的结果中三个字段 id,name,email 的组合没有分隔符,我们可以加一个逗号作为分隔符。
select CONCAT(id,',',name,',',email) as userinfo from users;
二、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;
把分隔符指定为 null,结果全部变成了 null。
三、group_concat() 函数
前言:
在有 group by 的查询语句中,select 指定的字段要么就包含在 group by 语句的后面,作为分组的依据,要么就包含在聚合函数中。
举例1
该例查询了name 相同的的人中最小的 id。如果我们要查询 name 相同的人的所有的id呢?
当然我们可以这样查询:
但是这样同一个名字出现多次,看上去非常不直观。
有没有更直观的方法,既让每个名字都只出现一次,又能够显示所有的名字相同的人的 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 号:
将上面的 id 号从大到小排序,且用 ‘_’ 作为分隔符:
上面的查询中显示了以 name 分组的每组中所有的 id。接下来我们要查询以 name 分组的所有组的 id 和 email:
示例
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
测试数据
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
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
使用 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
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
但我们发现,虽然 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
数据库
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,'|')
2 MySQL 中批量替换某个字段的值:replace
例如我们有一个表:mxd_goods
中字段 goods_pic
的值要全部去掉 goods/
。
现在要将该表中的所有 goods_pic
字段的值去掉 goods/
。
update mxd_goods set goods_pic= replace(goods_pic, 'goods/', '');
注:goods_pic
是表字段值,goods/
是被替换的值,即搜索的值,‘’是替换后的值,此处替换为空,意思是去掉了 goods/
。
一定要加 where id =数字
,否则将是全表修改哦。
怎么撤销呢?
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’ 字符开始截取两个字符
运行结果如下:
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;
项目案例
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`
SELECT
id,
GROUP_CONCAT(
title,
concat('(', cc_emails, ');') SEPARATOR ''
) AS email_title
FROM
hhir_crm_email_send_content
WHERE
id IN (12, 13)
相关文章
- 【MySQL高级】MySql中常用工具及Mysql 日志
- 深入浅出MySQL++数据库开发、优化与管理维护+第2版+唐汉明 -- 存储引擎 - 数据类型 - 字符集和校验规则 -
- MySQL 显示版本、端口、状态
- MySQL使用初步—mysql数据库的基本命令
- 当MySQL数据库遭到攻击篡改后,使用备份和binlog进行数据恢复
- 工具--按指定的word模板, 将MySQL数据库表信息导出生成word文档
- MySQL中CONCAT() ,CONCAT_WS() ,GROUP_CONCAT()的用法
- mysql InnoDB 的行锁
- MySQL体系结构图详解
- 通过Navicat for MySQL远程连接的时候报错mysql 1130的解决方法
- MySQL/MariaDB表表达式(3):视图
- Mysql 5.7源码编译启动 报error问题:The server quit without updating PID file (/data/data_mysql/mysql.pid).
- MYSQL导入csv类型的数据出现The MySQL server is running with the --secure-file-priv option
- Mysql命令mysql:连接Mysql数据库
- mySql top用法,查询前n条,前几条
- mysql远程连接 Host is not allowed to connect to this MySQL server
- 【转】Mysql学习---MySQL悲观锁中的排它锁
- Python 生成MySQL数据库的表结构到word文档
- (5.6)mysql高可用系列——MySQL Utilities 管理工具
- mysql查看执行sql语句的记录日志
- Eclipse中使用MySql遇到:Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading o
- MySQL 数据库的常见命令