Script d'ajout d'un champ dans une table

La principale bonne pratique lors de la rédaction d'un script SQL d'installation ou de mise à jour d'une base de données est que celui-ci soit réexécutable à l'infini sans corrompre la base de données.

Parmi les erreurs à éviter il y a :

• Ajout de lignes à chaque exécution d'un script avec des INSERT
• Erreur lorsque qu'une table ou une procédure stockée doit être créée et qu'elle existe déjà
• Et j'en passe...

Aujourd'hui je souhaitais donc partager une petite astuce lorsque vous avez besoin de modifier la structure d'une table en y ajoutant un champ supplémentaire. Si vous tenter d'éxécuter plusieurs fois le script suivant, vous aurez à partir de la seconde fois une belle erreur SQL.

ALTER TABLE `matable` ADD `monchamp` INT(0) NOT NULL ;

L'astuce est de créer une procédure stockée à la volée qui va se charger de vérifier si le champ n'existe pas et, le cas échéant, de le créer. Voici la formule magique.

-- On supprime la procédure stockée si elle existe déjà, afin d'éviter les erreurs et l'obsolescence de celle-ci
DROP PROCEDURE IF EXISTS AddColumnIfNotExists;
DELIMITER $$

CREATE PROCEDURE AddColumnIfNotExists(IN dbName tinytext, IN tableName tinytext, IN fieldName tinytext, IN fieldDef text)
BEGIN
	-- On vérifie si le champ existe déjà pour la base de données et la table données
	IF NOT EXISTS (
		SELECT * 
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE column_name = fieldName
		AND table_name = tableName
		AND table_schema = dbName
	)
	THEN
		-- On ajoute le champ s'il n'existe pas déjà
		SET @ddl = CONCAT('ALTER TABLE `', dbName, '`.`', tableName, '` ADD `', fieldName, '` ', fieldDef, ';');
		PREPARE stmt FROM @ddl;
		EXECUTE stmt;
	END IF;
END $$

DELIMITER ;

-- On appelle la procédure stockée pour ajouter le champ à la table si nécessaire (Database() renvoie le nom de la base de données en cours)
CALL AddColumnIfNotExists(Database(), 'matable', 'monchamp', 'INT(0) NOT NULL');

-- On supprime la procédure stockée pour éviter qu'elle n'encombre la base de données pour rien
DROP PROCEDURE AddColumnIfNotExists;

Voilà, c'est aussi simple que ça.