[MySQL/Mariadb] 正規表達式(regular express) @新精讚

[無邊框]

9659

[MySQL/Mariadb] 正規表達式(regular express)

“

mysql的正規表達式很詭異，很不能接受

„

分享此文連結 //n.sfs.tw/11494

分享連結 [MySQL/Mariadb] 正規表達式(regular express)@新精讚

(文章歡迎轉載，務必尊重版權註明連結來源)

2019-10-25 09:51:18 最後編修

2017-07-26 07:36:32 By 張○○

自動目錄

正則表達式語法
以分類比對
特殊字符
範例
參考資料

MySQL也有正規表達式，可以讓我們在選取時就能先判斷。

舉例而言，假設在欄位`role`中的資料是字串，內容長成這樣 1,2,3,6,8,12,14,15,21,22,25,....99

我希望取出欄位 `role` 中如果有數字 2 的資料，如果用這種方法取用，也許會取回我要的資料，但如果 2 在第一筆或最後一筆，就會取不到資料：

SELECT * FROM `table` WHERE `role` likes '%,2,%' <== 誤

再如果把 ',' 拿掉，則含有 2, 12, 21, 22, 25... 的資料都會被取出：

SELECT * FROM `table` WHERE `role` likes '%2%' <== 誤

所以用like沒辦法正確的取出我們要的資料，這時正規表達式就派上用場，只是mysql的正規表達式比較怪異！

正則表達式語法

REGEXP + '正則表達式字串'

ex: 以數字2結尾

SELECT * FROM `table` WHERE `role` REGEXP '2$'

上式等同於

SELECT * FROM `table` WHERE `role` like '%2'

這樣子還是取不出單一個數字 2 的結果，這時就要用到mysql的邊界符號：

[[:<:]] 和 [[:>:]] 是MySQL 的邊界符號，分別代表左和右邊界，邊界的定義和 perl 的 \b 類似，只要 2 的左右邊界：

都不是¹數字、²字母或³_ 就符合

因此上式改為：

SELECT * FROM `table` WHERE `role` REGEXP '[[:<:]]2[[:>:]]'

就能取出正確的結果。

以分類比對

mysql也定義了一些常用的字元分類(posix格式)^[3]，稱之為 character class，其中有定義一些字元的群組：

字元分類名稱	定義
alnum	A-Za-z0-9 注意不包含底線'_'
alpha	A-Za-z_ 不含數字
blank	空白、\t
cntrl	控制字元 \x00-\x1F及\x7F
digit	0-9
graph	我認為是拿來作base64圖形會用到的字元 \x21-\x7E，不含空白。這些字元也是能顯示的字元(非控制字元)
lower	a-z
print	同graph但多了一個空白
punct	符號 !"\#$%&'()*+, \-./:;<=>?@\[ \\\]^_`{\|}~
space	空白、\t、\r、\n 這四個，\v, \f這兩個不確定，mysql官網沒寫到註：\t=tab, \r=回到行首, \n=換行, \v=`ctrl`+`K`, \f=form_feed=\x12
upper	A-Z
xdigit	0-9, a-f, A-F

使用時把名稱包在 [:分類名稱:] 即可。

舉例而言，數字0-9就是[:digit:]，換句話說 [:digit:] 只是代表 "0-9"

假如要比對所有的數字，還得用"[ ]"給包起來：

select "12345" regexp "[:digit:]+" =>0 錯誤

select "12345" regexp "[[:digit:]]+" =>1 正確

特殊字符

有些特殊字符在mysql中是用[.和.]這個包起來表示

[.字符名稱.]

例如反斜線 '\' [.backslash.]

TAB鍵 [.tab.]

中括號的右邊 ']' [.right-square-bracket.]

其他更多定義的特殊字符，請參考[2]

範例

ex1: 字串'justalnums'是否全由A-Za-z_組成

SELECT 'justalnums' REGEXP '[[:alnum:]]+';

結果1

ex2. 字串'justalnums'是否全由A-Za-z_組成

SELECT '!!' REGEXP '[[:alnum:]]+';

結果0

ex3. 字串"123\\12324" 是否由全數字和反斜線組成

SELECT "123\\12324" regexp "^[[:digit:][.backslash.]]+$"

結果1

ex4. 比對一個`range`欄位帶有括號的數字，如"(12)", "(0)" 比對成功；"(不是數字)", "(0a)" 比對失敗

SELECT * FROM `table` WHERE `range` REGEXP "[[.left-parenthesis.]][[:digit:]]+[[.right-parenthesis.]]"

ex5. 比對email格式

SELECT * FROM `user` WHERE `email` REGEXP '^[^@]+@[^@]+[.][^@]{2,}$'

ex6. 比對電話號碼是否為 (數字)數字或連字號'-' 所組成，但字尾不得為 '-'

SELECT "(032)312-432324" regexp "^[[.left-parenthesis.]][[:digit:]]+[[.right-parenthesis.]][0-9\-]+[0-9]$"

mysql的regexp比較其實很陽春，只能比對個大概

ex7. 字串開頭是 0-9A-F連著一個點 '.'，注意點的寫法不是\.而是[.]請參考[4]，\. 在mysql中還是會被當成「任一個字元」

SELECT 'E.2.4.5.8.8.2.0.1.0.0.2.ip6.arpa.' REGEXP '^[0-9A-F]{1}[.]'

參考資料

[1] http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/

[2] Mysql 特殊符號的比對 https://dev.mysql.com/doc/refman/5.7/en/regexp.html

[3] http://www.regular-expressions.info/posixbrackets.html

[4] https://stackoverflow.com/questions/18153898/whole-word-matching-with-dot-characters-in-mysql

END

你可能感興趣的文章

[Mysql/Mariadb] 利用phpMyAdmin 建立關聯式資料表利用phpMyAdmin 建立關聯式資料表

[Mysql/Mariadb] 密碼設定強度修改新的mysql對於密碼預設也把原來的規則改得更嚴格，此篇教你修改密碼的設定政策

[Mysql] 建立叢集式資料庫4/4--觀察及測試觀察及測試haproxy+galera建立Mysql的叢集式資料庫，並進行壓力測試

[Mysql] 將記錄由0變1、1變0；或是判斷後再改值的寫法 MySQL 將記錄由0變1，1變0的SQL寫法假如欄位 `active` 為0 是失效；1 為有效，他的型態是 tin

[Mysql] Mysqldump 的預設編碼 --default-character-set 有時Mysqldump 也得要指定編碼才不會造成匯出的亂碼

兩次使用InnoDB的慘痛經驗 Mysql 的Innodb引擎雖然好用，但是我得說說我兩次的慘痛經驗，這讓我考慮以後可能不會再使用innodb了

我有話要說

訪客留言

[無留言]

隨機好文

SELinux 常用指令和檔案在Redhat系列中，Centos5以後加入了selinux，他並沒有這麼可怕，不必每次看到Selinux ，就想把他

[CodeIgniter 3] 資料庫的使用方法整理2/2 CI3 承襲 CI2，有很多的builder class可以用，依各人的使用習慣，有人喜歡一堆sql字串，有人喜歡用helper

正則表達式：Email名稱防止連續輸入點(.)及下底線(_) Email的名稱中要允許特殊符號，但又不允許連續出現的正則表達式

「許功蓋」的字以及源由有玩過電腦一段時間的人，都聽過這個人(有一天我才發現7年級的竟然都不認識這個

[Bootstrap] Datepicker使用/Bootstrap日期選日期選擇器的使用 Bootstrap日期選日期選擇器的使用

正則表達式語法
以分類比對
特殊字符
範例
參考資料

到最底端