MySQL之模糊查询

目录

1.标准SQL模式匹配

2.扩展正则表达式模式匹配

3.两种匹配模式的区别

---

在MySQL中实现模糊查询有2种方式：1.使用标准SQL模式匹配 2.使用扩展正则表达式模式匹配。

1.标准SQL模式匹配

在标准SQL模式下进行的匹配，缺省是忽略大小写的，并且不能使用 "=" 或 "!=" ，而必须使用 "LIKE" 或 "NOT LIKE" 关键字.

该模式共有"_" 和 "%" 两个通配符：

"_"  匹配任意单个字符

"%"  匹配任意多个字符(包括0个)

例如：

SELECT * FROM table_name WHERE filed_name LIKE '_abc_';  
#查询 filed_name 为 5 个字符，且中间三个字符是 "abc" 或 "ABC" 的所有记录
SELECT * FROM table_name WHERE filed_name LIKE 'abc%';  
#查询 filed_name 以 "abc" 或 "ABC" 开头的所有记录
SELECT * FROM table_name WHERE filed_name LIKE '%abc';  
#查询 filed_name 以 "abc" 或 "ABC" 结尾的所有记录
SELECT * FROM table_name WHERE filed_name LIKE '%abc%'; 
#查询 filed_name 中包含 "abc" 或 "ABC" 的所有记录，包含以 "abc" 或 "ABC" 开头或结尾的记录

PS： 虽然似乎 "%" 可以匹配任何东西，但有一个例外，那就是 NULL。 

如果查询条件中包含有 "_" 或 "%" 通配符，就需要使用 "ESCAPE" 关键字指定转义字符来对查询条件中的特殊字符进行转义，转义字符后面的 "_" 或 "%" 将不再作为通配符了，前面没有转义字符的"_" 或 "%" 仍然起通配符作用，若未指定转义字符则使用缺省的 "\" 作为转义字符。

例如： 

SELECT * FROM table_name WHERE filed_name LIKE 'abc\%%';
#查询 filed_name 以 "abc%" 或 "ABC%" 开头的所有记录,使用缺省转义字符 "\"
SELECT * FROM table_name WHERE filed_name LIKE 'abc/%%' ESCAPE '/'; 
#查询 filed_name 以 "abc%" 或 "ABC%" 开头的所有记录，指定转义字符为 "/"

2.扩展正则表达式模式匹配

在此种模式下，在MySQL中使用正则表达式进行匹配，缺省也是不区分大小写的，要使用关键词 "REGEXP"和 "NOT REGEXP"（或"RLIKE" 和 "NOT RLIKE"，它们是同义词）。

正则表达式模式常用通配符：

"."      匹配除 "\n" 之外的任意单个字符，要匹配包括 '\n' 在内的任意字符，请使用'[.\n]'
"[]"     匹配在方括号内的任意字符。如，"[abc]" 则匹配"a"、"b"或者"c"，为了限定字符的取值范围，可以使用 "-"，
         "［a-z］" 匹配小写字母中的任意一个，而 "［^a-z］" 匹配非小写字母中的任意一个
"?"      匹配0个或1个在它前面的字符
"+"      匹配1个或多个在它前面的字符
"*"      匹配0个或多个在它前面的字符。如，"x*" 匹配任意数量的 "x" 字符，"［0-9］*"匹配任意数量的数字，而".*"匹配任意数量的任意字符
"{n}"    匹配n个在它前面的字符。"8{5}" 匹配5个8，即88888
"{n,}"   匹配至少n个在它前面的字符。"8{5，}" 匹配至少5个8
"{n,m}"  匹配n-m个在它前面的字符。"[0-9]{5,11}" 匹配任意数字重复5到11次。
"^"      匹配开始位置。
"$"      匹配结束位置。

例如：  

SELECT * FROM table_name WHERE filed_name REGEXP '^abc%{1,3}$';
#查询 filed_name 以 "abc" 或 "ABC" 开头，并以1至3个 "%" 结尾的所有记录

PS：正则表达式本身是区分大小写的，但是在MySQL中使用正则表达式进行匹配是不区分大小写的，要区分大小写需使用BINARY关键字，例如：  

SELECT * FROM table_name WHERE filed_name REGEXP BINARY '^abc%{1,3}$';
#查询 filed_name 以 "abc" 开头，并以1至3个 "%" 结尾的所有记录

3.两种匹配模式的区别

-标准SQL模式使用 "LIKE" 或 "NOT LIKE" 进行查询，正则表达式模式使用 "REGEXP" 或 "NOT REGEXP"（或 "RLIKE" 和 "NOT RLIKE"）进行查询。

-"LIKE" 会对整个字段值进行匹配，而 "REGEXP" 会在字段值内进行匹配。