MySQL <> 不工作 - 返回那些结果

Question

<> (NOT) 运算符有问题。

我正在尝试检索 habbo_name 不等于给定的两个的行，但它仍然返回这些行。有人对我如何解决这个问题有任何建议吗？

代码:

<?php
        $query = "SELECT `user_id`, `rank`, `habbo_name`, `rating`, `branch` FROM `personnel` WHERE status='active' AND `rating` LIKE '%(SDP%'  OR `rating` LIKE '%/SDP)%' AND (`habbo_name` <> '-Jose,' AND `habbo_name` <> 'tharuka$')  AND status='active' ORDER BY `habbo_name`";
        $result = $con->prepare($query);
        $result->execute();
                while($row = $result->fetch()) {
                    echo "<b>SDP:</b>&nbsp" . htmlspecialchars($row['habbo_name']) . "<br>";
                }
?>

结果:

SDP: -Jose,
SDP: -Wyatt-
SDP: CPT.Black
SDP: Dr.Jacobson
SDP: Malwarebyte 
SDP: NShadow
SDP: tharuka$ (Dudestetson) 

Answer 1

AND 具有更高的 precedence而不是 OR，因此当混合使用这两个运算符时，通常需要包含括号以强制执行所需的逻辑。就目前而言，您的过滤器表达式被评估为:

WHERE (
            status = 'active'
        AND rating LIKE '%(SDP%'
      ) OR (
            rating LIKE '%/SDP)%'
        AND habbo_name <> '-Jose,'
        AND habbo_name <> 'tharuka$'
        AND status = 'active'
      )

我怀疑你想要的是:

WHERE status = 'active'
  AND (rating LIKE '%(SDP%' OR rating LIKE '%/SDP)%')
  AND habbo_name <> '-Jose,' AND habbo_name <> 'tharuka$'

在这种情况下，可以使用 MySQL 的 IN()运算符以简化 habbo_name 上的条件:

WHERE status = 'active'
  AND (rating LIKE '%(SDP%' OR rating LIKE '%/SDP)%')
  AND habbo_name NOT IN ('-Jose,', 'tharuka$')