Mysql 简明教程
MySQL - Regular Expressions
MySQL 支持各种模式匹配操作，以从大型数据库表中检索筛选结果集。在前面的章节中，我们已经学习过用于模式匹配的 LIKE 运算符。在本章中，我们将看到另一个基于正则表达式的模式匹配操作。
MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. In previous chapters, we have already learned about the LIKE operator for pattern matching. In this chapter, we will see another pattern matching operation based on regular expressions.
MySQL Regular Expressions
正则表达式松散地定义为一系列字符，这些字符表示输入文本中的一个模式。它用于使用某些模式查找或替换文本字符串；此模式可以是单个字符、多个字符或单词等。
A regular expression is loosely defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single character, multiple characters or words, etc.
在 MySQL 中，这是一种在数据库中执行复杂搜索操作以检索所需内容的强大方法。与 LIKE 运算符不同，正则表达式不受搜索模式（如 % 和 _）的限制，因为它们使用几个其他元字符来扩展模式匹配中的灵活性和控制。这是使用 REGEXP 运算符执行的。
In MySQL, it is a powerful way to perform a complex search operations in a database to retrieve desired content. And unlike the LIKE operator, the regular expressions are not restricted on search patterns (like % and _) as they use several other meta characters to expand the flexibility and control during pattern matching. This is performed using the REGEXP operator.
Patterns used with REGEXP
以下是模式表，可与 REGEXP 运算符一起使用。
Following is the table of pattern, which can be used along with the REGEXP operator.
Examples
以下示例演示了表中提到的某些模式与 REGEXP 运算符的用法。为此，我们首先创建一个数据库表以执行搜索。
The following example demonstrates the usage of some patterns mentioned in the table above, along with the REGEXP operator. For that, we are first creating a database table to perform the search on.
假设我们使用以下查询创建一个名为 CUSTOMERS 的表：
Assume we are creating a table called CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，使用下面给出的 INSERT 语句向其中插入一些值：
Now, insert some values into it using the INSERT statements given below −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );执行以下查询以显示上述创建的表中存在的全部记录：
Execute the following query to display all the records present in above created table −
SELECT * FROM CUSTOMERS;CUSTOMERS 表中存在的记录如下：
Following are the records present in CUSTOMERS table −
REGEXP with Patterns −
REGEXP with Patterns −
现在，我们正在查找 CUSTOMERS 表中所有名称以 'k' 开头的记录：
Now, we are finding all the records in the CUSTOMERS table whose name starts with 'k' −
SELECT * FROM CUSTOMERS WHERE NAME REGEXP '^k';执行上述查询将产生以下输出：
Executing the query above will produce the following output −
以下查询检索 CUSTOMERS 表中所有名称以 'sh' 结尾的记录：
The following query retrieves all records in CUSTOMERS table whose name ends with 'sh' −
SELECT * FROM CUSTOMERS WHERE NAME REGEXP 'sh$';执行上述查询将产生以下输出：
Executing the query above will produce the following output −
在此，我们正在检索所有名称包含 'sh' 的记录：
Here, we are retrieving all the records whose name contain 'sh' −
SELECT * FROM CUSTOMERS WHERE NAME REGEXP 'sh';正如我们看到输出所示，只有两个名称包含“sh”。
As we can see the output, there are only two names that contain 'sh'.
在以下查询中，我们正在查找所有以元音开头并以 'ol' 结尾的名称：
In the following query, we are finding all the names starting with a vowel and ending with 'ol' −
SELECT * FROM CUSTOMERS WHERE NAME REGEXP '^[aeiou].*ol$';它返回一个空集，因为 CUSTOMERS 表中没有任何名称以元音开头并以“ol”结尾。
It returned an empty set because the CUSTOMERS table do not have any names who starts with vowel and ends with 'ol'
Empty set (0.00 sec)以下查询找到 CUSTOMERS 表中所有名称以辅音开头的名称：
The following query finds all the names in the CUSTOMERS table whose name starts with a consonant −
SELECT * FROM CUSTOMERS WHERE NAME REGEXP '^[^aeiou]';执行上述查询将产生以下输出：
Executing the query above will produce the following output −