What does @variable := variable2 mean in a MySQL query?

Question

I have the following query within inside a select if in my code and I can't remember what the line containing @lastComment := time actually does?

SELECT 
    IF(
        (SELECT @lastComment := `time` 
         FROM usermessages 
         WHERE userId = $userId 
         ORDER BY id DESC 
         LIMIT 1)
    IS NOT NULL,
    DATE_SUB(NOW(), INTERVAL 30 SECOND) >= @lastComment, 1
    )

It looks like @lastComment is just a temporary variable holding time but I don't understand why it's needed, is it just to pass it to the other query?

This query is written awkwardly; it could be rewritten to be clearer in its intent. — p.campbell, May 11 '12 at 15:45
good explanation is here http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference — Elen, May 11 '12 at 15:50
@p.campbell Yes that's what I intend to do now, just working out what the third line actually does is what's stopping me though — Dan, May 11 '12 at 15:51
Looks like its examining the most recent 'usermessage' to see if its within 30seconds of now(). If it is, save the string 'time' into @lastcomment. It is awkward phrasing though. — ethrbunny, May 11 '12 at 16:32

score 0 · Accepted Answer · answered May 13 '12 at 06:44

Obviously you didn't want to execute the query twice and that's correct but you can easily omit the user-defined variable and thus make it clearer:

SELECT 
  IFNULL(
     (
       SELECT DATE_SUB(NOW(), INTERVAL 30 SECOND) >= `time` 
       FROM usermessages 
       WHERE userId = $userId 
       ORDER BY id DESC 
       LIMIT 1
     )
     , 1
)

which translates to: check if the given expression is NULL, if so return 1, else return the expression value.

What does @variable := variable2 mean in a MySQL query?

1 Answers1