MySQL:将列值分配给变量 [英] MySQL: Assign column value to variable

问题描述

制作消息系统并处理收件箱页面.

Making a messaging system and working on the inbox page.

收件箱页面以类似线程"的方式组织，我想展示两方之间的最新通信.想想 facebook 收件箱(左侧部分)，无论是您发送的还是对方发送的，它都会显示最新发送的消息.

The inbox page is organized in a 'thread' like manner and I would like to show the latest communication between 2 parties. Think the facebook inbox (the left hand part) where it shows the latest message sent whether you sent the message or the other party did.

这是我当前的查询:

SELECT 
    m.*, 
    s.*, 
    c.*, 
    u.*,
    CASE    
     WHEN m.uid_recipient <> 292 THEN @user_id := m.uid_recipient
     WHEN m.uid_sender <> 292 THEN @user_id := m.uid_sender
    END 
FROM messages m
JOIN signup s ON s.uid = @user_id
LEFT JOIN companies c ON c.uid = s.uid
LEFT JOIN users u ON u.uid = s.uid
WHERE COALESCE(u.uid, c.uid) IS NOT NULL
    AND (m.uid_recipient = 292 AND deleted_recipient = '0')
    OR (m.uid_sender = 292 AND deleted_sender = '0')
ORDER BY datetime DESC

此查询运行但返回一个空结果集，它应该返回 3 个结果.

This query runs but returns an empty result set where it should return 3 results.

消息在一个表中，其中包含发件人的 ID 和收件人的 ID.现在我要做的是将对方的信息加入到行中.所以我的case语句会评估接收者id是否是当前用户的id，如果不是，它应该将@user_id变量设置为对方的id，这样它就可以用来将对方的信息加入到该行中.

The messages are in a table with the sender's id and the recipient's id. Now what I'm trying to do is join the information of the other party to the rows. So my case statement evaluates if the recipient id is the current user's id, and if it isn't, it should set the @user_id variable to the other party's id so it can then be used to join the other party's information to that row.

消息表如下(相关列):

The message table looks like so (relevant columns):

----------------------------------------------
|uid_sender    |uid_recipient    |message    |
----------------------------------------------
|292           |1040             |Test 3     |
----------------------------------------------
|1040          |292              |Test reply |
----------------------------------------------
|292           |1040             |First msg  |
----------------------------------------------

我倾向于认为列的值未分配给变量，但我不知道如何调试此查询以返回分配给@user_id 的值.无论如何要转储mysql查询中的值?

I'm inclined to think that the value of column is not assigned to the variable but I do not know how I could debug this query to return the value assigned to @user_id. Is there anyway to dump the value in a mysql query?

感谢任何帮助.

谢谢.

推荐答案

这个表达式:

CASE    
 WHEN m.uid_recipient <> 292 THEN @user_id := m.uid_recipient
 WHEN m.uid_sender <> 292 THEN @user_id := m.uid_sender
END 

我相信是为@user_id 变量赋值.没有关于何时相对于查询中的其他表达式求值的规则.我会做出有根据的猜测，SELECT 子句中的表达式总是在 joins 和 group bys 之后计算.在这种情况下，from 子句中使用的值始终是初始值.

is, I believe, assiging values to the @user_id variable. There is no rule on when this is evaluated relative to other expressions in the query. I would make an educated guess that expressions in the SELECT clause are always evaluated after joins and group bys. In that case, the value used in the from clause is always the initial value.

我认为你应该把它移到 from 子句:

I think you should just move it to the from clause:

FROM messages m join
     signup s
     ON s.uid = (CASE WHEN m.uid_recipient <> 292 THEN m.uid_recipient
                      WHEN m.uid_sender <> 292 THEN  m.uid_sender
                 END)

可能还有其他表达方式，但这是一个开始.

There are probably other ways to express this, but this is a start.

这篇关于MySQL:将列值分配给变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！