WHCSRL 技术网

MySQL数据库——正则表达式带你秀操作


前言

MySQL
正则表达式通常是在检索数据库记录的时候,根据指定的匹配模式匹配记录中 符合要求的特殊字符串。MySQL 的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式。


一、正则表达式

1.1 正则表达式匹配符

匹配描述
^匹配文本的开始字符
$匹配文本的结束字符
.匹配任何单个字符
*匹配零个或多个在它前面的字符
+匹配前面的字符 1 次或多次
字符串匹配包含指定的字符串
p1 | p2匹配 p1 或 p2
[…]匹配字符集合中的任意一个字符
[^…]匹配不在括号中的任何字符
{n}匹配前面的字符串 n 次
{n,m}匹配前面的字符串至少 n 次,至多 m 次

1.2 正则表达式使用案例

1.2.1 查询开头字段

案例1:查询以sh开头的字段信息

mysql> select id,name from info where name regexp '^sh';
+----+-----------+
| id | name      |
+----+-----------+
|  2 | shangzhen |
|  1 | shidapeng |
+----+-----------+
2 rows in set (0.01 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

案例2:查询名字以s-x开头的字段信息

mysql> select id,name from info where name regexp '^[s-x]';
+----+-----------+
| id | name      |
+----+-----------+
|  2 | shangzhen |
|  1 | shidapeng |
|  3 | tangyan   |
|  9 | xiawenjie |
+----+-----------+
4 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

1.2.2 查询结尾字段

案例3:查询以n结尾的字段信息

mysql> select id,name from info where name regexp 'n$';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  3 | tangyan   |
| 10 | zhaobin   |
|  8 | zhaokun   |
+----+-----------+
5 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

1.2.3查询开头+结尾字段

案例4:查询名字是tangy开头,n结尾,中间不知道是一个什么字符的字段信息

mysql> select id,name from info where name regexp 'tangy.n';
+----+---------+
| id | name    |
+----+---------+
|  3 | tangyan |
+----+---------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

1.2.4 查询中间字段

案例5:查询名字中包含an的字段信息

mysql> select id,name from info where name regexp 'an';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  3 | tangyan   |
+----+-----------+
3 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

案例6:查询名字中包含an或者zh的字段信息

mysql> select id,name from info where name regexp 'an|zh';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  3 | tangyan   |
| 10 | zhaobin   |
|  8 | zhaokun   |
+----+-----------+
5 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

案例7:查询名字中有an,g可有可无的字段信息
必须要有的部分是’an’ 而’g’可有可无

mysql> select id,name from info where name regexp 'ang*';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  3 | tangyan   |
+----+-----------+
3 rows in set (0.01 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

案例8:查询名字中含有an,g至少出现一次的字段信息

mysql> select id,name from info where name regexp 'ang+';
+----+-----------+
| id | name      |
+----+-----------+
|  2 | shangzhen |
|  3 | tangyan   |
+----+-----------+
2 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

1.2.5 查询不包含的字段信息

案例9:查询名字不是caicai的学生信息

mysql> select id,name from info where name regexp '[^caicai]';
+----+-----------+
| id | name      |
+----+-----------+
|  6 | chengu    |
| 11 | nannan    |
|  2 | shangzhen |
|  1 | shidapeng |
|  3 | tangyan   |
|  9 | xiawenjie |
| 10 | zhaobin   |
|  8 | zhaokun   |
+----+-----------+
8 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

案例10:查询学生名字不以czx各字母开头的学生信息

mysql> select id,name from info where name regexp '^[^czx]';
+----+-----------+
| id | name      |
+----+-----------+
| 11 | nannan    |
|  2 | shangzhen |
|  1 | shidapeng |
|  3 | tangyan   |
+----+-----------+
4 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

二、运算符

MySQL 的运算符用于对记录中的字段值进行运算。MySQL 的运算符共有四种,分别 是:算术运算符、比较运算符、逻辑运算符和位运算符

2.1 算术运算符

以 SELECT 命令来实现最基础的加减乘除运算,MySQL 支持使用的算术运算符,如表所示:

运算符描述
+加法
-减法
*乘法
/除法
%%取余

在除法运算和求余数运算中,除数不能为 0,若除数是 0,返回的结果则为 NULL。需 要注意的是,如果有多个运算符,按照先乘除后加减的优先级进行运算,相同优先级的运算符没有先后顺序。

案例:

mysql> select 1+2,2-1,3*4,4/2,5%%2;
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 3*4 | 4/2    | 5%%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |  12 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

mysql> create table mt as select 1+2,2-1,3*2,4/2,5%%2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from mt;
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 3*2 | 4/2    | 5%%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |   6 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

mysql> desc mt;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| 1+2   | int(3)       | NO   |     | 0       |       |
| 2-1   | int(3)       | NO   |     | 0       |       |
| 3*2   | int(3)       | NO   |     | 0       |       |
| 4/2   | decimal(5,4) | YES  |     | NULL    |       |
| 5%%2   | int(1)       | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

2.2 比较运算符

比较运算符是查询数据记录时经常使用的一类运算符。通过使用比较运算符可以判断出 表中有哪些记录是符合条件的,如果比较的结果(以布尔值的方式进行返回判断)为真则返回 1,如果为假则返回 0,比较的结果如果不确定则返回NULL。其中字符串在进行比较的时候默认是不区分大小写的,如果要区分大小写可以通过binary关键字来实现

运算符描述
=等于
>大于
<小于
>=大于等于
<=小于等于
!=或<>不等于
is null判断一个值是否为 NULL
IS NOT NULL判断一个值是否不为 NULL
BETWEEN AND两者之间
IN在集合中
LIKE通配符匹配
GREATEST两个或多个参数时返回最大值
LEAST两个或多个参数时返回最小值
REGEXP正则表达式

等号(=) 是用来判断数字、字符串和表达式是否相等的,如果相等则返回 1(true),如果不相等则返回 0(flase)。如果比较的两者有一个值是 NULL,则比较的结果就是 NULL。其中字符的比较是根据 ASCII 码来判断的,如果 ASCII 码相等,则表示两个字符相同;如果 ASCII 码不相等,则表示两个字符不相同

例如,字符串(字母)比较:(‘a’>‘b’)其实比较的就是底层的ASCII码
需要关注的是ascii码有:a、A、0(97、65、48)
那么:
如果比较的是多字符串,如:‘abc’=‘acb’,如何比较(字符个数、字符顺序)
如果比较的是多字符,如:‘abc’ <‘baa’ 如何比较
与linux返回值表达相反,linux 中运行正常返回值是0,运行异常返回值是非0

案例:

mysql> select 2=4,2=2,2='2','e'='e','r'=null;
+-----+-----+-------+---------+----------+
| 2=4 | 2=2 | 2='2' | 'e'='e' | 'r'=null |
+-----+-----+-------+---------+----------+
|   0 |   1 |     1 |       1 |     NULL |
+-----+-----+-------+---------+----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

① 如果两者都是整数,则按照整数值进行比较。
②如果一个整数一个字符串,则会自动将字符串转换为数字,再进行比较。(在程序中,一般是不会吧这两者进行相比较的)
③ 如果两者都是字符串,则按照字符串进行比较。
④ 如果两者中至少有一个值是 NULL,则比较的结果是 NULL。

##不等于(<>,!=)
不等于号有两种写法,分别是<>或者!=,用于针对数字、字符串和表达式不相等的比较。 如果不相等则返回 1,如果相等则返回 0,这点正好跟等于的返回值相反。需要注意的是不等于运算符不能用于判断 NULL

案例:

mysql> select 'abc'<>'cba',2<>2,3!=2,null<>null;
+--------------+------+------+------------+
| 'abc'<>'cba' | 2<>2 | 3!=2 | null<>null |
+--------------+------+------+------------+
|            1 |    0 |    1 |       NULL |
+--------------+------+------+------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

① 大于(>)运算符用来判断左侧的操作数是否大于右侧的操作数,若大于返回 1,否则返回0,同样不能用于判断NULL。

② 小于(<)运算符用来判断左侧的操作数是否小于右侧的操作数,若小于返回 1,否则返回0,同样不能用于判断NULL。

③ 大于等于(>=)判断左侧的操作数是否大于等于右侧的操作数,若大于等于返回 1,否则返回 0,不能用于判断NULL。

④ 小于等于(<=)判断左侧的操作数是否小于等于右侧的操作数,若小于等于返回 1,否则返回 0,不能用于判断 NULL

案例:

mysql> select 5>4,3<4,'a'<'b',4.4<5,'u'>=null;
+-----+-----+---------+-------+-----------+
| 5>4 | 3<4 | 'a'<'b' | 4.4<5 | 'u'>=null |
+-----+-----+---------+-------+-----------+
|   1 |   1 |       1 |     1 |      NULL |
+-----+-----+---------+-------+-----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

判断一个值为/不为null ( IS NULL、IS NOT NULL )
#IS NULL判断一个值是否为 NULL,如果为NULL返回1,否则返回0。
#IS NOT NOLL判断一个值是否不为 NULL,如果不为NULL返回1,否则返回 0。示例:
select 2 IS NULL,'f' IS NOT NULL,NULL IS NULL;

案例:

mysql> select 2 is null,'a' is not null,null is null;
+-----------+-----------------+--------------+
| 2 is null | 'a' is not null | null is null |
+-----------+-----------------+--------------+
|         0 |               1 |            1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

BETWEEN AND
比较运算通常用于判断一个值是否落在某两个值之间。例如,判断某数字是否在另外两个数字之间,也可以判断某英文字母是否在另外两个字母之间,具体操作,条件符合返回1,否则返回0

案例:

mysql> select 4 between 2 and 5,'c' between 'a' and 'b';
+-------------------+-------------------------+
| 4 between 2 and 5 | 'c' between 'a' and 'b' |
+-------------------+-------------------------+
|                 1 |                       0 |
+-------------------+-------------------------+
1 row in set (0.00 sec
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • Between and 覆盖的范围是>= 和 <=关系

least 和greatest(取最小值、取最大值)
LEAST:当有两个或者多个参数时,返回其中的最小值。如果其中一个值为 NULL,则返回结果就为 NULL。
GREATEST:当有两个或者多个参数时,返回其中的最大值。如果其中一个值为NULL, 则返回结果就为 NULL。
若要判断一组数字或字母中哪个最小、哪个最大,可以通过使用 LEAST 和 GREATEST 来实现

案例:

mysql> select least(1,2,3),greatest(1,2,3),least('a','b','c'),greatest('a','b','c');
+--------------+-----------------+--------------------+-----------------------+
| least(1,2,3) | greatest(1,2,3) | least('a','b','c') | greatest('a','b','c') |
+--------------+-----------------+--------------------+-----------------------+
|            1 |               3 | a                  | c                     |
+--------------+-----------------+--------------------+-----------------------+
1 row in set (0.01 sec)

IN 判断一个值是否在对应的列表中,如果是返回 1,否则返回 0。
NOT IN 判断一个值是否不在对应的列表中,如果不是返回 1,否则返回 0
mysql> select 1 in (1,2,3), 2 not in ('a','b','c');
+--------------+------------------------+
| 1 in (1,2,3) | 2 not in ('a','b','c') |
+--------------+------------------------+
|            1 |                      1 |
+--------------+------------------------+
1 row in set, 3 warnings (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

IN 判断一个值是否在对应的列表中,如果是返回 1,否则返回 0。
NOT IN 判断一个值是否不在对应的列表中,如果不是返回 1,否则返回 0

案例:

mysql> select 1 in (1,2,3), 2 not in ('a','b','c');
+--------------+------------------------+
| 1 in (1,2,3) | 2 not in ('a','b','c') |
+--------------+------------------------+
|            1 |                      1 |
+--------------+------------------------+
1 row in set, 3 warnings (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

LIKE 用来匹配字符串,如果匹配成功则返回 1,反之返回 0
LIKE 支持两种通配符:’%%’ 用于匹配任意数目的字符(*匹配的是前面一个字符),而
’_’只能匹配一个字符。
NOT LIKE 正好跟 LIKE 相反,如果没有匹配成功则返回 1,反之返回 0。

案例:

mysql> select 'abc' like 'ab%%','abc' like '_bc','abc' not like 'a%%';
+------------------+------------------+---------------------+
| 'abc' like 'ab%%' | 'abc' like '_bc' | 'abc' not like 'a%%' |
+------------------+------------------+---------------------+
|                1 |                1 |                   0 |
+------------------+------------------+---------------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

2.3 逻辑运算符(布尔值)

逻辑运算符又被称为布尔运算符,通常用来判断表达式的真假,如果为真返回 1,否则 返回 0,真和假也可以用 TRUE 和 FALSE 表示。MySQL 中支持使用的逻辑运算符有四种, 具体如表所示。

运算符描述
not 或 !逻辑非
and 或 &&逻辑与
or逻辑或
xor逻辑异或

2.3.1 逻辑非

逻辑运算符中最简单的运算符就是逻辑非,逻辑非使用 NOT 或!表示。逻辑非将跟在它后面的逻辑测试取反,把真变为假,把假变为真。如果 NOT 后面的操作数为 0 时,所得值为 1;如果操作数为非 0 时,所得值为 0;如果操作数为 NULL 时,所得值为 NULL

案例:

mysql> select not 2,!3,not 0, ! null;
+-------+----+-------+--------+
| not 2 | !3 | not 0 | ! null |
+-------+----+-------+--------+
|     0 |  0 |     1 |   NULL |
+-------+----+-------+--------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

2.3.2 逻辑与(and)

当所有的操作数都为非0值且不为null时,返回值为1,否则为0
(null与0比较特殊)
逻辑与使用 AND 或者&&表示

案例:

mysql> select 2 and 3,4 && 0,4 && null,0 and null,null and null;
+---------+--------+-----------+------------+---------------+
| 2 and 3 | 4 && 0 | 4 && null | 0 and null | null and null |
+---------+--------+-----------+------------+---------------+
|       1 |      0 |      NULL |          0 |          NULL |
+---------+--------+-----------+------------+---------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

由结果可看出
and 和&& 的作用相同
1 and -1 没有0 或 null,所以返回值为1
1 and 0 中由有0,所以返回值为0
1 and null 有Null ,所以返回值为null
null and 0 返回值为0

2.3.3 逻辑或(or)

逻辑或通常使用 OR
逻辑或表示包含的操作数,任意一个为非零值并且不是 NULL 值时,返回 1,否则返回 0。
当有一个操作数为null时,如果另一个操作数为非0值,则返回值为1,否则为null
如两个操作数均为null,则返回值为null

案例:使用或运算符or 进行逻辑判断,运行结果如果如下:

mysql> select 2 or 3,2 or 0, 2 or null, 0 or 0,null or null,0 or null;
+--------+--------+-----------+--------+--------------+-----------+
| 2 or 3 | 2 or 0 | 2 or null | 0 or 0 | null or null | 0 or null |
+--------+--------+-----------+--------+--------------+-----------+
|      1 |      1 |         1 |      0 |         NULL |      NULL |
+--------+--------+-----------+--------+--------------+-----------+
1 row in set (0.00 sec)

mysql> select 0 or null or 2;
+----------------+
| 0 or null or 2 |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

小结:
1 OR -1 OR 0含有0,但同时包含有非0的值1和-1,所以返回结果为1;
1 OR 2 中没有操作数0,所以返回结果为1;
1 OR NULL虽然有 NULL,但是有操作数1,所以返回结果为1;
0 OR NULL中没有非0值,并且有NULL,所以返回值为NULL;
NULL OR NULL中只有NULL,所以返回值为NULL。

2.3.4 逻辑异或(xor)

两个非 NULL 值的操作数,如果两者都是 0 或者都是非 0,则返回 0;
如果一个为 0, 另一个为非 0,则返回结果为 1;
当任意一个值为 NULL 时,返回值为 NULL

案例:

mysql> select 2 xor 3,1 xor 0,0 xor null,null xor null;

+---------+---------+------------+---------------+
| 2 xor 3 | 1 xor 0 | 0 xor null | null xor null |
+---------+---------+------------+---------------+
|       0 |       1 |       NULL |          NULL |
+---------+---------+------------+---------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

2.4 位运算符

位运算符实际上是对二进制数进行计算的运算符。MySQL 内位运算会先将操作数变成二进制格式(1010 1111),然后进行位运算,最后在将计算结果从二进制变回到十进制格式,方便用户查看。MySQL 支持 6 种位运算符,具体如表所示。

位运算符描述
&按位与
|按位或
~按位取反
^按位异或
<<按位左移
>>按位右移

案例:

mysql> select 10 & 15,10 | 15,10 ^ 15,5 &~1;
+---------+---------+---------+-------+
| 10 & 15 | 10 | 15 | 10 ^ 15 | 5 &~1 |
+---------+---------+---------+-------+
|      10 |      15 |       5 |     4 |
+---------+---------+---------+-------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 10 转换为二进制数是 1010, 15 转换为二进制数是 1111
  • 按位与运算(&),是对应的二进制位都是1的,它们的运算结果为 1,否则为 0,所以 10 & 15 的结果为 10。
  • 按位或运算(|),是对应的二进制位有一个或两个为 1 的,运算结果为 1,否则为 0, 所以 10 | 15 的结果为 15。
  • 按位异或运算(^),是对应的二进制位不相同时,运算结果 1,否则为 0,所以 10 ^ 15 的结果为 5。
  • 按位取反(~),是对应的二进制数逐位反转,即 1 取反后变为 0, 0 取反后变为 1。数字 1 的二进制是 0001,取反后变为 1110, 数字 5 的二进制是 0101,将 1110 和 0101
  • 进行求与操作,其结果是二进制的 0100,转换为十进制就是 4

以上不管哪种运算符,在使用过程中都有优先级问题。运算符的优先级决定了不同的运 算符在计算过程中的先后顺序。级别高的运算符会先进行计算,如果运算符的级别相同, MySQL 会按照顺序从左到右依次进行计算,优先级如下表所示:

优先级运算符
1
2~
3^
4*、/、%%
5+,-
6>>,<<
7&
8|
9=,<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
10BETWEEN,CASE,WHEN,THEN,ELSE
11NOT
12&&,AND
13||,OR,XOR
14:=

总结

推荐阅读