Mysql 简明教程
MySQL - REGEXP_LIKE() Function
MySQL 支持多种模式匹配操作来从庞大的数据库表中检索筛选后的结果集。但是，使用正则表达式进行模式匹配是执行复杂搜索的强大方式。
MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search.
正如我们在上一章中看到的那样，MySQL regexp_instr() 函数用于返回找到的模式的位置。但是，如果您只想检测数据中是否存在模式，则可以使用 regexp_like() 函数。
As we have seen in the previous chapter, the MySQL regexp_instr() function is used to return the position of the pattern found. But if you want to just detect whether the pattern is present in the data or not, you can use the regexp_like() function.
MySQL REGEXP_LIKE() Function
MySQL regexp_like() 函数还用于搜索与指定模式匹配的字符串。如果此字符串与指定模式匹配，则此函数返回 1；如果找不到匹配项，则返回 0；如果字符串或模式为 NULL，则返回 NULL。此函数中使用的模式可以是扩展的正则表达式，而不仅仅是一个普通字符串。
The MySQL regexp_like() function is also used to search for a string that is matched with specified patterns. This function returns 1 if this string matches the specified pattern, 0 if there is no match, or NULL if the string or the pattern is NULL. The pattern used in this function can be an extended regular expression and not just an ordinary string.
Syntax
以下是 MySQL regexp_like() 函数的语法 -
Following is the syntax of the MySQL regexp_like() function −
REGEXP_LIKE(expr, pattern[, match_type])Parameters
regexp_like() 函数采用以下参数值 -
The regexp_like() function takes following parameter values −
-
expr: The string in which search is performed
-
pattern: The pattern that is searched in the string
-
match_type: (Optional argument) A string that specifies how to perform matching; includes case-sensitive matching(c), case-insensitive matching(i), multiple-line mode(m), matching line terminators(n), matching Unix-only line endings(u).
Example
在此示例中，我们使用 MySQL REGEXP_LIKE() 函数对一个简单的字符串执行搜索操作 -
In this example, we are performing a search operation on a simple string using the MySQL REGEXP_LIKE() function −
SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 'To')
AS RESULT;搜索模式 'To' 出现在字符串中，因此它返回 1 作为输出。
The search pattern 'To' is present in the string, so it returned 1 as output.
现在，如果在字符串中找不到匹配项，那么结果将获取为 '0'，如下所示 -
Now, if there is no match found in the string, the result will be obtained as '0' as shown below −
SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 'Hello')
AS RESULT;输出如下：
Following is the output −
我们还将可选参数作为区分大小写的匹配 (c) 传递给此函数，并观察结果 -
Let us also pass the optional arguments to this function as case-sensitive matching(c) and observe the result −
SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 't', 'c')
AS RESULT;执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Example
如果传递给此函数的前两个参数中的任何一个为 NULL，则此函数返回 NULL。在以下查询中，我们将 NULL 传递给字符串参数。
If either of the first two arguments passed to this function is NULL, this function returns NULL. In the below query, we are passing NULL to the string parameter.
SELECT REGEXP_LIKE(NULL, 'value') AS Result;输出如下：
Following is the output −
在此处，我们传递 NULL 作为搜索模式 -
Here, we are passing NULL as the search pattern −
SELECT REGEXP_LIKE('Welcome to Tutorialspoint', NULL)
AS Result;执行上述查询将产生以下输出：
Executing the query above will produce the following output −
Example
在另一个示例中，让我们使用 REGEXP_LIKE() 函数对名为 CUSTOMERS 的数据库表执行搜索操作。首先，让我们使用以下查询创建表 -
In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_LIKE() function. Firstly, let us create the table 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)
);以下查询在上述创建的表中插入 7 条记录 −
The following query inserts 7 records into the above created table −
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 of CUSTOMERS table −
Select * from CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
以下查询从 CUSTOMERS 表中选择 NAME 列以字母 'K' 开头的记录 -
The following query selects records from the CUSTOMERS table where the NAME column starts with the letter 'K' −
SELECT REGEXP_LIKE(NAME, '^K')
AS RESULT FROM CUSTOMERS;如果有一个以字母 'K' 开头的名称，则它会输出 1，否则输出 0 -
If there is a name that starts with letter 'K' it gives 1 as output, else 0 −
以下查询检查 'CUSTOMERS' 表中的 'ADDRESS' 列是否包含字母 'K'（不区分大小写）。如果地址包含 'K' 或 'k'，则结果为 1；否则为 0。
The following query checks whether the 'ADDRESS' column in the 'CUSTOMERS' table contains the letter 'K' (case-insensitive). If the address contains 'K' or 'k,' the result is 1; otherwise, it’s 0.
SELECT REGEXP_LIKE(ADDRESS, 'R', 'i')
AS RESULT FROM CUSTOMERS;正如我们在输出表中看到的那样，ADDRESS 列中的第 6 行和第 7 行包含一个字母 'K'（不区分大小写） -
As we can see in the output table, 6th and 7th row in ADDRESS column contains a letter 'K' (case-insensitive) −
REGEXP_LIKE() Function Using a Client Program
除了使用 MySQL 查询来执行 REGEXP_Like() 函数之外，我们还可以使用诸如 PHP、Node.js、Java 和 Python 之类的客户端程序来实现相同的结果。
Besides using MySQL queries to perform the REGEXP_Like() function, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result.