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附近使用正确的语法;在第2行计数<10 DO
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;
3 个解决方案
#1
11
MySQL docs on Flow Control Statements say:
关于流控制语句的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 Views say:
存储程序和视图的文档说:
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.
本节介绍BEGIN ... END复合语句的语法以及可在存储程序体中使用的其他语句:存储过程和函数,触发器和事件。
A compound statement is a block that can contain other blocks; declarations for variables, condition handlers, and cursors; and flow control constructs such as loops and conditional tests.
复合语句是一个可以包含其他块的块;变量,条件处理程序和游标的声明;和流控制结构,如循环和条件测试。
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 SELECT and it is OK to return result of your SELECT 10 times as one long result set (as opposed to 10 separate result sets) you can do something like this:
如果您的查询是SELECT,并且可以将SELECT 10的结果作为一个长结果集(而不是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
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 Numbers with int column Number with values from 1 to, say, 100K (as I do), and primary key on this column, then instead of this loop:
所以,如果你有一个带有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倍。
#2
5
You can do it direcly with MariaDB Sequence Engine. MariaDB is a binary replacement for MySQL.
您可以使用MariaDB Sequence Engine直接执行此操作。 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]>
#3
1
It is no possible.
这是不可能的。
I read all MySQL documentation and it is sentences can only be declared in function/procedure body.
我阅读了所有MySQL文档,它只能在函数/过程体中声明。
#1
11
MySQL docs on Flow Control Statements say:
关于流控制语句的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 Views say:
存储程序和视图的文档说:
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.
本节介绍BEGIN ... END复合语句的语法以及可在存储程序体中使用的其他语句:存储过程和函数,触发器和事件。
A compound statement is a block that can contain other blocks; declarations for variables, condition handlers, and cursors; and flow control constructs such as loops and conditional tests.
复合语句是一个可以包含其他块的块;变量,条件处理程序和游标的声明;和流控制结构,如循环和条件测试。
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 SELECT and it is OK to return result of your SELECT 10 times as one long result set (as opposed to 10 separate result sets) you can do something like this:
如果您的查询是SELECT,并且可以将SELECT 10的结果作为一个长结果集(而不是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
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 Numbers with int column Number with values from 1 to, say, 100K (as I do), and primary key on this column, then instead of this loop:
所以,如果你有一个带有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倍。
#2
5
You can do it direcly with MariaDB Sequence Engine. MariaDB is a binary replacement for MySQL.
您可以使用MariaDB Sequence Engine直接执行此操作。 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]>
#3
1
It is no possible.
这是不可能的。
I read all MySQL documentation and it is sentences can only be declared in function/procedure body.
我阅读了所有MySQL文档,它只能在函数/过程体中声明。