如何在Mysql中使用Regexp正則表達式

145次閱讀

沒有評論

共計 6221 個字符，預計需要花費 16 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

本篇文章給大家分享的是有關如何在 Mysql 中使用 Regexp 正則表達式，丸趣 TV 小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。

Mysql 中 Regexp 常見用法

模糊匹配，包含特定字符串
# 查找 content 字段中包含“車友俱樂部”的記錄

select * from club_content where content regexp 車友俱樂部

# 此時的 regexp 與 like 的以下用法是等同的

select * from club_content where content like % 車友俱樂部 %

模糊匹配，以特定字符串開頭
# 查找 content 字段中以“車友”開頭的記錄

select * from club_content where content regexp ^ 車友

# 此時的 regexp 與 like 的以下用法是等同的

select * from club_content where content like 車友 %

模糊匹配，以特定字符串結尾
# 查找 content 字段中以“車友”結尾的記錄

select * from club_content where content regexp 車友 $

# 此時的 regexp 與 like 的以下用法是等同的

select * from club_content where content like % 車友

模糊匹配，或關系
# 查找 content 字段中包含“心得”、“分享”或“技術貼”

select * from club_content where content REGEXP 心得 | 分享 | 技術貼

模糊匹配，不包含單個字符
# 查找 content 字段中不包含“車”字、“友”字的記錄

select * from club_content where content REGEXP [^ 車友]

這個結果跑出來一看大吃一驚，竟然把所有記錄給跑出來，這是為什么呢？
因為一旦加了這個方括號 []，它就把里面的內容拆成單個的字符再匹配，它會逐個字符去匹配判斷是不是等于“車”，或者是不是等于“友“，返回的結果是一組 0、1 的邏輯值。

如果想匹配不包含特定字符串，該怎么實現呢？

模糊匹配，不包含特定字符串
# 查找 content 字段不包含“車友”字符串的記錄

select * from club_content where content not REGEXP 車友

MySql REGEXP 運算符匹配字符串

1 ^ 匹配以該字符后面的字符開頭的字符串
舉個例子：REGEXP‘^x 表示匹配以 x 開頭的字符
2 $ 匹配以該字符前面的字符結尾的字符串
舉個例子：REGEXP‘y$ 表示匹配以 y 結尾的字符
3 . 匹配任意一個字符
4 […]匹配在方括號中的任意一個字符。
如：[1-9] 匹配 1 到 9 的數字, [abc]匹配其中任意一個
5 * 匹配零個或多個在它前面的字符
如：x* 匹配任何數量的 x 字符

mysql 如何判斷字符串是否為數字

這個問題有點怪 , 但很多時候我們會以字符串的形式存儲數字 , 反過來我們用字符串進行數學運算時, 好像也不會出錯 . 除非 , 用作數學運算的字符串不能轉換成數字 .
但是我們改如何判斷字符串是否能轉換成數字呢 ?

采用 mysql 的 REGEXP 運算符 . 怎么用?

{String} REGEXP [^0-9.]

前面的字符串是我們要做判斷的，后面的字符串是 mysql 的正則表達式，意思是匹配不是數字或者小數點的字符。

如果 String 中含有不是 0 - 9 之間的數字或者是小數點時, 返回 true，反之則返回 false。

比如說：

