由于建议使用循环，因此要求提供过程类型的解决方案.这是我的.

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:

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

然后别忘了重置定界符

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.