MySQL 如果不存在则添加列

MySQL add column if not exist

提问by phil88530

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'email_subscription' AND COLUMN_NAME = 'subscribe_all')
  THEN 
  ALTER TABLE email_subscription
  ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
  ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;

I had a look at huge amount of examples. but this query doesn't work, I got error of:

我看了大量的例子。但是这个查询不起作用，我得到了以下错误：

ERROR 1064 (42000): 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 * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =' at line 1

ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =') 附近使用的正确语法

采纳答案by John Woo

you can create a procedure for the query,

您可以为查询创建一个过程，

DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_NAME = 'email_subscription' AND 
                            COLUMN_NAME = 'subscribe_all');
    IF _count = 0 THEN
        ALTER TABLE email_subscription
            ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
            ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;
    END IF;
END $$
DELIMITER ;

回答by drsimonz

If your host doesn't give you permission to create or run procedures, I think I found another way to do this using PREPARE/EXECUTE and querying the schema:

如果您的主机没有授予您创建或运行过程的权限，我想我找到了另一种使用 PREPARE/EXECUTE 并查询架构的方法：

SET @s = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'table_name'
        AND table_schema = DATABASE()
        AND column_name = 'col_name'
    ) > 0,
    "SELECT 1",
    "ALTER TABLE table_name ADD col_name VARCHAR(100)"
));

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

回答by Hamlet Hakobyan

You are using MS SQL Server syntax in MySQL.

您在 MySQL 中使用 MS SQL Server 语法。

回答by spencer7593

There is no equivalent syntax to achieve this in a single MySQL statement.

在单个 MySQL 语句中没有等效的语法来实现这一点。

To get something simlilar, you can either

要获得类似的东西，您可以

1) attempt to add the column with an ALTER TABLE, and let MySQL raise an error if a column of that name already exists in the table, or

1) 尝试使用 ALTER TABLE 添加列，如果表中已存在该名称的列，则让 MySQL 引发错误，或

2) query the information_schema.columns view to check if a column of that name exists in the table.

2) 查询information_schema.columns 视图以检查表中是否存在该名称的列。

Note that you really do need to check for the table_schema, as well as the table_name:

请注意，您确实需要检查 table_schema 以及 table_name：

SELECT column_name
  FROM information_schema.columns 
 WHERE table_schema = 'foo'
   AND table_name   = 'email_subscription'
   AND column_name  = 'subscribe_all'

and based on that, decide whether to run the ALTER TABLE

并在此基础上决定是否运行 ALTER TABLE

回答by Saharsh Shah

Also add condition for database name to check column existance.

还为数据库名称添加条件以检查列是否存在。

Try this:

尝试这个：

DELIMITER $$
CREATE PROCEDURE sp_AlterTable()
BEGIN
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_SCHEMA = 'dbName' AND 
                         TABLE_NAME = 'email_subscription' AND 
                         COLUMN_NAME = 'subscribe_all') THEN 
       ALTER TABLE email_subscription
          ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
          ADD COLUMN subscribe_category VARCHAR(512) DEFAULT NULL;
    END IF; 
END $$
DELIMITER ;

回答by rkm432

SET @s = (SELECT IF(
    (SELECT COUNT(column_name)
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE table_name = 'oc_bitcoin_wallets_receive'
          AND table_schema = 'k_opencart2'
          AND column_name = 'test3'
    ) > 0,
    "SELECT 1",
    "ALTER TABLE `oc_bitcoin_wallets_receive` ADD COLUMN `test3` INT NOT NULL AFTER `test2`;"
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Values for edit: oc_bitcoin_wallets_receive - table name, k_opencart2 - database name, test3 - name of new column, oc_bitcoin_wallets_receive - second location table test3 - second location column, test2 - name of column before new column.

编辑值：oc_bitcoin_wallets_receive - 表名，k_opencart2 - 数据库名，test3 - 新列的名称，oc_bitcoin_wallets_receive - 第二个位置表 test3 - 第二个位置列，test2 - 新列之前的列名。