MySQL Λειτουργίες: Συμβολοσειρά, Αριθμητική, Καθορισμένη από το χρήστη, Αποθηκευμένη

Τι είναι MySQL Λειτουργίες;

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

Ας δούμε σύντομα ένα παράδειγμα. Από προεπιλογή, MySQL αποθηκεύει τύπους δεδομένων ημερομηνίας στη μορφή «ΕΕΕΕ-ΜΜ-ΗΗ». Ας υποθέσουμε ότι έχουμε δημιουργήσει μια εφαρμογή και οι χρήστες μας θέλουν να επιστραφεί η ημερομηνία στη μορφή «ΗΗ-ΜΜ-ΕΕΕΕ». Μπορούμε να χρησιμοποιήσουμε το MySQL ενσωματωμένη συνάρτηση DATE_FORMAT για να το πετύχει αυτό. Η DATE_FORMAT είναι μία από τις πιο χρησιμοποιούμενες συναρτήσεις στο MySQL, και θα το εξετάσουμε λεπτομερώς αργότερα σε αυτό το μάθημα.

Ανεξάρτητα από τον τύπο της, μια συνάρτηση επιστρέφει πάντα μία μόνο τιμή, μπορεί να δεχτεί μηδέν ή περισσότερες παραμέτρους μέσα σε παρενθέσεις, και μπορεί να χρησιμοποιηθεί οπουδήποτε επιτρέπεται μια έκφραση — σε μια λίστα SELECT, έναν όρο WHERE ή έναν όρο ORDER BY.

Γιατί να χρησιμοποιήσετε MySQL Λειτουργίες;

Τώρα που ξέρουμε τι είναι μια συνάρτηση, το επόμενο ερώτημα είναι γιατί θα πρέπει να εισάγουμε αυτή την εργασία στη βάση δεδομένων.

Όπως δείχνει το παραπάνω διάγραμμα, μια συνάρτηση δέχεται μια τιμή εισόδου, εφαρμόζει τη λογική μόλις εισέλθει στη μηχανή βάσης δεδομένων και επιστρέφει ένα μόνο αποτέλεσμα σε κάθε εφαρμογή που το ζητά.

Οι προγραμματιστές μπορεί να σκέφτονται: «Γιατί να ασχολούμαστε με MySQL Συναρτήσεις; Το ίδιο αποτέλεσμα μπορεί να επιτευχθεί με μια γλώσσα scripting ή προγραμματισμού.» Είναι αλήθεια ότι μπορούμε να το πετύχουμε αυτό γράφοντας μια διαδικασία στο πρόγραμμα εφαρμογής.

Επιστρέφοντας στο παράδειγμα DATE, για να λάβουν οι χρήστες μας τα δεδομένα στην επιθυμητή μορφή, το επιχειρηματικό επίπεδο θα πρέπει να κάνει το ίδιο την απαραίτητη επεξεργασία.

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

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

Τύποι MySQL Συναρτήσεις

Έχοντας διευθετήσει το «τι» και το «γιατί», μπορούμε τώρα να εξετάσουμε τις τρεις οικογένειες συναρτήσεων MySQL προσφέρει: ενσωματωμένες συναρτήσεις, αποθηκευμένες συναρτήσεις και συναρτήσεις που ορίζονται από τον χρήστη.

Ενσωματωμένες λειτουργίες

MySQL συνοδεύεται από μια σειρά ενσωματωμένων λειτουργιών — λειτουργίες που έχουν ήδη υλοποιηθεί στο MySQL διακομιστή. Μας επιτρέπουν να εκτελούμε πολλούς τύπους χειρισμού στα δεδομένα και εμπίπτουν στις ακόλουθες συνήθως χρησιμοποιούμενες ομάδες.

• Συναρτήσεις συμβολοσειράς – λειτουργεί σε τύπους δεδομένων συμβολοσειράς
• Αριθμητικές συναρτήσεις – λειτουργεί με αριθμητικούς τύπους δεδομένων
• Λειτουργίες ημερομηνίας – λειτουργεί με βάση τους τύπους δεδομένων ημερομηνίας
• Συγκεντρωτικές συναρτήσεις – λειτουργεί σε όλους τους παραπάνω τύπους δεδομένων και παράγει συνοπτικά σύνολα αποτελεσμάτων.
• άλλες λειτουργίες - MySQL υποστηρίζει επίσης και άλλους τύπους ενσωματωμένων συναρτήσεων, αλλά περιορίζουμε αυτό το μάθημα στις ομάδες που αναφέρονται παραπάνω.

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

Συναρτήσεις συμβολοσειράς

