Detailed explanation of MySQL regexp command

REGEXPCommands are operators for regular expression matching that allow regular expressions to be used in queries to match string pattern‌‌1.

Basic syntax

The basic syntax structure is as follows:

SHOW FULL PROCESSLIST;

here,patternis the regular expression pattern ‌1 you want to match.

Commonly used regular expression symbols and their meanings

^: Match the beginning of the string.

$: Match the end of the string.

.: Match any single character.

*: Match the previous character zero or multiple times.

+: Match the previous character once or more times.

?: Match the previous character zero or once.

|: means "or" operation.

[]: Match any single character in square brackets, for example[abc]Match a, b, or c‌1.

Sample query ‌

Match lines containing specific characters‌：

SELECT * FROM users WHERE name REGEXP '^[A-M]'; -- Match with A arrive M The name at the beginning

‌Match the order number containing the number‌：

SELECT * FROM orders WHERE order_number REGEXP '[0-9]'; -- Match the order number containing the number

‌Match multiple options‌：

SELECT * FROM products WHERE category REGEXP 'electronics|furniture'; -- Match electronics or furniture

‌case sensitive‌: If you need to be case sensitive, you can useBINARYKeywords:

SELECT * FROM users WHERE BINARY name REGEXP '^[A-M]'; -- Match with A arrive M The name at the beginning，case sensitive

‌Match emails under specific domain names‌：

SELECT * FROM employees WHERE email REGEXP '@$'; -- Match all in  Email under the domain name

‌Match rows containing only numbers‌：

SELECT buildnum FROM ya_evaluating_price WHERE buildnum REGEXP '^[0-9]+$';

‌Match data containing Chinese characters：

SELECT buildnum FROM ya_evaluating_price WHERE buildnum REGEXP '[\\x{4e00}-\\x{9fa5}]'
-- existMySQLmiddle，UTF8MB4Character sets support allUnicodecharacter，包括middle文character。我们可以将字段的character集设置为UTF8MB4，Then determine whether the length of the field content is greater than the length of the original content，以此来判断字段内容是否包含middle文。
SELECT buildnum FROM ya_evaluating_price WHERE CHAR_LENGTH(buildnum) &lt; LENGTH(buildnum)

Performance Impact and Best Practices

useREGEXPMay affect performance, especially on large datasets. Therefore, it is necessary to ensure the correctness of the regular expression to avoid accidental matches‌

This is the end of this article about the detailed explanation of the MySQL regexp command. For more related contents of the mysql regexp command, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!