MySQL:如果列不存在，则使用ALTER TABLE [英] MySQL: ALTER TABLE if column not exists

问题描述

我有此代码:

ALTER TABLE `settings`
ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1

而且我只想在此列不存在的情况下更改此表.

And I want to alter this table only if this column doesn't exist.

我正在尝试许多不同的方法，但是没有任何效果:

I'm trying a lot of different ways, but nothing works:

ALTER TABLE `settings`
ADD COLUMN IF NOT EXISTS `multi_user` TINYINT(1) NOT NULL DEFAULT 1

带有过程:

DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_NAME = 'settings' AND 
                            COLUMN_NAME = 'multi_user');
    IF _count = 0 THEN
        ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1
    END IF;
END $$
DELIMITER ; 

我在END IF中出错，然后在END中又在1中出错

I got error in END IF, then in END and then in 1

如何使这个过程尽可能简单?

How can I make this as simple as possible?

推荐答案

在存储过程中使用以下内容:

Use the following in a stored procedure:

IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tablename'
             AND table_schema = 'db_name'
             AND column_name = 'columnname')  THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

这篇关于MySQL:如果列不存在，则使用ALTER TABLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！