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

add column to mysql table if it does not exist

提问by E Wierda

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.

我正在修改一个应用程序的安装文件，该文件在以前的版本中没有我现在要添加的列。我不想手动添加列，而是在安装文件中并且仅当表中不存在新列时。

The table is created as follows:

该表的创建方式如下：

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.';

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):

如果我在 create table 语句下方添加以下内容，那么我不确定如果该列已经存在（并且可能已填充）会发生什么：

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

Does anyone have a good way to do this?

有没有人有这样做的好方法？

采纳答案by Bill Karwin

Note that INFORMATION_SCHEMAisn'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.

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

One solution used by frameworks that use database migrationsis 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.

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

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

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

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

Catch the error and disregard it in your upgrade script.

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

回答by geekQ

Here is a working solution (just tried out with MySQL 5.0 on Solaris):

这是一个可行的解决方案（刚刚在 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 ;

On a first glance it probably looks more complicated than it should, but we have to deal with following problems here:

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

• IFstatements only work in stored procedures, not when run directly, e.g. in mysql client
• more elegant and concise SHOW COLUMNSdoes not work in stored procedure so have to use INFORMATION_SCHEMA
• the syntax for delimiting statements is strange in MySQL, so you have to redefine the delimiter to be able to create stored procedures. Do not forget to switch the delimiter back!
• INFORMATION_SCHEMA is global for all databases, do not forget to filter on TABLE_SCHEMA=DATABASE(). DATABASE()returns the name of the currently selected database.

• IF语句只适用于存储过程，而不适用于直接运行，例如在 mysql 客户端
• 更优雅和简洁SHOW COLUMNS在存储过程中不起作用所以必须使用 INFORMATION_SCHEMA
• MySQL 中分隔语句的语法很奇怪，因此您必须重新定义分隔符才能创建存储过程。不要忘记将分隔符切换回来！
• INFORMATION_SCHEMA 对所有数据库都是全局的，不要忘记过滤TABLE_SCHEMA=DATABASE(). DATABASE()返回当前选定数据库的名称。

回答by giuseppe

If you are on MariaDB, no need to use stored procedures. Just use, for example:

如果您使用的是 MariaDB，则无需使用存储过程。只需使用，例如：

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name tinyint(1) DEFAULT 0;

See here

看这里

回答by rahvin_t

Most of the answers address how to add a column safely in a stored procedure, I had the need to add a column to a table safely without using a stored proc and discovered that MySQL does not allow the use of IF Exists()outside a SP. I'll post my solution that it might help someone in the same situation.

大多数答案都解决了如何在存储过程中安全地添加列，我需要在不使用存储过程的情况下安全地将列添加到表中，并发现 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;

回答by Jake

Another way to do this would be to ignore the error with a declare continue handler:

另一种方法是使用以下命令忽略错误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();;

I think its neater this way than with an existssubquery. Especially if you have a lot of columns to add, and you want to run the script several times.

我认为这种方式比使用exists子查询更整洁。特别是如果您有很多列要添加，并且您想多次运行脚本。

more info on continue handlers can be found at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

有关继续处理程序的更多信息，请访问http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

回答by Jonathan

I'm using MySQL 5.5.19.

我正在使用 MySQL 5.5.19。

I like having scripts that you can run and rerun without error, especially where warnings seem to linger, showing up again later while I'm running scripts that have no errors/warnings. As far as adding fields goes, I wrote myself a procedure to make it a little less typing:

我喜欢有脚本，您可以无错误地运行和重新运行，尤其是在警告似乎持续存在的情况下，稍后在我运行没有错误/警告的脚本时再次出现。就添加字段而言，我为自己编写了一个程序以减少输入：

-- add fields to template table to support ignoring extra data 
-- at the top/bottom of every page
CALL addFieldIfNotExists ('template', 'firstPageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageFooterBeginY', 'INT NOT NULL DEFAULT 792');

The code to create the addFieldIfNotExistsprocedure is as follows:

创建addFieldIfNotExists过程的代码如下：

DELIMITER $$

DROP PROCEDURE IF EXISTS addFieldIfNotExists 
$$

DROP FUNCTION IF EXISTS isFieldExisting 
$$

CREATE FUNCTION isFieldExisting (table_name_IN VARCHAR(100), field_name_IN VARCHAR(100)) 
RETURNS INT
RETURN (
    SELECT COUNT(COLUMN_NAME) 
    FROM INFORMATION_SCHEMA.columns 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = table_name_IN 
    AND COLUMN_NAME = field_name_IN
)
$$

CREATE PROCEDURE addFieldIfNotExists (
    IN table_name_IN VARCHAR(100)
    , IN field_name_IN VARCHAR(100)
    , IN field_definition_IN VARCHAR(100)
)
BEGIN

    -- http://javajon.blogspot.com/2012/10/mysql-alter-table-add-column-if-not.html

    SET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);
    IF (@isFieldThere = 0) THEN

        SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ;
        SET @ddl = CONCAT(@ddl, ' ', field_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);

        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END IF;

END;
$$

I didn't write a procedure to safely modify a column, but I think the above procedure could be easily modified to do so.

我没有编写程序来安全地修改列，但我认为可以很容易地修改上述程序来这样做。

回答by Thomas Paine

I've taken the OP's sproc and made it reusable and schema independent. Obviously it still requires MySQL 5.

我采用了 OP 的 sproc 并使其可重用且与模式无关。显然它仍然需要 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 ;

回答by mat crocker

If you are running this in a script, you'll want to add the following line afterwards to make it rerunnable, otherwise you get a procedure already exists error.

如果您在脚本中运行它，您需要在之后添加以下行以使其可重新运行，否则您会收到一个过程已经存在的错误。

drop procedure foo;

回答by Maher

The best way for add the column in PHP > PDO :

在 PHP > PDO 中添加列的最佳方法：

$Add = $dbh->prepare("ALTER TABLE `YourCurrentTable` ADD `YourNewColumnName` INT NOT NULL");
$Add->execute();

Note: the column in the table is not repeatable, that means we don't need to check the existance of a column, but for solving the problem we check the above code:

注意：表中的列是不可重复的，也就是说我们不需要检查列是否存在，但是为了解决问题我们检查上面的代码：

for example if it works alert 1,if not 0, which means the column exist ! :)

例如，如果它工作警报 1，如果不是 0，这意味着该列存在！:)

回答by Maher

Check if Column Exist or not in PDO (100%)

检查列是否存在于 PDO (100%)

{
    if(isset($_POST['Add']))
    {
        $ColumnExist = $dbh->prepare("SELECT * FROM ColumnChecker where column_name='$insert_column_name' LIMIT 1");
        $ColumnExist ->execute();
        $ColumnName = $ColumnExist->fetch(2);
        $Display_Column_Name = $ColumnName['column_name'];

        if($Display_Column_Name == $insert_column_name)
        {
            echo "$Display_Column_Name already exist";
        } //*****************************
        else 
        {
            $InsertColumn = $dbh->prepare("insert into ColumnChecker ( column_name ) values ('$insert_column_name')");
            $InsertColumn->execute();

            if($InsertColumn)
            {
                $Add = $dbh->prepare("ALTER TABLE `$Table` ADD `$insert_column_name` $insert_column_type($insert_column_Length) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ");
                $Add->execute();

                if($Add)
                {
                    echo 'Table has been updated';  
                }
                else 
                {
                    echo 'Sorry! Try again...'; 
                }
            }   
        }
    }
}#Add Column into Table :)