Οι συναρτήσεις συμβολοσειράς λειτουργούν με τιμές κειμένου. Στον πίνακα ταινιών μας, οι τίτλοι αποθηκεύονται χρησιμοποιώντας ένα μείγμα πεζών και κεφαλαίων γραμμάτων. Ας υποθέσουμε ότι θέλουμε ένα ερώτημα που επιστρέφει τους τίτλους με κεφαλαία γράμματα. Η συνάρτηση "UCASE" δέχεται μια συμβολοσειρά ως παράμετρο και μετατρέπει κάθε γράμμα σε κεφαλαίο, όπως δείχνει το παρακάτω σενάριο.

SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_title` FROM `movies`;

ΕΔΩ

• UCASE(`τίτλος`) είναι η ενσωματωμένη συνάρτηση που δέχεται τον τίτλο ως παράμετρο και τον επιστρέφει με κεφαλαία γράμματα.
• AS `κεφαλαίος_τίτλος` δίνει στην υπολογιζόμενη στήλη ένα ψευδώνυμο, επομένως το σύνολο αποτελεσμάτων φέρει μια αναγνώσιμη κεφαλίδα αντί για την ακατέργαστη έκφραση.

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

Δύο σύντροφοι που αξίζει να θυμόμαστε παράλληλα με το UCASE: LCASE μετατρέπει μια συμβολοσειρά σε πεζά γράμματα, και CONCAT ενώνει δύο ή περισσότερες συμβολοσειρές σε μία. Για την πλήρη λίστα, ανατρέξτε στο MySQL αναφορά συνάρτησης συμβολοσειράς.

Αριθμητικές συναρτήσεις

Όπως αναφέρθηκε προηγουμένως, οι αριθμητικές συναρτήσεις λειτουργούν σε αριθμητικούς τύπους δεδομένων. Μπορούμε επίσης να εκτελέσουμε μαθηματικούς υπολογισμούς σε αριθμητικά δεδομένα απευθείας στις εντολές SQL μας.

Αριθμητικοί τελεστές

MySQL Υποστηρίζει τους ακόλουθους αριθμητικούς τελεστές, οι οποίοι μπορούν να χρησιμοποιηθούν για την εκτέλεση υπολογισμών σε εντολές SQL.

Ακολουθούν παραδείγματα για κάθε τελεστή.

Ακέραιη διαίρεση (DIV) — Η συνάρτηση DIV απορρίπτει το κλασματικό μέρος και επιστρέφει μόνο τον ακέραιο αριθμό.

SELECT 23 DIV 6;

Η εκτέλεση του παραπάνω σεναρίου μας δίνει 3.

Χειριστής τμήματος (/) — σε αντίθεση με το DIV, ο τελεστής διαίρεσης διατηρεί το δεκαδικό μέρος του αποτελέσματος.

SELECT 23 / 6;

Η εκτέλεση του παραπάνω σεναρίου μας δίνει 3.8333.

Σεtracτελεστής tion (-)

SELECT 23 - 6;

Η εκτέλεση του παραπάνω σεναρίου μας δίνει 17.

Χειριστής προσθήκης (+)

SELECT 23 + 6;

Η εκτέλεση του παραπάνω σεναρίου μας δίνει 29.

τελεστής πολλαπλασιασμού (*)

SELECT 23 * 6 AS `multiplication_result`;

Αποτέλεσμα:

Τελεστής Modulo (% ή MOD)

Ο τελεστής modulo διαιρεί το N με το M και μας δίνει το υπόλοιπο. Ας δούμε το παράδειγμα του τελεστή modulo, χρησιμοποιώντας τις ίδιες τιμές όπως στα προηγούμενα παραδείγματα.

SELECT 23 % 6;
-- OR, equivalently:
SELECT 23 MOD 6;

Η εκτέλεση οποιουδήποτε από τα δύο σενάρια μας δίνει 5.

Ας δούμε τώρα μερικές από τις κοινές αριθμητικές συναρτήσεις MySQL.

ΠΑΤΩΜΑ – αυτή η συνάρτηση αφαιρεί τα δεκαδικά ψηφία από έναν αριθμό και τον στρογγυλοποιεί προς τα κάτω στον πλησιέστερο ακέραιο αριθμό. Το σενάριο που φαίνεται παρακάτω δείχνει τη χρήση της.

SELECT FLOOR(23 / 6) AS `floor_result`;

Αποτέλεσμα:

ΣΤΡΟΓΓΥΛΟ – αυτή η συνάρτηση στρογγυλοποιεί έναν αριθμό στον πλησιέστερο ακέραιο αριθμό. Επειδή το 23/6 αποτιμάται σε 3.8333, η συνάρτηση ROUND επιστρέφει 4 ενώ η συνάρτηση FLOOR επιστρέφει 3 — οι δύο συναρτήσεις δεν είναι εναλλάξιμες.

SELECT ROUND(23 / 6) AS `round_result`;

Αποτέλεσμα:

Rand – αυτή η συνάρτηση παράγει έναν τυχαίο αριθμό. Η τιμή της αλλάζει κάθε φορά που καλείται η συνάρτηση. Το παρακάτω σενάριο δείχνει τη χρήση της.

SELECT RAND() AS `random_result`;

Λειτουργίες ημερομηνίας

Οι συναρτήσεις ημερομηνίας λειτουργούν με βάση τους τύπους δεδομένων ημερομηνίας και ημερομηνίας-ώρας. Η συνάρτηση DATE_FORMAT είναι η συνάρτηση που λύνει το πρόβλημα «ΕΕΕΕ-ΜΜ-ΗΗ έναντι ΗΗ-ΜΜ-ΕΕΕΕ» που περιγράφεται στην εισαγωγή.

ΗΜΕΡΟΜΗΝΙΑ_ΜΟΡΦΗΣ δέχεται δύο παραμέτρους: την τιμή ημερομηνίας που θα μορφοποιηθεί και μια συμβολοσειρά μορφοποίησης που δημιουργείται από placeholders. Το παρακάτω σενάριο επιστρέφει κάθε ημερομηνία κυκλοφορίας στο στυλ ημέρας-μήνα-έτος που ζήτησαν οι χρήστες μας.

SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date`
FROM `movies`;

