mysql只保留数字排序

创建函数，复制下面代码

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); 
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

中奥
CREATE DEFINER=`root`@`localhost` FUNCTION `GetNum`(Varstring varchar(50)) RETURNS varchar(30) CHARSET utf8mb4
BEGIN 
#排序树使用函数
DECLARE v_length INT DEFAULT 0; 
DECLARE v_Tmp varchar(50) default ''; 
set v_length=CHAR_LENGTH(Varstring); 
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

保存

有可能保存报错

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)

去修改数据库配置文件

/etc/my.cnf

在mysqld下加上log_bin_trust_function_creators=1

 重新启动数据库生效

 使用