естественная сортировка в mysql

<p>есть ли элегантный способ иметь эффективную, естественную сортировку в базе данных mysql?</p><p>например, если у меня есть этот набор данных:</p><ul> <li>последняя фантазия</li> <li>final fantasy 4</li> <li>final fantasy 10</li> <li>final fantasy 12</li> <li>final fantasy 12: цепи проматии</li> <li>final fantasy adventure</li> <li>final fantasy origins</li> <li>final fantasy tactics</li> </ul><p>любое другое<strong> элегантное </strong>решение, кроме разделения названий игр на составляющие </p><ul> <li><em>title</em>: "final fantasy"</li> <li><em>number</em>: "12"</li> <li><em>подзаголовок</em>: «цепи проматии»</li> </ul><p>чтобы убедиться, что они выходят в правильном порядке? (10 после 4, а не до 2).</p><p>это - боль в a **, потому что время от времени появляется другая игра, которая нарушает этот механизм анализа названия игры (например, «warhammer 40,000», «james bond 007»)</p>

<p>я думаю, именно поэтому многие вещи отсортированы по дате выпуска.</p><p>решением может быть создание еще одного столбца в вашей таблице для «sortkey». это может быть очищенная версия заголовка, которая соответствует шаблону, который вы создаете для легкой сортировки или счетчика.</p>

<p>вот быстрое решение:</p><pre><code>select alphanumeric, integer from sorting\_test order by length(alphanumeric), alphanumeric </code></pre>

<p>просто нашел это:</p><pre><code>select names from your\_table order by games + 0 asc </code></pre><p>делает естественную сортировку, когда числа находятся впереди, может также работать для середины.</p>

<p>та же функция, что и в @plalx, но переписанная в mysql:</p><pre><code>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 ;; </code></pre><p>использование:</p><pre><code>select name from products order by udf\_naturalsortformat(name, 10, ".") </code></pre>

<p>mysql не допускает такого рода «естественную сортировку», поэтому похоже, что лучший способ получить то, что вам нужно, - это разделить ваши данные, как описано выше (отдельное поле идентификатора и т.д.), или выполнить сбой. что, выполнить сортировку на основе элемента без заголовка, индексированного элемента в вашей базе данных (дата, вставленный идентификатор в базе данных и т. д.).</p><p>выполнение сортировки за вас с помощью db почти всегда будет быстрее, чем считывание больших наборов данных на выбранный вами язык программирования и их сортировка там, так что если у вас есть какой-либо контроль над схемой db здесь, тогда посмотрите на добавление легко сортируемые поля, как описано выше, это избавит вас от хлопот и технического обслуживания в долгосрочной перспективе.</p><p>время от времени на <a href="http://bugs.mysql.com/bug.php?id=16348">дискуссионных форумах</a> и <a href="http://forums.mysql.com/read.php?10,34908,35157,quote=1">/</a> и/-/обсуждаются форумы// время от времени появляются запросы на добавление "естественной сортировки", и многие решения вращаются вокруг удаления определенных частей ваших данных и приведения их к части <code>order by</code> запрос, например.</p><pre><code>select * from table order by cast(mid(name, 6, length(c) -5) as unsigned) </code></pre><p>такого рода решение может быть почти готово для работы с вашим примером final fantasy, описанным выше, но оно не очень гибкое и вряд ли будет распространяться чисто на набор данных, включающий, скажем, «warhammer 40 000» и «james bond 007», я боюсь ,.</p>

