Triage/ordonnancement dans MySQL

Question

J'ai un petit problème pour essayer de trier le contenu d'un tableau. programs par la colonne prog_id qui contient l'identifiant de chaque programme dans le format suivant :

prog_id

1.0.1, 1.0.2, 1.0.3, ..., 1.0.10, 1.0.11, ..., 1.1.0, 1.1.1, etc.

En triant par prog_id, j'obtiens

1.0.1, 1.0.10, 1.0.11, 1.0.2, 1.0.3 ...

ce qui est correct du point de vue de MySQL mais pas de l'ordre dans lequel les données doivent s'afficher. J'ai essayé d'utiliser une autre colonne, orderby dans laquelle je pourrais sauvegarder un index et ordonner par celui-ci, mais je devrais entrer les valeurs manuellement et il y a quelques milliers de lignes dans ma table, ce qui prendrait beaucoup de temps à faire.

Y a-t-il des astuces que je pourrais utiliser pour que mes données s'affichent dans le "bon" ordre ? BTW, j'utilise PHP et MySQL.

Answer 1

Vous pourriez les diviser en leurs parties constituantes comme :

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

La meilleure méthode serait de créer les champs supplémentaires comme le dit yoda2k, mais si vous n'avez pas cet accès, vous pouvez utiliser la méthode ci-dessus.

Vous pourriez encapsuler cela dans une fonction comme :

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

Alors, faites-le :

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

Answer 2

Vous pourriez utiliser 3 champs, par exemple major_version, minor_version, build_number, en faire des champs entiers et utiliser le buildin mysqls "ORDER BY major_version,minor_version,build_number" qui ordonnera les champs de la manière souhaitée.

Answer 3

Pas de solution optimale -

...ORDER BY substring_index(prog_id, '.', 1), substring_index(substring_index(prog_id, '.', 2), '.', -1), substring_index(prog_id, '.', -1)

C'est une solution étrange, mais essayez-la.

...ORDER BY INET_ATON(prog_id)