MySQL 按包含中英文数字字段排序

太难了，太难了，我太难了……啊……

其实就是一个“简单”的需求，分页查询，按名称排序。字段是没有规则的中英文数字任意组合，直接 order by 就会出现了10在1前面的情况，瞬间头大蒙圈。

逛了很多帖子，终于“不费吹灰之力”，写了这条SQL出来。

表呢，是这样的:

CREATE TABLE `demo` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

数据呢，是这样式的：

id|name     |
--+---------+
 1|B123     |
 2|哈哈123A   |
 3|B2A3     |
 4|京津冀A1B2C3|
 5|招财11猫8   |
 6|招财6猫8    |
 7|招财10猫3   |
 8|哈哈       |
 9|招财猫18    |
10|招财猫2     |
11|招财猫3     |

直接查呢

select * from demo order by name

查询结果

id|name     |
--+---------+
 1|B123     |
 3|B2A3     |
 4|京津冀A1B2C3|
 8|哈哈       |
 2|哈哈123A   |
 7|招财10猫3   |
 5|招财11猫8   |
 6|招财6猫8    |
 9|招财猫18    |
10|招财猫2     |
11|招财猫3     |

这个结果呢，显然是不满足提出这个问题的小姐姐的需求的，肯定会说那“招财猫18”怎么可以排在“招财猫2”前面，你这不行。

没办法，接着就是逛逛帖子，动了动我这89岁生锈的脑子，想出来个不太老道的法子:

最右的几位数字，截取下来，左补0，统一长度；左边的那一堆中英文数字的组合呢，右补0(补啥都行)，统一长度；然后两部分连接成一个新字段，再按GBK编码排下序，搞定。

至于查询脚本，就是这样式的

SELECT 
	name AS 'NAME',
	CHAR_LENGTH(name) AS 'LEN_ORI',
	CHAR_LENGTH(name) - CHAR_LENGTH(REGEXP_SUBSTR(name, '[0-9]*$')) AS 'LEN_L',
	CHAR_LENGTH(REGEXP_SUBSTR(name, '[0-9]*$')) AS 'LEN_R',
	LEFT(name,CHAR_LENGTH(name)-CHAR_LENGTH(REGEXP_SUBSTR(name, '[0-9]*$'))) AS 'NAME_L',
	REGEXP_SUBSTR(name, '[0-9]*$') AS 'NAME_R',
	RPAD(LEFT(name,CHAR_LENGTH(name)-CHAR_LENGTH(REGEXP_SUBSTR(name, '[0-9]*$'))),20,'0') AS 'NAME_L_PAD',
	LPAD(REGEXP_SUBSTR(name, '[0-9]*$'), 20, '0') AS 'NAME_R_PAD',
	CONCAT( 
		RPAD(LEFT(name,CHAR_LENGTH(name)-CHAR_LENGTH(REGEXP_SUBSTR(name, '[0-9]*$'))),20,'0'),
		LPAD(REGEXP_SUBSTR(name, '[0-9]*$')  , 20, '0')
	) AS 'NAME_FULL'
FROM demo 
ORDER BY 
CONVERT(
	CONCAT( 
		RPAD(left(name,CHAR_LENGTH(name)-CHAR_LENGTH(REGEXP_SUBSTR(name, '[0-9]*$'))),20,'0'),
		LPAD(REGEXP_SUBSTR(name, '[0-9]*$')  , 20, '0')
	)	
	USING GBK
)

有点复杂，但是成了。

NAME     |LEN_ORI|LEN_L|LEN_R|NAME_L  |NAME_R|NAME_L_PAD          |NAME_R_PAD          |NAME_FULL                               |
---------+-------+-----+-----+--------+------+--------------------+--------------------+----------------------------------------+
B123     |      4|    1|    3|B       |123   |B0000000000000000000|00000000000000000123|B000000000000000000000000000000000000123|
B2A3     |      4|    3|    1|B2A     |3     |B2A00000000000000000|00000000000000000003|B2A0000000000000000000000000000000000003|
哈哈       |      2|    2|    0|哈哈      |      |哈哈000000000000000000|00000000000000000000|哈哈00000000000000000000000000000000000000|
哈哈123A   |      6|    6|    0|哈哈123A  |      |哈哈123A00000000000000|00000000000000000000|哈哈123A0000000000000000000000000000000000|
京津冀A1B2C3|      9|    8|    1|京津冀A1B2C|3     |京津冀A1B2C000000000000|00000000000000000003|京津冀A1B2C00000000000000000000000000000003|
招财10猫3   |      6|    5|    1|招财10猫   |3     |招财10猫000000000000000|00000000000000000003|招财10猫00000000000000000000000000000000003|
招财11猫8   |      6|    5|    1|招财11猫   |8     |招财11猫000000000000000|00000000000000000008|招财11猫00000000000000000000000000000000008|
招财6猫8    |      5|    4|    1|招财6猫    |8     |招财6猫0000000000000000|00000000000000000008|招财6猫000000000000000000000000000000000008|
招财猫2     |      4|    3|    1|招财猫     |2     |招财猫00000000000000000|00000000000000000002|招财猫0000000000000000000000000000000000002|
招财猫3     |      4|    3|    1|招财猫     |3     |招财猫00000000000000000|00000000000000000003|招财猫0000000000000000000000000000000000003|
招财猫18    |      5|    3|    2|招财猫     |18    |招财猫00000000000000000|00000000000000000018|招财猫0000000000000000000000000000000000018|

至于执行效率什么的，暂时先不考虑了，毕竟我这个表数据量少的就那么百十来条。