MySQL：如果列不存在，修改表 ALTER TABLE

Question

我有这段代码：

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

如果这一列不存在，我只想修改这个表格。

我尝试了很多不同的方式，但都没有成功：

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 中遇到了错误。

我该如何让这尽可能简单？

- BrunoRamalho

---

可能是MySQL添加列如果不存在的重复问题。 - Rikesh

可能是重复的问题：MySQL：如果不存在，如何添加列？ - Ankit Jaiswal

我尝试了这个答案！但是我得到了错误。 - BrunoRamalho

值得一提的是，MariaDB 允许使用 ALTER TABLE tablename ADD COLUMN IF NOT EXISTS columnname columntype; 命令添加列，如果该列不存在的话。 - Skippy le Grand Gourou

可能是重复问题 https://dev59.com/C3NA5IYBdhLWcg3wa9Gp - gawkface

Answer 1

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

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;

- Harsh

---

在这里需要重申的是，“[在]存储过程中”这个概念。以上代码本身是有效的T-SQL代码，我在过去的几年中主要使用这段代码，但在重新阅读本文之前曾经为此苦恼了一段时间。 - rstackhouse

比如说在一个临时脚本中，你会怎么做呢？这个答案在SQL Server中可以很好地工作，但是对我来说，MySql真的很让人烦恼！？！ - redevill

有没有更简单的方法来管理批量列插入并避免重复？ - Aadii Mughal

Answer 2

使用 PREPARE/EXECUTE 并查询模式。 主机不需要有创建或运行程序的权限:

SET @dbname = DATABASE();
SET @tablename = "tableName";
SET @columnname = "colName";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT(11);")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

- abahet

---

我使用过它，但在使用php时遇到了一些麻烦，但无论如何我喜欢这种方法。http://dba.stackexchange.com/questions/160476/mysql-throws-error-but-if-copied-from-error-message-or-die-into-phpmyadmin-it - ddlab

我正在遵循这个解决方案，但是出现了一些错误，请帮忙。 https://stackoverflow.com/questions/59999879/mysql-missing-expression-near-on-at-position-25-commands-out-of-sync-you-ca - fudu

Answer 3

这里有一个解决方案，不需要查询INFORMATION_SCHEMA，如果列不存在，则直接忽略错误。

DROP PROCEDURE IF EXISTS `?`;
DELIMITER //
CREATE PROCEDURE `?`()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
  ALTER TABLE `table_name` ADD COLUMN `column_name` INTEGER;
END //
DELIMITER ;
CALL `?`();
DROP PROCEDURE `?`;

附言：可以随意为它取一个名字，而不是 ?

- raugfer

---

我很惊讶这个答案排名不太靠前，有什么原因吗？在我看来它很优雅。 - rain_

通过使用这个存储过程，我可以节省先检查表是否存在的资源。很棒的答案，应该投票选为最佳答案。 - Kyad

@johnktejik 尝试分别调用mysqli_query() 4次，每个命令都要单独调用：第1行，第3-7行（省略第7行的//分隔符），第9行和第10行。 - raugfer

@rain_ 可能是因为这会跳过任何异常，而不仅仅是当column_name已经存在时。Harsh的回答不会抑制任何异常。 - oriadam

@johnk 在 @rain_ 的评论中补充一下，将第2行到第7行转换为一行应该可以解决问题（对我来说有效）... DELIMITER // CREATE PROCEDURE `?`() BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; ALTER TABLE `table_name` ADD COLUMN `column_name` INTEGER; END // DELIMITER ; - undefined

Answer 4

如不存在，添加字段：

CALL addFieldIfNotExists ('settings', 'multi_user', 'TINYINT(1) NOT NULL DEFAULT 1');

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

    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;
$$

• 这不是原创，但确实是复制粘贴
• 来源：javajon.blogspot.com/2012/10/

- Jonathan

Answer 5

我使用了以下方法(不使用存储过程): SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_name' AND COLUMN_NAME = 'column_name' 如果没有返回任何行，则表示该列不存在，然后需要修改表: ALTER TABLE tbl_name ADD COLUMN column_name TINYINT(1) NOT NULL DEFAULT 1 希望这可以帮到你。

- Adiyya Tadikamalla

Answer 6

希望这可以帮助您

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tbl_name'
AND table_schema = 'db_name'
AND column_name = 'column_name'

或者

delimiter '//'

CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME='new_column' AND TABLE_NAME='tablename' AND TABLE_SCHEMA='the_schema'
)
THEN
    ALTER TABLE `the_schema`.`the_table`
    ADD COLUMN `new_column` TINYINT(1) NOT NULL DEFAULT 1;;

END IF;
END;
//

delimiter ';'

CALL addcol();

DROP PROCEDURE addcol;

- Ezhil

Answer 7

$sql="SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Your_Table_Name' AND COLUMN_NAME = 'Your_New_Column_Name'";
$RESULT = mysqli_query($conn,$sql);

如果表中不存在该列，则上述查询返回0，那么您需要运行以下修改查询：

alter table 表名 add 列名 数据类型;

"Original Answer"的翻译是"最初的回答"

if($RESULT){
    $sqll="ALTER TABLE Your_table_Name ADD COLUMN Your_New_Column_Name varchar(20) NOT NULL DEFAULT 0";
}

- ABHISHEK TRIPATHI

Answer 8

这个对我有用：

    SELECT count(*)
    INTO @exist
    FROM information_schema.columns
    WHERE table_schema = 'mydatabase'
    and COLUMN_NAME = 'mycolumn'
    AND table_name = 'mytable' LIMIT 1;

    set @query = IF(@exist <= 0, 'ALTER TABLE mydatabase.`mytable`  ADD COLUMN `mycolumn` MEDIUMTEXT NULL',
    'select \'Column Exists\' status');

    prepare stmt from @query;

    EXECUTE stmt;

- Tejpal Sharma

Answer 9

SET @dbname = DATABASE();
SET @tablename = "table";
SET @columnname = "fieldname";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " DECIMAL(18,4) NULL;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists; 

- Mukund Thakkar

Answer 10

使用这个作为例子： https://dbabulletin.com/index.php/2018/03/29/best-practices-using-flyway-for-database-migrations/

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_database_4_to_5 $$

CREATE PROCEDURE upgrade_database_4_to_5()

BEGIN



IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()

AND COLUMN_NAME='multi_user' AND TABLE_NAME='settings') ) THEN

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

END IF;



END $$

CALL upgrade_database_4_to_5() $$

DELIMITER ;

- user1689987