MySQL Fonctions : chaîne, numérique, définie par l'utilisateur, stockée

Quels sont MySQL Les fonctions?

MySQL peut faire bien plus que simplement stocker et récupérer des données. Ça peut aussi effectuer des manipulations sur les données avant de le récupérer ou de l'enregistrer. C'est là que MySQL C'est là qu'interviennent les fonctions. Les fonctions sont simplement des portions de code qui effectuent une opération et renvoient un résultat. Certaines fonctions acceptent des paramètres, tandis que d'autres n'en acceptent aucun.

Prenons brièvement un exemple. Par défaut, MySQL enregistre les données de type date au format « AAAA-MM-JJ ». Supposons que nous ayons développé une application et que nos utilisateurs souhaitent que la date soit renvoyée au format « JJ-MM-AAAA ». Nous pouvons utiliser le MySQL La fonction intégrée DATE_FORMAT permet d'obtenir ce résultat. DATE_FORMAT est l'une des fonctions les plus utilisées. MySQLet nous l'examinerons en détail plus loin dans cette leçon.

Quel que soit son type, une fonction renvoie toujours une seule valeur, peut accepter zéro ou plusieurs paramètres entre parenthèses, et peut être utilisé partout où une expression est autorisée — dans une liste SELECT, une clause WHERE ou une clause ORDER BY.

Pourquoi utiliser MySQL Les fonctions?

Maintenant que nous savons ce qu'est une fonction, la question suivante est de savoir pourquoi nous devrions intégrer ce travail dans la base de données.

Comme le montre le schéma ci-dessus, une fonction prend une valeur en entrée, applique la logique une seule fois dans le moteur de base de données et renvoie un résultat unique à chaque application qui le demande.

Les programmeurs se demandent peut-être : « Pourquoi s'embêter avec… » MySQL Des fonctions ? Le même effet peut être obtenu avec un langage de script ou de programmation. Il est vrai que nous pouvons y parvenir en écrivant une procédure dans le programme d’application.

Pour revenir à notre exemple de DATE, afin que nos utilisateurs obtiennent les données au format souhaité, la couche métier devrait effectuer elle-même le traitement nécessaire.

Cela devient un problème lorsque l'application doit s'intégrer à d'autres systèmes. Quand on utilise MySQL Des fonctions telles que DATE_FORMAT sont intégrées à la base de données, et toute application ayant besoin des données les obtient au format requis. réduit les reprises dans la logique métier et les incohérences de données..

Une autre raison à considérer MySQL Leur fonction est de contribuer à réduire le trafic réseau dans les applications client/serveur.La couche métier n'a qu'à appeler la fonction stockée, sans avoir à récupérer les données brutes sur le réseau pour les manipuler. En moyenne, l'utilisation de fonctions peut améliorer considérablement les performances globales du système.

Types d' MySQL Les fonctions

Maintenant que nous avons clarifié le « quoi » et le « pourquoi », nous pouvons examiner les trois familles de fonctions. MySQL Il propose : des fonctions intégrées, des fonctions enregistrées et des fonctions définies par l’utilisateur.

Les fonctions intégrées

MySQL est livré avec un certain nombre de fonctions intégrées — des fonctions déjà implémentées dans le MySQL serveur. Ils nous permettent d'effectuer de nombreux types de manipulations sur les données et se répartissent dans les groupes couramment utilisés suivants.

• Fonctions de chaîne – opérer sur des types de données chaîne
• Fonctions numériques – opérer sur des types de données numériques
• Fonctions de date – opérer sur les types de données de date
• Fonctions d'agrégation – opérer sur tous les types de données ci-dessus et produire des ensembles de résultats résumés.
• Autres fonctions - MySQL Il prend également en charge d'autres types de fonctions intégrées, mais nous limitons cette leçon aux groupes mentionnés ci-dessus.

Examinons maintenant en détail chacun des groupes mentionnés ci-dessus. Nous expliquerons les fonctions les plus utilisées à l'aide de notre base de données exemple « Myflixdb ».

Fonctions de chaîne

