Ordinamento naturale in MySQL

C’è un modo elegante per avere un ordinamento performante e naturale in un database MySQL?

Ad esempio se ho questo set di dati:

  • Fantasia finale
  • Final Fantasy 4
  • Final Fantasy 10
  • Final Fantasy 12
  • Final Fantasy 12: Chains of Promathia
  • Final Fantasy Adventure
  • Final Fantasy Origins
  • Final Fantasy Tactics

Qualsiasi altra soluzione elegante che dividere i nomi dei giochi nei loro componenti

  • Titolo : “Final Fantasy”
  • Numero : “12”
  • Sottotitolo : “Chains of Promathia”

per assicurarsi che escano nel giusto ordine? (10 dopo 4, non prima di 2).

Fare così è un dolore nell’a ** perché ogni tanto c’è un altro gioco che rompe il meccanismo di analisi del titolo del gioco (ad esempio “Warhammer 40.000”, “James Bond 007”)

Penso che questo sia il motivo per cui molte cose sono ordinate per data di rilascio.

Una soluzione potrebbe essere quella di creare un’altra colonna nella tabella per “SortKey”. Questa potrebbe essere una versione sterilizzata del titolo che si conforma a un modello che crei per un ordinamento facile o un contatore.

Ecco una soluzione rapida:

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

Ho appena trovato questo:

 SELECT names FROM your_table ORDER BY games + 0 ASC 

Fa un ordinamento naturale quando i numeri sono in primo piano, potrebbe funzionare anche per il medio.

Stessa funzione di postato da @plalx, ​​ma riscritta su 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 ;; 

Uso:

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

MySQL non consente questo tipo di “ordinamento naturale”, quindi sembra che il modo migliore per ottenere ciò che stai cercando è dividere i tuoi dati come descritto sopra (campo ID distinto, ecc.) O non funzionante che, esegui un ordinamento basato su un elemento non title, un elemento indicizzato nel tuo db (data, id inserito nel db, ecc.).

Avere il db fai l’ordinamento per te sarà quasi sempre più rapido della lettura di set di dati di grandi dimensioni nel tuo linguaggio di programmazione di scelta e ordinamento lì, quindi se hai qualche controllo su tutto lo schema db qui, quindi guarda l’aggiunta campi facili da ordinare come descritto sopra, ti farà risparmiare un sacco di problemi e manutenzione a lungo termine.

Le richieste di aggiungere un “ordinamento naturale” emergono di volta in volta sui bug di MySQL e sui forum di discussione , e molte soluzioni ruotano attorno alla rimozione di parti specifiche dei tuoi dati e al loro casting per la parte ORDER BY della query, ad esempio

 SELECT * FROM table ORDER BY CAST(mid(name, 6, LENGTH(c) -5) AS unsigned) 

Questo tipo di soluzione potrebbe essere fatto per funzionare sul tuo esempio di Final Fantasy sopra, ma non è particolarmente flessibile e difficilmente si estenderà in modo pulito ad un set di dati che include, ad esempio, “Warhammer 40.000” e “James Bond 007”, ho paura .

