循环n次而不使用存储过程 [英] Loop n times without using a stored procedure

问题描述

如何编写一个在MySql中运行n次而不使用存储过程的循环.

How can I write a loop that runs n times in MySql without using a stored procedure.

这是我使用存储过程的方法:

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-您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在'DECLARE count INT DEFAULT 0附近使用；计数<第2行有10个DO

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

我一直在互联网上寻找没有运气的解决方案.

I have looked through the Internet for a solution with no luck.

基于评论进行

上面的存储过程完全符合我的要求:它循环10次并执行我的sql语句.现在，我想在不使用存储过程的情况下完成同样的事情.像这样:

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:

DECLARE count INT DEFAULT 0;
   WHILE count < 10 DO
      /**Sql statement**/
      SET count = count + 1;
   END WHILE;  

推荐答案

MySQL支持IF，CASE，ITERATE，LEAVE LOOP，WHILE和REPEAT 在存储程序内 中进行流控制的结构.

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs.

存储的程序和视图上的文档说:

存储的程序定义包括可能使用复合词的主体 语句，循环，条件和声明的变量.

Stored program definitions include a body that may use compound statements, loops, conditionals, and declared variables.

复合语句语法

本节描述BEGIN ... END复合物的语法 语句和其他可以在 stored正文中使用的语句 程序:存储过程和函数，触发器和事件.

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 loops and conditional tests.

因此，您似乎只能在存储过程，函数或触发器内运行显式循环.

So, it looks like you can run an explicit loop only within a stored procedure, function or trigger.

根据您在SQL语句中执行的操作，可以使用数字表(或视图)(， MYSQL:序号表).

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).

如果您的查询是SELECT，并且可以将10个结果作为长结果集(而不是10个单独的结果集)返回10次，则可以执行以下操作:

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

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.

因此，如果您有一个表Numbers，其中的int列Number的值从1到100K(例如，我这样做)，并且该列的主键是，则代替此循环: /p>

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:

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;

您可以写:

INSERT INTO table_name(col1,col2,col3)
SELECT ("val1", "val2", Numbers.Number-1)
FROM Numbers
WHERE Numbers.Number <= 10;

它的工作速度也快将近10倍.

It would also work almost 10 times faster.

这篇关于循环n次而不使用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！