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

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??

  • 写回答

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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 Jenkins+k8s部署slave节点offline
  • ¥15 微信小游戏反编译后,出现找不到分包的情况
  • ¥15 如何实现从tello无人机上获取实时传输的视频流,然后将获取的视频通过yolov5进行检测
  • ¥15 WPF使用Canvas绘制矢量图问题
  • ¥15 用三极管设计一个单管共射放大电路
  • ¥15 孟德尔随机化r语言运行问题
  • ¥15 pyinstaller编译的时候出现No module named 'imp'
  • ¥15 nirs_kit中打码怎么看(打码文件是csv格式)
  • ¥15 怎么把多于硬盘空间放到根目录下
  • ¥15 Matlab问题解答有两个问题