如何执行存储在MySQL表列中的查询? [英] How to execute a query which is stored in a table column MySQL?

问题描述

mysql> select * from CT;

| CID | MID | REPORT_QUERY           |
|   1 |   1 | select * from emp;     |
|   2 |   2 | select * from student; |

2 rows in set (0.00 sec)

我想在REPORT_QUERY列中执行查询.

DELIMITER //
 CREATE PROCEDURE TRYct()
   BEGIN
SET @str=(SELECT GROUP_CONCAT(REPORT_QUERY SEPARATOR ' ') FROM CT);
PREPARE q from @str;
EXECUTE q;
   END //
 DELIMITER ;

我使用此代码，但是如果我的表中只有一个查询，它会起作用.如果有两个查询，则给出错误.

i use this code but it works if there is only one query in my table. if there is two query than it gives an error.

ERROR 1064 (42000): 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 'select * from student' at line 1

问题出在哪里?救救我.

where is the problem ? Help me.

推荐答案

您可以使用游标获取CT表上的每个REPORT_QUERY，并使用准备好的语句执行该操作:

You can use a cursor to get each REPORT_QUERY on CT table, and execute that using prepared statements:

delimiter $$
drop procedure if exists run_queries$$
create procedure run_queries()
begin

    declare s_query varchar(255);

    declare done bool default false;
    declare c_queries cursor for    
        select REPORT_QUERY from CT;
    declare continue handler for not found set done = true;


    open c_queries;
    read_loop: loop

        fetch c_queries into s_query;
        if done then 
            leave read_loop;
        end if;

        -- run the query
        set @sql = s_query;
        prepare stmt from  @sql;
        execute stmt;
        deallocate prepare stmt;
    end loop;

end$$

创建过程后，您可以按以下方式调用:

After create procedure, you can call as bellow:

致电run_queries();

call run_queries();

就是这样.

这篇关于如何执行存储在MySQL表列中的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！