Details on using regular expressions in MySQL

1. Introduction

MySQL supports regular expression matching. In complex filtering conditions, you can consider using regular expressions. To use regular expressions, you need to master some regular expression syntax and instructions. Xiaoba recommends a learning address and online tools. Before learning how to use regular expressions in MySQL , you should understand the syntax and instructions of regular expressions.

Regular expression learning website:

www.runoob.com/regexp/reg…

Regular expression online test:

c.runoob.com/front-end/8…

It is worth noting that the regular expressions supported by MySQL are only a subset of the many regular expression implementations. It is recommended to test regular expressions before using them. When testing, you do not have to create a table and insert data first. You can directly use select to omit the form clause and process the expression in a simple way.

For example, as follows:

mysql> select 'I love you China' regexp 'I love you';
+------------------------------+
| 'I love you China' regexp 'I love you' |
+------------------------------+
| 1 |
+------------------------------+

mysql> select '12306' regexp '[:digit:]';
+----------------------------+
| '12306' regexp '[:digit:]' |
+----------------------------+
| 1 |
+----------------------------+

2. Prepare a product table

First, prepare a product table. The DDL and table data are as follows and can be copied and used directly.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Product name',
  `price` decimal(10, 2) UNSIGNED NOT NULL COMMENT 'Product price',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, 'Apple iPhone 13 (A2634)', 6799.00);
INSERT INTO `product` VALUES (2, 'HUAWEI P50 Pro', 6488.00);
INSERT INTO `product` VALUES (3, 'MIX4', 4999.00);
INSERT INTO `product` VALUES (4, 'OPPO Find X3', 3999.00);
INSERT INTO `product` VALUES (5, 'vivo X70 Pro+', 5999.00);

SET FOREIGN_KEY_CHECKS = 1;

The initial data looks like this:

mysql> select * from product;
+----+-------------------------+---------+
| id | product_name | price |
+----+-------------------------+---------+
| 1 | Apple iPhone 13 (A2634) | 6799.00 |
| 2 | HUAWEI P50 Pro | 6488.00 |
| 3 | MIX4 | 4999.00 |
| 4 | OPPO Find X3 | 3999.00 |
| 5 | vivo X70 Pro+ | 5999.00 |
+----+-------------------------+---------+

2.1 Statement Order

The function of regular expressions is text matching. By comparing a regular expression with a text content, you can verify whether the text conforms to the rules described by the regular expression. In MySQL , regular expressions are used in the where clause to filter the data in the select query.

select * from table_name where regexp 'your regular expression' order by cloumn_name ;

need:

Query the product table for products whose product names contain 3

Statement:

mysql> select * from product where product_name regexp '3';

result:

+----+-------------------------+---------+
| id | product_name | price |
+----+-------------------------+---------+
| 1 | Apple iPhone 13 (A2634) | 6799.00 |
| 4 | OPPO Find X3 | 3999.00 |
+----+-------------------------+---------+

2.2 How to distinguish between uppercase and lowercase letters

MySQL uses regular expressions that are case-insensitive by default, but in most cases we need to match the English case explicitly. In this case, we can use the binary keyword.

need:

Search the product table for products whose product names contain Huawei

Statement:

mysql> select * from product where product_name regexp 'huawei';

result:

+----+----------------+---------+
| id | product_name | price |
+----+----------------+---------+
| 2 | HUAWEI P50 Pro | 6488.00 |
+----+----------------+---------+

At this time, the query results are case-insensitive by default, so they can be queried directly. If we want the query to be case-sensitive, we only need to add the binary keyword after regexp .

Statement:

mysql> select * from product where product_name regexp binary 'huawei';

result:

Empty set (0.00 sec)

Since product table does not contain the lowercase huawei product, the returned result is Empty set

2.3 The difference between regular expressions and like

I believe some of you have discovered that the functions implemented above can actually be achieved using like. In many scenarios, we use like to match strings, but these scenarios are often very simple. Regular expressions are a very powerful text retrieval and filtering tool, and the functions they can achieve are much more powerful than the like operator. In short, anything that LIKE can do with regular expressions, basically nothing that regular expressions can do with LIKE (or it's very tricky).

For example, the following requirement can be easily implemented using regular expressions, but I don’t know how to implement the like operator.

need:

Query the product table, and find the product information where v appears at least once in the product name

Statement:

mysql> select * from product where product_name regexp 'v+';

result:

+----+---------------+---------+
| id | product_name | price |
+----+---------------+---------+
| 5 | vivo X70 Pro+ | 5999.00 |
+----+---------------+---------+

Note: The regular expression matches repeated metacharacters as entire consecutive occurrences. Here are some repeating metacharacters. I think some of you may misunderstand them.

Repeating Metacharacters