select (123a REGEXP [^0-9.] –‘123a 中含有字符 a 輸出結果為 1 mysql 中常量 true 輸出為 1 false 輸出為 0

注意：如果字符串中有空格，也會匹配到正則表達式，返回 1。如果是要去掉兩端的空格，就要將判斷的字符串，就要對字符串使用 trim()函數了。

這個只是 REGEXP 操作符的簡單應用，REGEXP 的詳細應用請參考官方文檔。

MySQL 正則表達式

在前面的章節我們已經了解到 MySQL 可以通過 LIKE …% 來進行模糊匹配。

MySQL 同樣也支持其他正則表達式的匹配，MySQL 中使用 REGEXP 操作符來進行正則表達式匹配。

如果您了解 PHP 或 Perl，那么操作起來就非常簡單，因為 MySQL 的正則表達式匹配與這些腳本的類似。

下表中的正則模式可應用于 REGEXP 操作符中。

模式描述 ^ 匹配輸入字符串的開始位置。如果設置了 RegExp 對象的 Multiline 屬性，^ 也匹配 \n 或 \r 之后的位置。$ 匹配輸入字符串的結束位置。如果設置了 RegExp 對象的 Multiline 屬性，$ 也匹配 \n 或 \r 之前的位置。. 匹配除 \n 之外的任何單個字符。要匹配包括 \n 在內的任何字符，請使用象 [.\n] 的模式。[…]字符集合。匹配所包含的任意一個字符。例如，[abc] 可以匹配 plain 中的 a。[^…]負值字符集合。匹配未包含的任意字符。例如，[^abc] 可以匹配 plain 中的 p。p1|p2|p3 匹配 p1 或 p2 或 p3。例如，z|food 能匹配 z 或 food。(z|f)ood 則匹配 zood 或 food。* 匹配前面的子表達式零次或多次。例如，zo* 能匹配 z 以及 zoo。* 等價于{0,}。+ 匹配前面的子表達式一次或多次。例如，zo+ 能匹配 zo 以及 zoo，但不能匹配 z。+ 等價于 {1,}。{n}n 是一個非負整數。匹配確定的 n 次。例如，o{2} 不能匹配 Bob 中的 o，但是能匹配 food 中的兩個 o。{n,m}m 和 n 均為非負整數，其中 n = m。最少匹配 n 次且最多匹配 m 次。

實例

了解以上的正則需求后，我們就可以根據自己的需求來編寫帶有正則表達式的 SQL 語句。以下我們將列出幾個小實例 (表名：person_tbl) 來加深我們的理解：

查找 name 字段中以 st 為開頭的所有數據：

mysql SELECT name FROM person_tbl WHERE name REGEXP ^st

查找 name 字段中以 ok 為結尾的所有數據：

mysql SELECT name FROM person_tbl WHERE name REGEXP ok$

查找 name 字段中包含 mar 字符串的所有數據：

mysql SELECT name FROM person_tbl WHERE name REGEXP mar

查找 name 字段中以元音字符開頭或以 ok 字符串結尾的所有數據：

mysql SELECT name FROM person_tbl WHERE name REGEXP ^[aeiou]|ok$

mysql 正則 REGEXP 學習練習筆記

REGEXP 在 mysql 是用來執行正則表達式的一個函數，像 php 中的 preg 之類的函數了，regexp 正則函數如果只是簡單的查詢使用 like 即可，但復雜的還是需要使用 regexp 了，下面我們來看看。

MySql 用戶手冊建議，在構造簡單查詢時，仍使用通配符。

如：

Select [*|fieldname list] From [tablename] where [fieldname] like [%someletter | %someletter% , _ , ?someletter

但在一些特殊查詢中，不用正則表達式是不行的。MYSQL 提供的正則表達式 WHERE 謂詞有三個，分別是：

REGEXP, RLIKE, NOT RLIKE

用這三個替換原有的 LIKE 謂詞, 后面即可以跟正則表達式。
例如要查詢字段中含有“_”的數據，則要用以下查詢語句：

SELECT * FROM TABLENAME WHERE FIELDNAME RLIKE .[_].

擴展正則表達式的一些字符是：

·‘. 匹配任何單個的字符。
· 字符類“[…]”匹配在方括號內的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。為了命名字符的范圍，使用一個“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何數字。
·“*”匹配零個或多個在它前面的字符。例如，“x*”匹配任何數量的“x”字符，“[0-9]*”匹配任何數量的數字，而“.*”匹配任何數量的任何字符。
如果 REGEXP 模式與被測試值的任何地方匹配，模式就匹配 (這不同于 LIKE 模式匹配，只有與整個值匹配，模式才匹配)。
為了定位一個模式以便它必須匹配被測試值的開始或結尾，在模式開始處使用“^”或在模式的結尾用“$”。
為了找出以“b”開頭的名字，使用“^”匹配名字的開始：
使用正則

SELECT * FROM pet WHERE name REGEXP BINARY‘^b
SELECT * FROM pet WHERE name REGEXP‘fy$
SELECT * FROM pet WHERE name REGEXP‘w
SELECT * FROM pet WHERE name REGEXP‘^…..$
SELECT * FROM pet WHERE name REGEXP‘^.{5}$

今天在應用中遇到了這樣的一個問題，

有一個字段 t1，其中的值類似于：1,1,1,2,3,3,4,4,5,5,2,4,3,2,1,2
需要從里面搜索出比如說：第一個逗號前的數字范圍為 3 - 5 之間，第三個逗號前的數字的范圍為 3 - 5 之間，第 10 個逗號前的數字范圍為 3 - 5 之間，其余的都為 1 - 5 之間。。。
則 sql 語句可以這么寫：

SELECT * FROM tb WHERE t1 REGEXP ^[3-5],[1-5],[3-5],[1-5],[1-5],[1-5],[1-5],[1-5],[1-5],[3-5],[1-5],[1-5],[1-5],[1-5],[1-5],[1-5]%

1. 使用 LIKE 和 NOT LIKE 比較操作符(注意不能使用 = 或!=);
2. 模式默認是忽略大小寫的；
3. 允許使用”_”匹配任何單個字符，”%”匹配任意數目字符(包括零字符)；

附一些 mysql 正則規則

^ 匹配字符串的開始部分
$ 匹配字符串的結束部分
. 匹配任何字符（包括回車和新行）
a* 匹配 0 或多個 a 字符的任何序列
a+ 匹配 1 個或多個 a 字符的任何序列
a? 匹配 0 個或 1 個 a 字符
de|abc 匹配序列 de 或 abc
(abc)* 匹配序列 adc 的 0 個或者多個實例

{n}、{m,n} {n}或 {m,n} 符號提供了編寫正則表達式的更通用方式，能夠匹配模式的很多前述原子（或“部分”）。m 和 n 均為整數。
a* 可被寫為 a{0,}
a+ 可被寫為 a{1,}
a? 可被寫為 a{0,1}

[a-dX] 匹配任何是 a,b,c,d 或者 X 的字符，兩個其他字符之間的 – 字符構成一個范圍
[^a-dX] 匹配任何不是 a,b,c,d 或者 X 的字符，前面的字符 ^ 是否定的意思

[.characters.] 在括號表達式中（使用[和]）, 匹配用于校對元素的字符序列，字符為單個字符或新行等字符名
mysql SELECT‘~ REGEXP‘[[.~.]] – 1
mysql SELECT‘~ REGEXP‘[[.tilde.]] – 1

[=character_ > 在括號表達式中（使用[和]），[=character_ >[[=a=]] 等同于[a(+)],[a+],[a{1,}]

[:character_class:]
在括號表達式中（使用 [和]），[:character_class:] 表示與術語類的所有字符匹配的字符類。

標準的類名稱是：

alnum 文字數字字符
alpha 文字字符
blank 空白字符
cntrl 控制字符
digit 數字字符
graph 圖形字符
lower 小寫文字字符
print 圖形或空格字符
punct 標點字符
space 空格、制表符、新行、和回車
upper 大寫文字字符
xdigit 十六進制數字字符

[[: :]], [[: :]]

這些標記表示 word 邊界。它們分別與 word 的開始和結束匹配。word 是一系列字字符，其前面和后面均沒有字字符。字符是 alnum 類中的字母數字字符或下劃線(_)。

mysql select‘fang shan zi regexp‘[[: :]]shan[[: :]] – 1
mysql select‘fang shan zi regexp‘[[: :]]fang[[: :]] – 1
mysql select‘fang shans zi regexp‘[[: :]]shan[[: :]] – 0

正則表達式使用特殊字符，應在其前面加上 2 個反斜杠字符

mysql SELECT 1+2′ REGEXP 1+2′; – 0
mysql SELECT 1+2′ REGEXP 1+2′; – 0
mysql SELECT 1+2′ REGEXP 1\+2′; – 1

MySQL 查尋條件使用正則 regexp

我用的是 Mybatis

t.hobby：條件字段
hobby : 查尋參數，值可以是多個逗號分隔的值：‘閱讀, 交友, 圍棋

!– t.hobby 的值最初可能是這樣：吃, 喝, 嫖, 賭 , 抽, 坑, 蒙, 拐, 騙, 偷每個詞的兩邊有空格不規則 —
!– 下面逐步演示 —
if test= hobby!=null and hobby!=
concat(, ,REPLACE (t.hobby, ,), , ) regexp concat(,( ,replace(#{hobby}, , , | ), ), )
/if
!– 表字段 t.hobby 取出值 —
if test= hobby!=null and hobby!=
concat(, ,REPLACE ( 吃, 喝, 嫖, 賭 , 抽, 坑, 蒙, 拐, 騙, 偷 , ,), , ) regexp concat(,( ,replace(#{hobby}, , , | ), ), )
/if
!– 表字段值處理后，去掉了多余的空格 —
if test= hobby!=null and hobby!=
concat(, , 吃, 喝, 嫖, 賭, 抽, 坑, 蒙, 拐, 騙, 偷 , ,) regexp concat(,( ,replace(#{hobby}, , , | ), ), )
/if
!– concat 后得到一個字符串，首尾加上了逗號 —
if test= hobby!=null and hobby!=
, 吃, 喝, 嫖, 賭, 抽, 坑, 蒙, 拐, 騙, 偷, regexp concat(,( ,replace(#{hobby}, , , | ), ), )
/if
!– 取出參數 #{hobby} 的值 —
if test= hobby!=null and hobby!=
, 吃, 喝, 嫖, 賭, 抽, 坑, 蒙, 拐, 騙, 偷, regexp concat(,( ,replace( 吃, 喝, 嫖, 嫖 , , , |), ), )
/if
!– 把逗號換成 | —
if test= hobby!=null and hobby!=
, 吃, 喝, 嫖, 賭, 抽, 坑, 蒙, 拐, 騙, 偷, regexp concat(,( , 吃 | 喝 | 嫖 | 嫖 ,), )
/if
!– concat 后得到一個字符串，首尾加上了括號，逗號 —
if test= hobby!=null and hobby!=
, 吃, 喝, 嫖, 賭, 抽, 坑, 蒙, 拐, 騙, 偷, regexp ,(吃 | 喝 | 嫖 | 嫖), !– 紀念于謙老師不賭的美德 —
/if