blocks|key|1727448|text|在MySQL中没有对此的语言级别支持。这里有一个在5.0%2B中涉及MySQL+information_schema元数据的变通方法，但它不会在4.0.18中解决您的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1727449|drop+procedure+if+exists+schema_change;

delimiter+';;'
create+procedure+schema_change()+begin

++++/*+delete+columns+if+they+exist+*/
++++if+exists+(select+*+from+information_schema.columns+where+table_schema+=+schema()+and+table_name+=+'table1'+and+column_name+=+'column1')+then
++++++++alter+table+table1+drop+column+`column1`;
++++end+if;
++++if+exists+(select+*+from+information_schema.columns+where+table_schema+=+schema()+and+table_name+=+'table1'+and+column_name+=+'column2')+then
++++++++alter+table+table1+drop+column+`column2`;
++++end+if;

++++/*+add+columns+*/
++++alter+table+table1+add+column+`column1`+varchar(255)+NULL;
++++alter+table+table1+add+column+`column2`+varchar(255)+NULL;

end;;

delimiter+';'
call+schema_change();

drop+procedure+if+exists+schema_change;|code-block|syntax|javascript|1727450|我在blog+post中写了一些更详细的信息。|offset|length|1727451|entityMap|0|LINK|mutability|MUTABLE|url|http://bitkickers.blogspot.com/2010/01/mysql-drop-column-if-exists.html^0|0|0|2|9|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

There is no language level support for this in MySQL. Here is a work-around involving MySQL information_schema meta-data in 5.0+, but it won't address your issue in 4.0.18.

<pre><code>drop procedure if exists schema_change;

delimiter ';;'
create procedure schema_change() begin

 /* delete columns if they exist */
 if exists (select * from information_schema.columns where table_schema = schema() and table_name = 'table1' and column_name = 'column1') then
 alter table table1 drop column `column1`;
 end if;
 if exists (select * from information_schema.columns where table_schema = schema() and table_name = 'table1' and column_name = 'column2') then
 alter table table1 drop column `column2`;
 end if;

 /* add columns */
 alter table table1 add column `column1` varchar(255) NULL;
 alter table table1 add column `column2` varchar(255) NULL;

end;;

delimiter ';'
call schema_change();

drop procedure if exists schema_change;
</code></pre>

I wrote some more detailed information in a <a href="http://bitkickers.blogspot.com/2010/01/mysql-drop-column-if-exists.html" rel="noreferrer">blog post</a>.

blocks|key|1727509|text|Chase+Seibert的答案是有效的，但我要补充的是，如果你有几个模式，你想要这样改变SELECT：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1727510|select+*+from+information_schema.columns+where+table_schema+in+(select+schema())+and+table_name=...|code-block|syntax|javascript|1727511|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Chase Seibert's answer works, but I'd add that if you have several schemata you want to alter the SELECT thus:

<pre><code>select * from information_schema.columns where table_schema in (select schema()) and table_name=...
</code></pre>

blocks|key|1512438|text|我知道这是一个很老的线程，但是有一种简单的方法可以在不使用存储过程的情况下处理这个需求。这可能会对某些人有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1512439|set+@exist_Check+:=+(
++++select+count(*)+from+information_schema.columns+
++++where+TABLE_NAME='YOUR_TABLE'+
++++and+COLUMN_NAME='YOUR_COLUMN'+
++++and+TABLE_SCHEMA=database()
)+;
set+@sqlstmt+:=+if(@exist_Check>0,'alter+table+YOUR_TABLE+drop+column+YOUR_COLUMN',+'select+''''')+;
prepare+stmt+from+@sqlstmt+;
execute+stmt+;|code-block|syntax|javascript|1512440|希望这对某人有帮助，就像它对我一样(在经历了许多试验和错误之后)。|1512441|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I know this is an old thread, but there is a simple way to handle this requirement without using stored procedures. This may help someone.

<pre><code>set @exist_Check := (
 select count(*) from information_schema.columns 
 where TABLE_NAME='YOUR_TABLE' 
 and COLUMN_NAME='YOUR_COLUMN' 
 and TABLE_SCHEMA=database()
) ;
set @sqlstmt := if(@exist_Check&gt;0,'alter table YOUR_TABLE drop column YOUR_COLUMN', 'select ''''') ;
prepare stmt from @sqlstmt ;
execute stmt ;
</code></pre>

Hope this helps someone, as it did me (after a lot of trial and error).

blocks|key|1512497|text|我刚刚构建了一个可重用的过程，它可以帮助使DROP+COLUMN幂等：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1512498|--+column_exists:

DROP+FUNCTION+IF+EXISTS+column_exists;

DELIMITER+$$
CREATE+FUNCTION+column_exists(
++tname+VARCHAR(64),
++cname+VARCHAR(64)
)
++RETURNS+BOOLEAN
++READS+SQL+DATA
++BEGIN
++++RETURN+0+<+(SELECT+COUNT(*)
++++++++++++++++FROM+`INFORMATION_SCHEMA`.`COLUMNS`
++++++++++++++++WHERE+`TABLE_SCHEMA`+=+SCHEMA()
++++++++++++++++++++++AND+`TABLE_NAME`+=+tname
++++++++++++++++++++++AND+`COLUMN_NAME`+=+cname);
++END+$$
DELIMITER+;

