测试 SQL 查询的真假 [英] Testing a SQL Query for True or False

问题描述

$sql = "SELECT # FROM users WHERE onduty = 1 AND loc_id = '{$site}';"; 
$result = mysql_query($sql);

我只是想测试一下这是对还是错.如果它返回 0 行，我希望下一行是这样的:

I simply want to test if this is true or false. If it returns 0 rows, I want next line to be something like:

if (!$result) { //do this; }

然而，在我的测试中，当我知道它应该是真的时，我得到了假.这里是合理的逻辑吗?

However, in my test, I am getting false when I know it should be true. Is this sound logic here?

(注意，是的，我知道我应该使用 mysqli_query，这不是我在这里问的)**

(note, yes I know I should be using mysqli_query, that is not what I am asking here)**

答案:这是我使用的:

$login_state = false;
if(mysql_num_rows(mysql_query("SELECT 1 FROM users WHERE onduty = 1 AND loc_id = '{$site}';"))) {
    $login_state = true;
}

推荐答案

Use EXISTS:

"SELECT EXISTS (SELECT 1 FROM users WHERE onduty = 1 AND loc_id = '{$site}');"

如果没有找到符合条件的行，您的原始查询将返回无行".这个每次返回 TRUE (1) 或 FALSE (0).

Your original query would return "no row" if no row is found that matches the criteria. This one returns TRUE (1) or FALSE (0) every time.

更多关于EXISTS 在手册中.

如果可以有多行符合条件，而您只关心是否至少存在一行，EXISTS 的性能优于普通查询.它可以在找到第一行后立即停止并且只返回 0 或 1.

In cases where there can be multiple rows matching the criteria and you are only interested whether at least one rows exists, performance of EXISTS is superior to a plain query. It can stop as soon as the first row is found and only returns 0 or 1.

->sqlfiddle 演示.

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