MySQL 不使用存储过程循环 n 次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32367957/
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
Loop n times without using a stored procedure
提问by Luthando Ntsekwa
How can I write a loop that runs n times in MySql without using a stored procedure.
如何在不使用存储过程的情况下编写在 MySql 中运行 n 次的循环。
This is how I do it with a stored procedure:
这是我使用存储过程的方式：
DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test()
BEGIN
DECLARE count INT DEFAULT 0;
WHILE count < 10 DO
/**Sql statement**/
SET count = count + 1;
END WHILE;
END$$
DELIMITER ;
And then I execute my procedure this way:
然后我以这种方式执行我的程序：
call test();
If I remove the stored procedure and run the normal query, then it fails with this error:
如果我删除存储过程并运行正常查询，则会失败并显示以下错误：
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 'DECLARE count INT DEFAULT 0; WHILE count < 10 DO at line' 2
1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在“DECLARE count INT DEFAULT 0”附近使用的正确语法；WHILE count < 10 DO 在第 2 行
I have looked through the Internet for a solution with no luck.
我已经通过互联网寻找了一个没有运气的解决方案。
Edit Based On comments:
根据评论编辑：
The above stored procedure does exactly what I want: It loops 10 times and execute my sql statement. Now I want to accomplish the same thing without using a stored procedure. Something like:
上面的存储过程完全符合我的要求：它循环 10 次并执行我的 sql 语句。现在我想在不使用存储过程的情况下完成同样的事情。就像是：
DECLARE count INT DEFAULT 0;
WHILE count < 10 DO
/**Sql statement**/
SET count = count + 1;
END WHILE;
回答by Vladimir Baranov
MySQL docs on Flow Control Statementssay:
关于Flow Control Statements 的MySQL 文档说：
MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs.
MySQL 支持 IF、CASE、ITERATE、LEAVE LOOP、WHILE 和 REPEAT 构造用于存储程序中的流控制。
Docs on Stored Programs and Viewssay:
存储程序和视图的文档说：
Stored program definitions include a body that may use compound statements, loops, conditionals, and declared variables.
存储的程序定义包括一个可以使用复合语句、循环、条件和声明变量的主体。
This section describes the syntax for the BEGIN ... END compound statement and other statements that can be used in the body of stored programs: Stored procedures and functions, triggers, and events.
A compound statement is a block that can contain other blocks; declarations for variables, condition handlers, and cursors; and flow control constructs such as loopsand conditional tests.
本节介绍 BEGIN ... END 复合语句和可用于存储程序主体的其他语句的语法：存储过程和函数、触发器和事件。
复合语句是一个可以包含其他块的块；变量、条件处理程序和游标的声明；和流程控制结构，例如循环和条件测试。
So, it looks like you can run an explicit loop only within a stored procedure, function or trigger.
因此，看起来您只能在存储过程、函数或触发器中运行显式循环。
Depending on what you do in your SQL statement, it may be acceptable to use a table (or view) of numbers (Creating a "Numbers Table" in mysql, MYSQL: Sequential Number Table).
根据您在 SQL 语句中执行的操作，使用数字表（或视图）可能是可以接受的（在 mysql 中创建“数字表”，MYSQL：顺序数字表）。
If your query is a SELECTand it is OK to return result of your SELECT10 times as one long result set (as opposed to 10 separate result sets) you can do something like this:
如果您的查询是 aSELECT并且可以将SELECT10 次的结果作为一个长结果集（而不是 10 个单独的结果集）返回，您可以执行以下操作：
SELECT MainQuery.*
FROM
(
SELECT 1 AS Number
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
) AS Numbers
CROSS JOIN
(
SELECT 'some data' AS Result
) AS MainQuery
Example for INSERT
插入示例
I recommend to have a permanent table of numbers in your database. It is useful in many cases. See the links above how to generate it.
我建议在您的数据库中有一个永久的数字表。它在许多情况下很有用。请参阅上面的链接如何生成它。
So, if you have a table Numberswith intcolumn Numberwith values from 1 to, say, 100K (as I do), and primary key on this column, then instead of this loop:
因此，如果您有一个表，Numbers其中的int列Number的值从 1 到 100K（就像我所做的那样），并且该列上有主键，那么请不要使用此循环：
DECLARE count INT DEFAULT 0;
WHILE count < 10 DO
INSERT INTO table_name(col1,col2,col3)
VALUES("val1","val2",count);
SET count = count + 1;
END WHILE;
you can write:
你可以写：
INSERT INTO table_name(col1,col2,col3)
SELECT ("val1", "val2", Numbers.Number-1)
FROM Numbers
WHERE Numbers.Number <= 10;
It would also work almost 10 times faster.
它的工作速度也快了近 10 倍。
回答by Bernd Buffen
You can do it direcly with MariaDB Sequence Engine. MariaDB is a binary replacement for MySQL.
您可以直接使用 MariaDB 序列引擎来完成。MariaDB 是 MySQL 的二进制替代品。
"A Sequence engine allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment."
“序列引擎允许创建具有给定起始值、结束值和增量的数字（正整数）的升序或降序序列。”
[Manual Sequence Engine]
[手动序列引擎]
Here are some Samples:
以下是一些示例：
mysql -uroot -p
Enter password: xxxxxxx
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.20-MariaDB-log Homebrew
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use tmp
Database changed
MariaDB [tmp]> select version();
+---------------------+
| version() |
+---------------------+
| 10.0.20-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)
MariaDB [tmp]> select * from seq_1_to_10;
+-----+
| seq |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+-----+
10 rows in set (0.00 sec)
MariaDB [tmp]> select * from seq_1_to_10_step_2;
+-----+
| seq |
+-----+
| 1 |
| 3 |
| 5 |
| 7 |
| 9 |
+-----+
5 rows in set (0.00 sec)
MariaDB [tmp]> SELECT DAYNAME('1980-12-05' + INTERVAL (seq) YEAR) day,
-> '1980-12-05' + INTERVAL (seq) YEAR date FROM seq_0_to_40;
+-----------+------------+
| day | date |
+-----------+------------+
| Friday | 1980-12-05 |
| Saturday | 1981-12-05 |
| Sunday | 1982-12-05 |
| Monday | 1983-12-05 |
| Wednesday | 1984-12-05 |
| Thursday | 1985-12-05 |
| Friday | 1986-12-05 |
| Saturday | 1987-12-05 |
| Monday | 1988-12-05 |
| Tuesday | 1989-12-05 |
| Wednesday | 1990-12-05 |
| Thursday | 1991-12-05 |
| Saturday | 1992-12-05 |
| Sunday | 1993-12-05 |
| Monday | 1994-12-05 |
| Tuesday | 1995-12-05 |
| Thursday | 1996-12-05 |
| Friday | 1997-12-05 |
| Saturday | 1998-12-05 |
| Sunday | 1999-12-05 |
| Tuesday | 2000-12-05 |
| Wednesday | 2001-12-05 |
| Thursday | 2002-12-05 |
| Friday | 2003-12-05 |
| Sunday | 2004-12-05 |
| Monday | 2005-12-05 |
| Tuesday | 2006-12-05 |
| Wednesday | 2007-12-05 |
| Friday | 2008-12-05 |
| Saturday | 2009-12-05 |
| Sunday | 2010-12-05 |
| Monday | 2011-12-05 |
| Wednesday | 2012-12-05 |
| Thursday | 2013-12-05 |
| Friday | 2014-12-05 |
| Saturday | 2015-12-05 |
| Monday | 2016-12-05 |
| Tuesday | 2017-12-05 |
| Wednesday | 2018-12-05 |
| Thursday | 2019-12-05 |
| Saturday | 2020-12-05 |
+-----------+------------+
41 rows in set (0.00 sec)
MariaDB [tmp]>
Here one Sample:
这是一个示例：
MariaDB [(none)]> use tmp
Database changed
MariaDB [tmp]> SELECT * FROM seq_1_to_5,
-> (SELECT * FROM animals) AS x
-> ORDER BY seq;
+-----+------+-----------+-----------------+
| seq | id | name | specie |
+-----+------+-----------+-----------------+
| 1 | 1 | dougie | dog-poodle |
| 1 | 6 | tweety | bird-canary |
| 1 | 5 | spotty | turtle-spotted |
| 1 | 4 | mr.turtle | turtle-snapping |
| 1 | 3 | cadi | cat-persian |
| 1 | 2 | bonzo | dog-pitbull |
| 2 | 4 | mr.turtle | turtle-snapping |
| 2 | 3 | cadi | cat-persian |
| 2 | 2 | bonzo | dog-pitbull |
| 2 | 1 | dougie | dog-poodle |
| 2 | 6 | tweety | bird-canary |
| 2 | 5 | spotty | turtle-spotted |
| 3 | 6 | tweety | bird-canary |
| 3 | 5 | spotty | turtle-spotted |
| 3 | 4 | mr.turtle | turtle-snapping |
| 3 | 3 | cadi | cat-persian |
| 3 | 2 | bonzo | dog-pitbull |
| 3 | 1 | dougie | dog-poodle |
| 4 | 2 | bonzo | dog-pitbull |
| 4 | 1 | dougie | dog-poodle |
| 4 | 6 | tweety | bird-canary |
| 4 | 5 | spotty | turtle-spotted |
| 4 | 4 | mr.turtle | turtle-snapping |
| 4 | 3 | cadi | cat-persian |
| 5 | 5 | spotty | turtle-spotted |
| 5 | 4 | mr.turtle | turtle-snapping |
| 5 | 3 | cadi | cat-persian |
| 5 | 2 | bonzo | dog-pitbull |
| 5 | 1 | dougie | dog-poodle |
| 5 | 6 | tweety | bird-canary |
+-----+------+-----------+-----------------+
30 rows in set (0.00 sec)
MariaDB [tmp]>
回答by Ivan Cachicatari
It is no possible.
这是不可能的。
I read all MySQL documentation and it is sentences can only be declared in function/procedure body.
我阅读了所有 MySQL 文档，并且它的句子只能在函数/过程体中声明。
回答by Nikolay Hristov
As mentioned by Berd you could do it with the built in sequence but it's a bit strange:
正如 Berd 所提到的，你可以使用内置的顺序来做到这一点，但这有点奇怪：
SET @i = 1;
set @str = 'a,b,c,d,e,f,g,h';
select temp.length into @length from
(select
ROUND(
(
LENGTH(dt.data)
- LENGTH( REPLACE (dt.data, ",", "") )
) / LENGTH(",")
)+1 AS length
from (select @str as data) dt
) temp;
SET @query = CONCAT('select substring_index(
substring_index(@str, '','', seq),
'','',
-1
) as letter from seq_', @i, '_to_',@length);
PREPARE q FROM @query;
EXECUTE q;