Ho scritto questa funzione per MSSQL 2000 qualche tempo fa:

 /** * 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 

Quindi, mentre so che hai trovato una risposta soddisfacente, ho avuto a che fare con questo problema per un po ‘, e in precedenza avevamo determinato che non poteva essere fatto abbastanza bene in SQL e dovevamo usare javascript su un JSON array.

Ecco come l’ho risolto usando solo SQL. Spero che questo sia utile per gli altri:

Ho avuto dati come:

 Scena 1
 Scena 1A
 Scena 1B
 Scena 2A
 Scena 3
 ...
 Scena 101
 Scena XXA1
 Scena XXA2

In realtà non ho “gettato” le cose anche se suppongo che potrebbe aver funzionato.

Prima ho sostituito le parti che erano immutabili nei dati, in questo caso “Scene”, e poi ho fatto un LPAD per allineare le cose. Questo sembra consentire abbastanza bene per le stringhe alfa per ordinare correttamente e quelli numerati.

La mia clausola ORDER BY aspetto:

 ORDER BY LPAD(REPLACE(`table`.`column`,'Scene ',''),10,'0') 

Ovviamente questo non aiuta il problema originale che non era così uniforms – ma immagino che questo probabilmente funzionerebbe per molti altri problemi correlati, quindi mettilo fuori.

  1. Aggiungi una chiave di ordinamento (Classifica) nella tua tabella. ORDER BY rank

  2. Utilizzare la colonna “Data di rilascio”. ORDER BY release_date

  3. Quando estrai i dati da SQL, fai l’ordinamento, ad esempio, se estrai in un Set, crea un TreeSet e rendi il tuo modello di dati implementabile Comparable ed esegui l’algoritmo di ordinamento naturale qui (insertion sort sarà sufficiente se stai usando una lingua senza raccolte) poiché leggerete le righe da SQL una per una mentre create il modello e lo inserite nella raccolta)

Per quanto riguarda la migliore risposta da Richard Toth https://stackoverflow.com/a/12257917/4052357

Fai attenzione alle stringhe codificate UTF8 che contengono caratteri e numeri da 2 byte (es

 12 南新宿 

L’utilizzo di LENGTH() di udf_NaturalSortFormat nella funzione udf_NaturalSortFormat restituirà la lunghezza in byte della stringa e sarà errata, utilizzando invece CHAR_LENGTH() che restituirà la lunghezza corretta del carattere.

Nel mio caso l’utilizzo di LENGTH() causato l’interruzione delle query e il conseguente utilizzo della CPU al 100% per MySQL

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

ps l'avrei aggiunto come commento all'originale ma non ho abbastanza reputazione (ancora)

Un’altra opzione è fare l’ordinamento in memoria dopo aver estratto i dati da mysql. Anche se non sarà l’opzione migliore dal punto di vista delle prestazioni, se non si stanno ordinando elenchi enormi, si dovrebbe andare bene.

Se dai un’occhiata al post di Jeff, puoi trovare molti algoritmi per qualsiasi lingua con cui potresti lavorare. http://www.codinghorror.com/blog/archives/001018.html

Se non vuoi reinventare la ruota o avere un mal di testa con un sacco di codice che non funziona, basta usare Drupal Natural Sort … Basta eseguire l’SQL che viene compresso (MySQL o Postgre), e il gioco è fatto. Quando si effettua una query, è sufficiente ordinare usando:

 ... ORDER BY natsort_canon(column_name, 'natural') 

Aggiungi un campo per “chiave di ordinamento” con tutte le stringhe di cifre aggiunte a zero a una lunghezza fissa e quindi ordina in quel campo.

Se potresti avere lunghe stringhe di cifre, un altro metodo è di anteporre il numero di cifre (a larghezza fissa, a riempimento zero) a ciascuna stringa di cifre. Ad esempio, se non si hanno più di 99 cifre di seguito, per “Super Blast 10 Ultra” la chiave di ordinamento sarà “Super Blast 0210 Ultra”.

Puoi anche creare in modo dinamico la “colonna di ordinamento”:

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

In questo modo, puoi creare gruppi da ordinare.

Nella mia domanda, volevo il ‘-‘ davanti a tutto, poi i numeri, poi il testo. Quale potrebbe risultare in qualcosa come:

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

In questo modo non è necessario mantenere la colonna di ordinamento nell’ordine corretto quando si aggiungono dati. Puoi anche modificare il tuo ordinamento a seconda di ciò che ti serve.

Per ordinare:
0
1
2
10
23
101
205
1000
un
aac
B
casdsadsa
css

Usa questa query:

 SELEZIONARE 
     column_name 
 A PARTIRE DAL 
     table_name 
 ORDINATO DA
     column_name REGEXP '^ \ d * [^ \ da-z & \. \' \ - \ "\! \ @ \ # \ $ \% \ ^ \ * \ (\) \; \: \\, \? \ / \ ~ \ `\ | \ _ \ -] 'DESC, 
     nome_colonna + 0, 
     column_name;

Ho provato diverse soluzioni ma in realtà è molto semplice:

 SELECT test_column FROM test_table ORDER BY LENGTH(test_column) DESC, test_column DESC /* Result -------- value_1 value_2 value_3 value_4 value_5 value_6 value_7 value_8 value_9 value_10 value_11 value_12 value_13 value_14 value_15 ... */ 

Se usi PHP puoi fare l’ordinamento naturale in 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]; } 

Spero che MySQL implementerà l’ordinamento naturale in una versione futura, ma la richiesta di funzionalità (# 1588) è aperta dal 2003, quindi non terrei il respiro.

Una versione semplificata non-udf della migliore risposta di @ plaix / Richard Toth / Luke Hoggett, che funziona solo per il primo intero nel campo, è

 SELECT name, LEAST( IFNULL(NULLIF(LOCATE('0', name), 0), ~0), IFNULL(NULLIF(LOCATE('1', name), 0), ~0), IFNULL(NULLIF(LOCATE('2', name), 0), ~0), IFNULL(NULLIF(LOCATE('3', name), 0), ~0), IFNULL(NULLIF(LOCATE('4', name), 0), ~0), IFNULL(NULLIF(LOCATE('5', name), 0), ~0), IFNULL(NULLIF(LOCATE('6', name), 0), ~0), IFNULL(NULLIF(LOCATE('7', name), 0), ~0), IFNULL(NULLIF(LOCATE('8', name), 0), ~0), IFNULL(NULLIF(LOCATE('9', name), 0), ~0) ) AS first_int FROM table ORDER BY IF(first_int = ~0, name, CONCAT( SUBSTR(name, 1, first_int - 1), LPAD(CAST(SUBSTR(name, first_int) AS UNSIGNED), LENGTH(~0), '0'), SUBSTR(name, first_int + LENGTH(CAST(SUBSTR(name, first_int) AS UNSIGNED))) )) ASC 

Inoltre c’è natsort . È destinato a far parte di un plugin drupal , ma funziona bene stand-alone.

So che questo argomento è antico ma penso di aver trovato un modo per farlo:

 SELECT * FROM `table` ORDER BY CONCAT( GREATEST( LOCATE('1', name), LOCATE('2', name), LOCATE('3', name), LOCATE('4', name), LOCATE('5', name), LOCATE('6', name), LOCATE('7', name), LOCATE('8', name), LOCATE('9', name) ), name ) ASC 

Scrapalo, ha ordinato il seguente set in modo errato (è inutile lol):

Final Fantasy 1 Final Fantasy 2 Final Fantasy 5 Final Fantasy 7 Final Fantasy 7: Advent Children Final Fantasy 12 Final Fantasy 112 FF1 FF2