MySql:如果在函数内部或外部调用，相同的 select count(*) 查询将返回不同的结果

Question

嗯，就这么简单。以下查询返回 1:

select count(*) as Total from conversations
where TargetUserID = 2
  and LastMessageSenderUserID = StarterUserID
  and TotalMessages > 0
  and Answered = 0
  and (@ReadDate := GetConversationReadDate(ID)) is not null
  and @ReadDate < date_sub(now(), interval 1 day)

我在带有一个参数 user_id 的存储函数主体中复制了完全相同的代码:

return (select count(*) as Total from conversations
where TargetUserID = user_id
  and LastMessageSenderUserID = StarterUserID
  and TotalMessages > 0
  and Answered = 0
  and (@ReadDate := GetConversationReadDate(ID)) is not null
  and @ReadDate < date_sub(now(), interval 1 day))

然而，后者以 user_id = 2 调用，返回 0。它不是确定性的，并被标记为“读取 sql 数据”。

Answer 1

您在 where 子句的两个部分中使用了相同的变量。这不安全。 MySQL 不保证 where 子句的执行顺序。你应该这样做:

and GetConversationReadDate(ID) < date_sub(now(), interval 1 day))

如果返回的值为NULL，这将自动失败，因此NULL检查是多余的。

这是 documentation 的引用:

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.