ajoute une colonne à la table mysql si elle n'existe pas

Question

Mes recherches et les expériences n'ont pas donné encore de réponse, donc je suis en espérant de l'aide.

Je suis en modifiant le fichier d'installation d'une application qui, dans les versions précédentes n'ont pas de colonne qui je veux ajouter maintenant. Je ne veux pas ajouter de la colonne manuellement, mais dans le fichier d'installation et seulement si la colonne n'existe pas déjà dans la table.

Le tableau est créé comme suit:

CREATE TABLE IF NOT EXISTS `#__comm_subscribers` (
      `subscriber_id` int(11) NOT NULL auto_increment,
      `user_id` int(11) NOT NULL default '0',
      `subscriber_name` varchar(64) NOT NULL default '',
      `subscriber_surname` varchar(64) NOT NULL default '',
      `subscriber_email` varchar(64) NOT NULL default '',
      `confirmed` tinyint(1) NOT NULL default '0',
      `subscribe_date` datetime NOT NULL default '0000-00-00 00:00:00',
      PRIMARY KEY  (`subscriber_id`),
      UNIQUE KEY `subscriber_email` (`subscriber_email`)
    ) ENGINE=MyISAM CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' COMMENT='Subscribers for Comm are stored here.';

Si j'ajoute la suite, ci-dessous l'instruction create table, puis je ne suis pas sûr de ce qui se passe si la colonne existe déjà (et peut-être est peuplée):

ALTER TABLE `#__comm_subscribers` ADD `subscriber_surname`;
ALTER TABLE `#__comm_subscribers` MODIFY `subscriber_surname` varchar(64) NOT NULL default '';

Donc, j'ai essayé à l'issue de laquelle j'ai trouvé quelque part. Cela ne semble pas fonctionner, mais je ne suis pas entièrement sûr que je l'ai utilisé correctement.

/*delimiter '//'
CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME='subscriber_surname' AND TABLE_NAME='#__comm_subscribers'
)
THEN
	ALTER TABLE `#__comm_subscribers`
	ADD COLUMN `subscriber_surname` varchar(64) NOT NULL default '';
END IF;
END;
//
delimiter ';'
CALL addcol();
DROP PROCEDURE addcol;*/

Quelqu'un aurait-il une bonne façon de le faire?

Answer 1

Voici une solution qui fonctionne (juste essayé avec MySQL 5.0 sur Solaris):

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$
CREATE PROCEDURE upgrade_database_1_0_to_2_0()
BEGIN

-- rename a table safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME='my_old_table_name') ) THEN
    RENAME TABLE 
        my_old_table_name TO my_new_table_name,
END IF;

-- add a column safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN
    ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT '';
END IF;

END $$

CALL upgrade_database_1_0_to_2_0() $$

DELIMITER ;

Sur un premier coup d'œil il ressemble probablement plus compliqué qu'il ne le devrait, mais nous devons traiter avec les problèmes suivants ici:

• IF états ne fonctionnent que dans des procédures stockées, pas lorsqu'il est exécuté directement, par exemple, dans le client mysql
• plus élégant et concis SHOW COLUMNS ne fonctionne pas dans une procédure stockée et utiliser INFORMATION_SCHEMA
• la syntaxe pour la délimitation des déclarations est étrange dans MySQL, de sorte que vous avez à redéfinir le délimiteur à être en mesure de créer des procédures stockées. Ne pas oublier basculer le délimiteur de retour!
• INFORMATION_SCHEMA est globale pour toutes les bases de données, n'oubliez pas de filtre sur TABLE_SCHEMA=DATABASE(). DATABASE() retourne le nom de la base de données actuellement sélectionnée.

Answer 2

Notez que INFORMATION_SCHEMA n'est pas pris en charge dans MySQL antérieures à la version 5.0. Ni sont stockées les procédures de prises en charge antérieures à la version 5.0, donc si vous avez besoin de support MySQL 4.1, cette solution n'est pas bonne.

Une solution utilisée par les cadres qui utilisent des migrations de base de données est à enregistrer dans votre base de données un numéro de révision du schéma. Juste une table avec une seule colonne et de ligne unique, avec un entier indiquant la révision est courant en effet. Lorsque vous mettez à jour le schéma, incrémenter le nombre.

Une autre solution serait de simplement essayer de l' ALTER TABLE ADD COLUMN commande. Il devrait lever une erreur si la colonne existe déjà.

ERROR 1060 (42S21): Duplicate column name 'newcolumnname'

Capture de l'erreur et de l'ignorer dans votre script de mise à niveau.

Answer 3

La plupart des réponses traitent de la façon d'ajouter une colonne en toute sécurité dans une procédure stockée. J'avais donc besoin d'ajouter une colonne à une table en toute sécurité sans utiliser de proc stockée. J'ai découvert que MySQL n'autorisait pas l'utilisation de IF Exists() en dehors d'un SP . Je vais poster ma solution que cela pourrait aider quelqu'un dans la même situation.

 SELECT count(*)
INTO @exist
FROM information_schema.columns 
WHERE table_schema = database()
and COLUMN_NAME = 'original_data'
AND table_name = 'mytable';

set @query = IF(@exist <= 0, 'alter table intent add column mycolumn4 varchar(2048) NULL after mycolumn3', 
'select \'Column Exists\' status');

prepare stmt from @query;

EXECUTE stmt;
 

Answer 4

Une autre façon de faire serait d’ignorer l’erreur avec un declare continue handler :

 delimiter ;;
create procedure foo ()
begin
    declare continue handler for 1060 begin end;
    alter table atable add subscriber_surname varchar(64);
end;;
call foo();;
 

Je pense que sa plus nette de cette façon qu'avec une sous-requête exists . Surtout si vous avez beaucoup de colonnes à ajouter et que vous voulez exécuter le script plusieurs fois.

Vous trouverez plus d'informations sur les gestionnaires de continuation à l' adresse http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Answer 5

J'ai pris le sproc de l'OP et l'ai rendu réutilisable et indépendant du schéma. De toute évidence, il faut toujours MySQL 5.

 DROP PROCEDURE IF EXISTS AddCol;

DELIMITER //

CREATE PROCEDURE AddCol(
    IN param_schema VARCHAR(100),
    IN param_table_name VARCHAR(100),
    IN param_column VARCHAR(100),
    IN param_column_details VARCHAR(100)
) 
BEGIN
    IF NOT EXISTS(
    SELECT NULL FROM information_schema.COLUMNS
    WHERE COLUMN_NAME=param_column AND TABLE_NAME=param_table_name AND table_schema = param_schema
    )
    THEN
        set @paramTable = param_table_name ;
        set @ParamColumn = param_column ;
        set @ParamSchema = param_schema;
        set @ParamColumnDetails = param_column_details;
        /* Create the full statement to execute */
        set @StatementToExecute = concat('ALTER TABLE `',@ParamSchema,'`.`',@paramTable,'` ADD COLUMN `',@ParamColumn,'` ',@ParamColumnDetails);
        /* Prepare and execute the statement that was built */
        prepare DynamicStatement from @StatementToExecute ;
        execute DynamicStatement ;
        /* Cleanup the prepared statement */
        deallocate prepare DynamicStatement ;

    END IF;
END //

DELIMITER ;