mysql：这些函数你真的会用吗？

不好意思，已经有一个多月没有更新了，这一个多月在外出差。在出差的过程中，与这边同事一起讨论一些业务，实现业务，发现原来MySql有很多函数自己并没有掌握，因此，干脆总结一番，当一个查询手册，以后用到可以直接进行查询。

废话不多说，开干！

准备工作：

我们随意创建一张表，添加数据：

create table user_info(
    id int auto_increment not null primary key comment '主键Id',
    user_name varchar(100) not null comment '用户名',
    age smallint not null comment '年龄',
     val varchar(30)
);


insert into user_info (user_name, age) values ('zhangsan',21,'1,2,3,4');
insert into user_info (user_name, age) values ('sili',25);
insert into user_info (user_name, age) values ('xili',32);
insert into user_info (user_name, age) values ('xiiili',26);
insert into user_info (user_name, age) values ('wangwu',31);
insert into user_info (user_name, age) values ('zhaoliu',22);

1、like

提到like，小伙伴们肯定会说这有啥难得，不就是模糊查询嘛，like 加单引号再加双百号，就能实现。

的确，like在我们日常工作中用的太多了，在熟悉不过了，但是我们还是要看下它的一些查询。

注：网上有很多说可以使用：[]查询区间内单个字符拼接查询，或者使用#单个数字的，其实mysql并不支持，like会将其整体作为一个字符串进行模糊查询。

1.1 _任意单个字符

需求： 查询以“ili”结尾的长度为4的用户名的数据

select * from user_info ui where ui.user_name like '_ili';

执行结果：

从执行结果可以发现xiiili这个用户名称的用户并没有查询出来，符合需求。

1.2 %多字符模糊匹配

需求： 查询以“zhao”或“zhan”开头的用户名用户信息。

根据需求，我之前的写法直接是两个like进行查询：

select * from user_info ui where (ui.user_name like 'zhao%' or ui.user_name like 'zhan%');

2、regexp

说实话这个函数我基本没有用过，不过在搜寻mysql为啥不支持like中使用[]时发现的，因此记录一下。

regexp：支持正则表达式进行字段查询。

需求： 查询以“zhao”或“zhan”开头的用户名用户信息。

SELECT * FROM user_info WHERE user_name regexp '^zhao|^zhan';

执行结果：

3、locate/find_in_set

locate与find_in_set都是查询字符或字符串在其他字符串中第一次出现的位置。

locate(n,m） ：即n在m中第一次出现的位置，不包含时返回0。locate会将m作为一整个字符来进行匹配，不会因为存在逗号","而算成多个字符。

find_in_set(n,'m,n,l'） ：可以理解为n在一个集合中第一次出现的位置，多个字符匹配需要用逗号进行拼接且分隔的字符必须与n相等。

需求：查询用户名称中存在zhao的用户信息。

-- locate查询
select * from user_info ui where locate('zhao',ui.user_name) > 0;

--find_in_set查询
select * from user_info ui where find_in_set('zhao',ui.user_name) > 0;

执行结果：

由查询结果我们可以看到locate实现了模糊匹配，而find_in_set查询是进行全匹配的，即与mysql中的=一样。

4、json_object

有时候我们希望一些字段以json的格式返回，此时可以使用json_object。

json_object：将查询的数据以json的格式输出。

json_object(key,value[,key,value......]) ：key与value必填。

需求： 将用户名以"zhao"开头的用户信息，以json格式输出。

select json_object('userName',user_name,'age',age) jsonData from user_info ui where locate('zhao',ui.user_name) > 0;

执行结果：

4.1 json操作

在很多数据库中一个字段可能会保存json格式的数据，我们也会对这些数据进行筛选等操作。那么我们来看下json数据怎么进行操作呢？

4.1.1 条件精确查找

格式：json字符 -> '$.字段名'=数值 或 json_contains(json数据,'查询值','查询json字段')

注：使用json_contains查询字符串匹配时，需加上""双引号，这是因为json数据中字符串是用双引号引用

例如，我们在上述生成的json中获取年龄为22的数据。

select *
from (
         select json_object('userName', user_name, 'age', age) jsonData
         from user_info ui
         where locate('zhao', ui.user_name) > 0) sel
where sel.jsonData -> '$.age' = 22;

-- 或

select *
from (
         select json_object('userName', user_name, 'age', age) jsonData
         from user_info ui
         where locate('zhao', ui.user_name) > 0) sel
where json_contains(sel.jsonData,'"zhaoliu"','$.userName');

执行结果：

4.1.2 获取json属性值

我们可能会获取json中的某些数据的对应值展示到前端，这时候我们需要用到：json_extract

格式：json_extract(json数据,$.json字段名)

例如：我们在上述生成的json中获取字段年龄的数据。

select json_extract(sel.jsonData,'$.age') age
from (
         select json_object('userName', user_name, 'age', age) jsonData
         from user_info ui
         where locate('zhao', ui.user_name) > 0) sel

执行结果:

4.1.3 条件模糊查找

对json数据模糊查询可以使用：json_extract获取字段值后结合like查询。

格式：json_extract(json数据,$.json字段名) like '%查询字符%' 或 json字符 -> '$.字段名' like '%查询字符%'

例如：在上述生成的json数据中查询用户名包含zhao的数据。

select *
from (
         select json_object('userName', user_name, 'age', age) jsonData
         from user_info ui) sel
where json_extract(sel.jsonData,'$.userName') like '%zhao%';

-- 或
select *
from (
         select json_object('userName', user_name, 'age', age) jsonData
         from user_info ui) sel
where sel.jsonData -> '$.userName' like '%zhao%';

执行结果：

当然mysql对于json的操作还提供了其他一些方法，有兴趣的小伙伴可以去官网看看，在此我就不一一介绍了。

5、substring_index

substring_index顾名思义就是通过索引来截取字符串。

应用：在字符串中可以根据某些字符的位置进行截取

substring_index（'待截取字符串'，'截取字符'，'截取字符位置N'）

当截取字符位置N为-1时表示截取从字符的最后出现的位置开始截取。相当于java中substring(str.lastIndexOf(",");

例如：定义一个字段：a,b,c，要截取第二个逗号前的字符

select SUBSTRING_INDEX('a,b,c', ',',2)

结果：

5.1 行转列

在很多时候一列中数据可能会将多个值用逗号分隔而保存。也会因为业务要求要将逗号分隔的数据换成一列列数据进行统计。例如：一列数据为：1,2,3 需要转换成1为一行，2为1行，3为一行三行数据。

select substring_index(substring_index(uio.val, ',', xg.ID), ',', -1) vals

from user_info uio

         join xmjg_gmjjhylb xg on xg.ID