dreamer2014520
2017-03-22 17:17
采纳率: 0%
浏览 36
已采纳

MYSQL游标无休止地运行

All right, first of all the disclaimer: I know using cursors is one of the worst practices for database programming, but I didn't find any other way of doing this.

I am creating a blog/chat like application so that our employees can communicate with the client, and have all company/client interactions saved and timestamped (there has been some response time comlaints).

Anyway, I am struggling to keep track of the unread messages, so I figured that I could make a simple math if I knew which messages have been read for each user, so whenever my app calls the messages table for the messages, it will update a separate table called "Read messages" where I will keep track of which messages have been read.

I think I have the logic, but whenever I try to run it, mysql just hangs thinking until it times out, however, if I check the 'messages read' table, the info was indeed updated.

Here's the code:

BEGIN 

DROP TEMPORARY TABLE IF EXISTS fetchedMessages;
CREATE TEMPORARY TABLE fetchedMessages AS (

SELECT 

blg.pkIdEntrada AS idMensaje,
blg.grlContenido AS Contenido,
blg.grlTimestamp AS FechaHora,
blg.grlReferencia AS Referencia,
usu.pkIdUsuario AS IdUsuario,
usu.gPrimerNombre AS PrimerNombre,
usu.gPrimerApellido AS PrimerApellido,
tml.pkIdRead AS idLectura


FROM blgEntries blg

LEFT JOIN tbl_usuarios      usu ON blg.fkIdUsuario = usu.pkIdUsuario
LEFT JOIN tbl_mensajesLeidos    tml ON blg.pkIdEntrada = tml.fkIdMensaje AND blg.fkIdUsuario = tml.fkIdUser

WHERE 

blg.grlReferencia = numReferencia

ORDER BY 
FechaHora DESC
);
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE c_idMess INT;
        DECLARE curs CURSOR 
            FOR SELECT idMensaje FROM fetchedMessages WHERE idLectura IS NULL AND idMensaje IS NOT NULL;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


        OPEN curs;
            LOOP 
                FETCH curs INTO c_idMess;
                INSERT INTO tbl_mensajesLeidos (fkIdMensaje, fkIdUser) VALUES (c_idMess, idEmp) ON DUPLICATE KEY UPDATE fkIdMensaje = c_IdMess, fkIdUser = idEmp; 
                ##SELECT CONCAT(c_idMess, ' - ', idEmp);
            END LOOP;

        CLOSE curs;
    END;

SELECT * FROM fetchedMessages;

END

Any ideas??

图片转代码服务由CSDN问答提供 功能建议

好的,首先是免责声明:我知道使用游标是数据库编程最糟糕的做法之一,但是 我没有找到任何其他方式这样做。

我正在创建一个博客/聊天应用程序,以便我们的员工可以与客户进行通信,并保存所有公司/客户端交互并加上时间戳(已经有一些响应时间投诉)。

无论如何,我正在努力跟踪未读消息,所以我想我可以做一个简单的数学运算,如果我知道为每个用户读取了哪些消息,那么每当我的应用程序 为消息调用消息表,它将更新一个名为“读取消息”的单独表,我将跟踪哪些消息已被读取。

我认为我有逻辑,但是 每当我尝试运行它时,mysql只会挂起思考,直到它超时,但是,如果我检查'messages read'表,信息确实更新了。

以下是代码:

  BEGIN 
 
DROP TEMPORARY TABLE IF EXISTS fetchedMessages; 
CREATE TEMPORARY TABLE fetchedMessages AS(
 \  nSELECT 
 
blg.pkIdEntrada AS idMensaje,
blg.grlContenido AS Contenido,
blg.grlTimestamp AS FechaHora,
blg.grlReferencia AS Referencia,
usu.pkIdUsuario AS IdUsuario,
usu.gPrimerNombre AS PrimerNombre,
usu.gPrimerApellido AS  PrimerApellido,
tml.pkIdRead AS idLectura 
 
 
FROM blgEntries blg 
 
LEFT JOIN tbl_usuarios usu ON blg.fkIdUsuario = usu.pkIdUsuario 
LEFT JOIN tbl_mensajesLeidos tml ON blg.pkIdEntrada = tml.fkIdMensaje AND blg.fkIdUsuario = tml  .fkIdUser 
 
WHERE 
 
 nblg.grlReferencia = numReferencia 
 
ORDER BY 
FechaHora DESC 
); 
 BEGIN 
 DECLARE完成INT DEFAULT FALSE; 
 DECLARE c_idMess INT; 
 DECLARE curs CURSOR 
  FOR SELECT idMensaje FROM fetchedMessages WHERE idLectura IS NULL AND idMensaje IS NOT NULL; 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
 
 
  OPEN curs; 
 LOOP 
 FETCH curs INTO c_idMess; 
 INSERT INTO tbl_mensajesLeidos(fkIdMensaje,fkIdUser)VALUES(c_idMess,idEmp)ON DUPLICATE KEY UPDATE fkIdMensaje = c_IdMess,fkIdUser = idEmp;  
 ## SELECT CONCAT(c_idMess,' - ',idEmp); 
 END LOOP; 
 
 CLOSE curs; 
 END; 
 
SELECT * FROM fetchedMessages; 
 
END 
    
 
 

任何想法?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dpiz9879 2017-03-22 17:35
    已采纳

    Most of the examples i see relating to MySql cursors and loops explicitly check the found variable in the loop for its true condition, 1 in your case, and exit the loop if true. You seem to be missing that step.

    I'm also curious why if you're declaring your found variable as an integer you're defaulting it to false instead of 0.

    已采纳该答案
    打赏 评论
  • douzhimao8656 2017-03-22 17:34

    You do not have exit from the LOOP.

    Have a look at LOOK syntax example. Here it is example with cursor.


    Another important one: try to use one simple INSERT ... SELECT statement instead of cursor. This statement allows inserting data from another data set. It also will simplify your code completely.

    打赏 评论

相关推荐 更多相似问题