比较运算符在MySQL查询中无效

Question

我有这个MySQL查询：

SELECT * FROM forex_pair_signals AS SENALES 
JOIN forex_pair_price AS PRECIO 
ON SENALES.forex_pair_price_id = PRECIO.forex_pair_price_id 
JOIN forex_pair AS PARES 
ON PRECIO.forex_pair_id = PARES.forex_pair_id
WHERE 
PRECIO.forex_pair_price_time >= '2019-02-01 00:00' AND
PRECIO.forex_pair_price_time <= '2019-02-07 01:00' AND 
TIME (PRECIO.forex_pair_price_time) BETWEEN '06:00' AND '13:00' AND 
PARES.forex_pair_id = 13 AND SENALES.forex_pair_signals_result = 1 AND
PRECIO.forex_pair_price_rsi >= '80.00' 
OR PRECIO.forex_pair_price_rsi <= '20.00' 
ORDER BY SENALES.forex_pair_signals_id DESC

除了添加以下筛选器外，此查询可以正常工作：

AND PRECIO.forex_pair_price_rsi >= '80.00' OR 
PRECIO.forex_pair_price_rsi <= '20.00'

问题是，最后一个过滤器忽略了所有其他过滤器，结果是获得符合最后一个过滤器的所有数据。

Answer 1

因为AND具有更高的优先级，所以会在OR之前评估AND。使用圆括号覆盖operator precendence

WHERE
    PRECIO.forex_pair_price_time >= '2019-02-01 00:00' AND
    PRECIO.forex_pair_price_time <= '2019-02-07 01:00' AND 
    TIME (PRECIO.forex_pair_price_time) BETWEEN '06:00' AND '13:00' AND 
    PARES.forex_pair_id = 13 AND SENALES.forex_pair_signals_result = 1 AND
    (
        PRECIO.forex_pair_price_rsi >= '80.00' 
        OR
        PRECIO.forex_pair_price_rsi <= '20.00'
    )

或者你可以重写你的条件，让它只有一个表达式：

    PRECIO.forex_pair_price_rsi not between '20.01' and '79.99'

Answer 2

运算符优先级当您在表达式中使用多个逻辑运算符时，MySQL将在AND运算符之后计算OR运算符。这称为运算符优先级。1

选择true或false和false；

结果

true OR false AND false -1 SELECT (true OR false) AND false；

结果

(true或false)和false -0

因此，您需要在这种情况下使用单行句

WHERE 
PRECIO.forex_pair_price_time >= '2019-02-01 00:00' AND
PRECIO.forex_pair_price_time <= '2019-02-07 01:00' AND 
TIME (PRECIO.forex_pair_price_time) BETWEEN '06:00' AND '13:00' AND 
PARES.forex_pair_id = 13 AND SENALES.forex_pair_signals_result = 1 AND
(
    PRECIO.forex_pair_price_rsi >= '80.00' OR
    PRECIO.forex_pair_price_rsi <= '20.00' 
)

Answer 3

在使用OR函数时，您应该使用括号，因为它对应于一个或两个WHERE子句，通过在括号中进行区分来定义它。

SELECT * FROM forex_pair_signals AS SENALES 
JOIN forex_pair_price AS PRECIO 
ON SENALES.forex_pair_price_id = PRECIO.forex_pair_price_id 
JOIN forex_pair AS PARES 
ON PRECIO.forex_pair_id = PARES.forex_pair_id
WHERE 
PRECIO.forex_pair_price_time >= '2019-02-01 00:00' AND
PRECIO.forex_pair_price_time <= '2019-02-07 01:00' AND 
TIME (PRECIO.forex_pair_price_time) BETWEEN '06:00' AND '13:00' AND 
PARES.forex_pair_id = 13 AND SENALES.forex_pair_signals_result = 1 AND
(PRECIO.forex_pair_price_rsi >= '80.00' 
OR PRECIO.forex_pair_price_rsi <= '20.00') 
ORDER BY SENALES.forex_pair_signals_id DESC;