如何在MySQL服务器中执行动态Sql字符串作为查询? [英] How to Execute Dynamic Sql String as a Query in mysql server?

问题描述

我创建了一个触发器，该触发器创建了一个动态查询.并执行了我曾尝试过'EXECU q'的触发器，但是它不起作用我如何运行/执行该动态查询.

I have create a trigger which is create a dynamic query.and execute it i had tried 'EXECU q' but it does not work. how can i run/execute that dynamic query.

BEGIN
    DECLARE a INT Default 0 ;
    DECLARE str  VARCHAR(255);
    DECLARE q VARCHAR(500);

    SET q = 'insert into '+new.master_name+' values(';

    simple_loop: LOOP
        SET a=a+1; 
        SET str = SPLIT_STRING(new.remarks,"|",a); 
        SET q = CONCAT(q,str+',');
        SET q = LEFT(q, LENGTH(q) - 1);
        IF str='' THEN
                LEAVE simple_loop; 
            END IF;


    END LOOP simple_loop; 
    SET q = CONCATE(q,');');

    EXEC q
END

这是Trigerr这是我做的功能退货(SUBSTRING(SUBSTRING_INDEX(str，delim，pos)，CHAR_LENGTH(SUBSTRING_INDEX(str，delim，pos-1))+ 1)，德林'')

This is Trigerr this is Function which i made RETURN REPLACE( SUBSTRING( SUBSTRING_INDEX(str , delim , pos) , CHAR_LENGTH( SUBSTRING_INDEX(str , delim , pos - 1) ) + 1 ) , delim , '' )

推荐答案

我已经编写了一个存储过程来执行动态构造的sql语句.

I've written a stored procedure to execute dynamically constructed sql statements.

SET @index := 7;
CALL eval(CONCAT('SELECT ', @index));

---

实施

DELIMITER $$

CREATE PROCEDURE eval(IN dynamic_statement TEXT)
  BEGIN
      SET @dynamic_statement := dynamic_statement;
      PREPARE prepared_statement FROM @dynamic_statement;
      EXECUTE prepared_statement;
      DEALLOCATE PREPARE prepared_statement;
  END$$

DELIMITER ;

这篇关于如何在MySQL服务器中执行动态Sql字符串作为查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！