检查列是否存在于ALTER TABLE之前-mysql [英] check if column exists before ALTER TABLE -- mysql
问题描述
是否可以在ALTER TABLE ADD coumn_name
语句运行之前(或之后)检查mySQL DB中是否存在列? IF column DOES NOT EXIST ALTER TABLE
之类的东西.
Is there a way to check if a column exists in a mySQL DB prior to (or as) the ALTER TABLE ADD coumn_name
statement runs? Sort of an IF column DOES NOT EXIST ALTER TABLE
thing.
我已经尝试过ALTER IGNORE TABLE my_table ADD my_column
,但是如果我要添加的列已经存在,这仍然会引发错误.
I've tried ALTER IGNORE TABLE my_table ADD my_column
but this still throws the error if the column I'm adding already exists.
用例是升级已经安装的Web应用程序中的表-为了使事情简单,我想确保需要的列存在,如果不存在,请使用ALTER TABLE
添加它们
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
推荐答案
由于mysql控制语句(例如"IF")仅在存储过程中起作用,因此可以创建并执行一个临时语句:
Since mysql control statements (e.g. "IF") only work in stored procedures, a temporary one can be created and executed:
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;
这篇关于检查列是否存在于ALTER TABLE之前-mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!