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

2021-11-27 00:00:00 mysql alter-table

有没有办法在 ALTER TABLE ADD coumn_name 语句运行之前(或运行时)检查 mySQL 数据库中是否存在列?类似于 IF 列不存在 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.

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

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;

相关文章