因为循环的建议暗示了对过程类型解决方案的请求.这是我的.

Since the suggestion of a loop implies the request for a procedure type solution. Here is mine.

对从表中提取的任何单个记录起作用的任何查询都可以包装在一个过程中，使其在表的每一行中运行，如下所示:

Any query which works on any single record taken from a table can be wrapped in a procedure to make it run through each row of a table like so:

首先删除具有相同名称的任何现有过程，然后更改分隔符，这样您的 SQL 就不会在您尝试编写过程时尝试运行每一行.

First delete any existing procedure with the same name, and change the delimiter so your SQL doesn't try to run each line as you're trying to write the procedure.

DROP PROCEDURE IF EXISTS ROWPERROW;
DELIMITER ;;

然后这是按照您的示例的程序(为清楚起见，使用 table_A 和 table_B)

Then here's the procedure as per your example (table_A and table_B used for clarity)

CREATE PROCEDURE ROWPERROW()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SELECT COUNT(*) FROM table_A INTO n;
SET i=0;
WHILE i<n DO 
  INSERT INTO table_B(ID, VAL) SELECT (ID, VAL) FROM table_A LIMIT i,1;
  SET i = i + 1;
END WHILE;
End;
;;

然后不要忘记重置分隔符

Then dont forget to reset the delimiter

DELIMITER ;

并运行新程序

CALL ROWPERROW();

您可以在INSERT INTO"中为所欲为我只是从您的示例请求中复制的行.

You can do whatever you like at the "INSERT INTO" line which I simply copied from your example request.

请仔细注意INSERT INTO"这里使用的线反映了问题中的线.根据对此答案的评论，您需要确保您的查询对于您正在运行的 SQL 版本在语法上是正确的.

Note CAREFULLY that the "INSERT INTO" line used here mirrors the line in the question. As per the comments to this answer you need to ensure that your query is syntactically correct for which ever version of SQL you are running.

在您的 ID 字段递增并从 1 开始的简单情况下，示例中的行可能变为:

In the simple case where your ID field is incremented and starts at 1 the line in the example could become:

INSERT INTO table_B(ID, VAL) VALUES(ID, VAL) FROM table_A WHERE ID=i;

替换SELECT COUNT"线与

Replacing the "SELECT COUNT" line with

SET n=10;

将让您仅在 table_A 中的前 10 条记录上测试您的查询.

Will let you test your query on the first 10 record in table_A only.

最后一件事.这个过程也很容易在不同的表中嵌套，并且是我可以在一个表上执行一个过程的唯一方法，该过程将不同数量的记录从父表的每一行动态插入到一个新表中.

One last thing. This process is also very easy to nest across different tables and was the only way I could carry out a process on one table which dynamically inserted different numbers of records into a new table from each row of a parent table.

如果你需要它运行得更快，那么一定要尝试让它基于设置，如果不是，那么这很好.您也可以以游标形式重写上述内容，但它可能不会提高性能.例如:

If you need it to run faster then sure try to make it set based, if not then this is fine. You could also rewrite the above in cursor form but it may not improve performance. eg:

DROP PROCEDURE IF EXISTS cursor_ROWPERROW;
DELIMITER ;;

CREATE PROCEDURE cursor_ROWPERROW()
BEGIN
  DECLARE cursor_ID INT;
  DECLARE cursor_VAL VARCHAR;
  DECLARE done INT DEFAULT FALSE;
  DECLARE cursor_i CURSOR FOR SELECT ID,VAL FROM table_A;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cursor_i;
  read_loop: LOOP
    FETCH cursor_i INTO cursor_ID, cursor_VAL;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO table_B(ID, VAL) VALUES(cursor_ID, cursor_VAL);
  END LOOP;
  CLOSE cursor_i;
END;
;;

请记住将您将使用的变量声明为与查询表中的变量相同的类型.

Remember to declare the variables you will use as the same type as those from the queried tables.

我的建议是尽可能使用基于集合的查询，并且仅在必要时使用简单的循环或游标.

My advise is to go with setbased queries when you can, and only use simple loops or cursors if you have to.