MySQL中使用正則表達式詳情

Posted on by

1、簡介

MySQL中支持正則表達式匹配，在復雜的過濾條件中，可以考慮使用正則表達式。使用正則表達式需要掌握一些正則表達式的語法和指令，小捌推薦一個學習地址和在線工具，在學習MySQL中使用正則表達式之前，去瞭解一下正則表達式的語法和指令。

正則表達式學習網址：

www.runoob.com/regexp/rege…

正則表達式在線測試：

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

值得註意的是，MySQL支持的正則表達式僅僅是正則表達式眾多實現的一個子集，在使用正則表達式之前，建議先測試一下。 測試的時候不一定要先建立表、插入數據，可以直接使用select省略form子句，以簡便的方式處理表達式，

比如如下方式：

mysql> select '我愛你中國' regexp '我愛你';
+------------------------------+
| '我愛你中國' regexp '我愛你' |
+------------------------------+
|                            1 |
+------------------------------+


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

2、準備一張product表

首先準備一張product表，DDL和表數據如下所示，可以直接復制使用。

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 '主鍵',
  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '產品名稱',
  `price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '產品價格',
  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;

初始數據如下所示：

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 語句順序

正則表達式的作用是文本匹配，使用一個正則表達式與一個文本內容進行比較，可以校驗文本是否符合正則表達式闡述的規則。在MySQL中，正則表達式用在where子句中，可以對select查詢的數據進行過濾。

select * from table_name where regexp ‘你的正則表達式’ order by cloumn_name;

需求：

查詢產品表中，產品名稱中包含3的產品

語句：

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

結果：

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

2.2 如何區分大小寫

MySQL使用正則表達式默認不區分大小寫，但是大部分情況下我們需要明確英文的大小寫進行匹配，這個時候我們可以使用binary關鍵字。

需求：

查詢產品表中，產品名稱包含huawei的產品

語句：

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

結果：

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

此時查詢結果默認不區分大小寫，所以可以直接查詢出來，如果我們希望查詢區分大小寫，此時隻需要在regexp後面加上binary關鍵字即可。

語句：

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

結果：

Empty set (0.00 sec)

由於product表中並沒有包含小寫huawei的產品，所以返回結果為Empty set

2.3 正則表達式與like的區別

相信有些小夥伴發現上面實現的功能，其實用like也能實現。很多場景下我們使用like來對字符串進行匹配，但是這些場景往往非常簡單，而正則表達式是一個非常強大的文本檢索過濾工具，它的所能實現的功能比like操作符強大太多啦。總之like能做的正則表達式都能做，正則表示能做的like基本上做不瞭（要麼非常棘手）。

比如如下需求，使用正則表達式可以輕松實現，但是like操作符卻不知道怎麼實現瞭。

需求：

查詢產品表中，產品名稱中v至少出現一次產品信息

語句：

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

結果：

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

註意：正則表達式重復元字符的匹配都是整個連續出現。下面給出這幾個重復元字符，感覺有些小夥伴會理解錯。

重復元字符

元字符 說明
* 0個或多個匹配，效果與{0,}一致
+ 1個或多個匹配，效果與{1,}一致
？ 1個或0匹配，效果與{0,1}一致
{n} 等於n個匹配數目
{n,} 大於等於n個匹配
{n,m} 大於等於n小於等於m個，m<255

推薦閱讀：