How to use regular expressions in MySQL

前言

有时候使用MySQL进行数据库查询数据的时候，like查询存在局限性，这时候就可以使用MySQL中的正则表达式查询的方式。

正则表达式是用来匹配文本的特殊的串（字符集合），将一个模式（正则表达式）与一个文本串进行比较。

• 从文本文件中提取电话号码

• 查找名字中间带有数字的文件

• 文本块中重复出现的单词

• 替换页面的URL为这些URL的实际链接(一个或两个正则表达式)

LIKE和REGEXP的区别

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

例如：

查询一个表name列含有chen字符串的数据，两条sql语句如下

select * from stu_info where name like '小白';
select * from stu_info where name regexp '小白';

第一条的查询结果是name值等于’小白‘的数据，而第二条的查询结果是name中包含’小白’的数据，也可以用like（模糊查询+通配符）来实现regexp查询结果。

OR进行匹配

select column from table where column regexp 'X|Y' order by column;
select column from table where column regexp '[XY]test' order by column;

第一种写法会将|应用于整个串，只要字符串中有X或Y的数据，都将被检索出来

第二种写法只将[XY]应用于搜索模式的开头处

匹配范围

([])集合可以用来定义要匹配的一个或多个字符，比如[0123456789]，为了简化这种类型的集合，可使用（-）来定义一个范围，即[0-9]，[a-z]

匹配特殊字符

则表达式由具有特定含义的特殊字符构成；如果要匹配这些特殊字符，就需要用\为前导,这种处理方式就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义；（为了匹配反斜杠{}字符本身，需要使用\\）

注意：多数正则表达式实现使用单个反斜杠转义特殊字符，以便能够使用这些字符本身，而MySQL要求两个反斜杠（MySQL自己解释一个，正则表达式库解释一个）

匹配字符类

有时候需要检索出我们需要的数字、所有字母字符或所有数字字母字符等的匹配，我们可以使用预定义的字符集，称为字符类。

匹配多个实例

有时候需要对匹配的数目进行更强的控制，比如：寻找所有的数，不管数中包含多少数字，或寻找一个单词并尾随一个s（如果存在）等情况，我们可以利用正则表达式中的重复元字符来完成。

select column from table where column regexp '\\([0-9] sticks?\\)' order by column；

上面的正则表达式‘\([0-9] sticks?\)’意为：\匹配（[0-9]匹配任意数字，这里指匹配的数值范围，sticks?匹配stick和sticks{?使s可选，因为？匹配它前面任何字符的0或者1次出现}）。

定位符

有时候为了匹配特定位置的文本，需要使用定位符，常用定位符列表如下：

找出一个以一个数（包括小数点开始的数）开始的所有数值，简单搜索[0-9\.]或（[[:digit:]\.]）不行，因为它将在文本内任意位置查找匹配，可以使用^定位符，如下：

select column from table where column regexp '^[0-9\\.]' order by column;

^有两种用法

• 在集合中（用[和]定义），用它来否定该集合

• 用来指串的开始处

使regexp和like起相同作用：like匹配整串二regexp匹配子串，可以利用定位符，用^开始每个表达式，用$结束每个表达式，就可以使regexp和like作用一样。

查询admin表中的group字段，数据格式为：1,2,3;4,5,6;1,87,101

例如查询101：

select * from xin_admin where `group` REGEXP '[,|;]?101[,|;]?';

附：Mysql字符串截取 和 截取字符进行查询

一、MySQL中字符串的截取

MySQL中有专门的字符串截取函数：其中常用的有两种：substring_index(str,delim,count) 和concat

1.substring_index(str,delim,count) 函数的使用较为普遍。

The order inside the function brackets is: the string to be separated and intercepted (such as: "aaa_bbb_ccc"), the delimiter (such as: "_"), and the position (indicating which delimiter is at, such as: "1") .

• If count is a positive number, then counting starts from the left, and the function returns the string to the left of the count separator;

• If count is a negative number, then counting starts from the right, and the function returns everything to the right of the count separator;

• count can be 0, and the return is empty.

##Example: substring_index("aaa_bbb_ccc","_",1), returned as aaa; Returns as aaa_bbb;

substring_index(substring_index("aaa_bbb_ccc","_",-2),"_",1), returns as bbb;

##2.concat is Concatenate several strings

Example: concat(‘m’,’y’,’s’,’q’,’l’);

Return: mysql

2. Query all data containing this character based on a field in the table

1.find_in_set: SELECT * FROM user WHERE find_in_set(&lsquo ;Wu’,name);

Query all data whose name contains "Wu" in the user table

2.REGEXP: SELECT * FROM user WHERE name REGEXP ‘(‘Wu&rsquo ;|‘Liu’)’;

Use regular rules to query data containing Liu or Wu

The above is the detailed content of How to use regular expressions in MySQL. For more information, please follow other related articles on the PHP Chinese website!