将列添加到mysql表(如果不存在) [英] add column to mysql table if it does not exist

问题描述

我的研究和实验还没有得到答案，所以我希望能有所帮助.

My research and experiments haven't yielded an answer yet, so I am hoping for some help.

我正在修改一个应用程序的安装文件，该应用程序在以前的版本中没有我现在要添加的列.我不想手动添加该列，而仅在表中尚不存在新列的情况下，才在安装文件中添加该列.

I am modifying the install file of an application which in previous versions did not have a column which I want to add now. I do not want to add the column manually, but in the installation file and only if the new column does not already exist in the table.

该表如下创建:

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语句下面添加以下内容，那么我不确定如果该列已经存在(可能已填充)会发生什么情况?

If I add the following, below the create table statement, then I am not sure what happens if the column already exists (and perhaps is populated):

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

因此，我尝试了在某处找到的以下内容.这似乎不起作用，但我不能完全确定我是否正确使用了它.

So, I tried the following which I found somewhere. This does not seem to work but I am not entirely sure I used it properly.

/*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;*/

有人能做到这一点吗?

推荐答案

请注意，5.0之前的MySQL不支持INFORMATION_SCHEMA. 5.0之前的版本也不支持存储过程，因此，如果您需要支持MySQL 4.1，则此解决方案不是很好.

Note that INFORMATION_SCHEMA isn't supported in MySQL prior to 5.0. Nor are stored procedures supported prior to 5.0, so if you need to support MySQL 4.1, this solution isn't good.

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

One solution used by frameworks that use database migrations is to record in your database a revision number for the schema. Just a table with a single column and single row, with an integer indicating which revision is current in effect. When you update the schema, increment the number.

另一种解决方案是仅 try ALTER TABLE ADD COLUMN命令.如果该列已经存在，它将引发错误.

Another solution would be to just try the ALTER TABLE ADD COLUMN command. It should throw an error if the column already exists.

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

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

Catch the error and disregard it in your upgrade script.

这篇关于将列添加到mysql表(如果不存在)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！