Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
758 views
in Technique[技术] by (71.8m points)

check if column exists before ALTER TABLE -- mysql

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.

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.

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...