工作中，mysql字符串排序如何做？

1.问题由来

如下图所示: 我们的需求是根据 后缀的数字排序，因为不是纯数字所以常用的解决方案例如，order by filed +1 ,或者是 order by CAST(server_id as SIGNED) desc 这种方案都是不可取的。

 

2.解题思路

   表结构: 

默认数据:

   2.1定义规范

    和客户(划重点沟通很重要)探讨之后，客户说后面的数字 最多是三个,最少2个。

    也是就 数据只会出现有 

     例1:  兴城10-1                   V
     例2:  兴城10-101-123        V

     反例1:兴城10                      X
     反例2:新城10-1-123-13      X

   2.2利用mysql 字符串拆分机制

   示例数据:兴城10-101-123

   1.获取第一个数字的位置 拆分成  兴城  和  10-101-123

   利用 find_first_int(自定义的寻找第一个数字位置函数), 和 字符串截取

CREATE DEFINER = 'root'@'localhost' FUNCTION `find_first_int`(
        pData CHAR(60)
    )
    RETURNS int(11)
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  DECLARE vPos INT DEFAULT 1;
  DECLARE vRes INT DEFAULT 0;
  DECLARE vChar INT;
  WHILE vPos <= LENGTH(pData) DO
    SET vChar = ASCII(SUBSTR(pData,vPos,1));
    IF vChar BETWEEN 48 AND 57 THEN
      RETURN vPos;
    END IF;
    SET vPos = vPos + 1;
  END WHILE;
  RETURN NULL;
END;

 select test,substring(test,find_first_int(test)) 截取后的 from `testtable`;

    

  2.把截取后的 分为 one ,tow ,three 三个字段，兴城10-101-123 对应的 onw = 10,two = 101,three = 123

select test,
LEFT(substring(test,Locate('1',test)  ),(LOCATE('-',substring(test,find_first_int(test)))-1)) one,

if(length(test)-length(replace(test,'-','')) = 1,
substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1)) 
,
left(substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1)),LOCATE( '-',substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1)))-1 )
)two,

right(substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1)),LOCATE( '-',substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1)))-1 ) three

from `testtable`;

3.最后 order by one,two,three 就可以得到想要的排序结果拉

select test
from `testtable`
order by 
LEFT(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))-1)) *1,
if(length(test)-length(replace(test,'-','')) = 1,
substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1))*1 
,
left(substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1)),LOCATE( '-',substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1)))-1 ))*1
,
right(substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1)),LOCATE( '-',substring(substring(test,find_first_int(test)  ),(LOCATE('-',substring(test,find_first_int(test)))+1)))-1 )*1
 ;

总结

工作中会遇到很多问题，沟通很重要，其次就是解题思路。

最后我准备发 获取 one ,two ,thrre 的地方小小封装一下，毕竟看上去有点  乱0 0，封装成函数会好点。