MySQL Κανονικές εκφράσεις (Regexp)

Τι είναι MySQL κανονικές εκφράσεις;

MySQL κανονικές εκφράσεις σας βοηθούν να αναζητήσετε δεδομένα που ταιριάζουν με σύνθετα κριτήρια. Μια κανονική έκφραση είναι ένα μοτίβο που περιγράφει το σχήμα της τιμής που αναζητάτε και όχι την ίδια την τιμή.

Αν έχετε ήδη συνεργαστεί με MySQL wildcards, μπορεί να αναρωτηθείτε γιατί αξίζει να μαθαίνουμε κανονικές εκφράσεις όταν το LIKE παράγει παρόμοια αποτελέσματα. Η απάντηση είναι η εκφραστική δύναμη: οι χαρακτήρες μπαλαντέρ προσφέρουν μόνο δύο σύμβολα, ενώ οι κανονικές εκφράσεις περιγράφουν εύρη χαρακτήρων, εναλλακτικές λύσεις, επανάληψη και θέσεις λέξεων σε ένα μόνο μοτίβο.

Έχοντας καθορίσει τον σκοπό, η επόμενη ενότητα παρουσιάζει τη σύνταξη που θα χρησιμοποιείτε σε κάθε ερώτημα REGEXP.

Βασική σύνταξη του REGEXP

Η βασική σύνταξη για μια κανονική έκφραση έχει ως εξής.

SELECT * FROM table_name
WHERE fieldname REGEXP 'pattern';

ΕΔΩ:

• «Δήλωση SELECT» είναι το πρότυπο Δήλωση SELECT.
• "WHERE όνομα πεδίου" είναι το όνομα της στήλης στην οποία εκτελείται η κανονική έκφραση.
• “Μοτίβο 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 μας δίνει τα ακόλουθα αποτελέσματα.

Στο μοτίβο '^[abcd]', ο κέρσορας (^) απαιτεί η αντιστοίχιση να ξεκινά στην αρχή της τιμής και η λίστα χαρακτήρων [abcd] δέχεται μόνο τίτλους των οποίων το πρώτο γράμμα είναι a, b, c ή d. Η σύγκριση δεν κάνει διάκριση πεζών-κεφαλαίων στην προεπιλεγμένη ταξινόμηση, γι' αυτό και "Code Επιστρέφεται το "Όνομα Μαύρο".

Εξαίρεση χαρακτήρων με λίστα αρνητικών χαρακτήρων

Ας τροποποιήσουμε τώρα το σενάριο και ας ακυρώσουμε τη λίστα χαρακτήρων για να δούμε ποιες γραμμές επιστρέφονται.

SELECT * FROM `movies`
WHERE `title` REGEXP '^[^abcd]';

Εκτέλεση του παραπάνω σεναρίου στο MySQL Το Workbench σε σχέση με τη βάση δεδομένων myflixdb μας δίνει τα ακόλουθα αποτελέσματα.

Μέσα σε μια λίστα χαρακτήρων, το σύμβολο caret αλλάζει νόημα: Το '^[^abcd]' εξακολουθεί να αγκυρώνει την αντιστοίχιση στην αρχή, ενώ το [^abcd] εξαιρεί πλέον κάθε τίτλο που ξεκινά με έναν από τους συνημμένους χαρακτήρες.

Αυτά τα δύο παραδείγματα χρησιμοποιούν μόνο άγκυρες και λίστες χαρακτήρων. Η επόμενη ενότητα καλύπτει το πλήρες σύνολο μεταχαρακτήρων.

Μεταχαρακτήρες κανονικής έκφρασης

Τα παραπάνω παραδείγματα δείχνουν την απλούστερη μορφή μιας κανονικής έκφρασης. Οι μεταχαρακτήρες σάς επιτρέπουν να βελτιώσετε την αναζήτηση μοτίβων: εκφράζουν επανάληψη, εναλλακτικές λύσεις, εύρη και θέσεις. Ο παρακάτω πίνακας παραθέτει κάθε μεταχαρακτήρα που υποστηρίζεται από το MySQL Τελεστής REGEXP, με διορθωμένο παράδειγμα για κάθε έναν.

