我开发了一个函数,里面有一个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
1条答案
按热度按时间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));