--+drop_column_if_exists:

DROP+PROCEDURE+IF+EXISTS+drop_column_if_exists;

DELIMITER+$$
CREATE+PROCEDURE+drop_column_if_exists(
++tname+VARCHAR(64),
++cname+VARCHAR(64)
)
++BEGIN
++++IF+column_exists(tname,+cname)
++++THEN
++++++SET+@drop_column_if_exists+=+CONCAT('ALTER+TABLE+`',+tname,+'`+DROP+COLUMN+`',+cname,+'`');
++++++PREPARE+drop_query+FROM+@drop_column_if_exists;
++++++EXECUTE+drop_query;
++++END+IF;
++END+$$
DELIMITER+;|code-block|syntax|javascript|1512499|用法：|1512500|CALL+drop_column_if_exists('my_table',+'my_column');|1512501|示例：|1512502|SELECT+column_exists('my_table',+'my_column');+++++++--+1
CALL+drop_column_if_exists('my_table',+'my_column');+--+success
SELECT+column_exists('my_table',+'my_column');+++++++--+0
CALL+drop_column_if_exists('my_table',+'my_column');+--+success
SELECT+column_exists('my_table',+'my_column');+++++++--+0|1512503|entityMap^0|L|B|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|S|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|T|$]]

I just built a reusable procedure that can help making <code>DROP COLUMN</code> idempotent:

<pre class="lang-sql prettyprint-override"><code>-- column_exists:

DROP FUNCTION IF EXISTS column_exists;

DELIMITER $$
CREATE FUNCTION column_exists(
 tname VARCHAR(64),
 cname VARCHAR(64)
)
 RETURNS BOOLEAN
 READS SQL DATA
 BEGIN
 RETURN 0 &lt; (SELECT COUNT(*)
 FROM `INFORMATION_SCHEMA`.`COLUMNS`
 WHERE `TABLE_SCHEMA` = SCHEMA()
 AND `TABLE_NAME` = tname
 AND `COLUMN_NAME` = cname);
 END $$
DELIMITER ;

-- drop_column_if_exists:

DROP PROCEDURE IF EXISTS drop_column_if_exists;

DELIMITER $$
CREATE PROCEDURE drop_column_if_exists(
 tname VARCHAR(64),
 cname VARCHAR(64)
)
 BEGIN
 IF column_exists(tname, cname)
 THEN
 SET @drop_column_if_exists = CONCAT('ALTER TABLE `', tname, '` DROP COLUMN `', cname, '`');
 PREPARE drop_query FROM @drop_column_if_exists;
 EXECUTE drop_query;
 END IF;
 END $$
DELIMITER ;
</code></pre>

Usage:

<pre class="lang-sql prettyprint-override"><code>CALL drop_column_if_exists('my_table', 'my_column');
</code></pre>

Example:

<pre class="lang-sql prettyprint-override"><code>SELECT column_exists('my_table', 'my_column'); -- 1
CALL drop_column_if_exists('my_table', 'my_column'); -- success
SELECT column_exists('my_table', 'my_column'); -- 0
CALL drop_column_if_exists('my_table', 'my_column'); -- success
SELECT column_exists('my_table', 'my_column'); -- 0
</code></pre>

blocks|key|1727638|text|您可以使用此脚本，使用您的列、模式和表名|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1727639|+IF+EXISTS+(SELECT+*
+++++++++++++++++++++++++FROM+INFORMATION_SCHEMA.COLUMNS
+++++++++++++++++++++++++WHERE+TABLE_NAME+=+'TableName'+AND+COLUMN_NAME+=+'ColumnName'+
+++++++++++++++++++++++++++++++++++++++++++++AND+TABLE_SCHEMA+=+SchemaName)
++++BEGIN
+++++++ALTER+TABLE+TableName+DROP+COLUMN+ColumnName;
++++END;|code-block|syntax|javascript|1727640|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

<h2>You can use this script, use your column, schema and table name</h2>
<pre><code> IF EXISTS (SELECT *
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName' 
 AND TABLE_SCHEMA = SchemaName)
 BEGIN
 ALTER TABLE TableName DROP COLUMN ColumnName;
 END;
</code></pre>

How can ALTER be used to drop a column in a MySQL table if that column exists? 

I know I can use <code>ALTER TABLE my_table DROP COLUMN my_column</code>, but that will throw an error if <code>my_column</code> does not exist. Is there alternative syntax for dropping the column conditionally?

I'm using MySQL version 4.0.18.

Using ALTER to drop a column if it exists in MySQL

MySQL

如果MySQL表中的列存在，如何使用ALTER删除该列？我知道我可以使用ALTER TABLE my_table DROP COLUMN my_column，但是如果my_column不存在，就会抛出一个错误。有没有其他语法可以有条件地删除该列？我使用的是MySQL版本4.0.18。

问如果列存在于MySQL中，则使用ALTER删除该列
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如果列存在于MySQL中，则使用ALTER删除该列EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如果列存在于MySQL中，则使用ALTER删除该列
EN