Η ανάστροφη κάθετος (\) είναι ο χαρακτήρας διαφυγής. Επειδή MySQL αναλύει πρώτα τη συμβολοσειρά και στη συνέχεια το μοτίβο, μια κυριολεκτική ανάστροφη κάθετος πρέπει να γραφτεί ως διπλή ανάστροφη κάθετος (\\) μέσα σε ένα μοτίβο REGEXP.

Τώρα που κάθε μεταχαρακτήρας έχει οριστεί, προκύπτει ένα εύλογο ερώτημα: πότε θα πρέπει το REGEXP να αντικαταστήσει τον απλούστερο τελεστή LIKE;

REGEXP vs LIKE: ποιο πρέπει να χρησιμοποιήσετε;

Και οι δύο τελεστές φιλτράρουν τις γραμμές κατά μοτίβο, αλλά επιλύουν διαφορετικά προβλήματα. Το LIKE κατανοεί μόνο δύο σύμβολα, ενώ το REGEXP κατανοεί το πλήρες σύνολο μεταχαρακτήρων που φαίνεται παραπάνω. Αυτή η δύναμη έχει ένα κόστος, επομένως η επιλογή είναι μια ανταλλαγή και όχι μια προτίμηση.

Επιλέξτε LIKE για άμεση αντιστοίχιση, επειδή διαβάζεται καλά και μπορεί να χρησιμοποιήσει ένα ευρετήριο. Επιλέξτε REGEXP όταν ένα μόνο μοτίβο πρέπει να εκφράζει πολλούς κανόνες ταυτόχρονα, όπως "ξεκινά με c ή d και τελειώνει με ένα ψηφίο". Σε μεγάλους πίνακες, περιορίστε πρώτα τις γραμμές με μια συνθήκη με ευρετήριο και, στη συνέχεια, εφαρμόστε REGEXP σε αυτό το μικρότερο σύνολο.

MySQL 8.0 συναρτήσεις κανονικών εκφράσεων

Ο τελεστής REGEXP απαντά μόνο σε μία ερώτηση: ταιριάζει η τιμή με το μοτίβο; MySQL Η έκδοση 8.0 πρόσθεσε τέσσερις λειτουργίες που προχωρούν παραπέρα και σας επιτρέπουν να εντοπίζετε, π.χ.tract και ξαναγράψτε το αντίστοιχο κείμενο. Κάθε ένα δέχεται ένα προαιρετικό όρισμα match_type, όπου το 'c' επιβάλλει μια σύγκριση με διάκριση πεζών-κεφαλαίων και το 'i' επιβάλλει μια σύγκριση χωρίς διάκριση πεζών-κεφαλαίων.

• REGEXP_LIKE(παράσταση, μοτίβο) Επιστρέφει 1 όταν η τιμή ταιριάζει με το μοτίβο. Είναι η μορφή συνάρτησης του τελεστή REGEXP και το όρισμα match_type καθιστά σαφή την ευαισθησία πεζών-κεφαλαίων.
• REGEXP_INSTR(παράσταση, μοτίβο) επιστρέφει τη θέση του πρώτου χαρακτήρα της αντιστοίχισης ή 0 όταν δεν βρεθεί το μοτίβο.
• REGEXP_SUBSTR(έκφραση, μοτίβο) επιστρέφει την ίδια την αντίστοιχη δευτερεύουσα συμβολοσειρά, η οποία είναι χρήσιμη για την εξαγωγή ενός έτους, ενός κωδικού ή ενός αριθμού από μια μεγαλύτερη τιμή κειμένου.
• REGEXP_REPLACE(παράσταση, μοτίβο, αντικατάσταση) επιστρέφει την τιμή με κάθε αντιστοίχιση που αντικαθίσταται, ώστε να μπορεί να καθαρίσει δεδομένα μέσα σε ένα Ερώτημα 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 παραμένει η μόνη επιλογή.

Συχνές Ερωτήσεις