MYSQL存储过程—执行动态SQL

MySQL存储过程中动态SQL是使用字符串拼接SQL语句，示例代码如下：

SET @selectSql = 'SELECT 	RECID, FCODE, FNAME, FDISCOUNT, FIMAGE, FVIDEO, FTYPE, FSORT, FISHOT FROM tsvbase ';
     IF(paintName<>'')THEN
        SET @selectSql =CONCAT(@selectSql,' where FNAME like ',"'%",paintName,"%'");
        IF(regionCode<>'')THEN
          SET @selectSql =CONCAT(@selectSql,' and FADMINMAPNM like', "'%",fGetRegionCode(regionCode),"%'");
        END IF;
     ELSE
     IF (regionCode<>'')THEN
        SET @selectSql =CONCAT(@selectSql,' where  FADMINMAPNM like', "'%",fGetRegionCode(regionCode),"%'");
        END IF;
     END IF;
       
     SET @selectSql =CONCAT(@selectSql,' order by FISHOT,FSORT  LIMIT ',CAST(m AS CHAR(10)),',',CAST(currPage AS CHAR(10)));
     SELECT @selectSql;
     
     PREPARE stmt FROM @selectSql;
     EXECUTE stmt ;
     DEALLOCATE PREPARE stmt;

注意：这里mysql变量前缀带“@”，无需使用DECLARE声明。

执行步骤说明如下：

1. 字符串@selectSql赋值；
2. 根据条件使用concat拼接字符串；
3. 如果需要拼接orderby和分页字符串；
4. 使用PREPARE 完成SQL预处理；
5. 执行SQL
6. 释放资源