Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to use regular expressions in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

1. Introduction

Regular expression matching is supported in MySQL, and regular expressions can be considered in complex filtering conditions. To use regular expressions, you need to master some syntax and instructions for regular expressions. Koji recommends a learning address and online tool to learn about regular expression syntax and instructions before learning to use regular expressions in MySQL.

Regular expression Learning website:

Www.runoob.com/regexp/rege...

Regular expression online testing:

C.runoob.com/front-end/8...

It is worth noting that the regular expressions supported by MySQL are only a subset of the many implementations of regular expressions, and it is recommended that you test them before using them. When testing, you don't have to create a table and insert data first. You can use select to omit the form clause and deal with expressions 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 of all, prepare a product table. The DDL and table data are shown below, which can be copied directly.

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0Mutual-Table structure for product-- DROP TABLE IF EXISTS `product` CREATE TABLE `product` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'key', `product_ name` varchar '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 Pro+', 3999.00); INSERT INTO `product` VALUES (5,' vivo X70 Pro+', 5999.00); SET FOREIGN_KEY_CHECKS = 1

The initial data is as follows:

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.1sentence order

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

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

Demand:

Query the products that contain 3 in the product table.

Statement:

Mysql > select * from product where product_name regexp'3'

Results:

+-+ | id | product_name | price | +-+ | 1 | Apple iPhone 13 (A2634) | 6799.00 | | 4 | OPPO Find X3 | | 3999.00 | +-+ 2.2 how to distinguish between case and case |

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

Demand:

Query the product table for the product whose name contains huawei

Statement:

Mysql > select * from product where product_name regexp 'huawei'

Results:

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

At this time, the query result is case-insensitive by default, so you can query it 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'

Results:

Empty set (0.00 sec)

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

2.3 the difference between regular expressions and like

I believe that some small partners find that the functions implemented above can also be realized with like. We use like to match strings in many scenarios, but these scenarios are often very simple, and regular expressions are a very powerful text retrieval and filtering tool that is much more powerful than the like operator. In short, regular expressions that like can do can be done, and regular expressions mean that like that can be done is basically impossible (or very tricky).

For example, the following requirements can be easily implemented using regular expressions, but the like operator does not know how to implement them.

Demand:

Query the product table, v appears at least once in the product name

Statement:

Mysql > select * from product where product_name regexp'Variety'

Results:

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

Note: the matching of regular expression repeating metacharacters occurs throughout the continuum. These repeated metacharacters are given below, and I feel that some of my friends will misunderstand them.

Repeat metacharacter

Metacharacter description * 0 or more matches, the effect is consistent with {0,} + 1 or more matches, the effect is consistent with {1,}? 1 or 0 match, the effect is the same as {0reagen1} {n} equals n matches {n,} greater than or equal to n matches {nfocus m} greater than or equal to n less than m, m

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.