MySQL白名单查询

我的查询是这样的，但如果每个 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 blLEFT JOIN whitelist w ON bl.id = w.book_list_id AND w.user_id <> 1WHERE w.book_list_id IS NULLNote: I always have the user_id from session that why it is hard coded. 解决方案 SELECT *FROM book_listLEFT JOIN whitelist ON (whitelist.book_list_id = book_list.id)WHERE (COALESCE(whitelist.user_id, 1) = 1)Edited to include explanationThe 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白名单查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！