如果 MySQL 中不存在则添加列

发布于 2024-10-18 13:50:04 字数 221 浏览 7 评论 0 原文

我认为这非常简单，但仅当这些列不存在时我才尝试运行此命令：

ALTER TABLE `surveytable` ADD IF NOT EXISTS  Survey_Name_Qualtrics VARCHAR(20);
ALTER TABLE `surveytable` ADD IF NOT EXISTS Survey_URL_Qualtrics VARCHAR(600);

原文

I figure this is very easy, but I am trying to run this command only if those columns already don't exist:

ALTER TABLE `surveytable` ADD IF NOT EXISTS  Survey_Name_Qualtrics VARCHAR(20);
ALTER TABLE `surveytable` ADD IF NOT EXISTS Survey_URL_Qualtrics VARCHAR(600);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小…楫夜泊 2024-10-25 13:50:04

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

       END IF;
    END;


     delimiter ';'

     CALL addcol();

DROP PROCEDURE addcol;

这是我使用的代码，如果 ALTER 列上不存在，我认为您不能使用。

另一种选择是，如果您尝试插入已经存在的列，您可以观察出现的错误，我认为是 1062。并处理该错误。

当然，更好的方法是一开始就不出现错误。

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

       END IF;
    END;


     delimiter ';'

     CALL addcol();

DROP PROCEDURE addcol;

This is the code that i use, i dont think you can use if not exists on a ALTER column.

An alternative you can watch for the error you get if you try to insert a column that already exists, i think its 1062. And handle the error.

Of course the better way is to not get the error in the first place.

回复收藏 0 原文

冷情 2024-10-25 13:50:04

这在互联网上随处可见。基本思想是检查您的列的系统表INFORMATION_SCHEMA.COLUMNS。我假设“不存在”仅适用于列的名称。

http://snippets.dzone.com/posts/show/4663

http：/ /www.genexbs.com/blog/gbs.php/2008/08/09/how-to-check-if-column-exists-in-mysql-t

回复收藏 0 原文

羁客 2024-10-25 13:50:04

另一个直接的查询是告诉MySQL忽略查询期间出现的任何问题。

ALTER IGNORE TABLE `surveytable` ADD COLUMN Survey_Name_Qualtrics VARCHAR(20);

Another straight forward query for this would be to tell MySQL to IGNORE any issues that comes up during the query.

ALTER IGNORE TABLE `surveytable` ADD COLUMN Survey_Name_Qualtrics VARCHAR(20);

回复收藏 0 原文

花落人断肠 2024-10-25 13:50:04

diagnonalbatman 的答案对我不起作用；当我尝试时，MySQL 5.7 会抱怨语法错误。它可能在早期版本的 MySQL 中运行良好，但该评论毕竟已经有 8 年多了。

经过一番尝试和错误，这就是我最终得到的结果：

DELIMITER //
DROP PROCEDURE IF EXISTS addColumn //
CREATE PROCEDURE addColumn()
BEGIN
  IF NOT EXISTS (
    SELECT *
    FROM information_schema.COLUMNS
    WHERE COLUMN_NAME = 'column_name'
      AND TABLE_NAME = 'table_name'
      AND TABLE_SCHEMA = DATABASE()
  ) THEN
    ALTER TABLE `table_name`
    ADD COLUMN `column_name` bigint(20) UNSIGNED NOT NULL DEFAULT 1;
  END IF;
END //
DELIMITER ;
CALL addColumn;
DROP PROCEDURE addColumn;

The answer by diagnonalbatman did not work for me; MySQL 5.7 would complain about syntax errors when I tried it. It probably worked fine with an earlier version of MySQL, but the comment is more than 8 years old after all.

After some trial and error, this is what I ended up with:

DELIMITER //
DROP PROCEDURE IF EXISTS addColumn //
CREATE PROCEDURE addColumn()
BEGIN
  IF NOT EXISTS (
    SELECT *
    FROM information_schema.COLUMNS
    WHERE COLUMN_NAME = 'column_name'
      AND TABLE_NAME = 'table_name'
      AND TABLE_SCHEMA = DATABASE()
  ) THEN
    ALTER TABLE `table_name`
    ADD COLUMN `column_name` bigint(20) UNSIGNED NOT NULL DEFAULT 1;
  END IF;
END //
DELIMITER ;
CALL addColumn;
DROP PROCEDURE addColumn;

回复收藏 0 原文

~没有更多了~