Mysql 简明教程
MySQL - RLIKE Operator
MySQL RLIKE Operator
MySQL 中的 RLIKE 运算符用于使用模式（或正则表达式）搜索数据库中的数据，也称为模式匹配。换句话说，RLIKE 运算符用于确定给定正则表达式是否与表中的记录匹配。如果记录匹配，它返回 1；否则返回 0。
The RLIKE operator in MySQL is used to search data in a database using patterns (or regular expressions), also known as pattern matching. In other words, the RLIKE operator is used to determine whether a given regular expression matches a record in a table or not. It returns 1 if the record is matched and 0, otherwise.
此运算符的功能等效于 MySQL REGEXP 运算符，通常用于搜索符合特定条件的特定模式。
The functionally of this operator is equivalent to the MySQL REGEXP operator and is commonly used to search for specific patterns that meets certain criteria.
Patterns used with RLIKE
RLIKE 运算符与多个模式或正则表达式一起使用。以下是可与该运算符一起使用的模式表。
RLIKE operator is used with several patterns or regular expressions. Following is the table of patterns that can be used along with the this operator.
Example
以下示例使用 RLIKE 运算符根据正则表达式检索记录。为此，我们首先使用以下查询创建一个名为 CUSTOMERS 的表：
The following example uses the RLIKE operator to retrieve records with the help of regular expressions. To do so, we are first creating a table named 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 the above created table 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 );执行以下查询以显示 CUSTOMERS 表中存在的所有记录：
Execute the following query to display all the records present in the CUSTOMERS table −
SELECT * FROM CUSTOMERS;CUSTOMERS 表中存在的记录如下：
Following are the records present in CUSTOMERS table −
RLIKE with Patterns -
RLIKE with Patterns −
在以下查询中，我们正在从 CUSTOMERS 表中查找以 'ch' 开头的所有记录：
In the following query, we are finding all the records from CUSTOMERS table whose name starts with 'ch' −
SELECT * FROM CUSTOMERS WHERE NAME RLIKE '^ch';执行上述查询将产生以下输出：
Executing the query above will produce the following output −
以下查询显示所有名称以 'sh' 结尾的记录：
The following query displays all the records whose names ends with 'sh' −
SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE 'sh$';以下是名称以“sh”结尾的记录：
Following are records whose name ends with 'sh' −
在这里，我们正在检索包含“an”的名称记录：
Here, we are retrieving the records that have names containing 'an' −
SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE 'an';以下是记录：
Following are the records −
以下查询检索所有名称以元音结尾的记录：
This following query retrieves all the records whose names are ending with an vowel −
SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE '[aeiou]$';以下是记录：
Following are the records −
以下查询查找所有以辅音开头并以 'ya' 结尾的名称：
The below query finds all the names starting with a consonant and ending with 'ya' −
SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE '^[^aeiou].*ya$';正如我们观察到的输出，没有记录以辅音开头并以“ya”结尾。
As we observe the output, there are no records that starts with consonant and ends with 'ya'.
Empty set (0.00 sec)RLIKE On Strings
RLIKE 运算符不仅可以对数据库表执行模式匹配，还可以对单个字符串执行模式匹配。如果模式存在于给定字符串中，则结果将获得 1；如果不存在，则获得 0。使用 SQL SELECT 语句将结果作为结果集检索。
The RLIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 1 if the pattern exists in the given string, or 0 if it doesn’t. The result is retrieved as a result-set using the SQL SELECT statement.
Syntax
以下是 MySQL 中 RLIKE 运算符的基本语法：
Following is the basic syntax of the RLIKE operator in MySQL −
SELECT expression RLIKE pattern;Example
在以下示例中，我们使用 RLIKE 查询来检查模式是否存在于单个字符串中：
In the following example, we are using the RLIKE query to check if a pattern exists in an individual string or not −
SELECT 'Welcome To Tutorialspoint!' RLIKE 'To';结果集将包含 1，因为模式“TO”存在于指定字符串中。
The result-set will contain 1 because the pattern 'TO' exists in the specifed string.
这里，模式“Hello”不存在于指定字符串中，因此它返回 0 作为输出。
Here, the pattern 'Hello' does not exist in the specifed string, thus it returns 0 as output.
SELECT 'Welcome To Tutorialspoint!' RLIKE 'Hello';执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Example
REGEXP 是 MySQL 中 RLIKE 的替代语法。两个运算符具有相同的结果。
REGEXP is alternative syntax to the RLIKE in MySQL. Both the operators have same result.
在下面的查询中，如果在指定字符串中未找到给定模式，此运算符返回 0 −
In the below query, if the given pattern is not found in the specifed string, this operator returns 0 −
SELECT 'Welcome to Tutorialspoint' REGEXP 'unknown';输出如下：
Following is the output −
这里，模式“is”不存在于指定字符串中，因此它返回 1 作为输出。
Here, the pattern 'is' does not exist in the specifed string, thus it returns 1 as output.
SELECT 'This is a sample string' REGEXP 'is';执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Example
如果前两个操作数中的任何一个为 NULL，则 RLIKE 运算符返回 NULL。
If either of the first two operands is NULL, the RLIKE operator returns NULL.
SELECT NULL RLIKE 'value';输出如下：
Following is the output −
这里，我们搜索的模式为 NULL，因此输出也将为 NULL。
Here, the pattern we are searching is NULL, thus the output will also be NULL.
SELECT 'Tutorialspoint' RLIKE NULL;执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Example
如果在 RLIKE 运算符之前使用 NOT 子句，则在匹配的情况下返回 0，否则返回 1（原始返回值的反向）。
If you use the NOT clause before RLIKE operator, it returns 0 in case of a match else returns 1 (reverse of the original return values).
SELECT NOT 'This is a sample string' RLIKE 'is';输出如下：
Following is the output −
这里，模式“unknown”不存在于指定字符串中，因此以下查询返回 1 作为输出。
Here, the pattern 'unknown' is not present in the specifed string, thus the following query returns 1 as output.
SELECT NOT 'Welcome to Tutorialspoint' REGEXP 'unknown';执行上述查询将产生以下输出：
Executing the query above will produce the following output −
RLIKE Operator Using a Client Program
我们还可以使用客户端程序执行 MySQL RLike 运算符，以使用模式（或正则表达式）在数据库中搜索数据。
We can also perform the MySQL RLike operator using the client programs to search data in a database using patterns (or regular expressions).