<p>я написал эту функцию для<strong> mssql 2000 </strong>некоторое время назад:</p><pre><code>/** * 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 </code></pre>

<ol> <li><p>добавьте ключ сортировки (ранг) в вашей таблице. <code>order by rank</code></p></li> <li><p>используйте столбец «дата выпуска». <code>order by release\_date</code></p></li> <li><p>при извлечении данных из sql, заставьте ваш объект выполнять сортировку, например, если вы извлекаете в set, сделайте его treeset и сделайте вашу модель данных реализуемой comparable и примените здесь алгоритм естественной сортировки (вставка будет достаточной, если вы используете язык без коллекций), поскольку вы будете читать строки из sql одну за другой, создавая модель и вставляя ее в коллекцию)</p></li> </ol>

<p>относительно лучшего ответа от ричарда тота <a href="https://stackoverflow.com/a/12257917/4052357">https://stackoverflow.com/a/12257917/4052357</a> </p><p>не упустите строки в кодировке utf8, которые содержат 2-байтовые (или более) символы и цифры, например,.</p><pre><code>12 南新宿 </code></pre><p>использование функции mysql <code>length()</code> в <code>udf\_naturalsortformat</code> вернет длину строки в байтах и будет неправильным, вместо этого используйте <code>char\_length()</code>, которая вернет правильную длину символа.</p><p>в моем случае использование <code>length()</code> приводило к тому, что запросы никогда не выполнялись и приводили к 100% загрузке цп для mysql.</p><pre><code>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 ;; </code></pre><p>постскриптум я бы добавил это как комментарий к оригиналу, но мне не хватает репутации (пока) </p>

<p>другой вариант - выполнить сортировку в памяти после извлечения данных из mysql. хотя это не будет лучшим вариантом с точки зрения производительности, если вы не сортируете огромные списки, все будет в порядке. </p><p>если вы посмотрите на пост джеффа, вы сможете найти множество алгоритмов для любого языка, с которым вы могли бы работать . <a href="https://blog.codinghorror.com/sorting-for-humans-natural-sort-order/">сортировка для людей: естественный порядок сортировки</a> </p>

<p><strong> заказать: </strong><br /> 0<br /> 1<br /> 2<br /> 10<br /> 23<br /> 101<br /> 205<br /> 1000<br /> <br /> аас<br /> б<br /> casdsadsa<br /> css<br /></p><p><strong> используйте этот запрос: </strong></p><pre> выбрать имя\_ столбца от table\_name order by имя\_ столбца regexp '^\d * [^\da-z & \. \'\-\"\!\@\#\$ %^() ; : , ? / ~ |_-] 'desc, column_name + 0, column_name; 

если вы не хотите изобретать велосипед или испытывать головную боль с большим количеством кода, который не работает, просто используйте <a href="https://www.drupal.org/project/natsort">drupal natural sort ... просто запустите sql, поставляемый в архиве (mysql или postgre), и все , делая запрос, просто закажите, используя:... order by natsort_canon(column_name, 'natural') 

я пробовал несколько решений, но на самом деле это очень просто: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 ... / 

вы также можете динамически создать «столбец сортировки»:select name, (name = '-') booldash, (name = '0') boolzero, (name+0 > 0) boolnum from table order by booldash desc, boolzero desc, boolnum desc, (name+0), name таким образом, вы можете создавать группы для сортировки. в своем запросе я хотел поставить перед всеми знак «-», затем цифры и текст. что может привести к чему-то вроде:- 0 1 2 3 4 5 10 13 19 99 102 chair dog table windows таким образом, вам не нужно поддерживать столбец сортировки в правильном порядке при добавлении данных. вы также можете изменить порядок сортировки в зависимости от того, что вам нужно.

добавьте поле для «ключа сортировки», в котором все строки цифр заполнены нулями до фиксированной длины, а затем вместо этого выполните сортировку по этому полю.если у вас могут быть длинные строки цифр, другой метод заключается в добавлении числа цифр (фиксированной ширины, с добавлением нуля) к каждой строке цифр. например, если у вас не будет более 99 цифр подряд, тогда для «super blast 10 ultra» ключом сортировки будет «super blast 0210 ultra».

упрощенная не-udf версия лучшего ответа @ plaix/richard toth/luke hoggett, которая работает только для первого целого числа в поле,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 

если вы используете php, вы можете сделать естественную сортировку в 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]; } </code></pre><p>я надеюсь, что mysql будет реализовывать естественную сортировку в будущей версии, но запрос <a href="http://bugs.mysql.com/bug.php?id=1588">(# 1588)</a> открыт с 2003 года, так что я не задерживаю дыхание.</p>

<p>также есть <a href="http://drupalcode.org/project/natsort.git/tree/refs/heads/5.x-1.x">нацорт</a> . он предназначен быть частью плагина <a href="http://drupal.org/project/natsort">drupal</a> , но он отлично работает в автономном режиме.</p>