最近小編在項目中遇到需要查詢某個工單還有多少剩餘處理時間的需求，并且要按照工作時的計算方式，對剩餘處理時間按升序排列，如果是對查詢出來的工單進行剩餘時間計算，那麼在程序中就能夠完成，但偏偏要求需要進行排序，這可就難了，因為數據表内記錄超過百萬，放在程序中處理肯定會溢出，最終還是選擇加個mysql函數，讓數據庫幫忙處理，但找遍各大論壇也沒有找到在mysql中計算工作時的方法，無奈隻能自己來寫個，在這裡分享給大家！

不說廢話，直接上代碼！

這裡要說明一下函數中使用的 frame_workingday 這個表，這是一張日曆表，記錄了哪天是工作日，跟百度搜索的萬年曆一樣的

表結構

drop function if exists getworkminute; create function getworkminute(starttime datetime, endtime datetime) returns integer begin -- 聲明變量 -- -- 最終結果 declare interval_minute integer default 0; -- 開始時間是否工作日 declare startiswork integer default 0; -- 結束時間是否工作日 declare endiswork integer default 0; -- 開始時間與結束時間之間的工作日天數，包含自身 declare workdaynum integer default 0; -- 定義上午、下午 上下班時間 -- set @monworkstart = '08:00:00'; set @monworkend = '12:00:00'; set @noonworkstart = '14:00:00'; set @noonworkend = '18:00:00'; set @startdate = date_format(starttime,'%Y-%m-%d'); set @enddate = date_format(endtime,'%Y-%m-%d'); set @timestart = date_format(starttime, '%H:%i:%s'); set @timeend = date_format(endtime, '%H:%i:%s'); -- 查詢 select count((@startdate = wdate and isworkingday = 1) or null) , count((@enddate = wdate and isworkingday = 1) or null) , count(isworkingday = 1 or null) into startiswork,endiswork,workdaynum from frame_workingday where wdate between @startdate and @enddate; -- 如果開始和結束時間是同一天，且是工作日 if @startdate = @enddate and startiswork = 1 and endiswork = 1 then set workdaynum = 0; -- 開始時間小于上午上班時間 if @timestart < @monworkstart then -- 結束時間處于上午工作時間，計算 上午上班時間與結束時間間隔 if @timeend > @monworkstart and @timeend < @monworkend then set interval_minute = interval_minute timestampdiff(minute, concat(@startdate,' ',@monworkstart), concat(@startdate,' ', @timeend)); -- 結束時間處于午休時間，計算 半天 elseif @timeend >= @monworkend and @timeend <= @noonworkstart then set interval_minute = interval_minute 240; -- 結束時間處于下午工作時間，計算 半天 下午上班時間與結束時間間隔 elseif @timeend >= @noonworkstart and @timeend <= @noonworkend then set interval_minute = interval_minute 240 timestampdiff(minute, concat(@startdate,' ',@noonworkstart), concat(@startdate,' ', @timeend)); -- 結束時間大于下午下班時間，計算 一天 elseif @timeend >= @noonworkend then set interval_minute = interval_minute 480; end if; -- 開始時間小于上午下班時間 elseif @timestart < @monworkend then -- 結束時間小于上午下班時間，計算 開始時間與結束時間間隔 if @timeend < @monworkend then set interval_minute = interval_minute timestampdiff(minute, starttime, concat(@startdate,' ', @timeend)); -- 結束時間是午休時間，計算 開始時間與上午下班時間間隔 elseif @timeend >= @monworkend and @timeend <= @noonworkstart then set interval_minute = interval_minute timestampdiff(minute, starttime, concat(@startdate,' ', @monworkend)); -- 結束時間在下午工作時間，計算 開始時間與上午下班時間間隔 下午上班時間與結束時間間隔 elseif @timeend >= @noonworkstart and @timeend <= @noonworkend then set interval_minute = interval_minute timestampdiff(minute, starttime, concat(@startdate,' ', @monworkend)) timestampdiff(minute, concat(@startdate,' ',@noonworkstart), concat(@startdate,' ', @timeend)); -- 結束時間大于下午下班時間 計算 開始時間與上午下班時間間隔 半天 elseif @timeend>= @noonworkend then set interval_minute = interval_minute timestampdiff(minute, starttime, concat(@startdate,' ', @monworkend)) 240; end if; -- 開始時間小于下午上班時間，即處于午休時間 elseif @timestart < @noonworkstart then -- 結束時間小于下午上班時間，計算 0 if @timeend < @noonworkstart then set interval_minute = interval_minute 0; -- 結束時間在下午工作時間，計算 下午上班時間與結束時間間隔 elseif @timeend >= @noonworkstart and @timeend <= @noonworkend then set interval_minute = interval_minute timestampdiff(minute, concat(@startdate,' ',@noonworkstart), concat(@startdate,' ', @timeend)); -- 結束時間大于下午下班時間 計算 半天 elseif @timeend>= @noonworkend then set interval_minute = interval_minute 240; end if; -- 開始時間小于下午下班時間 elseif @timestart < @noonworkend then -- 結束時間小于下午下班時間，計算 開始時間與結束時間間隔 if @timeend < @noonworkend then set interval_minute = interval_minute timestampdiff(minute, concat(@startdate,' ',@timestart), concat(@startdate,' ', @timeend)); -- 結束時間大于下午下班時間，計算 開始時間與下午下班時間間隔 elseif @timeend >= @noonworkend then set interval_minute = interval_minute timestampdiff(minute, concat(@startdate,' ',@timestart), concat(@startdate,' ', @noonworkend)); end if; end if; else -- 不是同一天的情況 if startiswork = 1 then -- 工作日減去1天 set workdaynum = workdaynum - 1; -- 小于上午上班時間，計算 一天 if @timestart <= @monworkstart then set interval_minute = interval_minute 480; -- 處于上午工作時間，計算 開始時間與上午下班時間間隔 半天 elseif @timestart <= @monworkend then set interval_minute = interval_minute timestampdiff(minute, starttime, concat(@startdate,' ', @monworkend)) 240; -- 處于午休區間，計算 半天 elseif @timestart <= @noonworkstart then set interval_minute = interval_minute 240; -- 處于下午工作時間，計算 開始時間與下午下班時間間隔 elseif @timestart <= @noonworkend then set interval_minute = interval_minute timestampdiff(minute, starttime, concat(@startdate,' ', @noonworkend)); end if; end if; if endiswork = 1 then -- 工作日減去1天 set workdaynum = workdaynum - 1; -- 小于上午上班時間，計算 0 if @timeend <= @monworkstart then set interval_minute = interval_minute 0; -- 處于上午工作時間，計算 上午上班時間與結束時間間隔 elseif @timeend <= @monworkend then set interval_minute = interval_minute timestampdiff(minute, concat(@enddate,' ', @monworkstart), endtime); -- 處于午休時間，計算 半天 elseif @timeend <= @noonworkstart then set interval_minute = interval_minute 240; -- 處于下午工作時間，計算 半天 下午上班時間與結束時間間隔 elseif @timeend <= @noonworkend then set interval_minute = interval_minute 240 timestampdiff(minute, concat(@enddate,' ', @noonworkstart), endtime); -- 大于下午下班時間，計算 一天 elseif @timeend > @noonworkend then set interval_minute = interval_minute 480; end if; end if; end if; -- 計算得到最終的工作分鐘數 return interval_minute workdaynum * 480; end;

我們使用4月的日曆進行測試，測試結果如下：

實測5萬5千條記錄，耗時1分10秒，當然這是在本機docker上的mysql跑的結果，本機的配置比較低，CPU為i5-8250U，mysql最大内存1G

函數在數據量少的情況下還是可以使用，多了就會占用寶貴的數據庫資源，得不償失，如果大家有更好的辦法，歡迎留言讨論！