Les fonctions de chaînes de caractères s'appliquent aux valeurs textuelles. Dans notre table de films, les titres sont stockés en utilisant un mélange de lettres minuscules et majuscules. Supposons que nous souhaitions une requête qui renvoie les titres en majuscules. La fonction « UCASE » prend une chaîne de caractères comme paramètre et convertit chaque lettre en majuscule, comme le montre le script ci-dessous.

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

ICI. (en anglais seulement)

• UCASE(`titre`) est la fonction intégrée qui prend le titre comme paramètre et le renvoie en lettres majuscules.
• AS `upper_case_title` attribue un alias à la colonne calculée, de sorte que l'ensemble de résultats comporte un en-tête lisible au lieu de l'expression brute.

Exécuter le script ci-dessus dans MySQL L'analyse de Workbench sur la base de données Myflixdb nous donne les résultats présentés ci-dessous.

Deux autres outils méritent d'être mentionnés aux côtés d'UCASE : CASE convertit une chaîne de caractères en minuscules, et CONCAT joint deux chaînes de caractères ou plus en une seule. Pour la liste complète, reportez-vous à la section suivante : MySQL référence de fonction de chaîne.

Fonctions numériques

Comme indiqué précédemment, les fonctions numériques s'appliquent aux types de données numériques. Nous pouvons également effectuer des calculs mathématiques directement sur des données numériques dans nos requêtes SQL.

Opérateurs arithmétiques

MySQL prend en charge les opérateurs arithmétiques suivants, qui peuvent être utilisés pour effectuer des calculs dans les instructions SQL.

Vous trouverez ci-dessous des exemples pour chaque opérateur.

Division entière (DIV) — La fonction DIV supprime la partie fractionnaire et ne renvoie que le nombre entier.

SELECT 23 DIV 6;

L'exécution du script ci-dessus nous donne : 3.

Opérateur de division (/) — Contrairement à DIV, l'opérateur de division conserve la partie décimale du résultat.

SELECT 23 / 6;

L'exécution du script ci-dessus nous donne : 3.8333.

Soltracopérateur de tion (-)

SELECT 23 - 6;

L'exécution du script ci-dessus nous donne : 17.

Opérateur d'addition (+)

SELECT 23 + 6;

L'exécution du script ci-dessus nous donne : 29.

Opérateur de multiplication (*)

SELECT 23 * 6 AS `multiplication_result`;

Résultat:

Opérateur modulo (% ou MOD)

L'opérateur modulo divise N par M et nous donne le reste. Prenons l'exemple de l'opérateur modulo, en utilisant les mêmes valeurs que dans les exemples précédents.

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

L'exécution de l'un ou l'autre script nous donne 5.

Examinons maintenant certaines des fonctions numériques courantes dans MySQL.

SOL Cette fonction supprime la virgule d'un nombre et l'arrondit à l'entier inférieur. Le script ci-dessous illustre son utilisation.

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

Résultat:

ROUND Cette fonction arrondit un nombre à l'entier le plus proche. Comme 23 / 6 vaut 3.8333, la fonction ARRONDI renvoie 4 tandis que la fonction PLANCHER renvoie 3 ; les deux ne sont donc pas interchangeables.

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

Résultat:

RAND Cette fonction génère un nombre aléatoire. Sa valeur change à chaque appel. Le script ci-dessous illustre son utilisation.

SELECT RAND() AS `random_result`;

Fonctions de date

Les fonctions de date s'appliquent aux types de données date et date-heure. La fonction DATE_FORMAT résout le problème « AAAA-MM-JJ versus JJ-MM-AAAA » décrit dans l'introduction.

FORMAT DE DATE Cette fonction prend deux paramètres : la date à formater et une chaîne de format construite à partir d’espaces réservés. Le script ci-dessous renvoie chaque date de publication au format jour-mois-année demandé par nos utilisateurs.

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

Les espaces réservés de format les plus fréquemment utilisés sont listés ci-dessous.

Trois autres fonctions de date apparaissent constamment dans le travail quotidien :

