短路逻辑评估算子 [英] Short-circuit logic evaluation operators

问题描述

我可以在MySQL 5.5的WHERE子句中使用任何短路逻辑运算符(特别是短路AND和短路OR)吗?如果没有，有什么替代方案?

Are there any short-circuit logic operators (specifically short-circuit AND and short-circuit OR) that I can use in a WHERE clause in MySQL 5.5? If there isn't, what are the alternatives?

在这个小提琴中可以找到关于我的问题的抽象观点以及对我为什么需要它的解释:

An abstract view at my problem along with an explanation as to why I need this can be found at this fiddle:

http://sqlfiddle.com/#!2/97fd1/3

实际上，我们正在研究数百个国家/地区的数千个城市中数百万家书店中的数百万本书，这就是为什么我们不能接受我们发送的每个查询都收到不必要信息的开销，并且迫切需要找到一种方法的原因在所有行都满足当前条件之后立即停止评估，然后继续进行下一个OR.

In reality we are looking at millions of books in millions of bookstores in thousands of cities in hundreds of countries, which is why we cannot accept the overhead of receiving the unneeded information with every query we dispatch and seriously need to find a way to make the evaluation stop as soon as we have all rows that satisfy the current condition, before moving on to the next OR.

如果您需要更多信息，请告诉我.预先感谢.

Let me know if you need more information. Thanks in advance.

根据要求，这是小提琴中使用的架构:

As requested, here is the schema used in the fiddle:

CREATE TABLE quantitycache (
  id INT AUTO_INCREMENT,
  quantity INT,
  book_id INT NOT NULL,
  bookstore_id INT NULL,
  city_id INT NULL,
  country_id INT NULL,
  PRIMARY KEY (id)
);

以及一些示例数据:

INSERT INTO quantitycache 
     (quantity, book_id, bookstore_id, city_id, country_id)
VALUES
     (5,        1,       1,            NULL,    NULL),
     (100,      2,       1,            NULL,    NULL),
     (7,        1,       2,            NULL,    NULL),
     (12,       1,       NULL,         1,       NULL),
     (12,       1,       NULL,         NULL,    1),
     (100,      2,       NULL,         1,       NULL),
     (100,      2,       NULL,         NULL,    1),
     (200,      3,       NULL,         1,       NULL),
     (250,      3,       NULL,         NULL,    1);

推荐答案

请记住，查询不是强制执行的.您编写的查询可能在多个线程上运行，因此where子句中的短路运算符不会仅导致一个结果.

Keep in mind that a query does not execute imperatively. The query you wrote may run on multiple threads, and therefore a short-circuit operator in the where clause would not result in only one result.

相反，使用LIMIT子句仅返回第一行.

Instead, use the LIMIT clause to only return the first row.

SELECT * FROM quantitycache
WHERE bookstore_id = 1 OR city_id = 1 OR country_id = 1
ORDER BY bookstore_id IS NULL ASC,
         city_id IS NULL ASC,
         country_id IS NULL ASC
LIMIT 1;

---

要获得结果集中所有书籍的最佳匹配，请将结果保存到临时表中，找到最佳结果，然后返回有趣的字段.

---

To get the best match for all books in a result set, save the results to a temp table, find the best result, then return interesting fields.

CREATE TEMPORARY TABLE results (id int, book_id int, match_rank int);

INSERT INTO results (id, book_id, match_rank)
SELECT id, book_id, 
    -- this assumes that lower numbers are better
    CASE WHEN Bookstore_ID is not null then 1 
         WHEN City_ID is not null then 2 
         ELSE 3 END as match_rank
FROM quantitycache
WHERE bookstore_id = 1 OR city_id = 1 OR country_id = 1;

Select * 
from (
    select book_id, MIN(match_rank) as best_rank 
    from results 
    group by book_id
) as r
inner join results as rid 
    on r.book_id = rid.book_id 
    and rid.match_rank = r.best_rank
inner join quantitycache as q on q.id = rid.id;

DROP TABLE results;

这篇关于短路逻辑评估算子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！