Enregistrer la date de création et de mise à jour automatiquement avec MySQL

Comprendre la contrainte de MySQL

La première solution qui vient à l’esprit serait l’ajout de deux colonnes définies comme ceci :

• created_on : valeur par défaut le timestamp courant
• updated_on : valeur par défaut « vide » et timestamp courant lors de la mise à jour

Malheureusement MySQL n’autorise pas d’avoir plusieurs colonnes de type timestamp dans la même table. Essayer d’exécuter le code SQL suivant :

1.CREATE TABLE `fail` (
2.`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
3.`created_on` TIMESTAMP NOT NULL DEFAULT NOW() ,
4.`updated_on` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW() 
5.);

… et vous obtiendrez l’erreur suivante : ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Solution 1 : T’es NULL pour enregistrer la date !

Pour cette première solution nous allons utiliser une astuce consistant à insérer la valeur NULL dans les champs de type timestamp afin que MySQL mette la valeur de la date courante. Voici la définition de la table :

1.CREATE TABLE `foobar` (
2.`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
3.`created_on` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ,
4.`updated_on` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE NOW() ,
5.`dummy` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
6.);

Essayez maintenant d’exécuter plusieurs INSERT différents :

1.INSERT INTO foobar (created_on, dummy) VALUES (null, 'created_on=null');
2.INSERT INTO foobar (created_on, updated_on, dummy) VALUES (null, null, 'created_on=null, updated_on=null');
3.INSERT INTO foobar (dummy) VALUES ('no value for timestamp');
4.INSERT INTO foobar (created_on, dummy) VALUES (null, 'created=null for update');
5.UPDATE foobar SET updated_on = null, dummy = "updated row" WHERE id = 4;

… et constatez le résultat obtenu :

mysql> select * from foobar;
+----+---------------------+---------------------+----------------------------------+
| id | created_on          | updated_on          | dummy                            |
+----+---------------------+---------------------+----------------------------------+
|  1 | 2009-11-21 14:55:44 | 0000-00-00 00:00:00 | created_on=null                  |
|  2 | 2009-11-21 14:56:10 | 2009-11-21 14:56:10 | created_on=null, updated_on=null |
|  3 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | aucune valeur pour les timestamp |
|  4 | 2009-11-21 14:59:46 | 2009-11-21 15:00:28 | updated row                      |
+----+---------------------+---------------------+----------------------------------+
4 rows in set (0.00 sec)

Comme vous pouvez le voir cette méthode fonctionne plutôt correctement mais comprend un inconvénient majeur : il faut au moins spécifier une valeur NULL au champ created_on au moment de la création. Or l’idée qu’on avait en tête au début était de ne spécifier à aucun moment la valeur des champs date, de cette manière le développeur se concentre uniquement sur le contenu réel des données insérées. Cette méthode n’est donc pas suffisante !

Remarque : pour ce qui est du comportement du champ updated_on, je suis d’avis de l’initialiser avec la valeur ’0000-00-00 00:00:00′, de cette manière on peut rapidement voir les données qui n’ont pas subit de mise à jour. Il est vrai que l’on pourrait faire une comparaison avec la date de création mais… flemme 

Solution 2 : Pull the TRIGGER it won’t hurt !

Attention, cette solution requiert MySQL Version 5.x minimum, et le droit SUPER pour pouvoir créer un TRIGGER sur votre base de données. La deuxième solution, vous allez le voir, est beaucoup plus efficace que la première mais impose une configuration minimale. Cette solution repose sur les TRIGGER qui ne supportés par MySQL que depuis la version 5. Pour connaître votre version de MySQL :

• Si vous utiliser PHPMyAdmin : c’est affiché sur votre page d’accueil
• Si vous êtes en ligne de commande, tapez : mysql -V pour connaître votre version

Qu’est-ce qu’un TRIGGER ?

Si vous n’êtes pas familier de la conception de base de données et des possibilités offertes par votre SGDB, une petite explication de l’intérêt d’un TRIGGER s’impose.

Pour faire simple, un TRIGGER de base de données est une procédure stockée se déclenchant lors d’un évènement. Pour les non anglophones, sachez que « trigger » veut dire « gachette » ou « détente ». Il peut par exemple être déclenché sur l’insertion de données pour effectuer des vérifications par rapport à un référentiel de contraintes ou bien pour mettre à jour automatiquement une autre table, etc. Les TRIGGER sont développés en SQL et permettent donc d’interagir avec votre BDD comme bon vous semble.

Création de la table

La table précédente n’étant plus adaptée à notre nouveau cas, nous allons en créer un nouvelle :

1.CREATE TABLE `footrigg` (
2.`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
3.`created_on` TIMESTAMP NOT NULL DEFAULT NOW() ,
4.`updated_on` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
5.);

Dans cette nouvelle table c’est le champ created_on qui bénéficie de la gestion par défaut du timestamp de MySQL, ainsi à l’insertion notre champ sera initialisé avec la date de création. Vous pouvez déjà faire quelques insertions et mises à jour sur la table fraichement créée, vous constaterez que pour le moment le champ updated_on ne change pas, c’est tout à fait normal, nous n’avons pas encore implémenté notre TRIGGER.

Création du TRIGGER sur l’évènement UPDATE

Dans notre cas, le TRIGGER que nous voulons mettre en place va devoir effectuer une modification des données lors de l’UPDATE ; son rôle sera de mettre en place le timestamp courant.

Pour créer votre TRIGGER, il va être nécessaire de passer en mode ligne de commande car PHPMyAdmin rencontre un problème à l’interprétation du code. En effet, lorsque l’on développe un TRIGGER, il faut terminer chaque instruction par un « ; », et bien entendu lorsque PHPMyAdmin interprète la commande SQL, celle-ci échoue avec une erreur de syntaxe. Heureusement MySQL en mode console offre la possibilité de changer le DELIMITER, c’est-à-dire le caractère qui détermine si une instruction est terminée. Exécuter le code suivant dans votre terminal :

1.DELIMITER $$
2.CREATE TRIGGER footrigg_update BEFORE UPDATE ON footrigg
3.FOR EACH ROW BEGIN
4.SET NEW.updated_on = NOW();
5.END;
6.$$
7.DELIMITER ;

Et voilà le tour est joué, votre TRIGGER est créé, il ne vous reste plus qu’à le tester !

Pour finir

Bien entendu, chacune de ses deux méthodes présente ses avantages et inconvénients, la première solution est portable sur bon nombre de configurations mais impose au moins de définir la valeur de la date à la création avec NULL. La seconde fonctionnera nativement sans code devoir gérer les dates dans votre application mais requiert que vous puissiez créer des TRIGGER sur votre base de données.

La solution idéale n’existe pas… il reste juste à attendre que MySQL autorise plusieurs champs timestamp dans la même table…