MYSQL 安全添加/移除column 脚本

1. 如果不存在，就添加;

 用存储过程解决，定义成模板，可以通用；
 应用场景：必须拥有创建存储过程的权限

DELIMITER $$

USE `ebt_shop_test`$$
DROP PROCEDURE IF EXISTS `addFieldIfNotExists`$$
DROP FUNCTION IF EXISTS `isFieldExisting`$$

CREATE FUNCTION isFieldExisting (
 table_name_IN VARCHAR(100),
 field_name_IN VARCHAR(100)
 )
RETURNS INT
RETURN (
    SELECT COUNT(COLUMN_NAME)
    FROM INFORMATION_SCHEMA.columns
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = table_name_IN
    AND COLUMN_NAME = field_name_IN
)
$$

CREATE PROCEDURE addFieldIfNotExists (
 IN table_name_IN VARCHAR(100),
 IN field_name_IN VARCHAR(100),
 IN field_definition_IN VARCHAR(100)
 )
BEGIN
    SET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);    
    IF (@isFieldThere = 0) THEN  
        SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);
        SET @ddl = CONCAT(@ddl, '', 'ADD COLUMN') ;
        SET @ddl = CONCAT(@ddl, '', field_name_IN);
        SET @ddl = CONCAT(@ddl, '', field_definition_IN);
        PREPARE stmt FROM @ddl;
       EXECUTE stmt;
       DEALLOCATE PREPARE stmt;
    END IF;
END$$

DELIMITER ;

【说明】使用了 “DELIMITER $$ … … DELIMITER ;” 分块语法，在此定义区域内的语句作为整体一并提交给服务器
调用添加列，例如：

-- call procedure add field if not exists

CALL addFieldIfNotExists ('dic_data', 'parentId', 'VARCHAR(50) NULL COMMENT \'父ID\'');

-- clean work
DROP PROCEDURE IF EXISTS `addFieldIfNotExists`;
DROP FUNCTION IF EXISTS `isFieldExisting`;

2.另一种方式： 如果不存在，就添加

如果没有权限创建函数和存储过程时，一种更直接的方式，直接写可执行的sql语句编译执行
应用场景：有受权限限制，每次都直接编写相应的SQL语句即可

SET @exeResult = (SELECT IF( (SELECT COUNT(*)
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = DATABASE()
   AND table_name = 'dic_data'
   AND column_name = 'isLeaf1'
   ) > 0,
   'SELECT 1 AS RESULT',
   'alter table dic_data add column isLeaf1 char(1) COMMENT \'是否为叶子节点\' NULL after parentId'
     )
);
PREPARE stmt FROM @exeResult;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

【语法解析】：
@exeResult： 临时变量定义的方式
IF(boolean表达式, param1,param2)：相当于程序中的三元表达式
PREPARE :预编译SQL语句
EXECUTE :执行SQL语句
DEALLOCATE ：清除预编译的语句
eg:

PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;
DEALLOCATE PREPARE stmt1;

3.安全地删除列，也可以先进行判断而后进行操作

SET @exeResult = (SELECT IF( (SELECT COUNT(*)
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = DATABASE()
   AND table_name = 'dic_data'
   AND column_name = 'isLeaf1'
   ) > 0,
   'alter table dic_data drop column isLeaf1',
   'SELECT 0 AS RESULT'
     )
);
PREPARE stmt FROM @exeResult;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

---