MySQL นิพจน์ปกติ (Regexp)
มาร์คัสอัลเลน
⚡ สรุปอย่างชาญฉลาด
MySQL นิพจน์ปกติ (REGEXP) จับคู่ค่าในคอลัมน์กับรูปแบบที่ยืดหยุ่นซึ่งไวด์การ์ดไม่สามารถแสดงได้ นี่คือคำอธิบายเกี่ยวกับไวยากรณ์ของ REGEXP คำพ้องความหมาย RLIKE อักขระพิเศษที่รองรับทั้งหมด ตัวอย่างการค้นหาที่แก้ไขแล้วกับฐานข้อมูล myflixdb และฟังก์ชันนิพจน์ปกติที่เพิ่มเข้ามา MySQL 8.0.
สิ่งที่เป็น MySQL นิพจน์ปกติ?
MySQL นิพจน์ทั่วไป ช่วยให้คุณค้นหาข้อมูลที่ตรงกับเกณฑ์ที่ซับซ้อนได้ นิพจน์ปกติ (Regular expression) คือรูปแบบที่อธิบายรูปร่างของค่าที่คุณกำลังมองหา ไม่ใช่ค่าจริง ๆ
หากคุณเคยทำงานร่วมกับ MySQL สัญลักษณ์แทนคุณอาจสงสัยว่าทำไมการเรียนรู้ regular expression จึงคุ้มค่า ในเมื่อคำสั่ง LIKE ก็ให้ผลลัพธ์ที่คล้ายคลึงกัน คำตอบคือพลังในการแสดงออก: ตัวอักษรตัวแทน (wildcards) ใช้ได้เพียงสองสัญลักษณ์ ในขณะที่ regular expression สามารถอธิบายช่วงของตัวอักษร ตัวเลือก การทำซ้ำ และตำแหน่งของคำได้ในรูปแบบเดียว
เมื่อกำหนดวัตถุประสงค์แล้ว ส่วนถัดไปจะแนะนำไวยากรณ์ที่คุณจะใช้ในทุกคำสั่ง REGEXP
ไวยากรณ์พื้นฐานของ REGEXP
ไวยากรณ์พื้นฐานของนิพจน์ปกติมีดังนี้
SELECT * FROM table_name WHERE fieldname REGEXP 'pattern';
ที่นี่:
- คำสั่ง SELECT เป็นมาตรฐาน คำสั่ง SELECT.
- “ชื่อฟิลด์อยู่ที่ไหน” คือชื่อของคอลัมน์ที่ใช้กับนิพจน์ปกติ
- “REGEXP 'รูปแบบ'” — REGEXP คือตัวดำเนินการนิพจน์ปกติ และ 'pattern' แทนรูปแบบที่จะใช้ในการจับคู่ RLIKE คือ คำพ้องความหมายสำหรับ REGEXP และให้ผลลัพธ์เหมือนกัน เพื่อหลีกเลี่ยงความสับสนกับตัวดำเนินการ LIKE ควรใช้ REGEXP จะดีกว่า
ต่อไปนี้เรามาดูตัวอย่างที่เป็นรูปธรรมกัน
SELECT * FROM `movies` WHERE `title` REGEXP 'code';
คำสั่งค้นหาข้างต้นค้นหาชื่อภาพยนตร์ทั้งหมดที่มีคำว่า “code” อยู่ ไม่ว่าคำว่า “code” จะอยู่ต้น กลาง หรือท้ายชื่อภาพยนตร์ก็ตาม ตราบใดที่ชื่อภาพยนตร์มีรูปแบบดังกล่าว ผลลัพธ์ก็จะถูกส่งคืน
การจับคู่จุดเริ่มต้นของค่ากับรายการอักขระ
สมมติว่าเราต้องการภาพยนตร์ที่มีชื่อเรื่องขึ้นต้นด้วย a, b, c หรือ d ตามด้วยอักขระอื่นๆ อีกจำนวนหนึ่ง เราใช้รายการอักขระร่วมกับอักขระพิเศษ caret เพื่อให้ได้ผลลัพธ์นั้น
SELECT * FROM `movies` WHERE `title` REGEXP '^[abcd]';
ดำเนินการสคริปต์ข้างต้นใน MySQL ม้านั่งทำงานของช่างเครื่อง การค้นหาข้อมูลในฐานข้อมูล myflixdb ให้ผลลัพธ์ดังต่อไปนี้
| movie_id | ชื่อเรื่อง | ผู้อำนวยการ | year_released | Category_id |
|---|---|---|---|---|
| 4 | Code ชื่อ แบล็ค | เอ็ดการ์ จิมซ์ | 2010 | NULL |
| 5 | สาวน้อยของพ่อ | NULL | 2007 | 8 |
| 6 | เทวดาและปีศาจ | NULL | 2007 | 6 |
| 7 | Davinci Code | NULL | 2007 | 6 |
ในรูปแบบ '^[abcd]' เครื่องหมายแคเร็ต (^) กำหนดให้การจับคู่เริ่มต้นที่จุดเริ่มต้นของค่า และรายการอักขระ [abcd] ยอมรับเฉพาะชื่อที่มีอักษรตัวแรกเป็น a, b, c หรือ d เท่านั้น การเปรียบเทียบจะไม่คำนึงถึงตัวพิมพ์ใหญ่เล็กภายใต้การเรียงลำดับเริ่มต้น ซึ่งเป็นเหตุผลว่าทำไม “Code ระบบส่งคืนชื่อ “Name Black”
ไม่รวมตัวละครที่มีรายชื่อตัวละครที่ถูกปฏิเสธ
ต่อไปนี้เราจะแก้ไขสคริปต์และกลับลำดับตัวอักษรในรายการเพื่อดูว่ามีแถวใดถูกส่งกลับมาบ้าง
SELECT * FROM `movies` WHERE `title` REGEXP '^[^abcd]';
ดำเนินการสคริปต์ข้างต้นใน MySQL การทดสอบด้วย Workbench กับฐานข้อมูล myflixdb ให้ผลลัพธ์ดังต่อไปนี้
| movie_id | ชื่อเรื่อง | ผู้อำนวยการ | year_released | Category_id |
|---|---|---|---|---|
| 1 | โจรสลัดแห่งแคริบเบียน 4 | มาร์แชลล์ร็อบ | 2011 | 1 |
| 2 | ลืมซาราห์ มาร์แชล | นิโคลัสสตอลเลอร์ | 2008 | 2 |
| 3 | X-Men | 2008 | ||
| 9 | Honey moonERS | จอห์น ชูลท์ซ | 2005 | 8 |
| 16 | 67% มีความผิด | 2012 | ||
| 17 | มหาราชจอมบงการ | ชาลี แชปลี | 1920 | 7 |
| 18 | ตัวอย่างภาพยนตร์ | ไม่ระบุชื่อ | 8 | |
| 19 | ภาพยนตร์ 3 | บราวน์จอห์น | 1920 | 8 |
ภายในรายการตัวอักษร เครื่องหมายเคอร์เซอร์จะเปลี่ยนความหมาย: '^[^abcd]' ยังคงใช้เป็นจุดยึดสำหรับการจับคู่ที่จุดเริ่มต้น ในขณะที่ [^abcd] จะยกเว้นชื่อเรื่องทั้งหมดที่ขึ้นต้นด้วยตัวอักษรที่อยู่ในวงเล็บ
ตัวอย่างทั้งสองนี้ใช้เพียงจุดยึดและรายการอักขระเท่านั้น ส่วนถัดไปจะกล่าวถึงชุดอักขระพิเศษทั้งหมด
เมตาอักขระนิพจน์ทั่วไป
ตัวอย่างข้างต้นแสดงรูปแบบที่ง่ายที่สุดของนิพจน์ปกติ เมตาแคแรคเตอร์ช่วยให้คุณปรับแต่งการค้นหารูปแบบได้อย่างละเอียด: พวกมันแสดงถึงการทำซ้ำ ทางเลือก ช่วง และตำแหน่ง ตารางด้านล่างแสดงรายการเมตาแคแรคเตอร์ทั้งหมดที่รองรับโดย MySQL ตัวดำเนินการ REGEXP พร้อมตัวอย่างที่แก้ไขแล้วสำหรับแต่ละตัว
| ถ่าน | Descriptไอออน | ตัวอย่าง | |
|---|---|---|---|
| * | การขอ เครื่องหมายดอกจัน (*) ตรงกับกรณีศูนย์ (0) หรือมากกว่าของ ตัวอักษรตัวเดียว ที่อยู่ข้างหน้ามัน | SELECT * จากภาพยนตร์ โดยที่ชื่อ REGEXP 'da*'; ตรงกับตัวอักษร “d” ตามด้วยตัวอักษร “a” ตั้งแต่ศูนย์ตัวขึ้นไป ดังนั้น Da Vinci Code และ Daddy's Little Girls ก็เข้าข่ายด้วย ใช้ 'da+' เมื่อจำเป็นต้องมีตัวอักษร “a” อยู่จริง | |
| + | การขอ บวก (+) ตรงกับอักขระที่ระบุไว้ข้างหน้าอย่างน้อยหนึ่งครั้ง | SELECT * จาก `ภาพยนตร์` โดยที่ `title` REGEXP 'mon+'; แสดงผลลัพธ์ภาพยนตร์ทั้งหมดที่มีคำว่า “mon” ตามด้วยตัวอักษร “n” หนึ่งตัวขึ้นไป ตัวอย่างเช่น Angels and Demons | |
| ? | การขอ เครื่องหมายคำถาม (?) ตรงกับอักขระที่อยู่ข้างหน้าศูนย์ (0) หรือหนึ่งครั้ง | SELECT * จาก `หมวดหมู่` WHERE `category_name` REGEXP 'com?'; จับคู่ “co” กับ “m” ที่เป็นตัวเลือกเสริม ตัวอย่างเช่น comedy และ romantic comedy | |
| . | การขอ จุด (.) ตรงกับอักขระเดี่ยวใดๆ ก็ได้ ยกเว้นอักขระขึ้นบรรทัดใหม่ | SELECT * จากภาพยนตร์ที่ `year_released` REGEXP '200.'; แสดงภาพยนตร์ทั้งหมดที่ออกฉายในปีที่ขึ้นต้นด้วย “200” ตามด้วยตัวอักษรเดี่ยวใดๆ ก็ได้ เช่น 2005, 2007, 2008 | |
| [เอบีซี] | การขอ รายชื่อตัวละคร [abc] ตรงกับอักขระตัวใดตัวหนึ่งที่อยู่ในกรอบ | SELECT * จาก `ภาพยนตร์` WHERE `title` REGEXP '[vwxyz]'; แสดงภาพยนตร์ทั้งหมดที่มีตัวละครใดตัวหนึ่งจาก “vwxyz” ตัวอย่างเช่น X-Men และ Da Vinci Code. | |
| [^เอบีซี] | การขอ รายการที่ถูกปฏิเสธ [^abc] ตรงกับอักขระทุกตัว ยกเว้นอักขระที่ระบุไว้ในกรอบ | SELECT * จาก `ภาพยนตร์` WHERE `title` REGEXP '^[^vwxyz]'; แสดงภาพยนตร์ทั้งหมดที่มีชื่อเรื่องไม่ขึ้นต้นด้วยตัวอักษรในรูปแบบ “vwxyz” | |
| [AZ] | การขอ ช่วง [AZ] ตรงกับตัวอักษรพิมพ์ใหญ่ทุกตัว | SELECT * จาก `สมาชิก` WHERE `postal_address` REGEXP '[AZ]'; แสดงรายชื่อสมาชิกทั้งหมดที่มีที่อยู่ทางไปรษณีย์ประกอบด้วยตัวอักษรระหว่าง A ถึง Z ตัวอย่างเช่น Janet Jones ที่มีหมายเลขสมาชิก 1 | |
| [az] | การขอ ช่วง [az] ตรงกับตัวอักษรพิมพ์เล็กทุกตัว | SELECT * จาก `สมาชิก` WHERE `postal_address` REGEXP '[az]'; แสดงรายชื่อสมาชิกทั้งหมดที่มีที่อยู่ทางไปรษณีย์ประกอบด้วยตัวอักษรระหว่าง a ถึง z โปรดทราบว่าการเรียงลำดับเริ่มต้นจะไม่คำนึงถึงตัวพิมพ์ใหญ่หรือเล็ก ดังนั้นช่วงนี้จึงรวมถึงตัวอักษรพิมพ์ใหญ่ด้วย | |
| [0 9-] | การขอ ช่วง [0-9] ตรงกับตัวเลขใดก็ได้ตั้งแต่ 0 ถึง 9 | SELECT * FROM `members` WHERE `contact_number` REGEXP '[0-9]'; แสดงรายชื่อสมาชิกทั้งหมดที่มีหมายเลขติดต่ออย่างน้อยหนึ่งหลัก ตัวอย่างเช่น Robert Phil | |
| ^ | การขอ คาเร็ต (^) ยึดการจับคู่ไว้ที่จุดเริ่มต้นของค่า | SELECT * จาก `ภาพยนตร์` WHERE `title` REGEXP '^[cd]'; แสดงภาพยนตร์ทั้งหมดที่มีชื่อเรื่องขึ้นต้นด้วยตัวอักษร “c” หรือ “d” ตัวอย่างเช่น Code ชื่อ Black, Daddy's Little Girls และ Da Vinci Code. | |
| $ | การขอ เครื่องหมายดอลลาร์ ($) ยึดการจับคู่ไว้ที่จุดสิ้นสุดของค่า | SELECT * FROM `movies` WHERE `title` REGEXP 'code$'; แสดงภาพยนตร์ทั้งหมดที่มีชื่อลงท้ายด้วย “code” ตัวอย่างเช่น Davinci Code. | |
| | | การขอ แถบแนวตั้ง (|) แยกแยะทางเลือกอื่น ๆ | SELECT * จาก `ภาพยนตร์` WHERE `title` REGEXP '^[cd]|^[u]'; แสดงภาพยนตร์ทั้งหมดที่มีชื่อเรื่องขึ้นต้นด้วยตัวอักษร “c”, “d” หรือ “u” ตัวอย่างเช่น Code ชื่อ แบล็ก, ดา วินชี Codeและโลกใต้ดิน – Awakenไอเอ็นจี | |
| \b | การขอ ขอบเขตคำ (\b) ตรงกับจุดเริ่มต้นหรือจุดสิ้นสุดของคำ มันมาแทนที่เครื่องหมาย [[:<:]] และ [[:>:]] แบบเก่า ซึ่ง MySQL เวอร์ชัน 8.0 ถูกลบออกแล้ว | SELECT * FROM `movies` WHERE `title` REGEXP '\\bfor'; แสดงภาพยนตร์ทั้งหมดที่มีคำขึ้นต้นด้วย “for” ตัวอย่างเช่น Forgetting Sarah Marshal. On MySQL 5.7 รูปแบบที่เทียบเท่าคือ '[[:<:]]for' | |
| [[:ระดับ:]] | การขอ คลาสตัวละคร ตรงกับกลุ่มอักขระที่กำหนด: [[:alpha:]] สำหรับตัวอักษร, [[:space:]] สำหรับช่องว่าง, [[:punct:]] สำหรับเครื่องหมายวรรคตอน และ [[:upper:]] สำหรับตัวอักษรพิมพ์ใหญ่ โปรดสังเกต สอง วงเล็บเหลี่ยม | SELECT * FROM `movies` WHERE `title` REGEXP '^[[:alpha:][:space:]]+$'; แสดงรายการภาพยนตร์ทั้งหมดที่มีชื่อเรื่องประกอบด้วยตัวอักษรและช่องว่างเท่านั้น ตัวอย่างเช่น Forgetting Sarah Marshal จะถูกละเว้น ในขณะที่ Pirates of the Caribbean 4 จะถูกละเว้นเนื่องจากมีตัวเลข | |
เครื่องหมายแบ็กสแลช (\) เป็นอักขระหลีก เนื่องจาก MySQL โปรแกรมจะแยกวิเคราะห์สตริงก่อน แล้วจึงแยกวิเคราะห์รูปแบบ ดังนั้นเครื่องหมายแบ็กสแลชแบบตรงตัวจะต้องเขียนเป็นแบ็กสแลชสองตัวติดกัน (\\) ภายในรูปแบบ REGEXP
⚠️ คำเตือนเกี่ยวกับเวอร์ชัน: MySQL เวอร์ชัน 8.0.4 ได้เปลี่ยนมาใช้ไลบรารี ICU แทนเอ็นจิ้นนิพจน์ปกติแบบเก่า และได้ลบตัวคั่นคำ [[:<:]] และ [[:>:]] ออกไปในเวอร์ชันนั้น ดังนั้นรูปแบบที่คัดลอกมาจากเนื้อหาเก่าจึงอาจเกิดข้อผิดพลาด "ไวยากรณ์ผิดพลาด" ได้ MySQL 8.0. ใช้ \b แทน
เมื่อกำหนดความหมายของอักขระพิเศษทุกตัวแล้ว คำถามที่น่าสนใจต่อไปคือ เมื่อใดควรใช้ REGEXP แทนตัวดำเนินการ LIKE ที่ใช้งานง่ายกว่า?
REGEXP กับ LIKE: ควรใช้อันไหนดี?
ตัวดำเนินการทั้งสองแบบใช้กรองแถวตามรูปแบบ แต่แก้ปัญหาที่แตกต่างกัน LIKE เข้าใจเพียงสองสัญลักษณ์ ในขณะที่ REGEXP เข้าใจชุดอักขระพิเศษทั้งหมดที่แสดงไว้ข้างต้น ความสามารถนั้นมาพร้อมกับต้นทุน ดังนั้นการเลือกจึงเป็นการแลกเปลี่ยนมากกว่าความชอบส่วนตัว
| เกณฑ์ | LIKE | REGEXP |
|---|---|---|
| สัญลักษณ์รูปแบบ | % และ _ เท่านั้น | Anchors, ช่วง, การสลับ, ตัวบ่งปริมาณ, คลาสอักขระ |
| การใช้งานทั่วไป | การค้นหาด้วยคำนำหน้า คำต่อท้าย และการค้นหาแบบ "มี" | การตรวจสอบความถูกต้อง ทางเลือกหลายทาง การจับคู่ที่คำนึงถึงตำแหน่ง |
| การใช้งานดัชนี | เป็นไปได้เมื่อรูปแบบไม่ได้เริ่มต้นด้วย % | ไม่เคยใช้ดัชนี |
| ส่งคืนค่า | จริงหรือเท็จ | 1 หรือ 0 และ NULL เมื่อตัวถูกดำเนินการตัวใดตัวหนึ่งเป็น NULL |
เลือกใช้ LIKE สำหรับการจับคู่แบบตรงไปตรงมา เพราะอ่านง่ายและยังสามารถใช้ดัชนีได้ เลือกใช้ REGEXP เมื่อต้องการแสดงกฎหลายข้อพร้อมกันในรูปแบบเดียว เช่น “ขึ้นต้นด้วย c หรือ d และลงท้ายด้วยตัวเลข” สำหรับตารางขนาดใหญ่ ให้จำกัดจำนวนแถวก่อนด้วยเงื่อนไขที่มีดัชนี จากนั้นจึงใช้ REGEXP กับชุดข้อมูลที่ได้มาแล้ว
MySQL 8.0 ฟังก์ชันนิพจน์ปกติ
ตัวดำเนินการ REGEXP ตอบคำถามเพียงข้อเดียวเท่านั้น คือ ค่าตรงกับรูปแบบหรือไม่ MySQL เวอร์ชัน 8.0 เพิ่มฟังก์ชันเพิ่มเติมอีกสี่ฟังก์ชันที่ช่วยให้คุณค้นหาตำแหน่งได้ เช่นtract และเขียนข้อความที่ตรงกันใหม่ โดยแต่ละฟังก์ชันจะรับอาร์กิวเมนต์ match_type ที่เป็นตัวเลือก ซึ่ง 'c' จะบังคับให้เปรียบเทียบแบบคำนึงถึงตัวพิมพ์ใหญ่เล็ก และ 'i' จะบังคับให้เปรียบเทียบแบบไม่คำนึงถึงตัวพิมพ์ใหญ่เล็ก
- REGEXP_LIKE(expr, pattern) ฟังก์ชันนี้จะคืนค่า 1 เมื่อค่าตรงกับรูปแบบที่กำหนด โดยเป็นรูปแบบฟังก์ชันของตัวดำเนินการ REGEXP และอาร์กิวเมนต์ match_type จะทำให้พิจารณาความเล็กหรือใหญ่ของตัวพิมพ์ใหญ่ได้อย่างชัดเจน
- REGEXP_INSTR(expr, pattern) ฟังก์ชันนี้จะส่งคืนตำแหน่งของอักขระตัวแรกที่ตรงกับรูปแบบ หรือส่งคืนค่า 0 เมื่อไม่พบรูปแบบที่ตรงกัน
- REGEXP_SUBSTR(expr, pattern) ฟังก์ชันนี้จะส่งคืนสตริงย่อยที่ตรงกัน ซึ่งมีประโยชน์สำหรับการดึงปี รหัส หรือตัวเลขออกจากค่าข้อความที่ยาวกว่า
- REGEXP_REPLACE(expr, pattern, replacement) ส่งคืนค่าโดยแทนที่ค่าที่ตรงกันทุกค่า ดังนั้นจึงสามารถทำความสะอาดข้อมูลภายในได้ คำสั่ง SQL UPDATE.
SELECT title, REGEXP_SUBSTR(title, '[0-9]+') AS number_in_title FROM `movies` WHERE REGEXP_LIKE(title, '[0-9]');
คำสั่งค้นหาข้างต้นจะส่งคืนชื่อภาพยนตร์ทุกเรื่องที่มีตัวเลข พร้อมทั้งตัวเลขเหล่านั้นด้วย MySQL 5.7 ฟังก์ชันเหล่านี้ไม่สามารถใช้งานได้ ดังนั้นตัวดำเนินการ REGEXP จึงยังคงเป็นตัวเลือกเดียวที่มีอยู่
