mysql表存储查询_如何执行存储在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)

I want to execute queries in REPORT_QUERY column.

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.

解决方案

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:

call run_queries();

That's it.