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

Τι είναι MySQL Functions?

MySQL μπορεί να κάνει πολλά περισσότερα από την απλή αποθήκευση και ανάκτηση δεδομένων. Μπορεί επίσης πραγματοποιήστε χειρισμούς στα δεδομένα before retrieving or saving it. That is where MySQL Functions come in. Functions are simply pieces of code that perform an operation and then return a result. Some functions accept parameters, while other functions accept none.

Let’s briefly look at an example. By default, MySQL saves date data types in the format “YYYY-MM-DD”. Suppose we have built an application and our users want the date returned in the format “DD-MM-YYYY”. We can use the MySQL built-in function DATE_FORMAT to achieve this. DATE_FORMAT is one of the most used functions in MySQL, and we look at it in detail later in this lesson.

Whatever its type, a function always returns a single value, may accept zero or more parameters inside parentheses, and can be used anywhere an expression is allowed — in a SELECT list, a WHERE clause, or an ORDER BY clause.

Γιατί να χρησιμοποιήσετε MySQL Functions?

Now that we know what a function is, the next question is why we should push this work into the database at all.

As the diagram above shows, a function takes an input value, applies the logic once inside the database engine, and hands back a single result to every application that asks for it.

Programmers may be thinking, “Why bother with MySQL Functions? The same effect can be achieved with a scripting or programming language.” It is true that we can achieve that by writing a procedure in the application program.

Getting back to our DATE example, for our users to get the data in the desired format, the business layer would have to do the necessary processing itself.

Αυτό γίνεται πρόβλημα όταν η εφαρμογή πρέπει να ενσωματωθεί με άλλα συστήματα. Όταν χρησιμοποιούμε MySQL functions such as DATE_FORMAT, that functionality is embedded into the database, and any application that needs the data gets it in the required format. This reduces re-work in the business logic and reduces data inconsistencies.

Άλλος ένας λόγος που πρέπει να εξετάσετε MySQL functions is that they can help reduce network traffic in client/server applications. The business layer only needs to call the stored function, without pulling raw rows across the network to manipulate them. On average, the use of functions can greatly improve overall system performance.

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

With the “what” and the “why” settled, we can now look at the three families of functions MySQL offers: built-in functions, stored functions, and user-defined functions.

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

MySQL comes bundled with a number of built-in functions — functions already implemented in the MySQL server. They allow us to perform many types of manipulation on the data, and fall into the following commonly used groups.

• Συναρτήσεις συμβολοσειράς – λειτουργεί σε τύπους δεδομένων συμβολοσειράς
• Αριθμητικές συναρτήσεις – λειτουργεί με αριθμητικούς τύπους δεδομένων
• Λειτουργίες ημερομηνίας – λειτουργεί με βάση τους τύπους δεδομένων ημερομηνίας
• Συγκεντρωτικές συναρτήσεις – λειτουργεί σε όλους τους παραπάνω τύπους δεδομένων και παράγει συνοπτικά σύνολα αποτελεσμάτων.
• άλλες λειτουργίες - MySQL also supports other types of built-in functions, but we limit this lesson to the groups named above.

Let’s now look at each of the groups mentioned above in detail. We will explain the most used functions using our “Myflixdb” sample database.

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

String functions operate on text values. In our movies table, titles are stored using a mix of lower and upper case letters. Suppose we want a query that returns the titles in upper case. The “UCASE” function takes a string as a parameter and converts every letter to upper case, as the script below demonstrates.

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

ΕΔΩ

• UCASE(`title`) is the built-in function that takes the title as a parameter and returns it in upper case letters.
• AS `upper_case_title` gives the computed column an alias, so the result set carries a readable header instead of the raw expression.

Εκτέλεση του παραπάνω σεναρίου στο MySQL Workbench against the Myflixdb gives us the results shown below.

Two companions are worth remembering alongside UCASE: LCASE converts a string to lower case, and CONCAT joins two or more strings into one. For the complete list, refer to the MySQL string function reference.

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

As mentioned earlier, numeric functions operate on numeric data types. We can also perform mathematical computations on numeric data directly in our SQL statements.

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

MySQL supports the following arithmetic operators, which can be used to perform computations in SQL statements.

Examples of each operator follow.

Integer division (DIV) — DIV discards the fractional part and returns only the whole number.

SELECT 23 DIV 6;

Executing the above script gives us 3.

Χειριστής τμήματος (/) — unlike DIV, the division operator keeps the decimal part of the result.

