Techniques avancées avec MySQL

1. Introduction

Une sous-requête est une requête SELECT qui est utilisée à l’intérieur d’une autre requête. 

Une sous-requête peut être utilisée :

La sous-requête est toujours écrite entre parenthèses.

Elle peut contenir des jointures, ainsi que des clauses WHERE, GROUP BY, ORDER BY, etc. Une sous-requête peut aussi elle-même contenir des sous-requêtes !

Depuis la version 8, MySQL supporte les « expressions de table communes » (Common Table Expression - CTE), à savoir la possibilité de définir des sous-requêtes nommées dans une clause WITH qui précède la requête principale et de les utiliser dans cette dernière comme si c’étaient des tables.

2. Sous-requête scalaire

Une sous-requête qui retourne une seule colonne et au plus une seule ligne est appelée sous-requête scalaire.

Une telle sous-requête peut être utilisée partout où une valeur (un opérande) est attendu :

Exemple

mysql> -- Ecart entre le prix de la collection 1 et le prix moyen  ...

1. Introduction

Depuis la version 8, MySQL supporte les fonctions de fenêtrage (window functions), aussi parfois appelées fonctions analytiques.

Une fonction de fenêtrage est une fonction dont le résultat pour une ligne donnée est dérivé d’un ensemble de lignes relatif à cette ligne (une « fenêtre »). Ces fonctions permettent souvent de résoudre assez simplement des problèmes complexes qui auraient nécessité l’utilisation de plusieurs requêtes et des traitements dans une application cliente. Elles sont particulièrement utiles pour réaliser des rapports de type décisionnel.

Les fonctions analytiques permettent :

Dans cette partie, nous présenterons les bases de l’utilisation des fonctions de fenêtrage en donnant quelques exemples représentatifs de leur intérêt. Pour avoir plus de détails, n’hésitez pas à vous reporter à la documentation (chapitre « 12.21 Window Functions » du manuel « MySQL 8.0 Reference Manual »).

La syntaxe générale d’une fonction analytique est la suivante :

fonction([paramètres]) OVER([partitionnement][tri][fenêtre]) 

La présence de la clause OVER indique l’utilisation d’une fonction de fenêtrage ; pour l’appel de cette dernière, les parenthèses sont obligatoires même s’il n’y a pas de paramètres....

1. Définition

Dans la terminologie des bases de données relationnelles, une transaction est un ensemble d’ordres de mise à jour qui forme un tout indissociable du point de vue de la logique applicative. Les ordres de mise à jour d’une transaction ne peuvent être définitivement enregistrés dans la base de données que s’ils se sont tous exécutés sans erreur ; si un des ordres de mise à jour échoue, toutes les modifications déjà effectuées dans la transaction doivent être annulées. À la fin d’une transaction, la base de données est toujours dans un état cohérent du point de vue de la logique applicative.

À titre d’exemple, considérons une transaction de virement bancaire qui serait constituée de trois ordres de mise à jour :

Si le deuxième UPDATE échoue pour une raison ou pour une autre, il faut annuler le premier UPDATE et ne pas effectuer l’ordre INSERT.

2. Gérer les transactions

Par défaut, MySQL fonctionne dans un mode validation automatique (option AUTOCOMMIT égale à 1) : chaque modification effectuée est immédiatement et définitivement enregistrée dans la base de données, ce qui ne permet pas de gérer correctement les transactions.

Par ailleurs, la gestion des transactions est supportée uniquement pour le type de table InnoDB.

À partir du moment où vous utilisez une table qui supporte les transactions, vous pouvez gérer les transactions à l’aide...

1. Principes

MySQL permet d’effectuer des recherches de mots sur l’ensemble d’un texte (ou de plusieurs textes).

Pour utiliser cette fonctionnalité, il faut :

Les index FULLTEXT peuvent être créés sur des colonnes de type CHAR, VARCHAR ou TEXT, mais uniquement sur des tables MyISAM ou InnoDB.

2. Création de l’index FULLTEXT

Les index FULLTEXT peuvent être créés lors de la création initiale de la table (dans l’ordre CREATE TABLE) ou ultérieurement (par un ordre ALTER TABLE ou un ordre CREATE FULLTEXT INDEX).

Pour créer un index FULLTEXT dans un ordre CREATE TABLE ou ALTER TABLE, il faut utiliser une clause FULLTEXT similaire à la clause INDEX présentée dans le chapitre Construire une base de données dans MySQL. L’ordre CREATE FULLTEXT INDEX est une variante de l’ordre CREATE INDEX présenté dans le chapitre Construire une base de données dans MySQL.

Syntaxe

CREATE TABLE nom_table 
( 
spécification_colonnes, 
FULLTEXT(nom_colonne[,...]) 
) 

ALTER TABLE nom_table ADD FULLTEXT(nom_colonne[,...]) 

CREATE FULLTEXT INDEX nom_index ON nom_table(nom_colonne[,...]) 

Exemple

mysql> CREATE FULLTEXT INDEX ix_texte   
    -> ON livre(titre,sous_titre,description);  
Query OK, 0 rows affected , 1 warning (0.09 sec)  
Records: 0  Duplicates: 0  Warnings: 1 
 
mysql> SHOW WARNINGS; 
+---------+------+--------------------------------------------------+ 
| Level   | Code | Message                                          | 
+---------+------+--------------------------------------------------+ ...

1. Introduction

Un programme stocké est un ensemble d’ordres SQL et d’instructions procédurales (structures de contrôle, déclaration de variables, etc.) qui réalise une tâche spécifique et qui est enregistré avec un nom dans la base de données. Le programme stocké peut ensuite être appelé à partir de différents environnements de développement pour exécuter la tâche en question.

Utiliser des programmes stockés offre plusieurs avantages :

Il existe deux types de programmes stockés :

2. Gestion des droits

Les privilèges suivants sont nécessaires pour gérer les programmes stockés :

Par ailleurs, pour créer un programme stocké, il faut avoir les privilèges adaptés sur les objets (tables, vues...

1. Définition

Un trigger (déclencheur en français) est un programme stocké associé à une table et qui se déclenche automatiquement lorsqu’un événement de mise à jour (INSERT, UPDATE ou DELETE) survient sur la table. Un trigger n’est jamais explicitement exécuté par un autre programme.

Les triggers permettent d’implémenter des règles de gestion côté serveur. Les principales utilisations des triggers sont les suivantes :

2. Gestion des triggers

L’ordre SQL CREATE TRIGGER permet de créer un trigger.

Syntaxe :

CREATE TRIGGER [IF NOT EXISTS] [nom_base.]nom_trigger 
  {BEFORE | AFTER}  
  {INSERT | UPDATE | DELETE} 
  ON nom_table 
  FOR EACH ROW  
  [{FOLLOWS | PRECEDES} autre_trigger]
BEGIN 
  instructions; 
END; 

La clause IF NOT EXISTS empêche qu’une erreur se produise s’il existe déjà un trigger qui porte le même nom ; cette clause est apparue en version 8.0.29.

nom_base désigne la base de données dans laquelle le trigger doit être défini. Par défaut, le programme est stocké dans la base de données courante.

nom_trigger spécifie le nom du trigger. Ce nom doit respecter les règles de nommage...