NATURAL_SORT_KEY

MariaDB starting with 10.7.0

NATURAL_SORT_KEY was added in MariaDB 10.7.0.

Syntax

NATURAL_SORT_KEY(str)

Description

The NATURAL_SORT_KEY function is used for sorting that is closer to natural sorting. Strings are sorted in alphabetical order, while numbers are treated in a way such that, for example, 10 is greater than 2, whereas in other forms of sorting, 2 would be greater than 10, just like z is greater than ya.

There are multiple natural sort implementations, differing in the way they handle leading zeroes, fractions, i18n, negatives, decimals and so on.

MariaDB's implementation ignores leading zeroes when performing the sort.

You can use also use NATURAL_SORT_KEY with generated columns. The value is not stored permanently in the table. When using a generated column, the virtual column must be longer than the base column to cater for embedded numbers in the string and MDEV-24582.

Examples

ENUMs

Natural sort can be useful for sorting ENUMs:

CREATE TABLE t (
  pk INT AUTO_INCREMENT PRIMARY KEY,
  fname VARCHAR(64),
  ftype ENUM('AUDIO','VIDEO','TEXT','ARCHIVE')
);

INSERT INTO t (fname,ftype) VALUES 
  ('doc.txt','TEXT'), ('movie.mp4','VIDEO'), 
  ('song.mp3','AUDIO'), ('arch.zip','ARCHIVE');

SELECT * FROM t ORDER BY ftype;
+----+-----------+---------+
| pk | fname     | ftype   |
+----+-----------+---------+
|  3 | song.mp3  | AUDIO   |
|  2 | movie.mp4 | VIDEO   |
|  1 | doc.txt   | TEXT    |
|  4 | arch.zip  | ARCHIVE |
+----+-----------+---------+

SELECT * FROM t ORDER BY NATURAL_SORT_KEY(ftype);
+----+-----------+---------+
| pk | fname     | ftype   |
+----+-----------+---------+
|  4 | arch.zip  | ARCHIVE |
|  3 | song.mp3  | AUDIO   |
|  1 | doc.txt   | TEXT    |
|  2 | movie.mp4 | VIDEO   |
+----+-----------+---------+

Strings and Numbers

CREATE TABLE t1 (c TEXT);

INSERT INTO t1 VALUES ('b1'),('a2'),('a11'),('a1');

SELECT c FROM t1;
+------+
| c    |
+------+
| b1   |
| a2   |
| a11  |
| a1   |
+------+

SELECT c FROM t1 ORDER BY c;
+------+
| c    |
+------+
| a1   |
| a11  |
| a2   |
| b1   |
+------+

Unsorted, regular sort and natural sort:

TRUNCATE t1;

INSERT INTO t1 VALUES 
  ('5.5.31'),('10.7.0'),('10.2.1'),
  ('10.1.22'),('10.3.32'),('10.2.12');

SELECT c FROM t1;
+---------+
| c       |
+---------+
| 5.5.31  |
| 10.7.0  |
| 10.2.1  |
| 10.1.22 |
| 10.3.32 |
| 10.2.12 |
+---------+

SELECT c FROM t1 ORDER BY c;
+---------+
| c       |
+---------+
| 10.1.22 |
| 10.2.1  |
| 10.2.12 |
| 10.3.32 |
| 10.7.0  |
| 5.5.31  |
+---------+

SELECT c FROM t1 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  |
+---------+

IPs

Sorting IPs, unsorted, regular sort and natural sort::

TRUNCATE t1;

INSERT INTO t1 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 c FROM t1;
+-----------------+
| 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         |
+-----------------+

SELECT c FROM t1 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     |
+-----------------+

SELECT c FROM t1 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     |
+-----------------+

Generated Columns

Using with a generated column:

CREATE TABLE t(c VARCHAR(3), k VARCHAR(4) AS (NATURAL_SORT_KEY(c)) INVISIBLE);

INSERT INTO t(c) VALUES ('b1'),('a2'),('a11'),('a10');

SELECT * FROM t ORDER by k;
+------+
| c    |
+------+
| a2   |
| a10  |
| a11  |
| b1   |
+------+

Note that if the virtual column is not longer, results may not be as expected:

CREATE TABLE t2(c VARCHAR(3), k VARCHAR(3) AS (NATURAL_SORT_KEY(c)) INVISIBLE);

INSERT INTO t2(c) VALUES ('b1'),('a2'),('a11'),('a10');

SELECT * FROM t2 ORDER by k;
+------+
| c    |
+------+
| a2   |
| a11  |
| a10  |
| b1   |
+------+

Leading Zeroes

Ignoring leading zeroes can lead to undesirable results in certain contexts. For example:

CREATE TABLE t3 (a VARCHAR(4));

INSERT INTO t3 VALUES 
  ('a1'), ('a001'), ('a10'), ('a001'), ('a10'), 
  ('a01'), ('a01'), ('a01b'), ('a01b'), ('a1');

SELECT a FROM t3 ORDER BY a;
+------+
| a    |
+------+
| a001 |
| a001 |
| a01  |
| a01  |
| a01b |
| a01b |
| a1   |
| a1   |
| a10  |
| a10  |
+------+
10 rows in set (0.000 sec)

SELECT a FROM t3 ORDER BY NATURAL_SORT_KEY(a);
+------+
| a    |
+------+
| a1   |
| a01  |
| a01  |
| a001 |
| a001 |
| a1   |
| a01b |
| a01b |
| a10  |
| a10  |
+------+

This may not be what we were hoping for in a 'natural' sort. A workaround is to sort by both NATURAL_SORT_KEY and regular sort.

SELECT a FROM t3 ORDER BY NATURAL_SORT_KEY(a), a;
+------+
| a    |
+------+
| a001 |
| a001 |
| a01  |
| a01  |
| a1   |
| a1   |
| a01b |
| a01b |
| a10  |
| a10  |
+------+

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.