时间戳

首页 > 解决方案 > 如何防止同一用户在 MySQL 中更新自己的记录？

mysql - 如何防止同一用户在 MySQL 中更新自己的记录？

问题描述

细节：

我有 3 张桌子：

create table request (
    id int not null primary key auto_increment,
    date_requested timestamp,
    user_id int,
    filename varchar(255),
    status enum('New','Verified','Rejected') not null default 'New',
    foreign key(user_id) references users(id)
);

create table users (
    id int not null primary key auto_increment,
    username varchar(50) not null,
    password varchar(255) not null
);

create table verify (
    id int not null primary key auto_increment,
    user_id int,
    request_id int,
    created timestamp,
    status enum('Verified','Rejected') not null,
    foreign key(request_id) references request(id)
);

这是请求表的示例数据：（抱歉，不确定如何提供带有以下标题的示例）。

id|    date_requested|   user_id|              filename|                status|
1 |  2020-04-06 13:33:51    3     C:\Users\LV98\Desktop\123 321.jpg    New

这是用户：

id   username   password
1      test1      123
2      test2      123@#
3      test1      123321

接下来，标记此文档。我有这个查询将记录添加到verify表中。您可以查看参考资料user_id和request_id

insert into verify (user_id, request_id, created, status, comments) values (3,1,now(), 'Rejected', 'Tester');

问题：

但是上面的查询不应该被允许，因为用户正在标记他们自己的记录。如何防止这种情况？我怎样才能以不同的方式查询它？

标签： mysql

解决方案

您可以通过更改主键（或使用不同的唯一键）来在数据库级别上强制执行它，以包括用户 ID 以及CHECK验证表中比较验证者用户 ID 和请求用户 ID 的约束。

首先，您创建这样的request表：

CREATE TABLE request (
    id int NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    dummy VARCHAR(100), -- just for testing
    PRIMARY KEY (id, user_id), -- two column primary key
    FOREIGN KEY (user_id) REFERENCES users(id)
);

现在verify表必须使用两列主键作为外键，否则无法引用。因为请求用户 ID 在verify表内，您可以添加一个CHECK强制您无法验证自己的内容：

CREATE TABLE verify (
    id int NOT NULL AUTO_INCREMENT,
    verifier_user_id INT NOT NULL,
    request_id INT NOT NULL,
    request_user_id INT NOT NULL,
    dummy VARCHAR(100), -- again, just for testing
    PRIMARY KEY (id),
    FOREIGN KEY (verifier_user_id) REFERENCES users(id),
    FOREIGN KEY (request_id, request_user_id) REFERENCES request(id, user_id),
    CHECK (verifier_user_id != request_user_id)
);

由于verifier_user_idandrequest_user_id不能相同，并且您必须包含request表中的整个外键，因此无法再验证自己：

mysql> SELECT * FROM users;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | first    | user     |
|  2 | second   | user     |
+----+----------+----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM request;
+----+---------+-------+
| id | user_id | dummy |
+----+---------+-------+
|  1 |       1 | test  |
+----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO verify (verifier_user_id, request_id, request_user_id, dummy) VALUES (2, 1, 1, 'test a');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO verify (verifier_user_id, request_id, request_user_id, dummy) VALUES (1, 1, 1, 'test a');
ERROR 3819 (HY000): Check constraint 'verify_chk_1' is violated.

推荐阅读