Comment générer des colonnes de séquence uniques basées sur des relations de clés étrangères dans MySQL ?

Amélioration de la base de données : génération de colonnes de séquence basées sur des relations de clés étrangères dans MySQL

Dans une base de données relationnelle comme MySQL, il peut s'avérer nécessaire d'ajouter une colonne dans une table qui capture un numéro de séquence. Cela peut constituer un défi lorsque la séquence doit être unique à des valeurs associées spécifiques dans une autre colonne. Explorons une solution à ce problème.

Énoncé du problème

Considérons l'exemple d'une table de base de données avec la structure suivante :

CREATE TABLE mytable (
  ID INT NOT NULL,
  ACCOUNT INT NOT NULL,
  some_other_stuff TEXT
);

Le L'objectif est d'ajouter une colonne séq qui attribue des numéros de séquence uniques pour chaque valeur de COMPTE distincte.

SQL Solution

Une solution élégante à ce problème consiste à utiliser un déclencheur dans MySQL. Un déclencheur est un objet de base de données qui exécute automatiquement un ensemble d'actions prédéfinies lorsque des événements spécifiques se produisent sur une table.

Création du déclencheur

Pour créer un déclencheur qui génère la colonne de séquence, exécutez l'instruction SQL suivante :

CREATE TRIGGER trg_mytable_bi
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
      DECLARE nseq INT;
      SELECT  COALESCE(MAX(seq), 0) + 1
      INTO    nseq
      FROM    mytable
      WHERE   account = NEW.account;
      SET NEW.seq = nseq;
END;

Comment le déclencheur Works

Ce déclencheur est exécuté avant que chaque ligne ne soit insérée dans la mytable. Il calcule un nouveau numéro de séquence pour la ligne en :

1. Sélectionne la valeur maximale de seq pour le compte spécifié ou renvoie 0 si aucune ligne n'existe.
2. Incrémente la valeur maximale de 1 .
3. Définition de la colonne seq de la nouvelle ligne sur la séquence calculée numéro.

Exemple

Considérez les données initiales du tableau :

ID	ACCOUNT	some_other_stuff
1	1	...
2	1	...
3	1	...
4	2	...
5	2	...
6	1	...

Insérer une nouvelle ligne dans le tableau avec ACCOUNT = 1 générerait une valeur séquentielle de 4.

ID	ACCOUNT	seq	some_other_stuff
1	1	1	...
2	1	2	...
3	1	3	...
4	2	1	...
5	2	2	...
**6	1	4	...	**

Conclusion

En tirant parti de la puissance des déclencheurs SQL, nous pouvons obtenir la fonctionnalité souhaitée consistant à ajouter une colonne de séquence basée sur un autre champ. Cette technique est efficace, flexible et facile à mettre en œuvre.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!