在 ALTER TABLE 之前检查列是否存在 -- mysql

Question

有没有办法在 ALTER TABLE ADD coumn_name 语句运行之前（或运行时）检查 mySQL 数据库中是否存在列？类似于 IF 列不存在 ALTER TABLE 的东西。

我已尝试 ALTER IGNORE TABLE my_table ADD my_column 但如果我要添加的列已存在，这仍然会引发错误。

编辑：用例是升级已安装的 Web 应用程序中的表 - 因此为了简单起见，我想确保我需要的列存在，如果不存在，请使用 ALTER TABLE< 添加它们/代码>

Answer 1

由于 mysql 控制语句（例如“IF”）仅在存储过程中起作用，因此可以创建并执行临时语句：

DROP PROCEDURE IF EXISTS add_version_to_actor;

DELIMITER $

CREATE DEFINER=CURRENT_USER PROCEDURE add_version_to_actor ( ) 
BEGIN
DECLARE colName TEXT;
SELECT column_name INTO colName
FROM information_schema.columns 
WHERE table_schema = 'connjur'
    AND table_name = 'actor'
AND column_name = 'version';

IF colName is null THEN 
    ALTER TABLE  actor ADD  version TINYINT NOT NULL DEFAULT  '1' COMMENT  'code version of actor when stored';
END IF; 
END$

DELIMITER ;

CALL add_version_to_actor;

DROP PROCEDURE add_version_to_actor;

Answer 2

实用函数和过程

首先，我有一组实用函数和过程，用于执行删除外键、普通键和列等操作。我只是将它们留在数据库中，以便我可以根据需要使用它们。

他们在这里。

delimiter $

create function column_exists(ptable text, pcolumn text)
  returns bool
  reads sql data
begin
  declare result bool;
  select
    count(*)
  into
    result
  from
    information_schema.columns
  where
    `table_schema` = 'my_database' and
    `table_name` = ptable and
    `column_name` = pcolumn;
  return result;
end $

create function constraint_exists(ptable text, pconstraint text)
  returns bool
  reads sql data
begin
  declare result bool;
  select
    count(*)
  into
    result
  from
    information_schema.table_constraints
  where
    `constraint_schema` = 'my_database' and
    `table_schema` = 'my_database' and
    `table_name` = ptable and
    `constraint_name` = pconstraint;
  return result;
end $

create procedure drop_fk_if_exists(ptable text, pconstraint text)
begin
  if constraint_exists(ptable, pconstraint) then
    set @stat = concat('alter table ', ptable, ' drop foreign key ', pconstraint);
    prepare pstat from @stat;
    execute pstat;
  end if;
end $

create procedure drop_key_if_exists(ptable text, pconstraint text)
begin
  if constraint_exists(ptable, pconstraint) then
    set @stat = concat('alter table ', ptable, ' drop key ', pconstraint);
    prepare pstat from @stat;
    execute pstat;
  end if;
end $

create procedure drop_column_if_exists(ptable text, pcolumn text)
begin
  if column_exists(ptable, pcolumn) then
    set @stat = concat('alter table ', ptable, ' drop column ', pcolumn);
    prepare pstat from @stat;
    execute pstat;
  end if;
end $

delimiter ;

使用上面的实用程序删除约束和列

有了这些实用程序，就可以很容易地使用它们来检查列和约束是否存在：

-- Drop service.component_id
call drop_fk_if_exists('service', 'fk_service_1');
call drop_key_if_exists('service', 'component_id');
call drop_column_if_exists('service', 'component_id');

-- Drop commit.component_id
call drop_fk_if_exists('commit', 'commit_ibfk_1');
call drop_key_if_exists('commit', 'commit_idx1');
call drop_column_if_exists('commit', 'component_id');

-- Drop component.application_id
call drop_fk_if_exists('component', 'fk_component_1');
call drop_key_if_exists('component', 'application_id');
call drop_column_if_exists('component', 'application_id');

Answer 3

