MySQL Mysql循环并插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12627735/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql loop and insert
提问by SynerCoder
I have the following MySql script:
我有以下 MySql 脚本：
SET @skip = 0;
SET @max = (SELECT COUNT(*) FROM table1);
CREATE TEMPORARY TABLE TempTable(
id INT NOT NULL,
name VARCHAR(32) NOT NULL
);
loop1: LOOP
INSERT INTO TempTable (id, name) SELECT id, name FROM table1 LIMIT @skip, 1;
IF @skip < @max THEN
SET @skip = @skip + 1;
ITERATE loop1;
END IF;
LEAVE loop1;
END LOOP loop1;
SELECT * FROM TempTable;
This script is not working but it should select all the id and names in table1. I am using a loop because I am also going to do other stuff in those loops but that is for later.
I am not looking for a solution like SELECT id, name FROM table1but I want my error fixed. So I can continue with my loop.
此脚本不起作用，但它应该选择table1. 我正在使用循环，因为我还将在这些循环中做其他事情，但那是以后的事情。我不是在寻找类似的解决方案，SELECT id, name FROM table1但我希望我的错误得到修复。所以我可以继续我的循环。
The error I get is:
我得到的错误是：
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'loop1: LOOP INSERT INTO TempTable (id, name) SELECT id, name FROM table1' at line 1
1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的“loop1: LOOP INSERT INTO TempTable (id, name) SELECT id, name FROM table1”附近使用的正确语法
回答by ProdigyProgrammer
try something like this for the syntax of your loop:
为循环的语法尝试这样的事情：
DECLARE @count INT;
DECLARE @max INT;
SET @count=1;
SET @max= (SELECT COUNT(*) FROM table1);
WHILE(@count < @max)
BEGIN
/*your database query logic*/
END
use "SET @count=(@count+1)" to increment your counter within the loop
使用“SET @count=(@count+1)”在循环内增加计数器
回答by ?ukasz Starowicz
/* set delimiter */
DELIMITER $$
/* remove procedure if exists... */
DROP PROCEDURE IF EXISTS insert_it $$
/* create procedure */
CREATE PROCEDURE insert_it ()
BEGIN
DECLARE varcount INT DEFAULT 1;
DECLARE varmax INT DEFAULT 15;
WHILE varcount <= varmax DO
INSERT INTO yourtable(fixed_val, count_val) VALUES(3493, varcount);
SET varcount = varcount + 1;
END WHILE;
END $$
/* reset delimiter back to normal */
DELIMITER ;
/* call procedure */
CALL insert_it();
回答by AndreKR
There isa syntax error in your code (the LIMIT @skipwhich is dynamic SQL and requires some tricks to make it work) but it is not at loop1: LOOP.
还有就是在你的代码中的语法错误（在LIMIT @skip这是动态SQL，需要一些技巧，以使其正常工作），但它不是loop1: LOOP。
My guess is you are trying to use LOOP outside a compound statement (BEGIN... END) like a stored procedure, which is not possible. You have to create a stored procedure to do that.
我的猜测是您正在尝试在复合语句 ( BEGIN... END)之外使用 LOOP，如存储过程，这是不可能的。您必须创建一个存储过程才能做到这一点。
