软件开发培训班 >> 编程开发 >> MySQL


MySQL数据库安全之防止撰改的方法

　　本文标签：MySQL数据库,防止撰改,方法

MySQL数据库可以通过触发器，使之无法修改某些字段的数据，同时又不会影响修改其他字段 。

DROP TRIGGER IF EXISTS `members`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=;
DELIMITER //
CREATE TRIGGER `members` BEFORE UPDATE ON `members` FOR EACH ROW BEGIN
 set new.name  = old.name;
 set new.cellphone = old.cellphone;
 set new.email  = old.email;
  set new.password = old.password;
END//
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;

再举一个例子：

CREATE TABLE `account` (
 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `user` VARCHAR(50) NOT NULL DEFAULT 0,
 `cash` FLOAT NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`)
)
COLLATE=utf8_general_ci
ENGINE=InnoDB;

每一次数据变化新增一条数据

INSERT INTO `test`.`account` (`user`, `cash`) VALUES (neo, -10);
INSERT INTO `test`.`account` (`user`, `cash`) VALUES (neo, -5);
INSERT INTO `test`.`account` (`user`, `cash`) VALUES (neo, 30);
INSERT INTO `test`.`account` (`user`, `cash`) VALUES (neo, -20);

保护用户的余额不被修改

DROP TRIGGER IF EXISTS `account`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=;
DELIMITER //
CREATE TRIGGER `account` BEFORE UPDATE ON `account` FOR EACH ROW BEGIN
 set new.cash  = old.cash;
END//
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;