SQL添加新列忽略重复项。

We have quite a few databases, and we're trying to run upgrade scripts on all of them to bring them up to date - as such, they all have different columns and tables.

我们有相当多的数据库，我们正试图在所有数据库上运行升级脚本，以使它们及时更新——因此，它们都有不同的列和表。

We want to add new tables and columns if they arent already present, so for instance

如果它们已经存在，我们希望添加新的表和列，例如。

CREATE TABLE IF NOT EXISTS `orders` ( `id` INT (11) NOT NULL , 
    `value` VARCHAR (50) , `designId` INT (11) , PRIMARY KEY ( `id`));

That works, but we're looking for the same kind of solution for columns. Our current solution throws Error Code: 1060 - Duplicate column name.

这是可行的，但是我们在寻找同样的列的解。我们当前的解决方案抛出错误代码:1060 -重复列名。

ALTER TABLE `orders` ADD COLUMN `customer` INT (1) NULL;

I've tried the following from garry passarella, but i get an error claiming incorrect sql syntax:

我尝试过garry passarella的以下语句，但是我得到了一个错误声明错误的sql语法:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'orders' AND COLUMN_NAME = 'customer') 
    BEGIN  ALTER TABLE orders
    ADD customer BIT DEFAULT NULL
END

If there is something we can use to get each line to ignore duplicates, or get the entire script to ignore error code 1060, it would be much appreciated.

如果有什么东西可以让我们让每一行忽略重复，或者让整个脚本忽略错误代码1060，我们将非常感激。

1 个解决方案

#1

The if ... begin ... end is SQL Server syntax. For MySQL, it's more like if ... then ... end if:

如果……开始……最后是SQL Server语法。对于MySQL来说，它更像是。然后……如果:

if not exists (select * from information_schema.columns
    where column_name = 'customer' and table_name = 'orders') then
    alter table orders add customer int(1) null;
end if

In reply to your comment: in MySQL, you can't type compound statements at the command line. They have to be in a function or stored procedure. For example:

在MySQL中，不能在命令行中输入复合语句。它们必须位于函数或存储过程中。例如:

drop procedure if exists sp_addcolumn;

delimiter //
create procedure sp_addcolumn()
begin
  if not exists (select * from INFORMATION_SCHEMA.COLUMNS 
      where table_name = 'orders' and column_name = 'customer') then
    alter table `orders` add column `customer` int(1) null;
  end if;
end//

delimiter ;
call sp_addcolumn;

There is an open request on the MySQL bug tracker to allow if statements outside stored procedures. It's current status is Needs Triage.

MySQL bug跟踪器上有一个打开的请求，允许在存储过程之外使用if语句。目前的状况是需要分类。

#1