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

Question

提问by julio

Is there a way to check if a column exists in a mySQL DB prior to (or as) the ALTER TABLE ADD coumn_namestatement runs? Sort of an IF column DOES NOT EXIST ALTER TABLEthing.

有没有办法在ALTER TABLE ADD coumn_name语句运行之前（或运行时）检查 mySQL DB 中是否存在列？某种IF column DOES NOT EXIST ALTER TABLE东西。

I've tried ALTER IGNORE TABLE my_table ADD my_columnbut this still throws the error if the column I'm adding already exists.

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

EDIT: use case is to upgrade a table in an already installed web app-- so to keep things simple, I want to make sure the columns I need exist, and if they don't, add them using ALTER TABLE

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

Answer 1

回答by gerardw

Since mysql control statements (e.g. "IF") only work in stored procedures, a temporary one can be created and executed:

由于 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

回答by Nonym

Do you think you can try this?:

你觉得你可以试试这个吗？：

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;

It's no one-liner, but can you at least see if it will work for you? At least while waiting for a better solution..

它不是单线，但你能不能至少看看它是否适合你？至少在等待更好的解决方案时..

Answer 3

回答by Nonym

Utility functions and procedures

实用功能和程序

First, I have a set of utility functions and procedures that I use to do things like drop foreign keys, normal keys and columns. I just leave them in the database so I can use them as needed.

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

Here they are.

他们来了。

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 ;

Dropping constraints and columns using the utilities above

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

With those in place, it is pretty easy to use them to check columns and constraints for existence:

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

-- 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 4

回答by Duvan Barros

Make a count sentence with the example below by John Watson.

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

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

Save that result in an integer and then make it a condition to apply the ADD COLUMNsentence.

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

Answer 5

回答by Wahib Ul Haq

Although its quite an old post but still i feel good about sharing my solution to this issue. If column doesn't exist then an exception would occur definitely and then i am creating the column in table.

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

I just used the code below:

我只是使用了下面的代码：

 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 6

回答by OMA

You can create a procedure with a CONTINUE handler in case the column exists (please note this code doesn't work in PHPMyAdmin):

如果列存在，您可以使用 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;

This code should not raise any error in case the column already exists. It will just do nothing and carry on executing the rest of the SQL.

如果该列已存在，此代码不应引发任何错误。它什么都不做，继续执行 SQL 的其余部分。

Answer 7

回答by Richards RIc

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 8

回答by shamcs

This syntax work for me :

这种语法对我有用：

SHOW COLUMNS FROM < tablename > LIKE '< columnName >'

More in this post : https://mzulkamal.com/blog/mysql-5-7-check-if-column-exist?viewmode=0

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

Answer 9

回答by John Watson

You can test if a column exists with:

您可以测试列是否存在：

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

...

Just fill in your column name, table name, and database name.

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

Answer 10

回答by HymanSparrow

As per MYSQL Community:

根据 MYSQL 社区：

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

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

So a working Code is: ALTER IGNORE TABLE CLIENTS ADD CLIENT_NOTES TEXT DEFAULT NULL;

所以一个工作代码是： ALTER IGNORE TABLE CLIENTS ADD CLIENT_NOTES TEXT DEFAULT NULL;

Data posted here: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

数据张贴在这里：http: //dev.mysql.com/doc/refman/5.1/en/alter-table.html

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

提问by julio

回答by gerardw

回答by Nonym

回答by Nonym

Utility functions and procedures

实用功能和程序

Dropping constraints and columns using the utilities above

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

回答by Duvan Barros

回答by Wahib Ul Haq

回答by OMA

回答by Richards RIc

回答by shamcs

回答by John Watson

回答by HymanSparrow

相关推荐

最近更新

标签

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

提问by julio

回答by gerardw

回答by Nonym

回答by Nonym

Utility functions and procedures

实用功能和程序

Dropping constraints and columns using the utilities above

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

回答by Duvan Barros

回答by Wahib Ul Haq

回答by OMA

回答by Richards RIc

回答by shamcs

回答by John Watson

回答by HymanSparrow

相关推荐

MySQL 从 SQL 数据库中选择特定的行和列

在 MySQL 中格式化日期以返回 MonthName、Year

MySQL 无法删除表，因为未知表（错误 1051）

如何更改 MySQL 中的自动增量计数器？

相关推荐

最近更新

标签