MYSQL 安全添加/移除column 脚本

最新推荐文章于 2024-03-13 11:29:57 发布

原创最新推荐文章于 2024-03-13 11:29:57 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #安全 #存储 #脚本

数据库专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一种通过存储过程及动态SQL实现数据库表字段的安全添加与删除方法。具体包括利用存储过程判断字段是否存在并据此执行添加操作，以及通过判断来决定是否执行删除操作。适用于有权限限制的场景。

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;