Mysqli 简明教程

MySQLi - Regexps

您已使用 LIKE …​% 看到 MySQL 模式匹配。MySQL 支持另一种基于正则表达式和 REGEXP 运算符的模式匹配操作。如果您熟悉 PHP 或 PERL，那么对您来说理解起来很简单，因为这种匹配与那些脚本正则表达式非常相似。

You have seen MySQL pattern matching with LIKE …​%. MySQL supports another type of pattern matching operation based on regular expressions and the REGEXP operator. If you are aware of PHP or PERL, then it’s very simple for you to understand because this matching is very similar to those scripting regular expressions.

以下是可与 REGEXP 运算符一起使用的模式表。

Following is the table of pattern, which can be used along with REGEXP operator.

Pattern

What the pattern matches

^

Beginning of string

$

End of string

.

Any single character

[…​]

Any character listed between the square brackets

[^…​]

Any character not listed between the square brackets

p1

p2

p3

Alternation; matches any of the patterns p1, p2, or p3

*

Zero or more instances of preceding element

+

One or more instances of preceding element

{n}

n instances of preceding element

{m,n}

m through n instances of preceding element

Examples

现在，基于上表，您可以设计各种类型的 SQL 查询以满足您的要求。这里我列出一些供您理解。假设我们有一张名为 tutorials_inf 的表，它有一个名为 name 的字段−

Now based on above table, you can device various type of SQL queries to meet your requirements. Here, I’m listing few for your understanding. Consider we have a table called tutorials_inf and it’s having a field called name −

查找所有以“sa”开头的名称的查询

Query to find all the names starting with 'sa'

mysql>  SELECT * FROM tutorials_inf WHERE name REGEXP '^sa';

示例输出如下所示 −

The sample output should be like this −

+----+------+
| id | name |
+----+------+
|  1 | sai  |
+----+------+
1 row in set (0.00 sec)

查找所有以“ai”结尾的名称的查询

Query to find all the names ending with 'ai'

mysql> SELECT * FROM tutorials_inf WHERE name REGEXP 'ai$';

示例输出如下所示 −

The sample output should be like this −

+----+------+
| id | name |
+----+------+
|  1 | sai  |
+----+------+
1 row in set (0.00 sec)

查找包含“a”的所有名称的查询

Query to find all the names, which contain 'a'

mysql> SELECT * FROM tutorials_inf WHERE name REGEXP 'a';

示例输出如下所示 −

The sample output should be like this −

+----+-------+
| id | name  |
+----+-------+
|  1 | sai   |
|  3 | ram   |
|  4 | johar |
+----+-------+
3 rows in set (0.00 sec)

查找所有以元音开头的名称的查询

Query to find all the names starting with a vowel

mysql>  SELECT * FROM tutorials_inf WHERE name REGEXP '^[aeiou]';