谈谈mysql locate函数

迈不过友情╰ 2022-02-17 10:31 364阅读 0赞

大家都知道，当我们mysql需要使用模糊查询的时候，往往会使用like去做相关查询条件的模糊匹配，但是很多场景下，使用locate会获取同样的效果

create table user(
    	id int primary key auto_increment,
    	name varchar(18),
    	description varchar(255)
    ) charset=UTF8;
    
    select * from user order by id;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70]  
测试环境插入如上数据：  
场景1，我么需要查询所有名字中含有 王 的所有数据，用like很简单

select * from user where name like '%王%' order by id;

那么使用locate如何去实现呢，首先我们先看看locate的用法，如下为官方给出的该函数的详细使用说明  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 1]  
意思也比较简单

LOCATE(substr,str)
    LOCATE(substr,str,pos)

1，从str的起始位置开始匹配str中的substr串，如果匹配上，返回下标，如果没有匹配上，返回0，如果substr或者str为null的时候，返回null  
2，表示从下标为pos（int值）处开始匹配

使用locate实现上述效果：

select * from user where locate('王',name)

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

但是如果我现在需求是找出所有名字中含有王，但是不姓王的人找出来，使用like就不太好实现了，但是使用locate就很容易实现，我们用下面那种实现方式如下

select * from user where locate('王',name,2)

![在这里插入图片描述][20190419141135882.png]  
我们从第2个字符串开始匹配，就很把所有姓王的给过滤掉了

关于locate的用法如上，结合具体的业务，配合合理的数据库设计，可以很容易避开复杂的like查询。

**问题：但是有一种说法是在同样数据量情况下，使用locate查询比使用like效率要高，但是查看语句的执行计划，二者是一模一样的，不明白是为什么，官方也没找到相关说明，还望知道的大神可以留言一起交流一波！**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70]: /images/20220217/d5ab5f309b8d41a7a735b28e080df23e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 1]: /images/20220217/7d9f0b1887b94e0c8d0e8307c75dbc14.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jhb213_size_16_color_FFFFFF_t_70 2]: /images/20220217/f56619222fb347fb8fdd8d7ef062ea61.png
[20190419141135882.png]: /images/20220217/7308a2c3a88c4f298d1b5bf4bd8495b5.png