mysql workbench-函数内部错误代码:1267

kmbjn2e3  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(497)

我开发了一个函数,里面有一个if语句。函数工作正常,直到它进入if,在那里我得到错误:
错误代码:1267。操作“<=”的排序规则(utf8\u bin,隐式)和(拉丁1\u瑞典语\u ci,数字)非法混合。
如果我抑制'<=',一切正常。
我检查了表和数据库的排序,它是utf8\u-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
xqkwcwgp

xqkwcwgp1#

问题是我的时间值是比较过的。只要我把时间当作秒( TIME_TO_SEC(time value) ),错误消失。
正确的代码替换了所有条件,其中“<”、“>”、“=”、“<=”、“=>”运算符与时间值结合使用,时间为\u到\u秒。
例子:
发件人: 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));

相关问题