CREATE FUNCTION `sort_col`(`input` VARCHAR(50)) RETURNS VARCHAR(50) LANGUAGE SQL NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER COMMENT '' BEGIN declare a int; declare b int; declare c int; set a = cast(substr(input, 3, 3) as int); set b = cast(substr(input, 9, 3) as int); set c = cast(substr(input, 15, 3) as int); if (a <= b && b <= c) then return concat('["', a, '","', b , '","', c , '"]'); elseif (b <= a && a <= c) then return concat('["', b, '","', a , '","', c , '"]'); elseif (c <= a && a <= b) then return concat('["', c, '","', a , '","', b , '"]'); elseif (c <= b && b <= a) then return concat('["', c, '","', b , '","', a , '"]'); elseif (a <= c && c <= b) then return concat('["', a, '","', c , '","', b , '"]'); elseif(b <= c && c <= a) then return concat('["', b, '","', c , '","', a , '"]'); end if; END
写个MySQL函数,在里面拆字符串,排序。
例如可以这样:
然后就可以更新了:
(上面的函数假设了数字都是3位,不用直接用哦)
MySQL 5.7支持JSON了,把字段转成JSON也许可以。
表设计的的时候就应该保证这个值的原子性、这是设计上的失误,mysql不会为这种失误出解决方案吧
靠mysql应该没什么办法,写个脚本处理下吧。