Mysql 简明教程
MySQL - NOT REGEXP Operator
MySQL NOT REGEXP Operator
从技术上讲，正则表达式被定义为一串字符，它表示输入文本中的一个模式。它用于使用一些模式查找或替换文本字符串；此模式可以是单个字符、多个字符或单词等。
Technically, a regular expression is 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 中， REGEXP 运算符是一个功能强大的工具，用于在数据库中执行复杂搜索操作，以使用正则表达式检索所需数据。与 LIKE 运算符不同，REGEXP 运算符不受搜索模式（如 % 和 _）的限制，因为它使用其他几个元字符来扩展模式匹配期间的灵活性和控制。
In MySQL, the REGEXP operator is a powerful tool to perform complex search operations in a database to retrieve required data using regular expressions. And unlike the LIKE operator, the REGEXP operator is not restricted on search patterns (like % and _), as it uses several other meta characters to expand the flexibility and control during pattern matching.
NOT REGEXP 运算符对该 REGEXP 运算符取反。它用于检索数据库中所有不满足指定模式的记录。让我们在本章中进一步详细了解此运算符。
NOT REGEXP Operator is a negation to this REGEXP operator. It is used to retrieve all the records present in a database that do not satisfy the specified pattern. Let us learn about this operator in detail further in this chapter.
Syntax
以下是 MySQL NOT REGEXP 运算符的基本语法 −
Following is the basic syntax of the MySQL NOT REGEXP operator −
expression NOT REGEXP patternNote − 由于这只是取反，NOT REGEXP 运算符的功能将与 REGEXP 运算符相同。
Note − Since this is just a negation, the functionality of a NOT REGEXP operator will be the same as a REGEXP operator.
Examples
让我们首先使用以下查询创建一个名为 CUSTOMERS 的表 −
Let us start by 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 语句将 7 条记录插入到上面创建的表中 −
The below INSERT statement 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 present in CUSTOMERS table −
Select * from CUSTOMERS;以下为 CUSTOMERS 表 −
Following is the CUSTOMERS table −
现在，让我们使用该表中的几个查询来展示 NOT REGEXPM 运算符的使用。
Now, let us show the usage of NOT REGEXPM operator using several queries on this table.
查找所有不以 'ra' 开头的名称的查询 −
Query to find all the names not starting with 'ra' −
SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP '^ra';输出如下：
Following is the output −
检索名称不以 'ik' 结尾的所有记录的查询 −
Query to retrieve all the records whose names not ending with 'ik' −
SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP 'ik$';输出如下：
Following is the output −
以下是查找所有不包含 'an' 的名称的查询 −
Following is the query to find all the names that do not contain 'an' −
SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP 'an';输出如下：
Following is the output −
Example
NOT RLIKE 是 MySQL 中 NOT REGEXP 的替代语法。两个运算符具有相同的结果。如果前两个操作数之一为 NULL，则此运算符返回 NULL。
The NOT RLIKE is alternative syntax to the NOT REGEXP in MySQL. Both the operators have same result. If either of the first two operands is NULL, this operator returns NULL.
在下面的查询中，字符串值为 NULL。因此，它将输出为 NULL。
In the below query, the string value is NULL. Thus it gives output as NULL.
SELECT NULL NOT RLIKE 'value';输出如下：
Following is the output −
此处，指定模式为 NULL。因此，结果将检索为 NULL。
Here, the specified pattern is NULL. So, the output will be retrieved as NULL.
SELECT 'Tutorialspoint' NOT REGEXP NULL;输出如下：
Following is the output −
NOT REGEXP Operator Using a Client Program
除了使用 MySQL 查询执行 NOT REGEXP 运算符之外，我们还可以使用诸如 PHP、Node.js、Java 和 Python 等客户端程序来实现相同的结果。
Besides using MySQL queries to perform the NOT REGEXP operator, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result.