MYSQL - 使用 while 循环更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9915315/
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 - Update using while loop
提问by pavelcc
declare c int
set c = 1
while c<700 do
update users set profile_display_name = concat(substring(first_name,1,1), last_name)
where profile_display_name is null and id between ((c-1)*10000+1) and (c*10000);
SET c = c+1;
End while ;
I am getting error. near declare and end while statement. where am i making mistake??
我收到错误。接近声明和结束 while 语句。我哪里出错了？？
回答by ypercube??
Here's how it would be defined as a stored procedure:
以下是如何将其定义为存储过程：
DELIMITER $$
CREATE PROCEDURE proc_name()
BEGIN
DECLARE c int ; --- added ;
SET c = 1 ; --- added ;
WHILE c<700 DO
UPDATE users
SET profile_display_name = concat(substring(first_name,1,1), last_name)
WHERE profile_display_name IS NULL
AND id BETWEEN ((c-1)*10000+1) AND (c*10000);
SET c = c + 1 ;
END WHILE ;
END $$
DELIMITER ;
回答by pavelcc
This Works much better for me
这对我来说效果更好
DELIMITER $$
CREATE DEFINER=`ops`@`localhost` PROCEDURE `myproc`()
BEGIN
DECLARE c INT;
SET c = 1;
WHILE c < 700 DO
SELECT CONCAT('Loop #:', c) ;
update users
set profile_display_name = concat(substring(first_name,1,1), last_name)
where (profile_display_name is null or profile_display_name = '')
and id between ((c-1)*10000+1) and (c*10000);
commit;
SET c=c+1;
END WHILE;
END
回答by nnichols
In MySQL compound statements and flow control structures can only be used in stored routines - MySQL Compound-Statement Syntax
在 MySQL 中复合语句和流控制结构只能在存储例程中使用 - MySQL Compound-Statement Syntax
