mysql 工作台 - 错误代码 : 1267 inside a function

Question

我开发了一个函数，里面有一个 IF 语句。该函数工作正常，直到它进入 IF 内部，我收到错误:

错误代码:1267。操作 '<=' 的排序规则 (utf8_bin,IMPLICIT) 和 (latin1_swedish_ci,NUMERIC) 的非法混合。

如果我抑制“<=”，一切都会正常。

我检查了表和数据库的排序规则，它是utf8_bin，所以已经读过的很多建议都不起作用。

有什么想法吗？

CREATE DEFINER=`root`@`localhost` FUNCTION `mutare_interval`(v_id_inspectie INT(10), v_user varchar(100),new_start_time varchar(100), new_end_time varchar(100), new_date DATE) RETURNS varchar(100) 
DETERMINISTIC
BEGIN

DECLARE v_mesaj VARCHAR(100);
DECLARE v_count VARCHAR(100);
DECLARE v_test VARCHAR(100);
DECLARE new_interval VARCHAR(100);
DECLARE old_start_time TIME;
DECLARE old_end_time TIME;
DECLARE old_date DATE;
DECLARE old_interval VARCHAR(100);
DECLARE old_user VARCHAR(100);

SET new_interval=concat ( left(new_start_time,5),'..',left(new_end_time,5),' ', new_date) COLLATE utf8_bin;

SELECT 
    start_time, end_time , start_date , CONCAT(LEFT(old_start_time, 5),
            '..',
            LEFT(old_end_time, 5),
            ' ',
            old_date) ,user INTO old_start_time, old_end_time, old_date, old_interval,old_user
FROM
    calendar
WHERE
    id_inspectie = v_id_inspectie;

SELECT 
    COUNT(*)
INTO v_count FROM
    calendar
WHERE
    user = v_user AND start_date = new_date
        AND (start_time<=new_end_time AND new_start_time<=end_time);

IF v_count=0 THEN 

-- facem update in Calendar pentru ca avem loc

UPDATE calendar SET start_time=new_start_time, end_time=new_end_time, start_date=new_date, end_date=new_date, user=v_user, 
start_date_time=CONCAT(new_date,' ',new_start_time), end_date_time=CONCAT(new_date,' ',new_end_time) WHERE id_inspectie=v_id_inspectie;

-- stergem intervalul vechi si cel nou in free time frames

DELETE FROM free_time_frames 
WHERE
    user_name = v_user
    AND LEFT(datestart , 10) = old_date
    AND (time_start<=old_end_time  AND old_start_time<=time_end);

DELETE FROM free_time_frames 
WHERE
    user_name = v_user
    AND LEFT(datestart , 10) = new_date
    AND (time_start <= new_end_time AND new_start_time <= time_end);

-- inserez intervale libere pe intervalul ramas liber (old)

INSERT INTO free_time_frames (interval_showv2,interval_show,id_activitate,durata_interval,time_start,time_end,datestart,
        dateend,dateendx,id,StartBreakLunch,EndBreakLunch,hh_name,wd,day_period,generation_time,user_name) 
        SELECT interval_showv2,interval_show,id_activitate,durata_interval,time_start,time_end,datestart,
        dateend,dateendx,id,StartBreakLunch,EndBreakLunch,hh_name,wd,day_period,generation_time,  old_user 
        FROM gth_ir.free_time_frames WHERE user_name='%ghost' AND LEFT(datestart,10)=old_date 
        AND (time_start <= old_end_time AND old_start_time <= time_end);


SET v_mesaj='Intervalul a fost modificat! ';

ELSE 

SET v_mesaj='Intervalul nu este eligibil! ';

END IF;
RETURN v_mesaj;
END

Answer 1

问题是我有比较的时间值。一旦我将时间视为秒( TIME_TO_SEC(time value) )，错误就消失了。

正确的代码替换了“<”、“>”、“=”、“<=”、“=>”运算符与 TIME_TO_SEC 时间值结合使用的所有条件。

示例:

来自:AND (start_time<=new_end_time AND new_start_time<=end_time);

致:AND (TIME_TO_SEC(start_time)<=TIME_TO_SEC(new_end_time) AND TIME_TO_SEC(new_start_time)<=TIME_TO_SEC(end_time));