php - SQL 能否返回有关查询中评估的不同条件的信息？或者我应该分成多个查询？

Question

我正在编写代码，在最终决定要做什么之前，我使用 SQL 来测试几个不同的条件。我试图决定是编写单个多部分 MySQL 查询，还是为每个条件编写一个查询，并由 PHP 处理组合它们的逻辑。我的问题:

1. 是否有任何好的做法或陷阱可以引导我朝任一方向发展？
2. 如果我在单个 SQL 查询中实现所有逻辑，我可以让它返回有关每个条件结果的信息吗？ (请参阅下面的解释。)

这是一个简化的示例。我想查看用户访问该网站已有多长时间，并找到合适的提醒电子邮件来发送给他们。我们测试 3 个独立条件:

1. 应根据用户上次访问以来的时间发送哪封电子邮件？
2. 对于每封电子邮件，用户之前是否没有收到过这封电子邮件？
3. 过去 3 天内用户是否没有收到我们发送的任何电子邮件？

下面是一个 SQL 查询，用于检查所有这 3 个内容。 (此查询是针对每个用户完成的；它使用 PDO 和以 : 开头的变量是绑定(bind)参数):

SELECT message_id, message_content FROM emailtemplates
WHERE time_before_reminder < :days_since_visit
AND message_id not in 
    (SELECT message_id FROM sentemails WHERE user = :userid)
AND :userid not in 
    (SELECT userid FROM sentemails WHERE date_add(timesent, interval 3 day) >= now())
ORDER BY time_before_reminder asc
LIMIT 1;

如果任何条件失败，则返回零行；如果全部通过，则返回一行。

或者，这里有一些执行多个单独查询的代码。它丑陋得多，但它可以为我提供解释性输出，准确说明为什么我们不向给定用户发送电子邮件。 (我已切换到伪代码，因此您不必阅读无数 $stmt->bindParam($params) 行):

//run the query for condition 1:
query("SELECT message_id, message_content FROM emailtemplates WHERE time_before_reminder < :days_since_visit;")
if 0 rows returned:
    echo("no appropriate emails found for this participant")
    return false

//then for condition 2:
query("SELECT message_id from sentemails where message_id = :msgid AND user = :userid")
if 1 or more rows returned:
    echo("user $userid qualified for message $msgid, but they already received it.");
    return false

//then for condition 3:
query("SELECT userid FROM sentemails WHERE user = :userid AND date_add(timesent, interval 3 day) >= now();")
if 1 or more rows returned:
    echo("user $userid qualified for message $msgid, but they already received an email from us in the past 3 days. No email sent.")
    return false

// if we get here, all tests were passed. Send the message that was found in the first query.
send_reminder($userid, $msgid)
echo("successfully sent message $msgid to user $userid!")
return true

第二个例子看起来确实很麻烦，但我喜欢找出到底是哪个条件失败以及原因。有没有办法让单个查询返回此类信息？ (我的意思是返回可以解析为解释的信息，当然，不是让MySQL为我写英文句子。)或者，对于处理像这样测试几个独立条件的查询还有其他建议吗？

Answer 1

您也许可以(在没有绑定(bind)参数的 MySQL 5.5 上测试)使用 SELECT 中的条件。子句，结果应该是 0(失败)或 1(通过):

SELECT
    message_id, 
    message_content, 
    (time_before_reminder < :days_since_visit) AS time, 
    (message_id NOT IN (SELECT message_id FROM sentemails WHERE user = :userid)) AS already, 
    (:userid NOT IN (SELECT userid FROM sentemails WHERE date_add(timesent, interval 3 day) >= now())) AS recent 
FROM emailtemplates
WHERE time_before_reminder < :days_since_visit
AND message_id not in 
    (SELECT message_id FROM sentemails WHERE user = :userid)
AND :userid not in 
    (SELECT userid FROM sentemails WHERE date_add(timesent, interval 3 day) >= now())
ORDER BY time_before_reminder asc
LIMIT 1;

但是，我认为这意味着 DBMS 的工作量增加了一倍。还必须可以使用一些 LEFT JOIN并查看哪些字段为空...这会更优雅，而且可能也更快。

另一件事 - 虽然与你的问题无关 - 有 ORDER BY 有什么意义？随后是 LIMIT 1 ？

编辑:LEFT JOIN基于请求应该是这样的(告诉我它是否有效，除非我创建数据库，否则我无法测试它......而且我感到很懒^^):

SELECT
  et.message_id AS messageId, 
  et.message_content AS messageContent, 
  se1.message_id AS nullIfNotSent, 
  se2.userid AS nullIfMoreThan3Days 
FROM 
  (SELECT :userid AS userid) AS param
    LEFT JOIN sentemails AS se2 
      ON param.userid=se2.userid AND date_add(se2.timesent, interval 3 days) >= now(),
  emailtemplates AS et
    LEFT JOIN sentemails AS se1 
      ON et.message_id=se1.message_id AND se1.userid=param.userid
WHERE 
  time_before_reminder < :days_since_visit
ORDER BY time_before_reminder 
LIMIT 1;

预期行为:返回零行意味着第一次测试失败 ( time_before_reminder )，否则如果 nullIfNotSent不为空(因此是有效的 message_id )，这意味着它未通过第二次测试(此类消息已经在某天发送过)，并且如果 nullIfMoreThan3days不为空(有效的用户 ID)，这意味着用户在过去 3 天内已经收到一条消息(即第三次测试失败)。因此，如果您与 nullIfNotSent 发生争执和nullIfMoreThan3Days ，这意味着您可以使用返回的 messageId 发送消息和messageContent .

请告知是否有效! (或不)；)