mysql - 对 mysql varchar 字段中的连字符分隔值进行排序

Question

如何对 mysql varchar 字段中连字符分隔的数字进行排序。我在字段中有数字列表

700-657-1
700-657-10
700-657-2
700-657-3
700-657-4

Answer 1

如果您始终具有三部分(固定计数或您可以确定最大部分计数)值，那么您有两种选择:

• 考虑重新考虑您的架构，将数据存储在单独的列中(确切的部分应该具有一定的意义)
• 使用字符串函数解析字符串，然后对解析后的值进行排序

如何执行第二个选项的示例:

SELECT
  val,
  CAST(REPLACE(SUBSTRING(SUBSTRING_INDEX(val, '-', 1), LENGTH(SUBSTRING_INDEX(VAL, '-', 0)) + 1), '-', '') AS UNSIGNED ) AS first,
  CAST(REPLACE(SUBSTRING(SUBSTRING_INDEX(val, '-', 2), LENGTH(SUBSTRING_INDEX(VAL, '-', 1)) + 1), '-', '') AS UNSIGNED ) AS second,
  CAST(REPLACE(SUBSTRING(SUBSTRING_INDEX(val, '-', 3), LENGTH(SUBSTRING_INDEX(VAL, '-', 2)) + 1), '-', '') AS UNSIGNED ) AS third
FROM
  test
ORDER BY
  first ASC, second ASC, third ASC

如果您不想返回表达式，可以将表达式移至 ORDER BY 子句。

SQL Fiddle demo

编辑

SELECT
  val
FROM
  test
ORDER BY
  CAST(REPLACE(SUBSTRING(SUBSTRING_INDEX(val, '-', 1), LENGTH(SUBSTRING_INDEX(val, '-', 0)) + 1), '-', '') AS UNSIGNED ) ASC,
  CAST(REPLACE(SUBSTRING(SUBSTRING_INDEX(val, '-', 2), LENGTH(SUBSTRING_INDEX(val, '-', 1)) + 1), '-', '') AS UNSIGNED ) ASC,
  CAST(REPLACE(SUBSTRING(SUBSTRING_INDEX(val, '-', 3), LENGTH(SUBSTRING_INDEX(val, '-', 2)) + 1), '-', '') AS UNSIGNED ) ASC