如果列不存在，请将列添加到mysql表中

Question

我的研究和实验尚未得出答案，所以我希望得到一些帮助。我正在修改应用程序的安装文件，在以前的版本中没有我想要添加的列。我不想手动添加列，而是在安装文件中，并且只有在表中不存在新列时才这样。该表创建如下：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.';如果我在create table语句下添加以下内容，那么我不确定如果列已经存在（并且可能已填充）会发生什么：ALTER TABLE `#__comm_subscribers` ADD `subscriber_surname`;ALTER TABLE `#__comm_subscribers` MODIFY `subscriber_surname` varchar(64) NOT NULL default '';所以，我尝试了以下我找到的地方。这似乎不起作用，但我不完全确定我正确使用它。/*delimiter '//'CREATE PROCEDURE addcol() BEGINIF NOT EXISTS(SELECT * FROM information_schema.COLUMNSWHERE 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;*/有没有人有一个很好的方法来做到这一点？

Answer 1

请注意，INFORMATION_SCHEMA5.0之前的MySQL不支持。5.0之前也不支持存储过程，所以如果你需要支持MySQL 4.1，这个解决方案并不好。

使用数据库迁移的框架使用的一种解决方案是在数据库中记录模式的修订号。只是一个包含单列和单行的表，其中一个整数表示哪个版本是当前有效的。更新架构时，请递增数字。

另一种解决方案是只尝试的ALTER TABLE ADD COLUMN命令。如果列已存在，则应该抛出错误。

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

在升级脚本中捕获错误并忽略它。

Answer 2

这是一个可行的解决方案（刚刚在Solaris上使用MySQL 5.0）：

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 ;

乍一看，它可能看起来比它应该更复杂，但我们必须在这里处理以下问题：

IF 语句只能在存储过程中使用，而不能直接运行，例如在mysql客户端中

更优雅和简洁SHOW COLUMNS在存储过程中不起作用，因此必须使用INFORMATION_SCHEMA

在MySQL中，分隔语句的语法很奇怪，因此您必须重新定义分隔符才能创建存储过程。不要忘记切换分隔符！

INFORMATION_SCHEMA是所有数据库的全局，不要忘记过滤TABLE_SCHEMA=DATABASE()。DATABASE()返回当前所选数据库的名称。

Answer 3

大多数答案都解决了如何在存储过程中安全地添加列，我需要在不使用存储过程的情况下安全地向表添加列，并发现MySQL不允许在SPIF Exists()外部使用。我会发布我的解决方案，它可能会帮助处于相同情况的人。

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;