Share
Vous savez probablement ce qu'est un déclencheur de base de données, du moins en termes conceptuels. Les chances sont encore plus grandes que vous sachiez que MySQL prend en charge les déclencheurs et les prend en charge depuis un certain temps. Je suppose, même avec ces connaissances, qu'un bon nombre d'entre vous ne tirez pas parti des déclencheurs de MySQL. Ils font partie de ces éléments qui devraient absolument figurer dans votre boîte à outils de développement, car ils peuvent réellement changer la façon dont vous regardez vos données..
"Cependant, au fur et à mesure que les applications deviennent de plus en plus compliquées, plus nous pouvons abstraire les couches d'une application pour gérer ce qu'elles devraient, plus notre capacité de développement interne devient grande."
Pour les non-initiés, un déclencheur est une règle que vous mettez sur une table et qui dit en gros que chaque fois que vous effacez, mettez à jour ou insérez quelque chose dans cette table, faites aussi autre chose. Par exemple, nous pourrions enregistrer une modification, mais au lieu d’écrire deux requêtes distinctes, une pour la modification et une pour le journal, nous pouvons écrire un déclencheur indiquant: "Chaque fois que cette ligne est mise à jour, créez une nouvelle ligne. dans un tableau différent pour me dire que la mise à jour a été faite ". Cela ajoute un peu de surcharge à la requête initiale, mais comme il n'y a pas deux paquets qui voyagent vers votre base de données pour faire deux choses distinctes, il y a un gain de performances global (en théorie de toute façon).
Les déclencheurs ont été introduits dans MySQL dans la version 5.0.2. La syntaxe d'un déclencheur est un peu étrangère au premier abord. MySQL utilise la norme ANSI SQL: 2003 pour les procédures et autres fonctions. Si vous êtes à l'aise avec un langage de programmation en général, ce n'est pas si difficile à comprendre. La spécification n'étant pas librement disponible, je ferai de mon mieux pour utiliser des structures simples et expliquer ce qui se passe dans le déclencheur. Vous aurez affaire aux mêmes structures logiques que tout langage de programmation.
Comme je l'ai mentionné ci-dessus, les déclencheurs seront exécutés de manière procédurale lors des événements UPDATE, DELETE et INSERT. Ce que je n'ai pas mentionné, c'est qu'ils peuvent être exécutés avant ou après l'événement défini. Par conséquent, vous pourriez avoir un déclencheur qui se déclenchera avant un DELETE ou après un DELETE, et ainsi de suite. Cela signifie que vous pouvez avoir un déclencheur qui se déclenche avant un INSERT et un déclencheur distinct qui se déclenche APRÈS un INSERT, ce qui peut être très puissant..
Je vais examiner trois utilisations que vous pourriez envisager d’ajouter à votre boîte à outils. Il y a plusieurs utilisations que je ne vais pas explorer, car j'estime qu'il existe de meilleures méthodes pour obtenir les mêmes résultats, ou qu'elles méritent leur propre tutoriel. Chacune de ces utilisations que j'explore a une contrepartie dans la couche logique côté serveur et ne constitue pas un nouveau concept. Cependant, au fur et à mesure que les applications deviennent de plus en plus compliquées, plus nous pouvons abstraire les couches d’une application pour gérer ce qu’elles devraient, plus notre capacité de développement interne devient grande..
Je travaille avec un système de panier mythique, avec des articles qui ont des prix. J'ai essayé de garder la structure de données aussi simple que possible, uniquement à des fins d'illustration. Je nomme des colonnes et des tableaux dans un but de compréhension, et non pour une utilisation en production. J'utilise aussi TIMESTAMPS plutôt que d'autres alternatives pour plus de facilité. Pour ceux qui jouent à la version à la maison du jeu d'aujourd'hui, j'utilise les noms de table des chariots, cart_items, cart_log, items, items_cost..
Veuillez noter que tout au long de ce tutoriel, je vais utiliser des requêtes très simples pour exprimer mes points. Je ne lie aucune variable, car je n'utilise aucune entrée d'utilisateur. Je souhaite que les requêtes soient aussi faciles à lire que possible, mais n'utilisez ce didacticiel que pour des applications pratiques à déclencheur. Je sais qu'il pourrait y avoir un commentaire ou deux à ce sujet, alors considérez ceci comme un avertissement.
J'utilise le PHP Quick Profiler de Particle Tree pour connaître les temps d'exécution. J'utilise également la couche d'abstraction de base de données fournie dans l'outil pour mon propre bénéfice. C'est un bel outil qui fait bien plus que fournir des temps d'exécution SQL.
J'utilise également Chive pour illustrer les effets de base de données et créer mes déclencheurs. Ciboulette est uniquement MySQL 5+, et est très similaire à PHPMyAdmin. C'est plus joli, mais aussi beaucoup plus buggé pour le moment. J'utilise Chive, simplement parce que cela donne de bonnes captures d'écran de ce qui se passe avec les requêtes.
Une autre note rapide. Vous devrez peut-être modifier le délimiteur pour MySQL lors de la création d'un déclencheur. Le délimiteur naturel pour MySQL est; mais comme nous allons utiliser ce délimiteur pour nos requêtes ajoutées, vous devrez peut-être explicitement renommer le délimiteur si vous les créez via la ligne de commande. J'ai choisi de ne pas montrer cela, car avec Chive, il n'est pas nécessaire de changer le délimiteur.
Pour changer un délimiteur, faites simplement ceci avant votre commande de déclenchement:
DELIMITE $$
Et ceci après votre commande de déclenchement:
DELIMITER;
Si vous effectuez la moindre normalisation de la structure de votre base de données, vous avez probablement déjà rencontré une période au cours de laquelle vous avez supprimé la source de données principale, tout en maintenant des fragments dans votre flux de données. Par exemple, vous pouvez avoir un identifiant_cart qui est référencé dans deux ou trois tables sans clé étrangère, d'autant plus que les clés étrangères ne sont pas prises en charge par le moteur MyISAM..
Voici ce que vous avez probablement déjà fait par le passé (simplifié pour illustration):
$ sql = 'DELETE FROM no_trigger_cart_items WHERE cart_id = 1'; $ rs = $ this-> db-> query ($ sql); $ sql = 'DELETE FROM no_trigger_carts WHERE cart_id = 1'; $ rs = $ this-> db-> query ($ sql);
Maintenant, en fonction de votre organisation, vous pouvez avoir une seule API ou méthode pour vider vos paniers. Si tel est le cas, vous avez isolé votre logique pour exécuter ces deux requêtes. Si ce n'est pas le cas, vous devez toujours vous rappeler que vous devez effacer les éléments de votre panier lorsque vous supprimez un panier spécifique. Pas difficile, mais quand vous oubliez, vous perdez l'intégrité de vos données.
Entrez notre déclencheur. Je vais créer un déclencheur très simple afin que, chaque fois que je supprime un panier, mon déclencheur se déclenche pour supprimer tous les éléments du panier ayant le même identifiant:
CREATE TRIGGER 'tutorial'. 'Before_delete_carts' BEFORE DELETE ON 'trigger_carts' POUR CHAQUE ROW COMMENCER SUPPRIMER DE trigger_cart_items WHERE OLD.cart_id = cart_id; FIN
La syntaxe très simple, comme je l'ai dit ci-dessus. Passons par chaque ligne.
Ma première ligne indique "Tutoriel 'CREATE TRIGGER'. 'Before_delete_carts'". Je dis à MySQL de créer un déclencheur sur la base de données "tutorial" pour lui donner le nom "before_delete_carts". J'ai tendance à nommer mes déclencheurs avec la formule de "When_How_Table". Cela fonctionne pour moi, mais il y a beaucoup d'autres façons de le faire..
Ma deuxième ligne indique à MySQL la définition de ce déclencheur, "BEFORE DELETE ON 'trigger_carts' FOR CHAQUE RANG". Je dis à MySQL qu'avant de supprimer sur cette table, faites quelque chose pour chaque ligne. Ce quelque chose est expliqué ensuite, dans nos BEGIN et END. "DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;" Je dis à MySQL avant de supprimer de trigger_carts, prenez le fichier OLD.cart_id et supprimez-le de trigger_cart_items. La syntaxe OLD est la variable définie. Nous en discuterons dans la section suivante où nous combinerons OLD et NEW..
Il n'y a vraiment rien pour créer ce déclencheur. L'avantage est de déplacer votre logique d'intégrité des données vers votre couche de données, ce dont je pourrais parler, c'est à cela qu'elle appartient. Il y a aussi un autre léger avantage, à savoir le léger gain de performance vu ci-dessous..
Deux requêtes:
Une requête avec un déclencheur:
Comme vous pouvez le constater, il y a un léger gain de performance auquel il faut s'attendre. Ma base de données que j'utilise est située sur localhost avec mon serveur, mais si j'avais utilisé un serveur de base de données distinct, mon gain de performances serait un peu plus important en raison du temps de rotation entre les deux serveurs. La durée de suppression de mon déclencheur est légèrement supérieure, mais comme il n'y a qu'une seule requête, le temps global diminue. Multipliez ceci par-dessus tout le code que vous utilisez pour préserver l'intégrité de vos données, et le gain en performances devient au moins modeste..
Une note sur la performance, la première fois que le déclencheur fonctionne, il peut être beaucoup plus lent que les temps ultérieurs. Je n'utilise pas nécessairement des déclencheurs pour le gain de performances, mais plutôt pour déplacer ma logique de données vers ma couche de données, comme si vous vouliez déplacer votre présentation de votre balisage vers votre couche de présentation, également appelée CSS.
Le prochain exemple que nous examinerons portera sur la journalisation. Dites que je veux garder une trace de chaque article placé dans un panier. Peut-être que je veux surveiller le taux d’achat de mon panier. Peut-être, je veux juste avoir une copie de chaque article placé dans un panier, pas nécessairement vendu, juste pour avoir un aperçu de l'esprit de mes clients. Peut-être avez-vous créé les éléments de votre panier en tant que table MEMORY et souhaitez-vous enregistrer tous les éléments d'une table InnoDB? Quelle que soit la raison, examinons un déclencheur INSERT, qui ouvrira de bonnes possibilités de journalisation ou d’audit de nos données..
Avant les déclencheurs, nous avons probablement déjà fait quelque chose comme ceci (encore une fois, simplifié pour illustration):
Nous pouvons maintenant créer un déclencheur très simple pour ce processus de journalisation:
CREATE TRIGGER 'after_insert_cart_items' APRES L'INSERT SUR 'trigger_cart_items' POUR CHAQUE ROW COMMENCER A INSERER DANS trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id); FIN
Revoyons cela de nouveau, pour que tout soit clair sur le rôle de ce déclencheur. Commençons par la ligne "CREATE TRIGGER 'after_insert_cart_items" ". Je demande à nouveau à MySQL de créer un déclencheur portant le nom "after_insert_cart_items". Le nom pourrait être "Foo", ou "BullWinkle" ou peu importe comment vous voulez l'appeler, mais encore une fois, je préfère illustrer mes noms de déclencheurs. Ensuite, nous voyons, "AFTER INSERT ON 'trigger_cart_items' FOR EACH ROW". Encore une fois, cela signifie qu'après que nous ayons inséré quelque chose sur trigger_cart_items, pour chaque ligne insérée, exécutez ce qui est entre mon début et la fin.
Enfin, nous avons juste, "INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);" qui est une requête standard à l'exception de mes deux valeurs. J'utilise la nouvelle valeur insérée dans la table cart_items.
Et nous avons réduit nos requêtes de moitié avec le gain de performance subtil:
Et juste pour vérifier que notre déclencheur fonctionne, je vois les valeurs dans mon tableau:
C'est encore relativement facile, mais nous travaillons avec quelques valeurs, ce qui peut ajouter un peu à la complexité. Regardons quelque chose d'un peu plus difficile.
À ce stade, nous pouvons ignorer l'ancienne méthode de requêtes multiples avec une seule requête. J'imagine que cela deviendra un peu fastidieux de continuer à mesurer les performances des requêtes. Au lieu de cela, entrons dans quelques exemples plus avancés de déclencheurs.
La logique commerciale est l'endroit où les bugs se glissent toujours. Indépendamment de notre niveau de prudence ou d'organisation, quelque chose se glisse toujours entre les mailles du filet. Les déclencheurs sur UPDATE atténuent cela juste un peu. Nous avons un certain pouvoir dans un déclencheur pour évaluer la valeur de la valeur OLD et définir la valeur NEW en fonction de l'évaluation. Supposons, par exemple, que le prix de nos articles soit toujours majoré de 30% du coût de ceux-ci. Il est donc logique que lorsque nous actualisons notre coût, nous devions également actualiser notre prix. Gérons cela avec un déclencheur.
CREATE TRIGGER 'after_update_cost' APRES LA MISE A JOUR SUR 'trigger_items_cost' POUR CHAQUE ROW COMMENCER LA MISE A JOUR DU trigger_items SET price = (NEW.cost * 1.3) WHERE item_id = NEW.item_id; FIN
Ce que nous faisons, c'est mettre à jour la table des articles avec un prix basé sur le NEW.cost times 1.3. J'ai entré un coût de 50 $, alors mon nouveau prix devrait être de 65 $.
Effectivement, cette gâchette a également fonctionné.
Nous devons examiner un exemple un peu plus avancé. Nous avons déjà la règle pour modifier le prix d'un article en fonction de son coût. Disons que nous voulons hiérarchiser un peu nos coûts. Si le coût est inférieur à 50 $, notre coût est en réalité de 50 $. Si le coût est supérieur à 50 dollars mais inférieur à 100 dollars, notre coût devient 100 dollars. Bien que mon exemple ne corresponde probablement pas à une véritable règle commerciale, nous ajustons chaque jour les coûts en fonction de facteurs. J'essaie simplement de garder l'exemple facile à comprendre.
Pour ce faire, nous allons encore travailler avec une mise à jour, mais cette fois nous allons le lancer avant d'exécuter notre requête. Nous allons également travailler avec une déclaration IF, qui est disponible pour nous.
Voici le nouveau déclencheur:
CREATE TRIGGER 'before_update_cost' AVANT LA MISE A JOUR SUR 'trigger_items_cost' POUR CHAQUE ROW COMMENCE SI NEW_COST < 50 THEN SET NEW.cost = 50; ELSEIF NEW.cost > 50 ET NOUVEAU.cost < 100 THEN SET NEW.cost = 100; END IF; END
Ce que nous faisons maintenant ne consiste pas à appeler une requête, mais simplement à remplacer la valeur. Je dis que si le coût est inférieur à 50 $, gagnez-le simplement 50 $. Si le coût est compris entre 50 et 100 dollars, gagnez 100 dollars. Si c'est au-dessus de ça, alors je le laisse juste rester le même. Ma syntaxe ici n'est pas étrangère à celle d'un autre langage côté serveur. Nous devons fermer notre clause IF avec un END IF; mais à part ça, ce n'est vraiment pas compliqué.
Juste pour vérifier si notre déclencheur fonctionne, j'ai entré une valeur de 30 $ pour le coût, et cela devrait être de 50 $:
Lorsque j'entre un coût de 85 $, voici la valeur:
Et, juste pour vérifier si mon déclencheur AFTER UPDATE fonctionne toujours, mon prix devrait maintenant être de 130 $:
La vie est belle.
Je n'ai touché que le sommet de l'iceberg avec les déclencheurs et MySQL. Bien qu'il existe d'innombrables utilisations des déclencheurs, je m'entendais très bien sans eux auparavant en traitant mes données dans ma couche logique. Cela dit, la possibilité d'ajouter des règles à mes données dans la couche de données est tout à fait logique. Lorsque vous ajoutez les améliorations de performances modestes, l'avantage est encore plus grand.
Nous devons maintenant faire face à des applications Web complexes à trafic élevé. L'utilisation d'un déclencheur sur un site Web d'une page peut ne pas être la meilleure utilisation du temps et de l'énergie; un déclencheur sur une application Web complexe peut faire toute la différence. J'espère que vous avez apprécié les exemples et laissez-moi savoir ce qui doit être expliqué davantage.
Accentsconagua