标准SQL模式匹配

SQL模式匹配允许你使用“_”匹配任何单个而“%”匹配任意数目(包括零个)在 MySQL中SQL模式缺省是忽略大小写下面显示些例子注意在你使用SQL模式时你不能使用=或!=；而使用LIKE或NOT LIKE比较操作符

例如在表pet中为了找出以“b”开头名字:

mysql> SELECT * FROM pet WHERE name LIKE "b%";



+--------+--------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

+--------+--------+---------+------+------------+------------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

为了找出以“fy”结尾名字:

mysql> SELECT * FROM pet WHERE name LIKE "%fy";



+--------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+--------+--------+---------+------+------------+-------+

为了找出包含个“w”名字:

mysql> SELECT * FROM pet WHERE name LIKE "%w%";



+----------+-------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

+----------+-------+---------+------+------------+------------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

+----------+-------+---------+------+------------+------------+

为了找出包含正好5个名字使用“_”模式:

mysql> SELECT * FROM pet WHERE name LIKE "_____";



+-------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

MySQL提供标准SQL模式匹配以及种基于象Unix实用如vi、grep和sed扩展正则表达式模式匹配格式

扩展正则表达式模式匹配

由MySQL提供模式匹配其他类型是使用扩展正则表达式当你对这类模式进行匹配测试时使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE它们是同义词)

扩展正则表达式些是:

“.”匹配任何单个

个类“[...]”匹配在方括号内任何例如“[abc]”匹配“a”、“b”或“c”为了命名个范围使用个“-”

“[a-z]”匹配任何小写字母而“[0-9]”匹配任何数字

“ * ”匹配零个或多个在它前面东西例如“x*”匹配任何数量“x”“[0-9]*”匹配任何数量数字而“.*”匹配任何数量任何东西

正则表达式是区分大小写但是如果你希望你能使用个类匹配两种写法例如“[aA]”匹配小写或大写“a”而“[a-zA-Z]”匹配两种写法任何字母

如果它出现在被测试值任何地方模式就匹配(只要他们匹配整个值SQL模式匹配)

为了定位个模式以便它必须匹配被测试值开始或结尾在模式开始处使用“^”或在模式结尾用“$”

为了介绍说明扩展正则表达式如何工作上面所示LIKE查询在下面使用REGEXP重写:

为了找出以“b”开头名字使用“^”匹配名字开始并且“[bB]”匹配小写或大写“b”:

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";





+--------+--------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

+--------+--------+---------+------+------------+------------+

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

+--------+--------+---------+------+------------+------------+

为了找出以“fy”结尾名字使用“$”匹配名字结尾:

mysql> SELECT * FROM pet WHERE name REGEXP "fy___FCKpd___5quot;;





+--------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+--------+--------+---------+------+------------+-------+

| Fluffy | Harold | cat | f | 1993-02-04 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+--------+--------+---------+------+------------+-------+

为了找出包含个“w”名字使用“[wW]”匹配小写或大写“w”:

mysql> SELECT * FROM pet WHERE name REGEXP "[wW]";





+----------+-------+---------+------+------------+------------+

| name | owner | species | sex | birth | death |

+----------+-------+---------+------+------------+------------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |

| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |

+----------+-------+---------+------+------------+------------+

既然如果个正规表达式出现在值任何地方其模式匹配了就不必再先前查询中在模式两方面放置个通配符以使得它匹配整个值

就像如果你使用了个SQL模式那样

为了找出包含正好5个名字使用“^”和“$”匹配名字开始和结尾和5个“.”例子在两者的间:

mysql> SELECT * FROM pet WHERE name REGEXP "^.....___FCKpd___7quot;;





+-------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

你也可以使用“{n}”“重复n次”操作符重写先前查询:

mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}___FCKpd___8quot;;





+-------+--------+---------+------+------------+-------+

| name | owner | species | sex | birth | death |

+-------+--------+---------+------+------------+-------+

| Claws | Gwen | cat | m | 1994-03-17 | NULL |

| Buffy | Harold | dog | f | 1989-05-13 | NULL |

+-------+--------+---------+------+------------+-------+

整理总结

本介绍了有关串模式匹配有关知识标准SQL模式匹配是SQL语言标准可以被其它关系数据库系统接受扩展正规表达式模式匹配

是根据Unix系统标准开发了般只可使用在MySQL上但是其功能要比标准SQL模式匹配更强