MySQL 中的排序/排序

我在尝试对表格内容进行排序时遇到一些问题programs按列prog_id它以以下格式保存每个程序的 id：

prog_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。

您可以将它们分成几个组成部分，例如：

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, '');

Then do:

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))