Naturel de Tri dans MySQL

Ici est une solution rapide:

SELECT alphanumeric, 
       integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric

Même fonction que posté par @plalx, mais réécrit pour MySQL:

DROP FUNCTION IF EXISTS `udf_FirstNumberPos`;
DELIMITER ;;
CREATE FUNCTION `udf_FirstNumberPos` (`instring` varchar(4000)) 
RETURNS int
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
DECLARE position int;
DECLARE tmp_position int;
SET position = 5000;
SET tmp_position = LOCATE('0', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF; 
SET tmp_position = LOCATE('1', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('2', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('3', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('4', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('5', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('6', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('7', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('8', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('9', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
IF (position = 5000) THEN RETURN 0; END IF;
RETURN position;
END
;;
DROP FUNCTION IF EXISTS `udf_NaturalSortFormat`;
DELIMITER ;;
CREATE FUNCTION `udf_NaturalSortFormat` (`instring` varchar(4000), `numberLength` int, `sameOrderChars` char(50)) 
RETURNS varchar(4000)
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
DECLARE sortString varchar(4000);
DECLARE numStartIndex int;
DECLARE numEndIndex int;
DECLARE padLength int;
DECLARE totalPadLength int;
DECLARE i int;
DECLARE sameOrderCharsLen int;
SET totalPadLength = 0;
SET instring = TRIM(instring);
SET sortString = instring;
SET numStartIndex = udf_FirstNumberPos(instring);
SET numEndIndex = 0;
SET i = 1;
SET sameOrderCharsLen = CHAR_LENGTH(sameOrderChars);
WHILE (i <= sameOrderCharsLen) DO
SET sortString = REPLACE(sortString, SUBSTRING(sameOrderChars, i, 1), ' ');
SET i = i + 1;
END WHILE;
WHILE (numStartIndex <> 0) DO
SET numStartIndex = numStartIndex + numEndIndex;
SET numEndIndex = numStartIndex;
WHILE (udf_FirstNumberPos(SUBSTRING(instring, numEndIndex, 1)) = 1) DO
SET numEndIndex = numEndIndex + 1;
END WHILE;
SET numEndIndex = numEndIndex - 1;
SET padLength = numberLength - (numEndIndex + 1 - numStartIndex);
IF padLength < 0 THEN
SET padLength = 0;
END IF;
SET sortString = INSERT(sortString, numStartIndex + totalPadLength, 0, REPEAT('0', padLength));
SET totalPadLength = totalPadLength + padLength;
SET numStartIndex = udf_FirstNumberPos(RIGHT(instring, CHAR_LENGTH(instring) - numEndIndex));
END WHILE;
RETURN sortString;
END
;;

Utilisation:

SELECT name FROM products ORDER BY udf_NaturalSortFormat(name, 10, ".")

J'ai écrit cette fonction pour MSSQL 2000 il y a un moment:

/**
* Returns a string formatted for natural sorting. This function is very useful when having to sort alpha-numeric strings.
*
* @author Alexandre Potvin Latreille (plalx)
* @param {nvarchar(4000)} string The formatted string.
* @param {int} numberLength The length each number should have (including padding). This should be the length of the longest number. Defaults to 10.
* @param {char(50)} sameOrderChars A list of characters that should have the same order. Ex: '.-/'. Defaults to empty string.
*
* @return {nvarchar(4000)} A string for natural sorting.
* Example of use: 
* 
*      SELECT Name FROM TableA ORDER BY Name
*  TableA (unordered)              TableA (ordered)
*  ------------                    ------------
*  ID  Name                    ID  Name
*  1.  A1.                 1.  A1-1.       
*  2.  A1-1.                   2.  A1.
*  3.  R1      -->         3.  R1
*  4.  R11                 4.  R11
*  5.  R2                  5.  R2
*
*  
*  As we can see, humans would expect A1., A1-1., R1, R2, R11 but that's not how SQL is sorting it.
*  We can use this function to fix this.
*
*      SELECT Name FROM TableA ORDER BY dbo.udf_NaturalSortFormat(Name, default, '.-')
*  TableA (unordered)              TableA (ordered)
*  ------------                    ------------
*  ID  Name                    ID  Name
*  1.  A1.                 1.  A1.     
*  2.  A1-1.                   2.  A1-1.
*  3.  R1      -->         3.  R1
*  4.  R11                 4.  R2
*  5.  R2                  5.  R11
*/
CREATE FUNCTION dbo.udf_NaturalSortFormat(
@string nvarchar(4000),
@numberLength int = 10,
@sameOrderChars char(50) = ''
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @sortString varchar(4000),
@numStartIndex int,
@numEndIndex int,
@padLength int,
@totalPadLength int,
@i int,
@sameOrderCharsLen int;
SELECT 
@totalPadLength = 0,
@string = RTRIM(LTRIM(@string)),
@sortString = @string,
@numStartIndex = PATINDEX('%[0-9]%', @string),
@numEndIndex = 0,
@i = 1,
@sameOrderCharsLen = LEN(@sameOrderChars);
-- Replace all char that has to have the same order by a space.
WHILE (@i <= @sameOrderCharsLen)
BEGIN
SET @sortString = REPLACE(@sortString, SUBSTRING(@sameOrderChars, @i, 1), ' ');
SET @i = @i + 1;
END
-- Pad numbers with zeros.
WHILE (@numStartIndex <> 0)
BEGIN
SET @numStartIndex = @numStartIndex + @numEndIndex;
SET @numEndIndex = @numStartIndex;
WHILE(PATINDEX('[0-9]', SUBSTRING(@string, @numEndIndex, 1)) = 1)
BEGIN
SET @numEndIndex = @numEndIndex + 1;
END
SET @numEndIndex = @numEndIndex - 1;
SET @padLength = @numberLength - (@numEndIndex + 1 - @numStartIndex);
IF @padLength < 0
BEGIN
SET @padLength = 0;
END
SET @sortString = STUFF(
@sortString,
@numStartIndex + @totalPadLength,
0,
REPLICATE('0', @padLength)
);
SET @totalPadLength = @totalPadLength + @padLength;
SET @numStartIndex = PATINDEX('%[0-9]%', RIGHT(@string, LEN(@string) - @numEndIndex));
END
RETURN @sortString;
END
GO

1. Ajouter une Clé de Tri (Rang) de votre tableau. ORDER BY rank

2. Utiliser la "Date de sortie" de la colonne. ORDER BY release_date

3. Lors de l'extraction de données à partir de SQL, faites votre objet de faire le tri, par exemple, si l'extraction dans un Ensemble, d'en faire un TreeSet, et faire de votre modèle de données mettre en œuvre Comparable et de promulguer le naturel algorithme de tri ici (le tri par insertion suffira si vous utilisez une langue sans collections) comme vous le serez en lisant les lignes de SQL, un par un, comme vous créez votre modèle et l'insérer dans la collection)

À l'ordre:

0

1

2

10

23

101

205

1000

un

aac

b

casdsadsa

css

Utiliser cette requête:

SÉLECTIONNEZ 
column_name 
À PARTIR de 
table_name 
COMMANDE PAR 
column_name REGEXP '^\d*[^\da-z&\.\' \-\"\!\@\#\$\%\^\*\(\)\;\:\\,\?\/\~\`\|\_\-]' DESC, 
column_name + 0, 
column_name; 

Une autre option est de faire le tri dans la mémoire après avoir retiré les données de mysql. Bien que ce ne sera pas la meilleure option du point de vue des performances, si vous n'êtes pas le tri des listes énormes vous devriez être bien.

Si vous jetez un oeil à Jeff post, vous pouvez trouver de nombreux algorithmes pour ce que jamais la langue vous pouvez travailler avec.
De tri pour les êtres Humains : Naturel, l'Ordre de Tri

Vous pouvez également créer de façon dynamique, la "colonne de tri" :

SELECT name, (name = '-') boolDash, (name = '0') boolZero, (name+0 > 0) boolNum 
FROM table 
ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name

De cette façon, vous pouvez créer des groupes pour trier.

Dans ma requête, je voulais que le " - " à la face de tout, puis les nombres, puis le texte. Qui pourrait en résulter quelque chose comme :

-
0    
1
2
3
4
5
10
13
19
99
102
Chair
Dog
Table
Windows

De cette façon, vous n'avez pas à maintenir la colonne de tri dans l'ordre correct lorsque vous ajoutez des données. Vous pouvez également modifier l'ordre de tri en fonction de ce que vous avez besoin.

Si vous êtes à l'aide de PHP, vous pouvez faire de la naturel de tri en php.

$keys = array();
$values = array();
foreach ($results as $index => $row) {
$key = $row['name'].'__'.$index; // Add the index to create an unique key.
$keys[] = $key;
$values[$key] = $row; 
}
natsort($keys);
$sortedValues = array(); 
foreach($keys as $index) {
$sortedValues[] = $values[$index]; 
}

J'espère que MySQL va mettre en œuvre tri naturel dans une future version, mais la demande de fonctionnalité (#1588) est ouvert depuis 2003, je ne voudrais pas retenir mon souffle.

Il y a aussi natsort. Il est destiné à être une partie d'un drupal plug-in, mais il fonctionne très autonome.