时间戳

首页 > 解决方案 > 存储过程中的错误 1064 在准备好的语句设置其值后检查局部变量是否为 NULL

mysql - 存储过程中的错误 1064 在准备好的语句设置其值后检查局部变量是否为 NULL

问题描述

我正在使用 MySQL 创建 Twitter 克隆，并且正在编写一个存储过程来更新刚刚关注新用户的用户的 userfeed 表，以包括该用户关注的人的所有推文。

CREATE PROCEDURE `populate_userfeed_after_following`(IN username varchar(20), IN following_username varchar(20))
BEGIN
    DECLARE last_tweet timestamp default null;
    DECLARE following_id int;
    
    SELECT id from users where username=following_username into following_id;
    
    SET @timestamp_tweet = CONCAT('SELECT timestamp FROM userfeed_', username, ' ORDER BY timestamp DESC LIMIT 1 into', last_tweet);
    
    PREPARE timestamp_tweetQuery FROM @timestamp_tweet;
    EXECUTE timestamp_tweetQuery;
    
    IF (last_tweet is NULL) THEN
        SET @insert_statement = CONCAT('INSERT INTO userfeed_', username, '(tweet_id) SELECT id from tweets
        WHERE author=', following_id,
        ' LIMIT 200');
    ELSE
        SET @insert_statement = CONCAT('INSERT INTO userfeed_', username, '(tweet_id) SELECT id from tweets
        WHERE author=', following_id ,'and timestamp >', last_tweet);
    END IF;
    
    PREPARE insertQuery FROM @insert_statement;
    EXECUTE insertQuery; 
    
    DEALLOCATE PREPARE timestamp_tweetQuery;
    DEALLOCATE PREPARE insertQuery;
    
END

我将最过时的时间戳存储在一个局部变量中，该变量last_tweet是在从关注某人的用户的 userfeed 表中检索到它之后调用的，并且我正在检查它NULL以创建动态插入语句。

每当我通过传递参数来调用程序时，例如，

call populate_userfeed_after_following('joe', 'rocky'); 
#joe follows rocky

我得到一个错误，

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

如果您指出我的代码中的错误，我将不胜感激。

标签： mysql

解决方案

正如@P.Salmon 指出的那样，在这种情况下使用局部变量（使用 DECLARE 语句）将不起作用。所以我更正了代码以使用会话变量或用户定义的变量（使用 SET 语句）并重写它。

以下逻辑有效：

CREATE PROCEDURE `populate_userfeed_after_following`(IN username varchar(20), IN following_username varchar(20))
BEGIN
    -- DECLARE last_tweet timestamp default null;
    -- DECLARE following_id int;
    
    SET @following_id = 0;
    SET @last_tweet = NULL;
    
    SET @followingId_tweet = CONCAT('SELECT id from users where username=\'', following_username, '\' into @following_id');
    SET @timestamp_tweet = CONCAT('SELECT timestamp FROM userfeed_', username, ' ORDER BY timestamp DESC LIMIT 1 into @last_tweet');
    
    PREPARE followingIdQuery FROM @followingId_tweet;
    EXECUTE followingIdQuery; 
    DEALLOCATE PREPARE followingIdQuery;
    
    PREPARE timestamp_tweetQuery FROM @timestamp_tweet;
    EXECUTE timestamp_tweetQuery; 
    DEALLOCATE PREPARE timestamp_tweetQuery;
    
    IF (@last_tweet is NULL) THEN
        SET @insert_statement = CONCAT('INSERT INTO userfeed_', username, '(tweet_id) SELECT id from tweets
        WHERE author=@following_id
        LIMIT 200');
    ELSE
        SET @insert_statement = CONCAT('INSERT INTO userfeed_', username, '(tweet_id) SELECT id from tweets
        WHERE author=@following_id and timestamp > @last_tweet');
    END IF;
    
    PREPARE insertQuery FROM @insert_statement;
    EXECUTE insertQuery; 
    DEALLOCATE PREPARE insertQuery;
    
END

最初的错误是由于我使用参数中传递的值创建动态 SQL 语句的 select 语句以及使用 where 的下面的行SET @timestamp_tweet。正确的方法是使用准备好的语句。

推荐阅读