解决 mysql 查询字段中包含中文查询结果为0

最新推荐文章于 2022-08-16 19:10:46 发布

原创最新推荐文章于 2022-08-16 19:10:46 发布 · 852 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了一个MySQL存储过程的实例，用于批量查询特定日期范围内任务的完成情况。通过循环遍历日期并动态生成SQL查询语句，该过程展示了如何在MySQL中执行复杂的批处理操作。

设置 set names utf8; (如果你使用是gbk 请设置为gbk)

例如执行 sql 文件

set names utf8;
use record;
drop procedure if exists guideCal;
delimiter //
create procedure guideCal()
BEGIN
declare i int default 1;
   declare d int default 1;
   set @daypre = '2019-01-';
   set @sd = 17;
   set @se = 18;
   set d = @sd;
   while d >= @sd and d < @se do
       if d < 10 then
       SET @query_date = concat(@daypre,'0',d);
   else
       SET @query_date = concat(@daypre,d);
   end if;
       select @query_date;
       set i = 40;
       while i <= 40 do
       set @n = i;
       SET @task_name = concat('NewMain_1_1_',@n);

       SET @sql_texta = concat('select count(*) as ',@task_name,' from task where time like \'%',@query_date,'%\' and Param10=\'',@task_name,'\' and Param11=\'接受任务\'');
       select @sql_texta;
       PREPARE stmta FROM @sql_texta;
       EXECUTE stmta;
       DEALLOCATE PREPARE stmta;
       set i = i + 1;
       end while;
       set d = d + 1;
   end while;
END //
call guideCal()