MySQL之正则表达式

1 所有种类的程序设计语言、文本编辑器、操作系统等都支持正则表达式。

2 下面为大家举个例子：SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

表示检索列prod_name包含文本1000的所有的行；

SELECT prod_name FROM products WHERE prod_name REGEXP  '.000' ORDER BY prod_name;

'.000'是正则表达式中一个特殊的字符，它标识匹配任意一个字符，所以1000和2000都匹配返回。

3 关于LIKE和REGEXP的区别：LIKE匹配整个列。如果被匹配的文本仅在列值中出现，LIKE并不会找到它，相应的行也不会返回（当然，使用通配符除外）。而REGEXP在列值内进行匹配，如果被匹配的匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回，这时一个非常重要的差别（当然，如果适应定位符号^和$，可以实现REGEXP匹配整个列而不是列的子集）。

注意：关于大小写的区分：MySQL中正则表达式匹配（从版本3.23.4后）不区分大小写 。如果要区分大小写，应该使用BINARY关键字，如where post_name REGEXP BINARY 'Hello .000'。

4 关于OR匹配
为了搜索两个串之一，使用|, 如，select * from products where pro_id REGEXP '1000|2000' ORDER BY pro_id;

这样就1000和2000都能匹配并返回，当然，使用多个|就可以匹配多个串。

两个以上的OR条件：'1000|2000|3000'将匹配1000或2000或3000。

5 匹配几个字符之一：

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

这里使用了正则表达式[123]Ton, [123]定义一组字符，它的意思是匹配1或者2或者3，所以1 ton和2 ton都返回（没有3 ton)。

正如所见，[]是另一种形式的OR语句，[123]Ton是[1|2|3] Ton的缩写，注意[]不能省略，否则1|2|3它将会应用于整个串，不单单只是含有字段1或2或3 Ton的串会被匹配，只要整个串中有涉及的都将被匹配出来。

字符集[123]也是可以被否定的，即[^123]表示匹配除去这些字符以外的任何东西。

例如，这样将要匹配［0123456789］可以匹配0到9,也可以写成[0-9],［1-4］［4-9］也是合法的范围。此外，范围不一定只是数值的,［a-z]匹配任意字母字符

6.匹配特殊字符,都必须使用\\作为前导：如\\-表示查找-
\\.能够匹配  .
\\f换页
\\n换行
\\r回车
\\t制表
\\纵向制表
注意：为了匹配\本身，需要使用\\\

7 *［:alnum:］ 任意字母和数字（通［a-zA-Z0-9］）

  *［:alpha:］ 任意字符(同[a-zA-Z])

    [:blank:] 空格和制表符(同[\\t])

    [:cntrl:] ASCII控制字符 （ASCII 0到31和127）

    [:print:] 任意可打印字符

    [:graph:] 与 [:print:]相同，但不包括空格

   * [:digit:] 任意数字(同[0-9])
   * [:lower:] 任意小写字母
    [:upper:] 任意大写字母

    *[:space:] 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v])

    [:xdigit:] 任意十六进制数字（同[0-fA-F0-9])

    [:punct:]既不在［:alpha:］也不在[:cntrl:]中的任意字符

注：*有星号标记是常用的字符类。

8 匹配多个实例，关于重复元字符
* 0个或者多个匹配
+ 1个或者多个匹配（等于{1,}）
？ 1个或者多个匹配（等于{0,1})
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围（m不超过255）

例子： SELECT prod_name from products where prod_name REGEXP '[[:DIGIT:]]{4}' ORDER BY prod_name;

如前所述，[:digit:]匹配任意素子，因而它为数字的一个几何。{4}确切的要求它前面的字符（任意数字）出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字，当然，上面的例子也可以这样写REGEXP '[0-9][0-9][0-9][0-9]'。

9. 定位符
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾

通过使用这些定位符，能够使REGEXP起类似LIKE的作用。

如果你想找出一个数（包括以小数点开始的数）开始的所有产品，简单搜索[0-9\\.]是不行的哦，要在前面加上定位符^[0-9\\.]：

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name;

关于^双重用途：一是用在集合中用于否定集合[^123],二是用在串的开始处。

10 LIKE与正则模式的对比：

like模式

like意思是长得像，有两个模式：_和%

_表示单个字符，通常用来查询定长的数据，如查出所有姓王的三个字的人名，假设姓名列名为name，注意“王”后面有两个_

select name from 表名 where name like '王__';

%表示0个或多个任意字符，如查出所有姓王的人名

select name from 表名 where name like '王%';

查出所有包含“华”字的人名

select name from 表名 where name like '%华%';

 

正则模式 

^，匹配字符串开始位置，还是上面的例子，查询所有姓王的人名

select name from 表名 where name regexp '^王';

 

$，匹配字符串结束位置，如查询所有姓名末尾是“明”的人名

select name from 表名 where name regexp '明$';

 

.，匹配除\n之外的任意单个字符，类似于_，就不写sql语句了

[……]，匹配[]中包含的任何一个字符，abcdef……xyz可以简写为[a-z]，0123456789简写成[0-9]，如查询出w／z／s开头的的人名

select name from 表名 where name regexp '^[wzs]';

 

[^……]，匹配不包含在[]的字符，如查询出除了w／z／s开头之外的人名

select name from 表名 where name regexp '^[^wzs]';

 

a|b|c，匹配a或b或c，如将绩效为A-或A或A+的员工查出来，假设绩效列名performance

select performance from 表名 where performance regexp 'A-|A|A+';

*，重复0次或多次，熟悉javascript正则的同学都知道 

'str*'可以匹配st/str/strr/strrr……

?，重复0次或1次

'str?'可以匹配st/str

+，重复1次或多次

'str+'可以匹配str/strr/strrr/strrrr……

相比javascript里面的正则而言，这里的正则是简化版的，没有惰性匹配／贪婪匹配，[]内不支持\w\s\d这种语法，也不支持中文，相对简单。

需要注意的一点是：这两种模式不要混着用，like模式是不支持正则表达式的，REGEXP模式也不认识_和%。