MySQL通配符与正则表达式的使用

1. 通配符
   1.1. Like操作符
   mysql为了在搜索子句中使用通配符，必须使用like操作符
   mysql通配符有两种，一种是最常用多字符通配符“%”，另一种是单字符通配符“_”
   1.1.1. “%”通配符
   %表示任何字符出现的任意次数，%可以用在需要匹配字符的左边，表示检索以匹配字符结尾的项；放在匹配字符的右边表示检索以匹配字符开始的项；放在匹配字符的两表表示检索包含匹配字符的项

例如检索prod_name列中以o开头的记录

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name LIKE 'o%'
ORDER BY prod_price

检索prod_name列中以o结束的记录

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name LIKE '%o'
ORDER BY prod_price

检索prod_name列中包含o字符的记录

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name LIKE '%o%'
ORDER BY prod_price

注意：通配符%不能用来匹配值为null的项

1.1.2. “_”配符

"_"通配符一次只能匹配单个字符，匹配多个字符需要多个通配符

"_“通配符和”%"一样，可以放在匹配项的左边、右边、两边

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name LIKE '_ling'
ORDER BY prod_price

注意：不要过分使用通配符，如果能用其他操作符达到目的，应该使用其他操作符
在使用通配符时，不要把通配符放在陪陪想的左边，因为会导致全文检索，拖慢查询速度

2. 正则表达式

MySQL中使用REGEXP关键字后跟匹配项进行检索
下面例子时检索prod_name 列中包含ton字符的记录

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name REGEXP 'ton'
ORDER BY prod_price

2.1. OR匹配
mysql使用"|"链接两个匹配表达式，例如下列的例子，检索prod_name 列中包含ton或can字符的记录

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name REGEXP 'ton|can'
ORDER BY prod_price

2.2. 匹配单一字符
[12]定义一组字符，匹配以1或2开始的包含" ton"的记录，[12]也可写成[1|2]

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name REGEXP '[12] ton'
ORDER BY prod_price

2.3. 匹配范围

匹配范围和上述的匹配单字符类似，使用"[]"定义匹配集，在匹配集中表明匹配的范围，如 [1-9]x 表示匹配数字1-9开头的包含x的项。[1-9][a-z]表示匹配第一个字符以数字1-9开头，第二个字符以a-z字母开头的项。

2.4. 匹配特殊字符
mysql中匹配特殊字符时，需要使用"\“对特殊字符进行转义
例如 查找prod_name 列中 以”."开头的记录

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name REGEXP '\\.'
ORDER BY prod_price

"\"也用来引用元字符

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

2.5.匹配字符类

可以使用MySQL预定义的字符集检索经常使用的数字、所有字母字符或所有的数字字母

类	说明
[:alnum:]	任意字母或数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	任意空格和制表符（同\\t]）
[:cntrl:]	ASCII控制字符（ASCII 0-31和127）
[:digit:]	任意数字（同[0-9]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）
[:print:]	任意可打印字符
[:graph:]	同 [:print:]，任意可打印字符，不包含空格
[:lower:]	任意小写字母（同[a-z]）
[:upper:]	任意大写字母（同[A-Z]）
[:punct:]	既不在[:alnum:]也不在 [:cntrl:] 中的字符
[:space:]	包含空格在内的任意空白字符

例如
要检索连在一起的任意数字的记录

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_price

2.6. 匹配多个实例

元字符	说明
*	0个或多个匹配
+	1个或多个匹配{1,}
?	0个或1个匹配 ，等于{0,1}
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围 （m不超过255个）

2.7 定位符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:<:]]	词的结尾

例如要检索prod_name列中以0-9数字（包含以小数点开始的数）的记录

SELECT vend_id,prod_name,prod_price from products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_price

3. like和通配符的区别
   like匹配整个列，如果被匹配的文本在列值中出现，like将不会找到它，相应的行也不会被返回（除非使用通配符），而REGEXP在列值内进行匹配，如果列值在匹配的文本中出现，REGEXP将会找到它，相应的行也会被返回

例如
使用like匹配返回的结果

使用REGEXP匹配返回的结果

以上内容参考《MySQL必知必会》