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;