Appendix聽G.聽MySQL Regular Expressions - MySQL 5.1参考手册中文版

姝ｅ垯琛ㄨ揪寮忔槸涓哄�嶆潅鎼滅储鎸囧畾妯″紡鐨勫己澶ф柟寮忋��

MySQL閲囩敤Henry Spencer鐨勬�ｅ垯琛ㄨ揪寮忓疄鏂斤紝鍏剁洰鏍囨槸绗﹀��POSIX 1003.2銆傝�峰弬瑙�闄勫綍C锛氭劅璋�銆�MySQL閲囩敤浜嗘墿灞曠殑鐗堟湰锛屼互鏀�鎸佸��SQL璇�鍙ヤ腑涓�REGEXP鎿嶄綔绗︿竴璧蜂娇鐢ㄧ殑妯″紡鍖归厤鎿嶄綔銆傝�峰弬瑙�3.3.4.7鑺傦紝鈥滄ā寮忓尮閰嶁��銆�

鍦ㄦ湰闄勫綍涓�锛屽綊绾充簡鍦�MySQL涓�鍙�鐢ㄤ簬REGEXP鎿嶄綔鐨勭壒娈婂瓧绗﹀拰缁撴瀯锛屽苟缁欏嚭浜嗕竴浜涚ず渚嬨��鏈�闄勫綍鏈�鍖呭惈鍙�鍦�Henry Spencer鐨�regex(7)鎵嬪唽椤甸潰涓�鍙戠幇鐨勬墍鏈夌粏鑺傘�傝�ユ墜鍐岄〉闈㈠寘鍚�鍦�MySQL婧愮爜鍒嗗彂鐗堜腑锛屼綅浜�regex鐩�褰曚笅鐨�regex.7鏂囦欢涓�銆�

姝ｅ垯琛ㄨ揪寮忔弿杩颁簡涓�缁勫瓧绗︿覆銆傛渶绠�鍗曠殑姝ｅ垯琛ㄨ揪寮忔槸涓嶅惈浠讳綍鐗规畩瀛楃�︾殑姝ｅ垯琛ㄨ揪寮忋�備緥濡傦紝姝ｅ垯琛ㄨ揪寮�hello鍖归厤hello銆�

闈炲钩鍑＄殑姝ｅ垯琛ㄨ揪寮忛噰鐢ㄤ簡鐗规畩鐨勭壒瀹氱粨鏋勶紝浠庤�屼娇寰楀畠浠�鑳藉�熶笌1涓�浠ヤ笂鐨勫瓧绗︿覆鍖归厤銆備緥濡傦紝姝ｅ垯琛ㄨ揪寮�hello|word鍖归厤瀛楃�︿��hello鎴栧瓧绗︿覆word銆�

浣滀负涓�涓�鏇翠负澶嶆潅鐨勭ず渚嬶紝姝ｅ垯琛ㄨ揪寮�B[an]*s鍖归厤涓嬭堪瀛楃�︿覆涓�鐨勪换浣曚竴涓�锛�Bananas锛�Baaaaas锛�Bs锛�浠ュ強浠�B寮�濮嬨�佷互s缁撴潫銆佸苟鍦ㄥ叾涓�鍖呭惈浠绘剰鏁扮��a鎴�n瀛楃�︾殑浠讳綍鍏朵粬瀛楃�︿覆銆�

瀵逛簬REGEXP鎿嶄綔绗︼紝姝ｅ垯琛ㄨ揪寮忓彲浠ヤ娇鐢ㄤ换浣曚笅杩扮壒娈婂瓧绗﹀拰缁撴瀯锛�

路         ^

鍖归厤瀛楃�︿覆鐨勫紑濮嬮儴鍒嗐��

mysql> SELECT 'fo\nfo' REGEXP '^fo$';                   -> 0

mysql> SELECT 'fofo' REGEXP '^fo';                      -> 1

路         $

鍖归厤瀛楃�︿覆鐨勭粨鏉熼儴鍒嗐��

mysql> SELECT 'fo\no' REGEXP '^fo\no$';                 -> 1

mysql> SELECT 'fo\no' REGEXP '^fo$';                    -> 0

路         .

鍖归厤浠讳綍瀛楃�︼紙鍖呮嫭鍥炶溅鍜屾柊琛岋級銆�

mysql> SELECT 'fofo' REGEXP '^f.*$';                    -> 1

mysql> SELECT 'fo\r\nfo' REGEXP '^f.*$';                -> 1

路         a*

鍖归厤0鎴栧�氫��a瀛楃�︾殑浠讳綍搴忓垪銆�

mysql> SELECT 'Ban' REGEXP '^Ba*n';                     -> 1

mysql> SELECT 'Baaan' REGEXP '^Ba*n';                   -> 1

mysql> SELECT 'Bn' REGEXP '^Ba*n';                      -> 1

路         a+

