使用字符串和整数组合的Mysql顺序

Question

mysql表中有一列包含不同类型的值..。

A-1 A-2 B-7 AA-1 B-1 C-2

所有行中唯一常见的东西是"-“。

但是我想重新排列所有的价值观是这样的..。

A-1 A-2 AA-1 B-1 B-7 C-2

我试过几次按过程订购，例如

ORDER BY col+0

ORDER BY cast(col as unsigned)

ORDER BY length(col),col

ORDER BY CAST(col AS SIGNED) ASC

ORDER BY ABS(col)

他们中没有一个像预期的那样工作。有什么想法吗？

Answer 1

这将起作用：

ORDER BY SUBSTRING_INDEX(col, '-', 1), 
    CAST(SUBSTRING_INDEX(col, '-', -1) AS UNSIGNED)

在这里阅读有关此函数的更多信息：substring-index

但是，这将不利于性能，因为它必须为每一行生成这些子字符串，然后手动对它们进行排序。

如果使用MySQL 5.7或更高版本，则可以为它们创建虚拟列和索引：

ALTER TABLE MyTable
  ADD COLUMN col_field1 VARCHAR(2), AS (SUBSTRING_INDEX(col, '-', 1)),
  ADD COLUMN col_field2 INT UNSIGNED AS (SUBSTRING_INDEX(col, '-', -1)),
  ADD INDEX (col_field1, col_field2);

然后可以根据索引的虚拟列进行排序：

ORDER BY col_field1, col_field2