Τα πιο συχνά χρησιμοποιούμενα placeholders μορφής παρατίθενται παρακάτω.

Τρεις άλλες συναρτήσεις ημερομηνίας εμφανίζονται συνεχώς στην καθημερινή εργασία:

• CURDATE () επιστρέφει την τρέχουσα ημερομηνία ως ΕΕΕΕ-ΜΜ-ΗΗ.
• ΤΩΡΑ() επιστρέφει την τρέχουσα ημερομηνία και χρόνο.
• ΗΜΕΡΟΜΗΝΙΑ (d1, d2) Επιστρέφει τον αριθμό των ημερών μεταξύ δύο ημερομηνιών — τη βάση οποιασδήποτε αναφοράς εκπρόθεσμης ενοικίασης.

Για την πλήρη λίστα, δείτε το MySQL αναφορά συνάρτησης ημερομηνίας και ώρας.

Αποθηκευμένες λειτουργίες

Οι ενσωματωμένες συναρτήσεις καλύπτουν τις συνηθισμένες περιπτώσεις. Όταν ένας επιχειρησιακός κανόνας είναι πιο συγκεκριμένος, γράφουμε τον δικό μας — και αυτός είναι ο σκοπός μιας αποθηκευμένης συνάρτησης.

Οι αποθηκευμένες συναρτήσεις συμπεριφέρονται ακριβώς όπως οι ενσωματωμένες συναρτήσεις, εκτός από το ότι τις ορίζετε εσείς οι ίδιοι. Μόλις δημιουργηθεί, μια αποθηκευμένη συνάρτηση μπορεί να χρησιμοποιηθεί σε προτάσεις SQL ακριβώς όπως οποιαδήποτε άλλη συνάρτηση. Η βασική σύνταξη φαίνεται παρακάτω.

CREATE FUNCTION sf_name ([parameter(s)])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- procedural statements
END

ΕΔΩ

• «ΔΗΜΙΟΥΡΓΙΑ ΣΥΝΑΡΤΗΣΗΣ sf_name ([παράμετρος(-ες)])» είναι υποχρεωτικό και αναφέρει MySQL διακομιστή για να δημιουργήσει μια συνάρτηση με όνομα `sf_name` με προαιρετικές παραμέτρους που ορίζονται μέσα στις παρενθέσεις.
• «ΕΠΙΣΤΡΕΦΕΙ τύπο_δεδομένων» είναι υποχρεωτική και καθορίζει τον τύπο δεδομένων που επιστρέφει η συνάρτηση.
• «ΑΠΟΔΟΣΗ» δηλώνει ότι η συνάρτηση επιστρέφει την ίδια τιμή κάθε φορά που παρέχονται τα ίδια ορίσματα. «ΜΗ ΝΤΕΤΕΡΜΙΝΙΣΤΙΚΟ» δηλώνει το αντίθετο.
• «ΑΡΧΗ … ΤΕΛΟΣ» αναδιπλώνει τον διαδικαστικό κώδικα που εκτελεί η συνάρτηση.

