MySQL foreach循环 [英] MySQL foreach loop

问题描述

我必须遍历MySQL中表User中的每一行.我需要为用户中的每次迭代创建一个新的行地址，并具有以下所述的一些条件.

I have to iterate each row in my table User in MySQL. I need to create a new row Address for each iteration in User with some conditions described below.

我有3张桌子:

User: id, stuff, id_person, email
Person: id, stuff, id_address
Address: id, email

如果User.id_person不为NULL且person.id_address为NULL，则需要在地址中创建新行.我必须使用与User.email相同的电子邮件来创建行.我必须对User中的每一行都这样做.

I need to create a new row in Address if the User.id_person is NOT NULL and that person.id_address IS NULL. I have to create the row with the same email that User.email. I have to do that for each row in User.

我尝试使用MySQL游标，但是我不知道如何很好地使用它们.

I tried to use MySQL cursor's but I do not know how to use them very well.

我该怎么做?还有其他方法可以代替使用游标吗?

How can I do that? Is there any other way instead of using cursor's for that?

谢谢.

我刚刚意识到，我还必须用我刚创建的地址行的ID更新person.id_address.

I have just realized that I also have to update person.id_address with the id of the address' row I have just created.

推荐答案

根据我的收集，只要字段是您提供的，以下内容就足够了.

From what I can gather, the following should suffice, so long as the fields are what you have provided.

INSERT INTO Address (email)
  SELECT User.email
    FROM User JOIN person ON User.id_person = person.id
   WHERE person.id_address IS NULL
;

编辑(带有光标)

使用光标这应该很简单，但是我强烈建议您熟悉这些内容及其含义.

This should be pretty simple with a cursor, however I highly advise you familiarize yourself with these and the implications.

DROP PROCEDURE IF EXISTS _tmp_update_address;
DELIMITER $$
CREATE PROCEDURE _tmp_update_address()
BEGIN
   DECLARE cursor_List_isdone BOOLEAN DEFAULT FALSE;
   DECLARE cur_userId, cur_personId INT;
   DECLARE cur_email VARCHAR(250) DEFAULT '';

   DECLARE cursor_List CURSOR FOR 
      SELECT User.id, person.id_address, User.email
      FROM User JOIN person ON User.id_person = person.id
      WHERE person.id_address IS NULL
    ;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_List_isdone = TRUE;

   OPEN cursor_List;

   loop_List: LOOP
      FETCH cursor_List INTO cur_userId, cur_personId, cur_email;
      IF cursor_List_isdone THEN
         LEAVE loop_List;
      END IF;

      INSERT INTO Address (email) VALUES (cur_email);
      UPDATE person SET person.id_address = LAST_INSERT_ID()
         WHERE person.id = cur_personId;

   END LOOP loop_List;

   CLOSE cursor_List;
END

$$

DELIMITER ;

CALL _tmp_update_address();

这篇关于MySQL foreach循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！