Mariadb 简明教程
MariaDB - Regular Expression
除了 LIKE 子句中可用的模式匹配外，MariaDB 通过 REGEXP 运算符提供基于正则表达式的匹配。该运算符根据给定的模式对字符串表达式执行模式匹配。
Beyond the pattern matching available from LIKE clauses, MariaDB offers regular expression-based matching through the REGEXP operator. The operator performs pattern matching for a string expression based on a given pattern.
MariaDB 10.0.5 引入了 PCRE 正则表达式，这极大地增加了匹配范围，涉及递归模式、前瞻断言等领域。
MariaDB 10.0.5 introduced PCRE Regular Expressions, which dramatically increases the scope of matching into areas like recursive patterns, look-ahead assertions, and more.
查看下面给出的标准 REGEXP 运算符语法的用法 −
Review the use of standard REGEXP operator syntax given below −
SELECT column FROM table_name WHERE column REGEXP '[PATTERN]';如果模式匹配，REGEXP 返回 1；否则返回 0。
REGEXP returns 1 for a pattern match or 0 in the absence of one.
NOT REGEXP 形式的选项与之相反。MariaDB 还为 REGEXP 和 NOT REGEXP 提供了同义词 RLIKE 和 NOT RLIKE，这些同义词出于兼容性原因创建。
An option for the opposite exists in the form of NOT REGEXP. MariaDB also offers synonyms for REGEXP and NOT REGEXP, RLIKE and NOT RLIKE, which were created for compatibility reasons.
要比较的模式可以是文本字符串或诸如表列的其它内容。在字符串中，它使用 C 转义语法，因此对所有“\”字符进行加倍。REGEXP 也区分大小写，二进制字符串除外。
The pattern compared can be a literal string or something else such as a table column. In strings, it uses C escape syntax, so double any “\” characters. REGEXP is also case-insensitive, with the exception of binary strings.
下面给出了可以使用的一个模式表 −
A table of possible patterns, which can be used are given below −
Sr.No |
Pattern & Description |
1 |
^ It matches the start of the string. |
2 |
$ It matches the string’s end. |
3 |
. It matches a single character. |
4 |
[…] It matches any character in the brackets. |
5 |
[^…] It matches any character not listed in the brackets. |
6 |
*p1 |
p2 |
p3* It matches any of the patterns. |
7 |
* It matches 0 or more instances of the preceding element. |
8 |
+ It matches 1 or more instances of the preceding element. |
9 |
{n} It matches n instances of the preceding element. |
10 |
{m,n} It matches m to n instances of the preceding element. |
查看下面提供的模式匹配示例 −
Review the pattern matching examples given below −
以“pr”开头的产品 −
Products starting with “pr” −
SELECT name FROM product_tbl WHERE name REGEXP '^pr';以“na”结尾的产品 −
Products ending with “na” −
SELECT name FROM product_tbl WHERE name REGEXP 'na$';以元音开头的产品 −
Products starting with a vowel −
SELECT name FROM product_tbl WHERE name REGEXP '^[aeiou]';