MySQL模糊查询⽤法⼤全（正则、通配符、内置函数等）
⼀、MySQL通配符模糊查询(%,_)
1-1. 通配符的分类
"%" 百分号通配符: 表⽰任何字符出现任意次数 (可以是0次)。
"_" 下划线通配符：表⽰只能匹配单个字符，不能多也不能少，就是⼀个字符。当然，也可以like "陈____"，数量不限。
like操作符:LIKE作⽤是指⽰mysql后⾯的搜索模式是利⽤通配符⽽不是直接相等匹配进⾏⽐较；但如果like后⾯没出现通配符，则在SQL执⾏优化时将 like 默认为 “=”执⾏
注意: 如果在使⽤like操作符时，后⾯没有使⽤通⽤匹配符（%或_），那么效果是和“=”⼀致的。
在SQL执⾏优化时查询优化器将 like 默认为 “=”执⾏,SELECT * FROM movies WHERE movie_name like '唐伯虎';
只能匹配movie_name=“唐伯虎”的结果,⽽不能匹配像“唐伯虎点秋⾹”或“唐伯虎点⾹烟”这样的结果.
1-2. 通配符的使⽤
1) % 通配符:
-- 模糊匹配含有“⽹”字的数据
SELECT * from app_info where appName like '%⽹%';
-- 模糊匹配以“⽹”字结尾的数据
SELECT * from app_info where appName like '%⽹';
-- 模糊匹配以“⽹”字开头的数据
SELECT * from app_info where appName like '⽹%';
-- 精准匹配，appName like '⽹' 等同于：appName = '⽹'
SELECT * from app_info where appName = '⽹';
-- 等同于
SELECT * from app_info where appName like '⽹';
-- 模糊匹配含有“xxx⽹xxx车xxx”的数据，如："途途⽹约车司机端、⽹络约车平台"
SELECT * from app_info where appName like '%⽹%车%';
2) _ 通配符:
-- 查询以“⽹”为结尾的，长度为三个字的数据，如："链家⽹"，
SELECT * from app_info where appName like '__⽹';
注意：'%__⽹、__%⽹' 等同于 '%⽹'
-- 查询前三个字符为XX⽹，后⾯任意匹配，如："城通⽹盘、模具⽹平台"
SELECT * from app_info where appName like '__⽹%';
-- 模糊匹配含有“xx⽹x车xxx”的数据，如："携程⽹约车客户端"
SELECT * from app_info where appName like '__⽹_车%';
注意事项:
注意⼤⼩写，在使⽤模糊匹配时，也就是匹配⽂本时,MySQL默认配置是不区分⼤⼩写的。当你使⽤别⼈的MySQL数据库时，要注意是否区分⼤⼩写，
是否区分⼤⼩写取决于⽤户对MySQL的配置⽅式.
注意尾部空格,"%test"是不能匹配"test "这样的记录的。
注意NULL,%通配符可以匹配任意字符，但是不能匹配NULL，也就是说SELECT * FROM blog where title_name like '%'；是匹配不到title_name为NULL的的记录。
1-3. 技巧与建议:
正如所见，MySQL的通配符很有⽤。但这种功能是有代价的：通配符搜索的处理⼀般要⽐前⾯讨论的其他搜索所花时间更长，消耗更多的内存等资源。
这⾥给出⼀些使⽤通配符要记住的技巧。
不要过度使⽤通配符。如果其他操作符能达到相同的⽬的，应该使⽤其他操作符。
在确实需要使⽤通配符时，除⾮绝对有必要，否则不要把它们⽤在搜索模式的开始处。因为MySQL在where后⾯的执⾏顺序是从左往右执⾏的，如果把通配符置于 搜索模式的开始处（最左侧），搜索起来是最慢的(因为要对全库进⾏扫描)。
仔细注意通配符的位置。如果放错地⽅，可能不会返回想要的数据。
有细⼼地朋友会发现，如果数据中有“%”、“_”等符号，那岂不是和通配符冲突了？
SELECT * from app_info where appName LIKE '%%%';
SELECT * from app_info where appName LIKE '%_%';
确实如此，上⾯⾯两条SQL语句查询的都是全表数据，⽽不是带有"%"和"_"的指定数据。这⾥需要加 ESCAPE 关键字进⾏转义。
如下，ESCAPE 后⾯跟着⼀个字符，⾥⾯写着什么，MySQL就把那个符号当做转义符，⼀般我就写成"/"；然后就像 C语⾔中转义字符⼀样例如 ‘\n’,’\t’, 把这个字符写在你需要转义的那个%号前就可以了；
SELECT * from app_info where appName LIKE '%/_%' ESCAPE '/';
但是这种情况有没有更⾼端点的解决办法呢？能让检查你代码的同事或领导对你刮⽬相看那种~~
当然，下⾯我们就来看看MySQL的第⼆类模糊匹配⽅式 --- 内置函数查询
⼆、MySQL内置函数检索(locate,position,instr)
话接上⽂，通过内置函数locate,position,instr进⾏匹配，相当于Java中的ains()⽅法，返回的是匹配内容在字符串中的位置，效率和可⽤性上都优于通配符匹配。
SELECT * from app_info where INSTR(`appName`, '%') > 0;
SELECT * from app_info where LOCATE('%', `appName`) > 0;
SELECT * from app_info where POSITION( '%' IN `appName`) > 0;
如上，三种内置函数默认都是：> 0，所以下列 > 0 可加可不加，加上可读性更好。
OK，下⾯⼀起来看看这三种内置函数的使⽤⽅法吧。
先明确⼀下，MySQL中的⾓标从左往右是从1开始的，不像java最左边第⼀位⾓标是0，因此在MySQL中⾓标为0时说明不存在。
2-1. LOCATE()函数
语法： LOCATE（substr,str）
返回 substr 在 str 中第⼀次出现的位置。如果 substr 在 str 中不存在，返回值为 0，如果substr 在 str 中存在，返回值为：substr 在 str中第⼀次出现的位置。
注意：LOCATE（substr，str）与 POSITION（substr IN str）是同义词，功能相同。
语法： LOCATE（substr, str, [pos]）
从位置pos开始的字符串str中第⼀次出现⼦字符串substr的位置。如果substr不在str中，则返回0。如果substr或str为NULL，则返回NULL。SELECT locate('a', 'banana'); -- 2
SELECT locate('a', 'banana', 3); -- 4
SELECT locate('z', 'banana'); -- 0
SELECT locate(10, 'banana'); -- 0
SELECT locate(NULL , 'banana'); -- null
SELECT locate('a' , NULL ); -- null
实例：
-- ⽤LOCATE关键字进⾏模糊匹配，等同于："like '%⽹%'"
SELECT * from app_info where LOCATE('⽹', `appName`) > 0;
-- ⽤LOCATE关键字进⾏模糊匹配, 从第⼆个字符开始匹配"⽹"，则"⽹易云游戏、⽹来商家"等数据就被过滤了
SELECT * from app_info where LOCATE('⽹', `appName`, 2) > 0;
2-2. POSITION()⽅法
语法：POSITION(substr IN substr)
这个⽅法可以理解为locate(substr，str)⽅法的别名，因为它和locate(substr，str)⽅法的作⽤是⼀样的。
实例：
-- ⽤POSITION关键字进⾏模糊匹配，等同于："like '%⽹%'"
SELECT * from app_info where POSITION( '⽹' IN `appName`);
2-3. INSTR()⽅法
语法: INSTR(str,substr)
返回字符串str中第⼀次出现⼦字符串substr的位置。INSTR()与LOCATE()的双参数形式相同，只是参数的顺序相反。实例：
-- ⽤INSTR关键字进⾏模糊匹配，功能跟like⼀样 ，等同于："like '%⽹%'"
SELECT * from app_info where INSTR(`appName`, '⽹');
-- instr函数作⽤，⼀般⽤于检索某字符在某字符串中的位置，等同于："like '%⽹%'"
SELECT * from app_info where INSTR(`appName`, '⽹') > 0;
三、MySQL基于regexp、rlike的正则匹配查询
MySQL中的regexp和rlike关键字属于同义词，功能相同。本⽂以regexp为准。
REGEXP 不⽀持通配符"%、_"，⽀持正则匹配规则，是⼀种更细⼒度且优雅的匹配⽅式，⼀起来看看吧
-- 这⾥给出regexp包含的参数类型
-- REGEXP '⽹' 等同于 like '%⽹%'
SELECT * from app_info where appName REGEXP '⽹';
-- 等同于
SELECT * from app_info where appName like '%⽹%';
3-1. regexp中的 OR : |
功能：可以搜索多个字符串之⼀，相当于 or
-- ⽀持 "|" ‘或’符号，匹配包含“中国”或“互联⽹”或“⼤学”的数据，⽀持叠加多个
SELECT * from app_info where appName REGEXP '中国|互联⽹|⼤学';
-- 匹配同时命中“中国”、“⽹”的数据可以⽤".+"连接，代表中国xxxx⽹，中间允许有任意个字符，顺序不能反。
3-2. REGEXP中的正则匹配 : []
功能：匹配[]符号中⼏个字符之⼀，⽀持解析正则表达式
-- 匹配包含英⽂字符的数据，默认不区分⼤⼩写情况下
SELECT * from app_info where appName REGEXP '[a-z]';
-- 跟like⼀样，取反集加 "not REGEXP" 即可，下⾯不再赘述
-- 匹配包含⼤写英⽂字符的数据，默认忽略⼤⼩写，需要加上"BINARY"关键字。如where appName REGEXP BINARY 'Hello' -- 关于⼤⼩写的区分：MySQL中正则表达式匹配（从版本3.23.4后）不区分⼤⼩写。
-- 匹配包含数字的数据
SELECT * from app_info where appName REGEXP '[0-9]';
-- 匹配包含数字或英⽂的数据，
SELECT * from app_info where appName REGEXP '[a-z0-9]';
a-z、0-9都认定为⼀个单位，不要加多余符号
-- 之前写查询语句时多加了"|"符号，以为是"或"，没有在意，但万万没想到，查出数量竟不同SELECT
* from app_info where appName REGEXP '[567]'; -- 87条
SELECT * from app_info where appName REGEXP '[5|6|7]'; -- 88条
-- 原来"|"符号也参与到了匹配中，认定为⼀个单位
-- 查询以5、6、7其中⼀个为开头的数据
SELECT * from app_info where appName REGEXP '^[5|6|7]';
-- 查询以5、6、7其中⼀个为结尾的数据
SELECT * from app_info where appName REGEXP '[5|6|7]$';
温馨提⽰：MySQL中，UTF-8的中⽂=3个字节；GBK的中⽂=2个字节
-- 查询appName字节长度为10，任意内容的数据
SELECT * from app_info where appName REGEXP '^.{10}$';
-- 查询appName字节长度为10，且都为英⽂的数据
SELECT * from app_info where appName REGEXP '^[a-z]{10}$' ;
-- 查询appName字节长度为10，且都为⼤写英⽂的数据，加上BINARY即可
SELECT * from app_info where appName REGEXP BINARY '^[A-Z]{10}$';
-- 查询version_name字节长度为6，且都为数字或"." 的数据
regexp likeSELECT * from app_info where version_name REGEXP '^[0-9.]{6}$';
-- 查询version_name字节长度为6，且都为数字或"." 的数据；要求⾸位为1
SELECT * from app_info where version_name REGEXP '^1[0-9.]{5}$' ;
-- 查询version_name字节长度为6，且都为数字或"." 的数据；要求⾸位为1，末位为7
SELECT * from app_info where version_name REGEXP '^1[0-9.]{4}7$' ;
-- 查询version_name字节长度为6位以上，且都为数字或"." 的数据；要求⾸位为1，末位为7
SELECT * from app_info where version_name REGEXP '^1[0-9.]{4,}7$' ;
-- 查询version_name字节长度为 6 - 8 位，且都为数字或"." 的数据；要求⾸位为1，末位为7
SELECT * from app_info where appName REGEXP '^([a-z]|[0-9]|[A-Z])+$';
SELECT * from app_info where version_name REGEXP '^1[0-9.]{4,6}7$' ;
-- ⾸位字符不是中⽂的
SELECT * from app_info where appName REGEXP '^[ -~]';
-- ⾸位字符是中⽂的
SELECT * from app_info where appName REGEXP '^[^ -~]';//+代表⼀次或者多次
-- 查询不包含中⽂的数据
SELECT * from app_info where appName REGEXP '^([a-z]|[0-9]|[A-Z])+$';
-- 以5或F开头的，且包含英⽂的数据
SELECT * from app_info where appName REGEXP BINARY '^[5F][a-zA-Z].';
特殊符号的匹配，例如.，需要加\\(注意是两个斜杠)，但是如果在[]中可以不加:
-- 匹配name中含有.的
select * from app_info where appName regexp '\\.';
-- 匹配name中含有.的
select * from app_info where appName regexp '[.]';
3-3. 字符类匹配(posix)
mysql中有⼀些特殊含义的符号，可以代表不同类型的匹配:
-- 匹配name中含有数字的
select * from app_info where appName regexp '[[:digit:]]';
其他的这种字符类还有:
3-4. [:<:]和[:>:]
上⾯的字符类中有两个⽐较特殊的，这两个是关于位置的，[:<:]匹配词的开始,[:>:]匹配词的结束，它们和 ^、$ 不同。后者是匹配整个整体的开头和结束，⽽前者是匹配⼀个单词的开始和结束。
-- 只能匹配整体以a开头的，例如abcd
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论