MySQL의 자연 정렬
MySQL 데이터베이스에서 성능적이고 자연스러운 정렬을 수행하는 우아한 방법이 있습니까?
예를 들어이 데이터 세트가있는 경우 :
- 파이널 판타지
- 파이널 판타지 4
- 파이널 판타지 10
- 파이널 판타지 12
- 파이널 판타지 12 : 프로 마시 아의 사슬
- 파이널 판타지 어드벤처
- 파이널 판타지 오리진
- 파이널 판타지 전술
게임 이름을 구성 요소로 나누는 것보다 다른 모든 우아한 솔루션
- 제목 : "파이널 판타지"
- 번호 : "12"
- 부제 : "Promathia의 사슬"
올바른 순서로 나오는지 확인하려면? (2 이전이 아니라 4 이후 10).
그렇게하는 것은 a **에게 고통입니다. 왜냐하면 때때로 게임 제목을 파싱하는 메커니즘을 깨는 또 다른 게임이 있기 때문입니다 (예 : "Warhammer 40,000", "James Bond 007").
이것이 출시일별로 많은 것이 분류되는 이유라고 생각합니다.
해결책은 "SortKey"에 대한 테이블에 다른 열을 만드는 것입니다. 이것은 쉬운 정렬이나 카운터를 위해 만든 패턴을 따르는 제목의 정리 된 버전 일 수 있습니다.
다음은 빠른 해결책입니다.
SELECT alphanumeric,
integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
방금 발견 :
SELECT names FROM your_table ORDER BY games + 0 ASC
숫자가 앞에있을 때 자연스러운 정렬을 수행하고 중간에서도 작동 할 수 있습니다.
@plalx가 게시 한 것과 동일한 기능이지만 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
;;
용법:
SELECT name FROM products ORDER BY udf_NaturalSortFormat(name, 10, ".")
얼마 전에 MSSQL 2000 용으로이 함수를 작성했습니다 .
/**
* 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
MySQL은 이러한 종류의 "자연 정렬"을 허용하지 않으므로 위에서 설명한대로 (별도의 ID 필드 등) 데이터 설정을 분할하거나 실패하는 것이 가장 좋은 방법 인 것 같습니다. 즉, 제목이 아닌 요소, db의 인덱싱 된 요소 (날짜, db에 삽입 된 ID 등)를 기반으로 정렬을 수행합니다.
db가 정렬을 수행하도록하는 것은 대용량 데이터 세트를 선택한 프로그래밍 언어로 읽어서 정렬하는 것보다 거의 항상 빠릅니다. 따라서 여기에서 db 스키마를 제어 할 수있는 경우 추가를 살펴보십시오. 위에서 설명한대로 필드를 쉽게 분류하면 장기적으로 많은 번거 로움과 유지 관리를 줄일 수 있습니다.
"자연스러운 정렬"을 추가하라는 요청은 때때로 MySQL 버그 및 토론 포럼 에서 발생하며 많은 솔루션이 데이터의 특정 부분을 제거 ORDER BY하고 쿼리 의 일부로 캐스팅하는 것입니다.
SELECT * FROM table ORDER BY CAST(mid(name, 6, LENGTH(c) -5) AS unsigned)
이러한 종류의 솔루션은 위의 Final Fantasy 예제에서 작동하도록 만들 수 있지만 특별히 유연하지 않으며 "Warhammer 40,000"및 "James Bond 007"과 같은 데이터 세트로 깔끔하게 확장 할 가능성이 낮습니다. .
따라서 만족스러운 답을 찾았다는 것을 알고 있지만 잠시 동안이 문제로 어려움을 겪고 있었으며 이전에는 SQL에서 합리적으로 잘 수행 될 수 없다고 판단했고 JSON에서 자바 스크립트를 사용해야했습니다. 정렬.
다음은 SQL을 사용하여 해결 한 방법입니다. 다른 사람들에게 도움이되기를 바랍니다.
다음과 같은 데이터가 있습니다.
장면 1 장면 1A 장면 1B 장면 2A 장면 3 ... 장면 101 장면 XXA1 장면 XXA2
나는 그것이 또한 효과가있을 것이라고 생각하지만 실제로 물건을 "캐스트"하지는 않았다.
먼저 데이터에서 변경되지 않은 부분 (이 경우 "Scene")을 교체 한 다음 LPAD를 사용하여 정렬했습니다. 이것은 알파 문자열과 번호가 매겨진 문자열을 적절하게 정렬 할 수있게하는 것 같습니다.
내 ORDER BY조항은 다음과 같습니다.
ORDER BY LPAD(REPLACE(`table`.`column`,'Scene ',''),10,'0')
분명히 이것은 그렇게 획일적이지 않은 원래의 문제에는 도움이되지 않습니다. 그러나 나는 이것이 아마도 다른 많은 관련 문제에 대해 효과가있을 것이라고 생각합니다.
테이블에 정렬 키 (순위)를 추가합니다.
ORDER BY rank"출시일"열을 활용하십시오.
ORDER BY release_dateSQL에서 데이터를 추출 할 때 개체가 정렬을 수행하도록합니다. 예를 들어 집합으로 추출하는 경우 TreeSet으로 만들고 데이터 모델이 Comparable을 구현하고 여기에서 자연 정렬 알고리즘을 제정합니다 (사용중인 경우 삽입 정렬로 충분합니다. 컬렉션이없는 언어) 모델을 만들고 컬렉션에 삽입 할 때 SQL에서 하나씩 행을 읽게됩니다.
Richard Toth의 최고의 답변에 관하여 https://stackoverflow.com/a/12257917/4052357
2 바이트 (또는 그 이상) 문자와 숫자를 포함하는 UTF8 인코딩 문자열을주의하십시오.
12 南新宿
MySQL의 LENGTH()in udf_NaturalSortFormat함수를 사용 하면 문자열의 바이트 길이가 반환되고 잘못된 대신 CHAR_LENGTH()올바른 문자 길이를 반환하는 대신 사용 됩니다.
제 경우에는 LENGTH()쿼리가 완료되지 않고 MySQL에 대해 100 % CPU 사용량이 발생했습니다.
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
;;
추신 나는 이것을 원본에 주석으로 추가했을 것이지만 충분한 평판이 없습니다 (아직)
고정 길이로 0으로 채워지는 모든 숫자 문자열이있는 "정렬 키"필드를 추가 한 다음 대신 해당 필드에서 정렬합니다.
긴 자릿수 문자열이있는 경우 다른 방법은 각 자릿수 문자열 앞에 자릿수 (고정 너비, 0으로 채워짐)를 추가하는 것입니다. 예를 들어, 연속 된 숫자가 99 자리를 넘지 않는 경우 "Super Blast 10 Ultra"의 경우 정렬 키는 "Super Blast 0210 Ultra"가됩니다.
: 주문에
0
1
2
10
23
101
205
1,000 AAC ㄴ casdsadsa의 CSS
이 쿼리를 사용하십시오.
고르다
column_name
에서
table_name
주문
column_name REGEXP '^ \ d * [^ \ da-z & \. \'\-\ "\! \ @ \ # \ $ \ % \ ^ \ * \ (\) \; \ : \\, \? \ / \ ~ \`\ | \ _ \-] 'DESC,
column_name + 0,
column_name;
휠을 재발 명하고 싶지 않거나 작동하지 않는 많은 코드로 인해 골칫거리가된다면 Drupal Natural Sort를 사용 하세요. 압축 된 SQL (MySQL 또는 Postgre)을 실행하면됩니다. 쿼리를 작성할 때 다음을 사용하여 간단히 주문하십시오.
... ORDER BY natsort_canon(column_name, 'natural')
또 다른 옵션은 mysql에서 데이터를 가져온 후 메모리에서 정렬하는 것입니다. 성능 관점에서 볼 때 최선의 선택은 아니지만 방대한 목록을 정렬하지 않으면 괜찮습니다.
Jeff의 게시물을 살펴보면 작업중인 언어에 대한 많은 알고리즘을 찾을 수 있습니다. 인간을위한 정렬 : 자연 정렬 순서
동적 방식으로 "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
이렇게하면 정렬 할 그룹을 만들 수 있습니다.
내 쿼리에서 나는 모든 것 앞에 '-', 숫자, 텍스트를 원했다. 다음과 같은 결과가 나올 수 있습니다.
-
0
1
2
3
4
5
10
13
19
99
102
Chair
Dog
Table
Windows
이렇게하면 데이터를 추가 할 때 정렬 열을 올바른 순서로 유지할 필요가 없습니다. 필요한 항목에 따라 정렬 순서를 변경할 수도 있습니다.
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];
}
MySQL이 향후 버전에서 자연스러운 정렬을 구현하기를 바랍니다. 그러나 기능 요청 (# 1588) 은 2003 년부터 열려 있으므로 숨을 참지 않을 것입니다.
필드의 첫 번째 정수에 대해서만 작동하는 @ plaix / Richard Toth / Luke Hoggett의 최상의 응답의 단순화 된 non-udf 버전은 다음과 같습니다.
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
몇 가지 솔루션을 시도했지만 실제로는 매우 간단합니다.
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
...
*/
여기에 (그리고 중복 질문에서) 다른 많은 답변은 기본적으로 매우 구체적으로 형식이 지정된 데이터, 예를 들어 완전히 숫자이거나 고정 길이 알파벳 접두사가있는 문자열 에서만 작동 합니다. 이것은 일반적인 경우에는 작동하지 않습니다.
그것은 100 % 일반 NAT-종류의 MySQL은, 그것을 할 수 있기 때문에 무엇을 구현할 수있는 방법 정말 거기 있다는 사실 정말 필요로하는 수정 된 비교 함수 문자열의 사전 식 정렬 및 숫자 정렬 경우 사이 스위치 / 때 만남 숫자. 이러한 코드는 두 문자열 내의 숫자 부분을 인식하고 비교하기 위해 원하는 알고리즘을 구현할 수 있습니다. 그러나 불행히도 MySQL의 비교 기능은 코드 내부에 있으며 사용자가 변경할 수 없습니다.
이것은 어떤 종류의 해킹을 남깁니다. 여기서 숫자 부분이 다시 형식화되는 문자열에 대한 정렬 키 를 만들어서 표준 사전 식 정렬이 실제로 원하는 방식으로 정렬되도록합니다 .
최대 자릿수까지의 일반 정수의 경우 명백한 해결책은 모두 고정 너비가되도록 0으로 왼쪽을 채우는 것입니다. 이것은 Drupal 플러그인과 @plalx / @RichardToth의 솔루션이 취하는 접근 방식입니다. (@Christian은 다르고 훨씬 더 복잡한 솔루션을 가지고 있지만 제가 볼 수있는 이점은 없습니다).
@tye가 지적했듯이 단순히 왼쪽을 채우는 대신 각 숫자 앞에 고정 숫자 길이를 추가하여이를 개선 할 수 있습니다. 하지만 본질적으로 어색한 해킹이라는 한계를 감안하더라도 훨씬 더 개선 할 수있는 것이 있습니다. 그러나 거기에는 사전 구축 된 솔루션이없는 것 같습니다!
예를 들면 다음과 같습니다.
- 더하기 및 빼기 기호? +10 대 10 대 -10
- 소수? 8.2, 8.5, 1.006, .75
- 선행 0? 020, 030, 00000922
- 천 개의 구분 기호? "1,001 Dalmations"대 "1001 Dalmations"
- 버전 번호? MariaDB v10.3.18 대 MariaDB v10.3.3
- 아주 긴 숫자? 103,768,276,592,092,364,859,236,487,687,870,234,598.55
@tye의 메서드를 확장하여 임의의 문자열을 nat-sort 키로 변환하고 위의 모든 경우를 처리하고 합리적으로 효율적이며 전체 정렬을 유지하는 상당히 컴팩트 한 NatSortKey () 저장 함수를 만들었습니다. 순서 (두 개의 다른 문자열에 동일하게 비교되는 정렬 키가 없음). 두 번째 매개 변수는 각 문자열에서 처리되는 숫자의 수를 제한하는 데 사용할 수 있습니다 (예 : 처음 10 개의 숫자). 이는 출력이 주어진 길이에 맞는지 확인하는 데 사용할 수 있습니다.
참고 :이 두 번째 매개 변수의 주어진 값으로 생성 된 정렬 키 문자열은 매개 변수에 대해 동일한 값으로 생성 된 다른 문자열에 대해서만 정렬되어야합니다. 그렇지 않으면 올바르게 정렬되지 않을 수 있습니다!
주문시 직접 사용할 수 있습니다.
SELECT myString FROM myTable ORDER BY NatSortKey(myString,0); ### 0 means process all numbers - resulting sort key might be quite long for certain inputs
그러나 큰 테이블을 효율적으로 정렬하려면 정렬 키를 다른 열에 미리 저장하는 것이 좋습니다 (인덱스가있을 수 있음).
INSERT INTO myTable (myString,myStringNSK) VALUES (@theStringValue,NatSortKey(@theStringValue,10)), ...
...
SELECT myString FROM myTable ORDER BY myStringNSK;
[이상적으로는 다음과 같은 것을 사용하여 키 열을 계산 된 저장 열로 생성하여이 작업을 자동으로 수행합니다.
CREATE TABLE myTable (
...
myString varchar(100),
myStringNSK varchar(150) AS (NatSortKey(myString,10)) STORED,
...
KEY (myStringNSK),
...);
그러나 현재 MySQL이나 MariaDB는 계산 된 열에 저장된 함수를 허용 하지 않으므로 불행히도 아직이 작업을 수행 할 수 없습니다 .]
내 기능 은 숫자 정렬에만 영향을 미칩니다 . 당신은 모든 문장 부호를 제거하거나 양쪽 끝에 떨어져 공백을 트리밍, 또는 단일 공백으로 멀티 공백 시퀀스를 교체하는 등의 다른 정렬 정규화 일을 수행하려는 경우, 당신도 기능을 확장 할 수 있거나, 이전 또는 이후에 수행 될 수 NatSortKey()IS 데이터에 적용됩니다. ( REGEXP_REPLACE()이 목적으로 사용 하는 것이 좋습니다 ).
또한 내가 '.'라고 가정한다는 점에서 다소 앵글로 중심적입니다. 소수점은 소수점이고 ','는 천 단위 분리 자이지만, 반대로 원하거나 매개 변수로 전환 할 수 있도록하려면 쉽게 수정할 수 있어야합니다.
다른 방법으로 더 개선 할 수 있습니다. 예를 들어 현재 절대 값으로 음수를 정렬하므로 -1이 -2 앞에옵니다. 텍스트에 대한 ASC 사전 정렬을 유지하면서 숫자에 대한 DESC 정렬 순서를 지정하는 방법도 없습니다. 이 두 가지 문제는 조금 더 작업하면 해결할 수 있습니다. 시간이되면 코드를 업데이트하겠습니다.
사용중인 chaset 및 데이터 정렬에 대한 몇 가지 중요한 종속성을 포함하여 알아야 할 다른 세부 사항이 많이 있지만 SQL 코드 내의 주석 블록에 모두 넣었습니다. 직접 기능을 사용하기 전에이 내용을주의 깊게 읽으십시오!
자, 여기에 코드가 있습니다. 버그를 발견하거나 언급하지 않은 개선 사항이 있으면 의견을 통해 알려주십시오!
delimiter $$
CREATE DEFINER=CURRENT_USER FUNCTION NatSortKey (s varchar(100), n int) RETURNS varchar(350) DETERMINISTIC
BEGIN
/****
Converts numbers in the input string s into a format such that sorting results in a nat-sort.
Numbers of up to 359 digits (before the decimal point, if one is present) are supported. Sort results are undefined if the input string contains numbers longer than this.
For n>0, only the first n numbers in the input string will be converted for nat-sort (so strings that differ only after the first n numbers will not nat-sort amongst themselves).
Total sort-ordering is preserved, i.e. if s1!=s2, then NatSortKey(s1,n)!=NatSortKey(s2,n), for any given n.
Numbers may contain ',' as a thousands separator, and '.' as a decimal point. To reverse these (as appropriate for some European locales), the code would require modification.
Numbers preceded by '+' sort with numbers not preceded with either a '+' or '-' sign.
Negative numbers (preceded with '-') sort before positive numbers, but are sorted in order of ascending absolute value (so -7 sorts BEFORE -1001).
Numbers with leading zeros sort after the same number with no (or fewer) leading zeros.
Decimal-part-only numbers (like .75) are recognised, provided the decimal point is not immediately preceded by either another '.', or by a letter-type character.
Numbers with thousand separators sort after the same number without them.
Thousand separators are only recognised in numbers with no leading zeros that don't immediately follow a ',', and when they format the number correctly.
(When not recognised as a thousand separator, a ',' will instead be treated as separating two distinct numbers).
Version-number-like sequences consisting of 3 or more numbers separated by '.' are treated as distinct entities, and each component number will be nat-sorted.
The entire entity will sort after any number beginning with the first component (so e.g. 10.2.1 sorts after both 10 and 10.995, but before 11)
Note that The first number component in an entity like this is also permitted to contain thousand separators.
To achieve this, numbers within the input string are prefixed and suffixed according to the following format:
- The number is prefixed by a 2-digit base-36 number representing its length, excluding leading zeros. If there is a decimal point, this length only includes the integer part of the number.
- A 3-character suffix is appended after the number (after the decimals if present).
- The first character is a space, or a '+' sign if the number was preceded by '+'. Any preceding '+' sign is also removed from the front of the number.
- This is followed by a 2-digit base-36 number that encodes the number of leading zeros and whether the number was expressed in comma-separated form (e.g. 1,000,000.25 vs 1000000.25)
- The value of this 2-digit number is: (number of leading zeros)*2 + (1 if comma-separated, 0 otherwise)
- For version number sequences, each component number has the prefix in front of it, and the separating dots are removed.
Then there is a single suffix that consists of a ' ' or '+' character, followed by a pair base-36 digits for each number component in the sequence.
e.g. here is how some simple sample strings get converted:
'Foo055' --> 'Foo0255 02'
'Absolute zero is around -273 centigrade' --> 'Absolute zero is around -03273 00 centigrade'
'The $1,000,000 prize' --> 'The $071000000 01 prize'
'+99.74 degrees' --> '0299.74+00 degrees'
'I have 0 apples' --> 'I have 00 02 apples'
'.5 is the same value as 0000.5000' --> '00.5 00 is the same value as 00.5000 08'
'MariaDB v10.3.0018' --> 'MariaDB v02100130218 000004'
The restriction to numbers of up to 359 digits comes from the fact that the first character of the base-36 prefix MUST be a decimal digit, and so the highest permitted prefix value is '9Z' or 359 decimal.
The code could be modified to handle longer numbers by increasing the size of (both) the prefix and suffix.
A higher base could also be used (by replacing CONV() with a custom function), provided that the collation you are using sorts the "digits" of the base in the correct order, starting with 0123456789.
However, while the maximum number length may be increased this way, note that the technique this function uses is NOT applicable where strings may contain numbers of unlimited length.
The function definition does not specify the charset or collation to be used for string-type parameters or variables: The default database charset & collation at the time the function is defined will be used.
This is to make the function code more portable. However, there are some important restrictions:
- Collation is important here only when comparing (or storing) the output value from this function, but it MUST order the characters " +0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" in that order for the natural sort to work.
This is true for most collations, but not all of them, e.g. in Lithuanian 'Y' comes before 'J' (according to Wikipedia).
To adapt the function to work with such collations, replace CONV() in the function code with a custom function that emits "digits" above 9 that are characters ordered according to the collation in use.
- For efficiency, the function code uses LENGTH() rather than CHAR_LENGTH() to measure the length of strings that consist only of digits 0-9, '.', and ',' characters.
This works for any single-byte charset, as well as any charset that maps standard ASCII characters to single bytes (such as utf8 or utf8mb4).
If using a charset that maps these characters to multiple bytes (such as, e.g. utf16 or utf32), you MUST replace all instances of LENGTH() in the function definition with CHAR_LENGTH()
Length of the output:
Each number converted adds 5 characters (2 prefix + 3 suffix) to the length of the string. n is the maximum count of numbers to convert;
This parameter is provided as a means to limit the maximum output length (to input length + 5*n).
If you do not require the total-ordering property, you could edit the code to use suffixes of 1 character (space or plus) only; this would reduce the maximum output length for any given n.
Since a string of length L has at most ((L+1) DIV 2) individual numbers in it (every 2nd character a digit), for n<=0 the maximum output length is (inputlength + 5*((inputlength+1) DIV 2))
So for the current input length of 100, the maximum output length is 350.
If changing the input length, the output length must be modified according to the above formula. The DECLARE statements for x,y,r, and suf must also be modified, as the code comments indicate.
****/
DECLARE x,y varchar(100); # need to be same length as input s
DECLARE r varchar(350) DEFAULT ''; # return value: needs to be same length as return type
DECLARE suf varchar(101); # suffix for a number or version string. Must be (((inputlength+1) DIV 2)*2 + 1) chars to support version strings (e.g. '1.2.33.5'), though it's usually just 3 chars. (Max version string e.g. 1.2. ... .5 has ((length of input + 1) DIV 2) numeric components)
DECLARE i,j,k int UNSIGNED;
IF n<=0 THEN SET n := -1; END IF; # n<=0 means "process all numbers"
LOOP
SET i := REGEXP_INSTR(s,'\\d'); # find position of next digit
IF i=0 OR n=0 THEN RETURN CONCAT(r,s); END IF; # no more numbers to process -> we're done
SET n := n-1, suf := ' ';
IF i>1 THEN
IF SUBSTRING(s,i-1,1)='.' AND (i=2 OR SUBSTRING(s,i-2,1) RLIKE '[^.\p{Alphabetic}\p{Mark}\p{Decimal_Number}\p{Join_Control}]') AND (SUBSTRING(s,i) NOT RLIKE '^\\d++\\.\\d') THEN SET i:=i-1; END IF; # Allow decimal number (but not version string) to begin with a '.', provided preceding char isn't a "letter" or another '.'
IF i>1 AND SUBSTRING(s,i-1,1)='+' THEN SET suf := '+', j := i-1; ELSE SET j := i; END IF; # move any preceding '+' into the suffix, so equal numbers with and without preceding "+" signs sort together
SET r := CONCAT(r,SUBSTRING(s,1,j-1)); SET s = SUBSTRING(s,i); # add everything before the number to r and strip it from the start of s; preceding '+' is dropped (not included in either r or s)
END IF;
SET x := REGEXP_SUBSTR(s,IF(SUBSTRING(s,1,1) IN ('0','.') OR (SUBSTRING(r,-1)=',' AND suf=' '),'^\\d*+(?:\\.\\d++)*','^(?:[1-9]\\d{0,2}(?:,\\d{3}(?!\\d))*+|\\d++)(?:\\.\\d++)*+')); # capture the number + following decimals (including multiple consecutive '.<digits>' sequences)
SET s := SUBSTRING(s,LENGTH(x)+1); # NOTE: LENGTH() can be safely used instead of CHAR_LENGTH() here & below PROVIDED we're using a charset that represents digits, ',' and '.' characters using single bytes (e.g. latin1, utf8)
SET i := INSTR(x,'.');
IF i=0 THEN SET y := ''; ELSE SET y := SUBSTRING(x,i); SET x := SUBSTRING(x,1,i-1); END IF; # move any following decimals into y
SET i := LENGTH(x);
SET x := REPLACE(x,',','');
SET j := LENGTH(x);
SET x := TRIM(LEADING '0' FROM x); # strip leading zeros
SET k := LENGTH(x);
SET suf := CONCAT(suf,LPAD(CONV(LEAST((j-k)*2,1294) + IF(i=j,0,1),10,36),2,'0')); # (j-k)*2 + IF(i=j,0,1) = (count of leading zeros)*2 + (1 if there are thousands-separators, 0 otherwise) Note the first term is bounded to <= base-36 'ZY' as it must fit within 2 characters
SET i := LOCATE('.',y,2);
IF i>0 THEN # encode a version number (like 3.12.707, etc)
SET r := CONCAT(r,LPAD(CONV(LEAST(k,359),10,36),2,'0'),x); # k = count of digits in number, bounded to be <= '9Z' base-36
WHILE LENGTH(y)>0 AND n!=0 DO
IF i=0 THEN SET x := SUBSTRING(y,2); SET y := '', i := 0; ELSE SET x := SUBSTRING(y,2,i-2); SET y := SUBSTRING(y,i); SET i := LOCATE('.',y,2); END IF;
SET j := LENGTH(x);
SET x := TRIM(LEADING '0' FROM x); # strip leading zeros
SET k := LENGTH(x);
SET r := CONCAT(r,LPAD(CONV(LEAST(k,359),10,36),2,'0'),x); # k = count of digits in number, bounded to be <= '9Z' base-36
SET suf := CONCAT(suf,LPAD(CONV(LEAST((j-k)*2,1294),10,36),2,'0')); # (j-k)*2 = (count of leading zeros)*2, bounded to fit within 2 base-36 digits
SET n := n-1;
END WHILE;
SET r := CONCAT(r,y,suf);
ELSE
SET r := CONCAT(r,LPAD(CONV(LEAST(k,359),10,36),2,'0'),x,y,suf); # k = count of digits in number, bounded to be <= '9Z' base-36
END IF;
END LOOP;
END
$$
delimiter ;
Also there is natsort. It is intended to be a part of a drupal plugin, but it works fine stand-alone.
I know this topic is ancient but I think I've found a way to do this:
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
Scrap that, it sorted the following set incorrectly (It's useless 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
참고URL : https://stackoverflow.com/questions/153633/natural-sort-in-mysql
'developer tip' 카테고리의 다른 글
| 아이콘과 텍스트가있는 Android 버튼 (0) | 2020.10.20 |
|---|---|
| iOS-UIImageView-UIImage 이미지 방향 처리 방법 (0) | 2020.10.20 |
| Python 목록 회전 (0) | 2020.10.20 |
| 키를 기반으로 해시 맵 정렬 (0) | 2020.10.20 |
| Int를 Int로 나누고 Int 반환 (0) | 2020.10.19 |