Malheureusement, MySQL ne propose pas encore ce type d'objet. Il faut donc se débrouiller avec les outils disponibles.
"Fausses" vues matérialisées pour MySQL
On veut donc une structure qui permet de stocker des données, provenant d'une requête SELECT.
Qu'a-t-on comme structure qui permet de stocker les données ?
La réponse semble évidente : une table !
Pour créer une vue matérialisée avec MySQL, on utilise donc tout simplement une table, dans laquelle on stocke les résultats
d'une requête SELECT.
Exemple : on veut matérialiser V_Revenus_annee_espece.
Code : SQL
CREATE TABLE VM_Revenus_annee_espece
ENGINE = InnoDB
SELECT YEAR(date_reservation) AS annee, Espece.id AS espece_id,
SUM(Adoption.prix) AS somme, COUNT(Adoption.animal_id) AS nb
FROM Adoption
INNER JOIN Animal ON Animal.id = Adoption.animal_id
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY annee, Espece.id;
Par facilité, on a utilisé ici la commande CREATE TABLE ... SELECT. Mais il est tout à fait possible de créer la
table avec la commande habituelle, et de la remplir par la suite, avec un INSERT INTO... SELECT par exemple. Il
s'agit d'une table tout à fait normale. Ce n'est que l'usage auquel elle est destinée qui en fait une vue matérialisée.
On a maintenant matérialisé la vue V_Revenus_annee_espece, avec VM_Revenus_annee_espece.
Et c'est tout ?
Non bien sûr. C'est bien beau d'avoir créé une table, mais il va falloir aussi tenir cette vue matérialisée à jour.
Mise à jour des vues matérialisées
On l'a dit, il ne suffit pas de créer une vue matérialisée à partir des données, il faut aussi la tenir à jour lorsque les données des
tables d'origine changent.
Deux possibilités :
une mise à jour sur demande ;
une mise à jour automatique chaque fois qu'un changement est fait.
Mise à jour sur demande
Si l'on veut pouvoir mettre la vue matérialisée à jour ponctuellement, on peut utiliser une procédure stockée. Le plus simple sera
de supprimer les données de la vue matérialisée, puis d'insérer les données à jour grâce à la même requête de sélection ayant
servi à créer/initialiser la vue matérialisée.
Exemple : procédure stockée permettant de mettre à jour la vue VM_Revenus_annee_especes.
Code : SQL
DELIMITER |
CREATE PROCEDURE maj_vm_revenus()
Partie 6 : Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
367/414
www.openclassrooms.com
"Fausses" vues matérialisées pour MySQL
On veut donc une structure qui permet de stocker des données, provenant d'une requête SELECT.
Qu'a-t-on comme structure qui permet de stocker les données ?
La réponse semble évidente : une table !
Pour créer une vue matérialisée avec MySQL, on utilise donc tout simplement une table, dans laquelle on stocke les résultats
d'une requête SELECT.
Exemple : on veut matérialiser V_Revenus_annee_espece.
Code : SQL
CREATE TABLE VM_Revenus_annee_espece
ENGINE = InnoDB
SELECT YEAR(date_reservation) AS annee, Espece.id AS espece_id,
SUM(Adoption.prix) AS somme, COUNT(Adoption.animal_id) AS nb
FROM Adoption
INNER JOIN Animal ON Animal.id = Adoption.animal_id
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY annee, Espece.id;
Par facilité, on a utilisé ici la commande CREATE TABLE ... SELECT. Mais il est tout à fait possible de créer la
table avec la commande habituelle, et de la remplir par la suite, avec un INSERT INTO... SELECT par exemple. Il
s'agit d'une table tout à fait normale. Ce n'est que l'usage auquel elle est destinée qui en fait une vue matérialisée.
On a maintenant matérialisé la vue V_Revenus_annee_espece, avec VM_Revenus_annee_espece.
Et c'est tout ?
Non bien sûr. C'est bien beau d'avoir créé une table, mais il va falloir aussi tenir cette vue matérialisée à jour.
Mise à jour des vues matérialisées
On l'a dit, il ne suffit pas de créer une vue matérialisée à partir des données, il faut aussi la tenir à jour lorsque les données des
tables d'origine changent.
Deux possibilités :
une mise à jour sur demande ;
une mise à jour automatique chaque fois qu'un changement est fait.
Mise à jour sur demande
Si l'on veut pouvoir mettre la vue matérialisée à jour ponctuellement, on peut utiliser une procédure stockée. Le plus simple sera
de supprimer les données de la vue matérialisée, puis d'insérer les données à jour grâce à la même requête de sélection ayant
servi à créer/initialiser la vue matérialisée.
Exemple : procédure stockée permettant de mettre à jour la vue VM_Revenus_annee_especes.
Code : SQL
DELIMITER |
CREATE PROCEDURE maj_vm_revenus()
Partie 6 : Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
367/414
www.openclassrooms.com