• CURDATE() renvoie la date actuelle au format AAAA-MM-JJ.
• À PRÉSENT() renvoie la date actuelle et le temps.
• DATEDIFF(d1, d2) renvoie le nombre de jours entre deux dates — la base de tout rapport de loyer impayé.

Pour la liste complète, voir le MySQL Référence de la fonction date et heure.

Fonctions stockées

Les fonctions intégrées couvrent les cas courants. Lorsqu'une règle métier est plus spécifique, nous écrivons notre propre fonction ; c'est le rôle d'une fonction stockée.

Les fonctions stockées se comportent comme les fonctions intégrées, à la différence qu'elles sont définies par vos soins. Une fois créée, une fonction stockée peut être utilisée dans les requêtes SQL comme n'importe quelle autre fonction. La syntaxe de base est présentée ci-dessous.

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

ICI. (en anglais seulement)

• « CRÉER LA FONCTION sf_name ([paramètre(s)]) » est obligatoire et indique le MySQL serveur pour créer une fonction nommée `sf_name` avec des paramètres optionnels définis entre parenthèses.
• « RETOURNE le type de données » est obligatoire et spécifie le type de données que la fonction renvoie.
• « DÉTERMINISTE » déclare que la fonction renvoie la même valeur chaque fois que les mêmes arguments sont fournis. « NON DÉTERMINISTE » déclare le contraire.
• « DÉBUT… FIN » enrobe le code procédural exécuté par la fonction.

Supposons que nous souhaitions savoir quels films loués sont en retard. Nous pouvons créer une fonction stockée qui prend la date de retour en paramètre et la compare à la date actuelle sur le serveur. Si la date actuelle est postérieure à la date de retour, le film est en retard et nous renvoyons « Oui » ; sinon, nous renvoyons « Non ».

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 ;

⚠️ Avertissement — ne qualifiez pas cette fonction de DÉTERMINISTIQUE. Le corps de la fonction appelle CURDATE(), de sorte que le même argument peut renvoyer « Non » aujourd'hui et « Oui » demain. Déclarer une fonction dépendante du temps comme DETERMINISTIC induit en erreur l'optimiseur et est dangereux pour la réplication basée sur les instructions. PAS DÉTERMINISTE chaque fois que le corps appelle CURDATE(), NOW() ou RAND().

L'exécution du script ci-dessus crée la fonction stockée `sf_past_movie_return_date`. Testons-la maintenant.

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

Exécuter le script ci-dessus dans MySQL Workbench appliqué à myflixdb nous donne les résultats suivants.

Remarquez les deux lignes NULL. Lorsque `return_date` est NULL, les deux comparaisons renvoient NULL au lieu de TRUE ou FALSE ; par conséquent, aucune des deux branches IF n’est exécutée et la fonction renvoie NULL — le résultat attendu, puisqu’un film non retourné n’a pas de date de retour à laquelle se comparer.

Les fonctions définies par l'utilisateur

Lorsque SQL seul n'est pas suffisamment rapide, MySQL permet une troisième option. Les fonctions définies par l'utilisateur (UDF) sont écrites dans un langage compilé tel que C or C++Intégrées à une bibliothèque partagée et enregistrées auprès du serveur, les fonctions définies par l'utilisateur (UDF) s'appellent comme n'importe quelle autre fonction. Comme elles s'exécutent comme du code natif au sein du processus serveur, elles sont particulièrement adaptées aux calculs intensifs. Cependant, un bug peut entraîner le plantage du serveur ; c'est pourquoi les UDF sont beaucoup moins fréquemment utilisées que les fonctions stockées.

Fonctions intégrées, fonctions stockées et fonctions définies par l'utilisateur : lesquelles utiliser ?

Ces trois familles de fonctions renvoient une valeur unique et peuvent être appelées depuis n'importe quelle requête SQL. Elles diffèrent cependant par leur auteur, leur environnement d'exécution et le niveau de risque associé. Le tableau ci-dessous récapitule ces différences.

En règle générale, commencez par une fonction intégrée. Si aucune ne convient, créez une fonction stockée afin de centraliser la règle. N'utilisez une fonction définie par l'utilisateur (UDF) que si une fonction stockée est nettement trop lente.

FAQ