MySQL 添加列如果不存在错误

Question

如果 MySQL 中不存在，如何添加列？

我已经试过了，但是在最新的 MySQL 中它不再起作用了:

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;

错误:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (
    SELECT
        NULL
    FROM
        INFORMATION_SCHEMA.COLUMNS
    WHERE
    ' at line 1

Answer 1

出现该错误的唯一原因是您尝试单独运行它，而 IF block 必须在如下过程 block 内执行

create procedure usp_addcol
as
begin
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;
end;

现在您可以调用该过程了。