mysql - MySQL 中的排序/排序

Question

我在尝试按 prog_id 列对表 programs 的内容进行排序时遇到了一个小问题，该列包含以下格式的每个程序的 id:

程序编号

1.0.1、1.0.2、1.0.3、...、1.0.10、1.0.11、...、1.1.0、1.1.1 等

当我按 prog_id 排序时我得到

1.0.1、1.0.10、1.0.11、1.0.2、1.0.3 ...

就 MySQL 而言，这是正确的，但对于数据显示的顺序来说是不正确的。我尝试使用另一列 orderby，我可以在其中保存索引并按该列排序，但我必须手动输入值，而且我的表中有几千行，这需要很长时间是时候去做了。

我可以使用任何技巧让我的数据以“正确”顺序显示吗？顺便说一句，我正在使用 PHP 和 MySQL。

Answer 1

您可以将它们拆分成它们的组成部分，例如:

SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(prog_id, '.', 1),
       LENGTH(SUBSTRING_INDEX(prog_id, '.', 1 -1)) + 1),
       '.', '') AS id1,
       REPLACE(SUBSTRING(SUBSTRING_INDEX(prog_id, '.', 2),
       LENGTH(SUBSTRING_INDEX(prog_id, '.', 2 -1)) + 1),
       '.', '') AS id2,
       REPLACE(SUBSTRING(SUBSTRING_INDEX(prog_id, '.', 3),
       LENGTH(SUBSTRING_INDEX(prog_id, '.', 3 -1)) + 1),
       '.', '') AS id3
FROM programs
ORDER BY CAST(id1 AS INT(4)), CAST(id2 AS INT(4)), CAST(id3 AS INT(4))

最好的方法是像 yoda2k 所说的那样创建额外的字段，但如果您没有该访问权限，那么您可以使用上面的方法。

您可以将其封装到一个函数中，例如:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

然后做:

SELECT SPLIT_STR(prog_id, '.', 1) AS id1,
   SPLIT_STR(prog_id, '.', 2) AS id2,
   SPLIT_STR(prog_id, '.', 3) AS id3,
FROM programs
ORDER BY CAST(id1 AS INT(4)), CAST(id2 AS INT(4)), CAST(id3 AS INT(4))