你认为你可以尝试这个吗？：

SELECT IFNULL(column_name, '') INTO @colName
FROM information_schema.columns 
WHERE table_name = 'my_table'
AND column_name = 'my_column';

IF @colName = '' THEN 
    -- ALTER COMMAND GOES HERE --
END IF;

这不是一句空话，但你至少能看看它是否适合你？至少在等待更好的解决方案时..

Answer 4

用下面约翰·沃森 (John Watson) 的例子来造一个计数句子。

 SELECT count(*) FROM information_schema.COLUMNS
     WHERE COLUMN_NAME = '...'
     and TABLE_NAME = '...'
     and TABLE_SCHEMA = '...'

将该结果保存为整数，然后将其作为应用 ADD COLUMN 语句的条件。

Answer 5

您可以通过以下方式测试列是否存在：

IF EXISTS (
     SELECT * FROM information_schema.COLUMNS
     WHERE COLUMN_NAME = '...'
     and TABLE_NAME = '...'
     and TABLE_SCHEMA = '...')

...

只需填写列名称、表名称和数据库名称。

Answer 6

虽然这是一篇相当老的帖子，但我仍然对分享我对这个问题的解决方案感到高兴。如果列不存在，那么肯定会发生异常，然后我在表中创建列。

我刚刚使用了下面的代码：

 try
   {
         DATABASE_QUERY="SELECT gender from USER;";
         db.rawQuery(DATABASE_QUERY, null);
   }
   catch (Exception e)
   {
    e.printStackTrace();

        DATABASE_UPGRADE="alter table USER ADD COLUMN gender VARCHAR(10) DEFAULT 0;";
                db.execSQL(DATABASE_UPGRADE);
   } 

Answer 7

如果列存在，您可以使用 CONTINUE 处理程序创建一个过程（请注意，此代码在 PHPMyAdmin 中不起作用）：

DROP PROCEDURE IF EXISTS foo;
CREATE PROCEDURE foo() BEGIN
    DECLARE CONTINUE HANDLER FOR 1060 BEGIN END;
    ALTER TABLE `tableName` ADD `columnName` int(10) NULL AFTER `otherColumn`;
END;
CALL foo();
DROP PROCEDURE foo;

如果列已存在，此代码不应引发任何错误。它不会执行任何操作并继续执行 SQL 的其余部分。

Answer 8

DELIMITER $

DROP PROCEDURE IF EXISTS `addcol` $
CREATE DEFINER=`admin`@`localhost` PROCEDURE `addcol`(tbn varchar(45), cn varchar(45), ct varchar(45))
BEGIN
#tbn: table name, cn: column name, ct: column type
DECLARE CONTINUE HANDLER FOR 1060 BEGIN END;
set cn = REPLACE(cn, ' ','_');
set @a = '';
set @a = CONCAT("ALTER TABLE `", tbn ,"` ADD column `", cn ,"` ", ct);
PREPARE stmt FROM @a;
EXECUTE stmt;

END $

DELIMITER ;

Answer 9

这个语法对我有用：

SHOW COLUMNS FROM <表名>喜欢'<列名>'

更多内容请参见这篇文章：
https://mzulkamal.com/blog/ mysql-5-7-check-if-column-exist?viewmode=0

Answer 10

根据 MYSQL 社区：

IGNORE 是标准 SQL 的 MySQL 扩展。如果新表中的唯一键存在重复项或者启用严格模式时出现警告，它控制 ALTER TABLE 的工作方式。如果未指定 IGNORE，则在出现重复键错误时，复制将中止并回滚。如果指定了 IGNORE，则仅使用唯一键上有重复项的行中的一行。其他冲突的行将被删除。不正确的值将被截断为最接近匹配的可接受值。

所以一个有效的代码是：
ALTER IGNORE TABLE CLIENTS ADD CLIENT_NOTES TEXT DEFAULT NULL;

此处发布的数据：
http://dev.mysql.com/doc/refman/5.1 /en/alter-table.html