10.7 预览功能：自然排序 - MariaDB.org

自然排序是一种按字母顺序排列字符串，同时将数字视为数值的排序方式。这种排序方式比机器更能理解人类的意图。你可以在 Windows 文件管理器中找到这个功能的例子。在那里面，文件是按自然顺序排序的。尝试创建四个文件夹：“b1”、“a11”、“a2”、“a1”。

有几种编程语言具有自然排序功能。在 PHP 中，它是内置函数 natsort；在 Python 中，它是第三方模块 natsort；在 Perl 中，它是 Sort::Naturally；在 Matlab 中，它是 sort_nat。可以通过一些变通方法（例如添加前导零）来实现相同的行为。

在 MariaDB 10.7.0 中，基于 MDEV-4742，自然排序可以通过 natural_sort_key() 函数使用。

让我们看几个例子。首先，我们来看看前面提到的 Windows 文件管理器的情况。这是我们的测试数据

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() 的值被持久存储，因为我们可能会根据你的反馈对函数实现进行更改。一旦排序顺序稳定下来，该函数将允许用于存储和索引的生成列。

如何亲自尝试 10.7 自然排序预览？

Tarball

下载二进制文件：mariadb-10.7.0-mdev-4742-natural-sort-linux-x86_64.tar.gz。

容器

使用容器 quay.io/mariadb-foundation/mariadb-devel:10.7-mdev-4742-natural-sort。

# Start the server container
docker run --rm -d -e MARIADB_USER=anel \
  -e MARIADB_PASSWORD=anel -e MARIADB_DATABASE=natsort \
  -e MARIADB_RANDOM_ROOT_PASSWORD=1 \
  --name mariadb-natsort \
   quay.io/mariadb-foundation/mariadb-devel:10.7-mdev-4742-natural-sort
10.7-mdev-4742-natural-sort: Pulling from mariadb-foundation/mariadb-devel
5297cdb712ed: Pull complete 
4a3bb3ae52c1: Pull complete 
110d0c5ac1d6: Pull complete 
Digest: sha256:8e2cbb009eeee5c5e76bc107e91cd4423a323a80e65d42d7aa745bb2cb697483
Status: Downloaded newer image for quay.io/mariadb-foundation/mariadb-devel:10.7-mdev-4742-natural-sort
21ee676ae4e33f7db43a90a902991d25ff825aefe3119f98903266bfd96f32e9

# Start the client container
docker exec -it mariadb-natsort mariadb -uanel -panel natsort
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.7.0-MariaDB-MDEV-4742-natural-sort mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [natsort]> create table t1 (c text);
Query OK, 0 rows affected (0.047 sec)

MariaDB [natsort]> insert into t1 values ('b1'),('a2'),('a11'),('a1');
Query OK, 4 rows affected (0.001 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [natsort]> select c from t1;
+------+
| c    |
+------+
| b1   |
| a2   |
| a11  |
| a1   |
+------+
MariaDB [natsort]> select * from t1 order by natural_sort_key(c);
+------+
| c    |
+------+
| a1   |
| a2   |
| a11  |
| b1   |
+------+
4 rows in set (0.000 sec)