mysql8.0 预编译语句Prepared Statements(动态sql)

p_dl_save_collection_batch过程的输入参数为下面的值

exec p_dl_save_collection_data 'OP2165.Upload.Cmd.SNum,OP2165.Upload.Cmd.PNum,OP2165.Upload.Cmd.PalletID,OP2165.Upload.Cmd.Ok,OP2165.Upload.Cmd.Nok,OP2165.Upload.Cmd.NokCode,OP2165.Upload.Result.MinMax[0].CycleCount,OP2165.Upload.Result.MinMax[0].Res,OP2165.Upload.Result.MinMax[1].CycleCount,OP2165.Upload.Result.MinMax[1].Res,OP2165.Upload.Result.MinMax[2].CycleCount,OP2165.Upload.Result.MinMax[2].Res,OP2165.Upload.Result.MinMax[3].CycleCount,OP2165.Upload.Result.MinMax[3].Res,OP2165.Upload.Result.MinMax[4].CycleCount,OP2165.Upload.Result.MinMax[4].Res,OP2165.Upload.Result.MinMax[5].CycleCount,OP2165.Upload.Result.MinMax[5].Res,OP2165.Upload.Result.MinMax[6].CycleCount,OP2165.Upload.Result.MinMax[6].Res,OP2165.Upload.Result.MinMax[7].CycleCount,OP2165.Upload.Result.MinMax[7].Res','CBQPY/2402413XPW01B/211112001A,2402413XPW01B,0,true,false,0,1,67.859,1,2.8579998,1,113.27,1,2.8555,0,0.0,0,0.0,0,0.0,0,0.0','2021-11-13 10:19:20:316,2021-11-13 10:19:20:323,2021-11-13 10:19:20:328,2021-11-13 10:19:20:336,2021-11-13 10:19:20:342,2021-11-13 10:19:20:349,2021-11-13 10:19:20:354,2021-11-13 10:19:20:358,2021-11-13 10:19:20:363,2021-11-13 10:19:20:368,2021-11-13 10:19:20:374,2021-11-13 10:19:20:378,2021-11-13 10:19:20:383,2021-11-13 10:19:20:388,2021-11-13 10:19:20:393,2021-11-13 10:19:20:398,2021-11-13 10:19:20:403,2021-11-13 10:19:20:408,2021-11-13 10:19:20:413,2021-11-13 10:19:20:418,2021-11-13 10:19:20:423,2021-11-13 10:19:20:427' 
exec p_dl_save_collection_data 'OP1305.Upload.Cmd.SNum,OP1305.Upload.Cmd.PNum,OP1305.Upload.Cmd.PalletID,OP1305.Upload.Cmd.Ok,OP1305.Upload.Cmd.Nok,OP1305.Upload.Cmd.NokCode,OP1305.Upload.Result.MinMax[0].CycleCount,OP1305.Upload.Result.MinMax[0].Res,OP1305.Upload.Result.MinMax[1].CycleCount,OP1305.Upload.Result.MinMax[1].Res,OP1305.Upload.Result.MinMax[2].CycleCount,OP1305.Upload.Result.MinMax[2].Res,OP1305.Upload.Result.MinMax[3].CycleCount,OP1305.Upload.Result.MinMax[3].Res,OP1305.Upload.Result.MinMax[4].CycleCount,OP1305.Upload.Result.MinMax[4].Res,OP1305.Upload.Result.MinMax[5].CycleCount,OP1305.Upload.Result.MinMax[5].Res,OP1305.Upload.Result.MinMax[6].CycleCount,OP1305.Upload.Result.MinMax[6].Res,OP1305.Upload.Result.MinMax[7].CycleCount,OP1305.Upload.Result.MinMax[7].Res,OP1305.Upload.Result.MinMax[8].CycleCount,OP1305.Upload.Result.MinMax[8].Res','CBQPY/2302461XKV1BA/231010019D,2302461XKV1BA,222,true,false,0,0,0.0,0,0.0,0,0.0,0,0.0,0,0.0,0,0.0,0,0.0,0,0.0,1,2.0','2023-10-10 20:09:38:824,2023-10-10 20:09:38:831,2023-10-10 20:09:38:839,2023-10-10 20:09:38:847,2023-10-10 20:09:38:851,2023-10-10 20:09:38:856,2023-10-10 20:09:38:863,2023-10-10 20:09:38:871,2023-10-10 20:09:38:877,2023-10-10 20:09:38:882,2023-10-10 20:09:38:888,2023-10-10 20:09:38:895,2023-10-10 20:09:38:902,2023-10-10 20:09:38:908,2023-10-10 20:09:38:914,2023-10-10 20:09:38:924,2023-10-10 20:09:38:932,2023-10-10 20:09:38:942,2023-10-10 20:09:38:948,2023-10-10 20:09:38:952,2023-10-10 20:09:38:958,2023-10-10 20:09:38:965,2023-10-10 20:09:38:972,2023-10-10 20:09:38:978'

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_dl_save_collection_batch`(`keys` varchar(21845), `values` varchar(21845),`delimiter` varchar(50),`tempTableName` varchar(50),`batchSql` varchar(2000))
BEGIN
    #存储过程作用：解析采集数据，批量插入数据
    declare idx int default 1;-- key值索引
    declare idx2 int default 1;-- value值索引
    declare slice varchar(21845);-- key值
    declare slice2 varchar(21845);-- value值
    declare values_sql varchar(2000) default '';-- 批量插入临时表的值
    
    -- select `keys`;
    -- select `values`;
    
    -- 删除未成功删除的临时表
    set @sql0 = concat('drop temporary table if exists ',`tempTableName`);
    prepare stmt0 from @sql0;
    execute stmt0;
    drop prepare stmt0;        
    
    -- 用动态sql创建创建临时表
    set @sql1 = concat('create temporary table ',`tempTableName`,'(variable varchar(200),variableValue varchar(200))');
     prepare stmt1 from @sql1;
     execute stmt1;
     deallocate prepare stmt1;    
    
    -- 循环解析key值、value值
    if length(`keys`) < 1 OR length(ifnull(`keys`,'')) = 0 then 
        call p_bd_sql_log('存储过程执行过程中发生异常','p_dl_save_collection_batch','参数无效');
    else
        while idx != 0 do
            set idx = locate(`delimiter`,`keys`); -- select idx;-- 取逗号下标
            set idx2 = locate(`delimiter`,`values`);-- 取逗号下标
            if idx != 0 then
                set slice = left(`keys`,idx - 1); -- select slice;
                set slice2 = left(`values`,idx2 - 1); -- select slice2;
            else
                set slice = `keys`;
                set slice2 = `values`;
            end if;    
            
            -- 拼接插入临时表的value值
            set values_sql = concat(values_sql,'(''',slice,''',''',slice2,'''),');
            
            -- 重新赋值
            set `keys` = right (`keys`, length(`keys`) - idx); -- select `keys`;
            set `values` = right (`values`, length(`values`) - idx2); -- select `values`;

        end    while;
    end if;
    
    -- 批量插入临时表
    set values_sql = left(values_sql,length(values_sql)-1);
    set @sql2 = concat('insert into ',`tempTableName`,'(variable,variableValue) values ',values_sql);
    -- select @sql2;
    prepare stmt2 from @sql2;
    execute stmt2;
    drop prepare stmt2;        
    
    -- 批量插入采集表，执行batchSql
    set @sql3 = `batchSql`;
    -- select @sql3;
    prepare stmt3 from @sql3;
    execute stmt3;
    drop prepare stmt3;
    
    -- 删除临时表
    set @sql4 = concat('drop table ',`tempTableName`);
    prepare stmt4 from @sql4;
    execute stmt4;
    drop prepare stmt4;        
    
END

sql syntax的prepare语句可以用于procedure但是不能用于function或trigger。游标也不能使用prepared statement，因为游标需要在创建时check，因此不可写动态游标。

However, a cursor cannot be used for a dynamic statement that is prepared and executed with PREPARE and EXECUTE. 
The statement for a cursor is checked at cursor creation time, so the statement cannot be dynamic.

 

官方文档：https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

网文：https://blog.csdn.net/ashic/article/details/52135717