鍖归厤1涓�鎴栧�氫釜a瀛楃�︾殑浠讳綍搴忓垪銆�

mysql> SELECT 'Ban' REGEXP '^Ba+n';                     -> 1

mysql> SELECT 'Bn' REGEXP '^Ba+n';                      -> 0

路         a?

鍖归厤0涓�鎴�1涓�a瀛楃�︺��

mysql> SELECT 'Bn' REGEXP '^Ba?n';                      -> 1

mysql> SELECT 'Ban' REGEXP '^Ba?n';                     -> 1

mysql> SELECT 'Baan' REGEXP '^Ba?n';                    -> 0

路         de|abc

鍖归厤搴忓垪de鎴�abc銆�

mysql> SELECT 'pi' REGEXP 'pi|apa';                     -> 1

mysql> SELECT 'axe' REGEXP 'pi|apa';                    -> 0

mysql> SELECT 'apa' REGEXP 'pi|apa';                    -> 1

mysql> SELECT 'apa' REGEXP '^(pi|apa)$';                -> 1

mysql> SELECT 'pi' REGEXP '^(pi|apa)$';                 -> 1

mysql> SELECT 'pix' REGEXP '^(pi|apa)$';                -> 0

路         (abc)*

鍖归厤搴忓垪abc鐨�0涓�鎴栧�氫釜瀹炰緥銆�

mysql> SELECT 'pi' REGEXP '^(pi)*$';                    -> 1

mysql> SELECT 'pip' REGEXP '^(pi)*$';                   -> 0

mysql> SELECT 'pipi' REGEXP '^(pi)*$';                  -> 1

路         {1}, {2,3}

{n}鎴�{m,n}绗﹀彿鎻愪緵浜嗙紪鍐欐�ｅ垯琛ㄨ揪寮忕殑鏇撮�氱敤鏂瑰紡锛岃兘澶熷尮閰嶆ā寮忕殑寰堝�氬墠杩板師瀛愶紙鎴�鈥�閮ㄥ垎鈥�锛夈��m鍜�n鍧囦负鏁存暟銆�

o        a*

鍙�琚�鍐欏叆涓�a{0,}銆�

o        a+

鍙�琚�鍐欏叆涓�a{1,}銆�

o        a?

鍙�琚�鍐欏叆涓�a{0,1}銆�

鏇村噯纭�鍦拌�诧紝a{n}涓�a鐨�n涓�瀹炰緥鍑嗙‘鍖归厤銆�a{n,}鍖归厤a鐨�n涓�鎴栨洿澶氬疄渚嬨��a{m,n}鍖归厤a鐨�m锝�n涓�瀹炰緥锛屽寘鍚�m鍜�n銆�

m鍜�n蹇呴』浣嶄簬0锝�RE_DUP_MAX锛堥粯璁や负255锛夌殑鑼冨洿鍐咃紝鍖呭惈0鍜�RE_DUP_MAX銆傚�傛灉鍚屾椂缁欏畾浜�m鍜�n锛�m蹇呴』灏忎簬鎴栫瓑浜�n銆�

mysql> SELECT 'abcde' REGEXP 'a[bcd]{2}e';              -> 0

mysql> SELECT 'abcde' REGEXP 'a[bcd]{3}e';              -> 1

mysql> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';           -> 1

路         [a-dX], [^a-dX]

鍖归厤浠讳綍鏄�锛堟垨涓嶆槸锛屽�傛灉浣跨敤^鐨勮瘽锛塧銆乥銆乧銆乨鎴朮鐨勫瓧绗︺�備袱涓�鍏朵粬瀛楃�︿箣闂寸殑鈥�-鈥濆瓧绗︽瀯鎴愪竴涓�鑼冨洿锛屼笌浠庣��1涓�瀛楃�﹀紑濮嬪埌绗�2涓�瀛楃�︿箣闂寸殑鎵�鏈夊瓧绗﹀尮閰嶃�備緥濡傦紝[0-9]鍖归厤浠讳綍鍗佽繘鍒舵暟瀛� 銆傝�佹兂鍖呭惈鏂囧瓧瀛楃�︹�淽鈥濓紝瀹冨繀椤荤揣璺熷湪寮�鎷�鍙封�淸鈥濅箣鍚庛�傝�佹兂鍖呭惈鏂囧瓧瀛楃�︹��-鈥濓紝瀹冨繀椤婚�栧厛鎴栨渶鍚庡啓鍏ャ�傚�逛簬[]瀵瑰唴鏈�瀹氫箟浠讳綍鐗规畩鍚�涔夌殑浠讳綍瀛楃�︼紝浠呬笌鍏舵湰韬�鍖归厤銆�

mysql> SELECT 'aXbc' REGEXP '[a-dXYZ]';                 -> 1

mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]$';               -> 0

mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';              -> 1

mysql> SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';             -> 0

mysql> SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';            -> 1

mysql> SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';           -> 0

路         [.characters.]

