更新MySQL数据库表结构

Question

我希望更新MySQL数据库模式（使用MySQL代码），但不幸的是，由于它们是分布式的，我不确定表的状态。

假设一些“客户端”有一个名为“user”的表，其模式如下：

name VARCHAR(64) NOT NULL
password VARCHAR(64) NOT NULL

我想添加一个名为“email”的列，但很有可能他们已经安装了此列（具体取决于他们的版本）。

如何运行一个命令来确保有一个“email”列，并且如果已经存在，则不执行任何操作？请注意，我需要对许多更复杂的表格执行此操作。

我知道我可以创建临时表并重新填充（如果这是唯一的解决方案，我会这样做），但我认为可能有一些“CREATE”或“UPDATE”表命令，具有“如果你已经有该列，请跳过”的逻辑。

- tdoakiiii

Answer 1

你可以尝试这样做：

DELIMITER $$
CREATE PROCEDURE Alter_MyTable()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_NAME = 'user' AND 
                            COLUMN_NAME = 'email');
    IF _count = 0 THEN
        ALTER TABLE user
            ADD COLUMN email varchar(512);
    END IF;
END $$
DELIMITER ;

或者将其作为通用存储过程来实现，例如：

create procedure AddColumnIfDoesntExists(
    IN dbName tinytext,
    IN tableName tinytext,
    IN fieldName tinytext,
    IN fieldDef text)
begin
    IF NOT EXISTS (
        SELECT * FROM information_schema.COLUMNS
        WHERE column_name=fieldName
        and table_name=tableName
        and table_schema=dbName
        )
    THEN
        set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
            ' ADD COLUMN ',fieldName,' ',fieldDef);
        prepare stmt from @ddl;
        execute stmt;
    END IF;
end;
//

delimiter ';'

- user3846015

---

有没有一种实用的方法可以为每个表的每个列都这样做，还是我需要逐个进行操作？ - tdoakiiii

@tdoakiiii：我更新了我的答案，加入了一个通用的存储过程。现在你需要通过提供表名和列名来调用这个存储过程。希望能帮到你 :) - user3846015

@tdoakiiii：- 这样调用 call AddColumnIfDoesntExists(Database(), 'user', 'email', 'varchar(100) null'); - user3846015

太棒了，谢谢。如果该列已经存在，这不会产生错误，对吧？ - tdoakiiii

我在这一行 ' ADD COLUMN ',fieldName,' ',fieldDef); 上遇到了一个错误：#1064 - 您的SQL语法有误，请检查与您的MySQL服务器版本相对应的手册，以获取正确的语法使用方法。该错误出现在第15行附近。 - tdoakiiii

Answer 2

如果该列已经存在，则 ALTER TABLE ADD COLUMN 语句将抛出错误，因此，如果您认为由于尝试添加已经存在的列而可能丢失数据，那么这不是问题，如果有任何问题，您需要处理错误。请参见add column to mysql table if it does not exist 还有一些资源告诉您如何使用存储过程等来处理这些问题。请参见MySQL add column if not exist。

希望对您有所帮助。

- jbarrueta

---

问题在于我不知道是缺少一列还是五列，而且我试图避免添加列的混乱。相反，我希望有一个神奇的“更新”程序，而不必逐个处理每一列。这是因为我在开始之前没有预见性所导致的结果。 - tdoakiiii