mysql好使的4个函数GROUP_CONCAT，FIND_IN_SET，REPLACE，TRIM以及一个复杂sql解析

最近在做的一个项目中学到mysql的4个函数，平常竟然都没发现，记录一下

1.GROUP_CONCAT 函数

语法：GROUP_CONCAT(字段 SEPARATOR ',')

这个函数的功能可以将多行字段转为一行数据（纵转行），类似效果如图

执行这条sql后 

SELECT GROUP_CONCAT(role_name SEPARATOR ',') FROM  mooc_film actor_ t WHERE film_id = '2';

查询后结果如下，可以看到把多行数据放到一行表格

---

---

2.REPLACE 函数 与 TRIM函数

REPLACE

语法：REPLACE (字段名,oldstr,newstr)

这个函数的功能可以将某个函数中的字段替换成指定字符串

TRIM

语法：TRIM((BOTH | LEADING | TRAILING) 'removeStr' FROM str)

这个函数可以删除指定的字符串内容，以及去除空格。

例如film_cats字段存的是一条这样的字符串 #2#4#22# 

执行如下sql后将变成2#4#22 删除了两边的#号

SELECT TRIM(BOTH '#' FROM film.film_cats)  FROM mooc_film_t film

与上面sql进行一个合并，执行这条sql：可以将 film表里面的film_cats字段中含有#的值替换成 , 号

SELECT REPLACE(TRIM(BOTH '#' FROM film.film_cats) ,'#',',')  FROM mooc_film_t film

最终结果是2,4,22

---

3.FIND_IN_SET 函数

语法：FIND_IN_SET(str,strlist)

假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,' 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则FIND_IN_SET() 函数被优化，使用比特计算。 如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,')时将无法正常运行。

适用于模糊查询并且是一个有规律的字符串，中间必须由逗号分隔符分割

mysql find_in_set 与 in 的用法与区别

1、find_in_set 用于模糊查询，并且数据库中的值是用英文逗号分隔的 ，并且find_n_set 中支持变量 ；

2、in 用于范围精准查询，并且数据库中值是单一的，数据库中不是多个值，in不支持变量。

---

例如 现在有这样一个场景，一个电影表包含如图一个字段

#2#4#22# 这里面的数字分别代表如下图所示另外一张表的电影类型

 

现在我们要将这个电影表中类型以这样形式查询出来

就可以使用find_in_set函数与前文中查询出的一个这样结果2,4,22组合使用得到如下sql语句

select cat.uuid,cat.show_name from mooc_cat_dict_t cat where 
FIND_IN_SET(cat.UUID,(SELECT REPLACE(TRIM(BOTH '#' FROM film.film_cats) ,'#',',')  
FROM mooc_film_t film where film.uuid =1))

如果大家觉得的比较复杂可以等于如下

select cat.uuid,cat.show_name from mooc_cat_dict_t cat where 
FIND_IN_SET(cat.UUID,'2,4,22')

查询结果如下：

而我们使用 in的话只能得到如下结果,很明显不满足我们的条件，结果错误

select cat.uuid,cat.show_name from mooc_cat_dict_t cat where cat.UUID 
in((SELECT REPLACE(TRIM(BOTH '#' FROM film.film_cats) ,'#',',')  
FROM mooc_film_t film where film.uuid =1))

 

然后再使用上面的GROUP_CONCAT函数（纵转行即可）

完整的sql语句

      SELECT
         film.`UUID` AS filmId,
         film.`film_name` AS filmName,
         info.`film_length` AS filmLength,
         (SELECT
             GROUP_CONCAT(show_name SEPARATOR ',')
            FROM mooc_cat_dict_t cat
            WHERE FIND_IN_SET (
             cat.`UUID`,(
               SELECT
                 REPLACE(TRIM(BOTH '#' FROM film.film_cats),"#",",")
               FROM mooc_film_t f
               WHERE f.uuid = film.`uuid`
             )
            )) AS filmCats,
         (SELECT GROUP_CONCAT(role_name SEPARATOR ',')
            FROM mooc_film_actor_t actor WHERE actor.film_id = film.uuid) AS actors,
         film.`img_address` AS imgAddress,
         info.`film_imgs` AS subAddress
        FROM mooc_film_t film
        LEFT JOIN mooc_film_info_t info
        ON film.`UUID` = info.`film_id`
        where film.`uuid` = #{filmId}

需要sql文件的可以留下联系方式