MySQL白名单查询 [英] MySQL whitelist query

问题描述

我正在制作一个自定义白名单，其中我的主表是 book_list，包含 id - 主键和其他不重要的列.另一个表是白名单，其中包含来自 book_list 的外键和另一个键是 user_id.

book_list 表

--------------|身份证 |---------------|10 ||11 ||12 ||13 |---------------

白名单表

---------------------------|book_list_id |用户 ID |-----------------------------|10 |1 ||10 |2 |-----------------------------

所以当 user_id 1 正在浏览时，他/她应该看到所有的书 (10, 11, 12, 13)，但如果 id 为 5 的用户看起来他应该只看到 book_list ids (11, 12, 13)

我的查询是这样的，但如果每个 book_id 只有一个 id 可用，它就可以工作.

SELECT * FROM book_list bl左加入白名单 w ON bl.id = w.book_list_id AND w.user_id <>1WHERE w.book_list_id 为空

注意:我总是从会话中获得 user_id，这就是为什么它是硬编码的.

解决方案

SELECT *FROM book_list左加入白名单开启(whitelist.book_list_id = book_list.id)哪里 (COALESCE(whitelist.user_id, 1) = 1)

编辑以包含解释

条件语句同时执行两件事.如果whitelist 中没有该书的记录，则COALESCE 函数将返回用户的id.如果whitelist 中有该书的记录，则条件将阻止任何匹配发生，除非用户的 id 附加到该书.

I'm making a custom white list, where my main table is book_list, containing id - primary key, and other columns which are unimportant. Another table is whitelist which contains a foreign key from book_list and another key which is user_id.

book_list table

--------------
| id          |
---------------
| 10          |
| 11          |
| 12          |
| 13          |
---------------

whitelist table

---------------------------
| book_list_id   | user_id |
----------------------------
| 10             | 1       |
| 10             | 2       |
----------------------------

So when user_id 1 is browsing, he/she should see all the books (10, 11, 12, 13), but if user with id 5 looks he should see just book_list ids (11, 12, 13)

My query was something like this, but it works if just one id per book_id is available.

SELECT * FROM book_list bl
LEFT JOIN whitelist w ON bl.id = w.book_list_id AND w.user_id <> 1
WHERE w.book_list_id IS NULL

Note: I always have the user_id from session that why it is hard coded.

解决方案

SELECT *
FROM book_list
LEFT JOIN whitelist
  ON (whitelist.book_list_id = book_list.id)
WHERE (COALESCE(whitelist.user_id, 1) = 1)

Edited to include explanation

The conditional performs two things at once. If there are no records in whitelist for the book, then the COALESCE function will return the user's id. If there are records in whitelist for the book, then the conditional will prevent any matches occurring unless the user's id is attached to that book.

这篇关于MySQL白名单查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！