鍦ㄦ嫭鍙疯〃杈惧紡涓�锛堜娇鐢�[鍜�]锛夛紝鍖归厤鐢ㄤ簬鏍″�瑰厓绱犵殑瀛楃�﹀簭鍒椼�傚瓧绗︿负鍗曚釜瀛楃�︽垨璇稿�傛柊琛岀瓑瀛楃�﹀悕銆傚湪鏂囦欢regexp/cname.h涓�锛屽彲鎵惧埌瀛楃�﹀悕绉扮殑瀹屾暣鍒楄〃銆�

mysql> SELECT '~' REGEXP '[[.~.]]';                     -> 1

mysql> SELECT '~' REGEXP '[[.tilde.]]';                 -> 1

路         [=character_class=]

鍦ㄦ嫭鍙疯〃杈惧紡涓�锛堜娇鐢╗鍜宂锛夛紝[=character_class=]琛ㄧず绛夊悓绫汇�傚畠涓庡叿鏈夌浉鍚屾牎瀵瑰�肩殑鎵�鏈夊瓧绗﹀尮閰嶏紝鍖呮嫭瀹冩湰韬�锛屼緥濡傦紝濡傛灉o鍜�(+)鍧囨槸绛夊悓绫荤殑鎴愬憳锛岄偅涔圼[=o=]]銆乕[=(+)=]]鍜孾o(+)]鏄�鍚屼箟璇嶃�傜瓑鍚岀被涓嶅緱鐢ㄤ綔鑼冨洿鐨勭��鐐广��

路         [:character_class:]

鍦ㄦ嫭鍙疯〃杈惧紡涓�锛堜娇鐢╗鍜宂锛夛紝[:character_class:]琛ㄧず涓庢湳璇�绫荤殑鎵�鏈夊瓧绗﹀尮閰嶇殑瀛楃�︾被銆傛爣鍑嗙殑绫诲悕绉版槸锛�

alnum	鏂囧瓧鏁板瓧瀛楃��
alpha	鏂囧瓧瀛楃��
blank	绌虹櫧瀛楃��
cntrl	鎺у埗瀛楃��
digit	鏁板瓧瀛楃��
graph	鍥惧舰瀛楃��
lower	灏忓啓鏂囧瓧瀛楃��
print	鍥惧舰鎴栫┖鏍煎瓧绗�
punct	鏍囩偣瀛楃��
space	绌烘牸銆佸埗琛ㄧ�︺�佹柊琛屻�佸拰鍥炶溅
upper	澶у啓鏂囧瓧瀛楃��
xdigit	鍗佸叚杩涘埗鏁板瓧瀛楃��

瀹冧滑浠ｈ〃鍦�ctype(3)鎵嬪唽椤甸潰涓�瀹氫箟鐨勫瓧绗︾被銆�鐗瑰畾鍦板尯鍙�鑳戒細鎻愪緵鍏朵粬绫诲悕銆傚瓧绗�绫讳笉寰楃敤浣滆寖鍥寸殑绔�鐐广��

mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+';       -> 1

mysql> SELECT '!!' REGEXP '[[:alnum:]]+';               -> 0

路         [[:<:]], [[:>:]]

杩欎簺鏍囪�拌〃绀簑ord杈圭晫銆傚畠浠�鍒嗗埆涓巜ord鐨勫紑濮嬪拰缁撴潫鍖归厤銆倃ord鏄�涓�绯诲垪瀛楀瓧绗︼紝鍏跺墠闈㈠拰鍚庨潰鍧囨病鏈夊瓧瀛楃�︺�傚瓧瀛楃�︽槸alnum绫讳腑鐨勫瓧姣嶆暟瀛楀瓧绗︽垨涓嬪垝绾�(_)銆�

mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';   -> 1

mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';  -> 0

瑕佹兂鍦ㄦ�ｅ垯琛ㄨ揪寮忎腑浣跨敤鐗规畩瀛楃�︾殑鏂囧瓧瀹炰緥锛屽簲鍦ㄥ叾鍓嶉潰鍔犱笂2涓�鍙嶆枩鏉犫��\鈥濆瓧绗︺��MySQL瑙ｆ瀽绋嬪簭璐熻矗瑙ｉ噴鍏朵腑涓�涓�锛屾�ｅ垯琛ㄨ揪寮忓簱璐熻矗瑙ｉ噴鍙︿竴涓�銆備緥濡傦紝瑕佹兂涓庡寘鍚�鐗规畩瀛楃�︹��+鈥濈殑瀛楃�︿覆鈥�1+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鍙傝�冩墜鍐岀殑缈昏瘧鐗堟湰锛屽叧浜嶮ySQL鍙傝�冩墜鍐岋紝璇疯�块��dev.mysql.com銆傚師濮嬪弬鑰冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝�冩墜鍐岀浉姣旓紝鏈�缈昏瘧鐗堝彲鑳戒笉鏄�鏈�鏂扮殑銆