Ας υποθέσουμε ότι θέλουμε να μάθουμε ποιες ενοικιασμένες ταινίες έχουν περάσει την ημερομηνία επιστροφής τους. Μπορούμε να δημιουργήσουμε μια αποθηκευμένη συνάρτηση που δέχεται την ημερομηνία επιστροφής ως παράμετρο και τη συγκρίνει με την τρέχουσα ημερομηνία στον διακομιστή. Εάν η τρέχουσα ημερομηνία είναι μεγαλύτερη από την ημερομηνία επιστροφής, η ταινία έχει καθυστερήσει και επιστρέφουμε "Ναι". Διαφορετικά επιστρέφουμε "Όχι".

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
NOT DETERMINISTIC
BEGIN
    DECLARE sf_value VARCHAR(3);
    IF CURDATE() > return_date THEN
        SET sf_value = 'Yes';
    ELSEIF CURDATE() <= return_date THEN
        SET sf_value = 'No';
    END IF;
    RETURN sf_value;
END|
DELIMITER ;

⚠️ Προειδοποίηση — μην επισημάνετε αυτήν τη συνάρτηση με την ένδειξη DETERMINISTIC (ΝΤΕΤΕΡΜΙΝΙΣΤΙΚΗ). Το σώμα καλεί την CURDATE(), επομένως το ίδιο όρισμα μπορεί να επιστρέψει "Όχι" σήμερα και "Ναι" αύριο. Η δήλωση μιας χρονικά εξαρτώμενης συνάρτησης DETERMINISTIC παραπλανά τον βελτιστοποιητή και δεν είναι ασφαλής για αναπαραγωγή που βασίζεται σε δηλώσεις. Χρησιμοποιήστε ΟΧΙ ΝΤΕΤΕΜΙΝΙΣΤΙΚΟ κάθε φορά που το σώμα καλεί CURDATE(), NOW() ή RAND().

Η εκτέλεση του παραπάνω σεναρίου δημιουργεί την αποθηκευμένη συνάρτηση `sf_past_movie_return_date`. Ας τη δοκιμάσουμε τώρα.

SELECT `movie_id`, `membership_number`, `return_date`, CURDATE(),
       sf_past_movie_return_date(`return_date`) AS `is_overdue`
FROM `movierentals`;

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

Παρατηρήστε τις δύο γραμμές NULL. Όταν η τιμή `return_date` είναι NULL, και οι δύο συγκρίσεις αξιολογούνται ως NULL αντί για TRUE ή FALSE, επομένως καμία από τις δύο διακλαδώσεις IF δεν εκτελείται και η συνάρτηση επιστρέφει NULL — το αναμενόμενο αποτέλεσμα, καθώς μια ταινία που δεν έχει επιστραφεί δεν έχει ημερομηνία επιστροφής για σύγκριση.

Λειτουργίες που καθορίζονται από το χρήστη

Όταν η SQL από μόνη της δεν είναι αρκετά γρήγορη, MySQL επιτρέπει μια τρίτη επιλογή. Οι συναρτήσεις που ορίζονται από τον χρήστη (UDF) γράφονται σε μια μεταγλωττισμένη γλώσσα όπως η C or C++, ενσωματωμένα σε μια κοινόχρηστη βιβλιοθήκη και καταχωρημένα στον διακομιστή. Μόλις προστεθούν, καλούνται όπως ακριβώς οποιαδήποτε άλλη συνάρτηση. Επειδή ένα UDF εκτελείται ως εγγενής κώδικας μέσα στη διεργασία του διακομιστή, είναι κατάλληλο για βαρείς υπολογισμούς — αλλά ένα σφάλμα σε ένα από αυτά μπορεί να προκαλέσει διακοπή λειτουργίας του διακομιστή, επομένως τα UDF χρησιμοποιούνται πολύ λιγότερο συχνά από τις αποθηκευμένες συναρτήσεις.

Ενσωματωμένες έναντι αποθηκευμένων έναντι καθορισμένων από τον χρήστη συναρτήσεων: Ποιες πρέπει να χρησιμοποιήσετε;

Και οι τρεις οικογένειες επιστρέφουν μία μόνο τιμή και μπορούν να κληθούν από οποιαδήποτε πρόταση SQL, αλλά διαφέρουν ως προς το ποιος τις γράφει, πού εκτελούνται και πόσο κίνδυνο φέρουν. Ο παρακάτω πίνακας συνοψίζει αυτές τις διαφορές.

Κατά γενικό κανόνα, ξεκινήστε με μια ενσωματωμένη συνάρτηση. Εάν δεν ταιριάζει καμία, γράψτε μια αποθηκευμένη συνάρτηση έτσι ώστε ο κανόνας να βρίσκεται σε ένα μέρος. Χρησιμοποιήστε ένα UDF μόνο όταν μια αποθηκευμένη συνάρτηση είναι μετρήσιμα πολύ αργή.

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