mysql中,怎样进行根据房间号排序
小白619 2019-09-09 11:24:52 mysql数据库:
房间号1-1-101、1-1-202、1-1-1003,varchar类型,
普通排序:1-1-1003、1-1-101、1-1-202
怎样按照从小到大排序,实现1-1-101、1-1-202、1-1-1003顺序。
初步步骤:
1、创建从字符串中取数字的函数,获取出数字
2、将数字转换成int类型,进行排序
问题:创建从字符串中取数字的函数时,总是报错:
CREATE FUNCTION GetNum (Varstring varchar(50))
RETURNS varchar(30)
BEGIN
DECLARE v_length INT DEFAULT 0;
DECLARE v_Tmp varchar(50) default '';
set v_length=CHAR_LENGTH(Varstring);
set global log_bin_trust_function_creators=TRUE;
WHILE v_length > 0 DO
IF (ASCII(mid(Varstring,v_length,1))>47 and ASCII(mid(Varstring,v_length,1))<58 ) THEN
set v_Tmp=concat(v_Tmp,mid(Varstring,v_length,1));
END IF;
SET v_length = v_length - 1;
END WHILE;
RETURN REVERSE(v_Tmp);
END
报错:[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
请问各位大神,有什么好的方法进行房间号的排序吗?