SELECT 23 / 6;

Executing the above script gives us 3.8333.

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

SELECT 23 - 6;

Executing the above script gives us 17.

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

SELECT 23 + 6;

Executing the above script gives us 29.

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

SELECT 23 * 6 AS `multiplication_result`;

Αποτέλεσμα:

Modulo operator (% or MOD)

The modulo operator divides N by M and gives us the remainder. Let’s look at the modulo operator example, using the same values as in the previous examples.

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

Executing either script gives us 5.

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

ΠΑΤΩΜΑ – this function removes the decimal places from a number and rounds it down to the nearest whole number. The script shown below demonstrates its usage.

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

Αποτέλεσμα:

ΣΤΡΟΓΓΥΛΟ – this function rounds a number to the nearest whole number. Because 23 / 6 evaluates to 3.8333, ROUND returns 4 while FLOOR returns 3 — the two are not interchangeable.

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

Αποτέλεσμα:

Rand – this function generates a random number. Its value changes every time the function is called. The script shown below demonstrates its usage.

SELECT RAND() AS `random_result`;

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

Date functions operate on date and date-time data types. DATE_FORMAT is the function that solves the “YYYY-MM-DD versus DD-MM-YYYY” problem described in the introduction.

ΗΜΕΡΟΜΗΝΙΑ_ΜΟΡΦΗΣ takes two parameters: the date value to format, and a format string built from placeholders. The script below returns each release date in the day-month-year style our users asked for.

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

The most frequently used format placeholders are listed below.

Three other date functions appear constantly in day-to-day work:

• CURDATE () returns the current date as YYYY-MM-DD.
• ΤΩΡΑ() returns the current date και χρόνο.
• DATEDIFF(d1, d2) returns the number of days between two dates — the basis of any overdue-rental report.

For the full list, see the MySQL date and time function reference.

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

Built-in functions cover the common cases. When a business rule is more specific, we write our own — and that is what a stored function is for.

Stored functions behave just like built-in functions, except that you define them yourself. Once created, a stored function can be used in SQL statements exactly like any other function. The basic syntax is shown below.

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

ΕΔΩ

• “CREATE FUNCTION sf_name ([parameter(s)])” is mandatory and tells the MySQL server to create a function named `sf_name` with optional parameters defined inside the parentheses.
• “RETURNS data_type” is mandatory and specifies the data type that the function returns.
• «ΑΠΟΔΟΣΗ» declares that the function returns the same value whenever the same arguments are supplied. “NOT DETERMINISTIC” declares the opposite.
• “BEGIN … END” wraps the procedural code that the function executes.

Suppose we want to know which rented movies are past their return date. We can create a stored function that accepts the return date as a parameter and compares it with the current date on the server. If the current date is greater than the return date, the movie is overdue and we return “Yes”; otherwise we return “No”.

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 ;

⚠️ Warning — do not label this function DETERMINISTIC. The body calls CURDATE(), so the same argument can return “No” today and “Yes” tomorrow. Declaring a time-dependent function DETERMINISTIC misleads the optimizer and is unsafe for statement-based replication. Use ΟΧΙ ΝΤΕΤΕΜΙΝΙΣΤΙΚΟ whenever the body calls CURDATE(), NOW(), or RAND().

Executing the above script creates the stored function `sf_past_movie_return_date`. Let’s now test it.

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

Εκτέλεση του παραπάνω σεναρίου στο MySQL Workbench against the myflixdb gives us the following results.

Notice the two NULL rows. When `return_date` is NULL, both comparisons evaluate to NULL rather than TRUE or FALSE, so neither IF branch runs and the function returns NULL — the expected result, since an unreturned movie has no return date to compare against.

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

When SQL alone is not fast enough, MySQL allows a third option. User-defined functions (UDFs) are written in a compiled language such as C or C++, built into a shared library, and registered with the server. Once added, they are called just like any other function. Because a UDF runs as native code inside the server process, it suits heavy computation — but a bug in one can crash the server, so UDFs are used far less often than stored functions.

Built-in vs Stored vs User-Defined Functions: Which Should You Use?

All three families return a single value and can be called from any SQL statement, but they differ in who writes them, where they run, and how much risk they carry. The table below summarizes those differences.

As a rule of thumb, start with a built-in function. If none fits, write a stored function so the rule lives in one place. Reach for a UDF only when a stored function is measurably too slow.

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