Sortowanie naturalne w MySQL
Czy istnieje elegancki sposób na skuteczne, naturalne sortowanie w bazie danych MySQL?
Na przykład jeśli mam ten zestaw danych:
- Final Fantasy
- Final Fantasy 4
- Final Fantasy 10
- Final Fantasy 12
- Final Fantasy 12: Chains of Promathia
- Final Fantasy Adventure
- FINAL Fantasy Origins
- Final Fantasy Tactics
Inne eleganckie rozwiązanie niż podział nazw gier na ich komponenty
- Tytuł: "Final Fantasy"
- Liczba: "12"
- Subtitle : "Chains of Promathia"
Aby upewnić się, że wychodzą w odpowiedniej kolejności? (10 po 4, nie przed 2).
Robienie tego jest uciążliwe, ponieważ co jakiś czas pojawia się kolejna gra, która łamie ten mechanizm parsowania tytułu gry (np." Warhammer 40,000","James Bond 007")
19 answers
Myślę, że dlatego wiele rzeczy jest posortowanych według daty premiery.
Rozwiązaniem może być utworzenie innej kolumny w tabeli dla "SortKey". Może to być odkażona wersja tytułu, która jest zgodna z utworzonym wzorem w celu łatwego sortowania lub licznika.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-09-30 15:44:44
Oto szybkie rozwiązanie:
SELECT alphanumeric,
integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-09-06 14:57:24
Just found this:
SELECT names FROM your_table ORDER BY games + 0 ASC
Robi sortowanie naturalne, gdy liczby są z przodu, może działać również dla środka.
Ta sama funkcja, którą napisał @plalx, ale przepisana do 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
;;
Użycie:
SELECT name FROM products ORDER BY udf_NaturalSortFormat(name, 10, ".")
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-18 13:42:26
MySQL nie pozwala na tego rodzaju "naturalne sortowanie", więc wygląda na to, że najlepszym sposobem, aby uzyskać to, czego szukasz, jest podzielenie konfiguracji danych zgodnie z opisem powyżej (oddzielne pole id, itp.), lub w przeciwnym razie, wykonaj sortowanie oparte na elemencie nie-title, zindeksowanym elemencie w db (data, wstawione id w db, itp.).
Sortowanie przez db jest prawie zawsze szybsze niż odczytywanie dużych zbiorów danych do wybranego języka programowania i sortowanie ich tam, jeśli więc masz jakąkolwiek kontrolę nad schematem db tutaj, spójrz na dodawanie łatwo posortowanych pól, jak opisano powyżej, zaoszczędzi ci to wiele kłopotów i konserwacji na dłuższą metę.
Prośby o dodanie "sortowania naturalnego" pojawiają się od czasu do czasu na błędy MySQL i forach dyskusyjnych , a wiele rozwiązań kręci się wokół usuwania określonych części danych i odlewania ich dla ORDER BY
części zapytania, np.]}
SELECT * FROM table ORDER BY CAST(mid(name, 6, LENGTH(c) -5) AS unsigned)
Takie rozwiązanie może po prostu o być wykonane do pracy na przykład Final Fantasy powyżej, ale nie jest szczególnie elastyczny i mało prawdopodobne, aby rozszerzyć czysto do zbioru danych, w tym, powiedzmy, "Warhammer 40,000" i "James Bond 007" obawiam się.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-09-30 16:02:43
Napisałem tę funkcję dla MSSQL 2000 jakiś czas temu:
/**
* 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-09-26 18:57:10
Tak więc, chociaż wiem, że znalazłeś satysfakcjonującą odpowiedź, zmagałem się z tym problemem przez jakiś czas, a my wcześniej stwierdziliśmy, że nie można tego zrobić rozsądnie dobrze w SQL i będziemy musieli użyć javascript na tablicy JSON.
Oto jak rozwiązałem to za pomocą SQL. Mam nadzieję, że jest to pomocne dla innych:
Miałem dane takie jak:
Scene 1 Scene 1A Scene 1B Scene 2A Scene 3 ... Scene 101 Scene XXA1 Scene XXA2
Właściwie nie" rzucałem " rzeczy, choć przypuszczam, że to również mogło zadziałać.
I first replaced części, które były niezmienne w danych, w tym przypadku " scena ", a następnie zrobił LPAD wyrównać rzeczy. Wydaje się, że pozwala to dość dobrze na sortowanie ciągów Alfa, jak również ponumerowanych.
Moja ORDER BY
klauzula wygląda następująco:
ORDER BY LPAD(REPLACE(`table`.`column`,'Scene ',''),10,'0')
Oczywiście nie pomaga to w oryginalnym problemie, który nie był tak jednolity - ale wyobrażam sobie, że prawdopodobnie zadziała to w przypadku wielu innych powiązanych problemów, więc umieszczanie go tam.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-11-25 22:29:40
Dodaj klucz sortowania (Rank) w tabeli.
ORDER BY rank
Użyj kolumny "Data Wydania".
ORDER BY release_date
-
Podczas ekstrakcji danych z SQL, upewnij się, że obiekt wykonuje sortowanie, na przykład, jeśli rozpakowujesz do zestawu, uczyń go drzewem i spraw, aby twój model danych zaimplementował porównywalny i wdrożył algorytm sortowania naturalnego (sortowanie wstawiania wystarczy, jeśli używasz języka bez kolekcji), ponieważ będziesz czytać wiersze z SQL jeden po drugim, gdy utworzysz swój własny zestaw danych. model i wstaw go do kolekcji)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-11-25 22:28:39
Odnośnie najlepszej odpowiedzi Richarda Totha https://stackoverflow.com/a/12257917/4052357
Uważaj na zakodowane w UTF8 ciągi znaków, które zawierają 2byte (lub więcej) znaków i cyfr, np.
12 南新宿
Użycie funkcji MySQL LENGTH()
w udf_NaturalSortFormat
zwróci długość bajtów łańcucha i będzie niepoprawne, zamiast tego użyj CHAR_LENGTH()
, która zwróci prawidłową długość znaków.
W moim przypadku użycie LENGTH()
spowodowało, że zapytania nigdy się nie skończyły i spowodowało 100% wykorzystanie procesora dla 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
;;
P. s. dodałbym to jako komentarz do oryginału, ale nie mam wystarczającej reputacji (jeszcze)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:18:20
Inną opcją jest sortowanie w pamięci po pobraniu danych z mysql. Chociaż nie będzie to najlepsza opcja z punktu widzenia wydajności, jeśli nie sortujesz dużych list, powinieneś być w porządku.
Jeśli spojrzysz na post Jeffa, znajdziesz mnóstwo algorytmów do tego, z jakim językiem możesz pracować. http://www.codinghorror.com/blog/archives/001018.html
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-09-30 15:54:00
Jeśli nie chcesz odkrywać koła na nowo lub masz ból głowy z dużą ilością kodu, który nie działa, po prostu użyj Drupal Natural Sort ... Po prostu uruchom SQL, który jest zipped (MySQL lub Postgre), i to wszystko. Przy składaniu zapytania, po prostu zamów za pomocą:
... ORDER BY natsort_canon(column_name, 'natural')
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-04 17:56:31
Dodaj pole dla "sort key", które ma wszystkie ciągi cyfr zerowo wyściełane do ustalonej długości, a następnie posortuj na tym polu.
Jeśli możesz mieć długie ciągi cyfr, inną metodą jest dodanie liczby cyfr (o stałej szerokości, wyściełane zero) do każdego ciągu cyfr. Na przykład, jeśli w wierszu nie będzie więcej niż 99 cyfr, to dla "Super Blast 10 Ultra" kluczem sortowania będzie "Super Blast 0210 Ultra".
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-10-02 04:31:08
Można również utworzyć w dynamiczny sposób "sort column":
SELECT name, (name = '-') boolDash, (name = '0') boolZero, (name+0 > 0) boolNum
FROM table
ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name
W ten sposób można tworzyć grupy do sortowania.
W moim zapytaniu, chciałem ' - ' przed wszystkim, potem liczby, potem tekst. Co może skutkować czymś w rodzaju:
-
0
1
2
3
4
5
10
13
19
99
102
Chair
Dog
Table
Windows
W ten sposób nie musisz utrzymywać kolumny sortowania w prawidłowej kolejności podczas dodawania danych. Możesz również zmienić kolejność sortowania w zależności od tego, czego potrzebujesz.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-10-17 12:35:27
Aby zamówić:
0
1
2
10
23
101
205
1000
a
aac
b
casdsadsa
css
Użyj tego zapytania:
SELECT column_name FROM table_name ORDER BY column_name REGEXP '^\d*[^\da-z&\.\' \-\"\!\@\#\$\%\^\*\(\)\;\:\\,\?\/\~\`\|\_\-]' DESC, column_name + 0, column_name;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-06-13 15:16:55
Próbowałem kilku rozwiązań, ale w rzeczywistości jest to bardzo proste:
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
...
*/
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-25 16:13:08
Jeśli używasz PHP, możesz zrobić sortowanie naturalne w 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];
}
Mam nadzieję, że MySQL zaimplementuje sortowanie naturalne w przyszłej wersji, ale feature request (#1588) jest otwarty od 2003 roku, więc nie wstrzymałbym oddechu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-03-09 22:36:50
Uproszczona wersja nie-udf najlepszej odpowiedzi @plaix / Richard Toth / Luke Hoggett, która działa tylko dla pierwszej liczby całkowitej w polu, to
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-03-02 11:33:19
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-06-20 07:58:55
Wiem, że ten temat jest starożytny, ale chyba znalazłem na to sposób:
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
Złom, który posortował nieprawidłowo następujący zestaw (jest bezużyteczny 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-12 15:43:00