[Mariadb] 10.7.0 버전에 새로 생긴 natural_sort_key()

Mariadb에서 NATURAL SORT를 정렬하는 방법은 이전까지의 버전에서는 포맷이 일정한 경우 일정한 포맷의 문자를 삭제한 다음 남아 있는 부분 가지고 정렬하는 방법이 있기는 했으나
완벽하게 하는 방법은 없는 것으로 알고있었는데 Mariadb 10.7.0 버전에 새로 생긴 natural_sort_key() 내장 함수를 통해 자연 정렬을 사용할 수 있습니다.

 

몇 가지 예를 들어보면

 

create table t1 (c text);
insert into t1 values ('b1'),('a2'),('a11'),('a1');
select c from t1;
+------+
| c    |
+------+
| b1   |
| a2   |
| a11  |
| a1   |
+------+

 

기존 방식으로의 order by 정렬 결과입니다.

select c from t1 order by c;
+------+
| c    |
+------+
| a1   |
| a11  |
| a2   |
| b1   |
+------+

 

natural_sort_key()결과로 열을 래핑 하면 다음과 같이 표시됩니다.

 

select c from t1 order by natural_sort_key(c);
+------+
| c    |
+------+
| a1   |
| a2   |
| a11  |
| b1   |
+------+

 

정렬의 다른 예를 살펴보겠습니다. 이번에는 MariaDB 버전을 정렬합니다.

 

# Test data
create table t2 (c text);
insert into t2 values ('5.5.31'),('10.7.0'),('10.2.1'),('10.1.22'),('10.3.32'),('10.2.12');
select c from t2;
+---------+
| c       |
+---------+
| 5.5.31  |
| 10.7.0  |
| 10.2.1  |
| 10.1.22 |
| 10.3.32 |
| 10.2.12 |
+---------+

# Unnatural sort

select c from t2 order by c;
+---------+
| c       |
+---------+
| 10.1.22 |
| 10.2.1  |
| 10.2.12 |
| 10.3.32 |
| 10.7.0  |
| 5.5.31  |
+---------+

# Natural sort

select c from t2 order by natural_sort_key(c);
+---------+
| c       |
+---------+
| 5.5.31  |
| 10.1.22 |
| 10.2.1  |
| 10.2.12 |
| 10.3.32 |
| 10.7.0  |
+---------+

 

마지막으로 일부 IP를 정렬해 보겠습니다.

 

create table t3 (c text);
insert into t3 values ('192.167.3.1'),('192.167.1.12'),('100.200.300.400'),('100.50.60.70'),('100.8.9.9'),('127.0.0.1'),('0.0.0.0');
select * from t3;
+-----------------+
| c               |
+-----------------+
| 192.167.3.1     |
| 192.167.1.12    |
| 100.200.300.400 |
| 100.50.60.70    |
| 100.8.9.9       |
| 127.0.0.1       |
| 0.0.0.0         |
+-----------------+

# Unnatural sort

select * from t3 order by c;
+-----------------+
| c               |
+-----------------+
| 0.0.0.0         |
| 100.200.300.400 |
| 100.50.60.70    |
| 100.8.9.9       |
| 127.0.0.1       |
| 192.167.1.12    |
| 192.167.3.1     |
+-----------------+

# Natural sort

select * from t3 order by natural_sort_key(c);
+-----------------+
| c               |
+-----------------+
| 0.0.0.0         |
| 100.8.9.9       |
| 100.50.60.70    |
| 100.200.300.400 |
| 127.0.0.1       |
| 192.167.1.12    |
| 192.167.3.1     |
+-----------------+

 

주목해야 할 또 다른 사항은 생성된(가상) 열과natural_sort_key() 함께 사용할 수 있다는 것 입니다. 제한 사항이 있습니다. 값은 테이블에 영구적으로 저장할 수 없습니다.

 

create table t(c varchar(3), k varchar(3) as (natural_sort_key(c)) invisible);
show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `c` varchar(3) DEFAULT NULL,
  `k` varchar(3) GENERATED ALWAYS AS (natural_sort_key(`c`)) VIRTUAL INVISIBLE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

# Insert data

insert into t(c) values ('b1'),('a2'),('a11'),('a1');

# No sorting

select * from t;
+------+
| c    |
+------+
| b1   |
| a2   |
| a11  |
| a1   |
+------+

# Unnatural sort

select * from t order by c;
+------+
| c    |
+------+
| a1   |
| a11  |
| a2   |
| b1   |
+------+

# Natural sort

select * from t order by k;
+------+
| c    |
+------+
| a1   |
| a2   |
| a11  |
| b1   |
+------+

를 통해 생성된 열에 강제로 저장하려고 STORED하면 오류가 발생합니다.

 

create table t2(c varchar(1), k varchar(2)  as (natural_sort_key(c)) stored);
ERROR 1901 (HY000): Function or expression 'natural_sort_key()' cannot be used in the GENERATED ALWAYS AS clause of `k`

 

이것은 natural_sort_key()값이 지속적으로 저장되는 것을 방지하기 위한 순전히 인위적인 제한이며, 피드백을 기반으로 함수 구현이 변경될 것으로 예상할 수 있습니다. 정렬 순서가 안정화되는 즉시 이 함수는 저장 및 인덱싱된 생성 열에서 사용할 수 있습니다.

 

 

 

참고로 PHP에서는 natsort(), natcasesort() 함수가 같은 역할을 합니다. (PHP 4, 5, 7, 8)

+ natsort()
- https://www.php.net/manual/en/function.natsort.php
- http://docs.php.net/manual/kr/function.natsort.php

+ natcasesort()
- https://www.php.net/manual/en/function.natcasesort.php
- http://docs.php.net/manual/kr/function.natcasesort.php

$temp = [
'10.1.22',
'10.2.1',
'10.2.12',
'10.3.32',
'10.7.0',
'5.5.31',
];
print_r($temp);
natsort($temp);
print_r($temp);

↓

Array
(
    [0] => 10.1.22
    [1] => 10.2.1
    [2] => 10.2.12
    [3] => 10.3.32
    [4] => 10.7.0
    [5] => 5.5.31
)
Array
(
    [5] => 5.5.31
    [0] => 10.1.22
    [1] => 10.2.1
    [2] => 10.2.12
    [3] => 10.3.32
    [4] => 10.7.0
)