MYSQL数据库字母数字混合字段排序

需求 mysql 同个字段中先按字母排序，再按数字排序

表的数据

select * from test;

常规的写法按编码排序查出来的结果达不到想要的

select * from test ORDER BY code;

修改后

drop function if exists f_fm_extract_number;
delimiter ;;
create function f_fm_extract_number(
  in_string VARCHAR(50)
)
returns varchar(50)
deterministic
begin
/*------------------------------------------------------------------------------
功能描述:数字截取
详细描述:
参数说明:
  in_string VARCHAR(50) -- 需要进行数字截取的参数名称
创建时间: 2023-05-17
最后修改时间:2023-05-17
修改说明：
调用例子:
	select *,
	REPLACE(`code`, f_fm_extract_number(`code`), '') as strpart, 
	CAST(f_fm_extract_number(`code`) AS UNSIGNED) as numpart 
from test
ORDER BY strpart, numpart;
------------------------------------------------------------------------------*/

  declare ctrNumber VARCHAR(50);
	declare finNumber VARCHAR(50) DEFAULT '';
	declare sChar VARCHAR(1);
  declare inti INTEGER DEFAULT 1;
	
	if(LENGTH(in_string) > 0) then
		while(inti <= LENGTH(in_string)) do
			set sChar = SUBSTRING(in_string, inti, 1);
			set ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9'); 
			if (ctrNumber > 0) then
				set finNumber = CONCAT(finNumber, sChar);
			end if;
			set inti = inti + 1;
		end while;
		return finNumber;
	else
		return '';
  end if;

end;;
delimiter ;

select *,
REPLACE(code, f_fm_extract_number(code), ‘’) as strpart,
CAST(f_fm_extract_number(code) AS UNSIGNED) as numpart
from test
ORDER BY strpart, numpart;