MySQL数据库安全之防止撰改的方法
发布网友
发布时间：2022-04-23 21:21
共1个回答
热心网友
时间：2022-04-09 00:30
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;
