Angular Ansible Apache HTTP Server C C++ CMake Composer CSS Cypress date-fns Django Django REST Framework Eigen3 ESLint FastAPI Git GNU Make HTML htmx HTTP JavaScript Julia LaTeX MariaDB Node.js NumPy Octave pandas Perl PHP PostgreSQL Pygame Python PyTorch Qt R SQLite Tailwind CSS

SQLで文字列を自然な順序でソートする：NATURAL_SORT_KEYと正規表現、PAD関数、CASE式


NATURAL_SORT_KEY 関数の利点

  • 従来のソート方法よりも直感的で使いやすい
  • アルファベットと数字を混在した文字列を正しくソートできる
  • 文字列内の数字を正しくソートできる

NATURAL_SORT_KEY 関数の使い方

NATURAL_SORT_KEY 関数は、次のように使用します。

SELECT NATURAL_SORT_KEY(column_name)
FROM table_name;

例

次の表の version 列を NATURAL_SORT_KEY 関数を使用してソートしてみましょう。

+------------------------------+
| version |
+------------------------------+
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-305.19.1.el8_4.x86_64 |
| 4.18.0-358.el8.x86_64 |
| 4.18.0-348.12.2.el8_5.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
+------------------------------+

上記のクエリを実行すると、次のようになります。

+------------------------------+
| version |
+------------------------------+
| 4.18.0-305.19.1.el8_4.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-348.12.2.el8_5.x86_64 |
| 4.18.0-358.el8.x86_64 |
+------------------------------+

NATURAL_SORT_KEY 関数の注意点

  • NATURAL_SORT_KEY 関数は、GROUP BY や ORDER BY 句でのみ使用できます。
  • NATURAL_SORT_KEY 関数は、インデックスの作成に使用できません。
  • NATURAL_SORT_KEY 関数は、MariaDB 10.7 以降でのみ使用できます。

NATURAL_SORT_KEY 関数の詳細

NATURAL_SORT_KEY 関数の詳細については、MariaDB の公式ドキュメントを参照してください。


+------------------------------+
| version |
+------------------------------+
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-305.19.1.el8_4.x86_64 |
| 4.18.0-358.el8.x86_64 |
| 4.18.0-348.12.2.el8_5.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
+------------------------------+

+------------------------------+
| version |
+------------------------------+
| 4.18.0-305.19.1.el8_4.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-348.12.2.el8_5.x86_64 |
| 4.18.0-358.el8.x86_64 |
+------------------------------+

例 2: アルファベットと数字を混在した文字列を正しくソートする

次の表の product_name 列を NATURAL_SORT_KEY 関数を使用してソートしてみましょう。

+--------------------+
| product_name       |
+--------------------+
| A100 Laptop        |
| Z10 Smartphone     |
| X20 Tablet         |
| Y30 Camera         |
| B20 Headphones     |
+--------------------+

+--------------------+
| product_name       |
+--------------------+
| A100 Laptop        |
| B20 Headphones     |
| X20 Tablet         |
| Y30 Camera         |
| Z10 Smartphone     |
+--------------------+

例 3: NATURAL_SORT_KEY 関数を GROUP BY 句と組み合わせて使用する

次の表の category 列と price 列を NATURAL_SORT_KEY 関数と GROUP BY 句を使用してグループ化し、各グループの平均価格を計算してみましょう。

+---------+-------+
| category | price |
+---------+-------+
| Laptop  | 1000  |
| Laptop  | 1200  |
| Tablet  | 500   |
| Tablet  | 600   |
| Phone   | 800   |
| Phone   | 900   |
+---------+-------+

+---------+----------+
| category | avg_price |
+---------+----------+
| Laptop  | 1100.00  |
| Phone   | 850.00  |
| Tablet  | 550.00  |
+---------+----------+

例 4: NATURAL_SORT_KEY 関数を ORDER BY 句と組み合わせて使用する

次の表の name 列と age 列を NATURAL_SORT_KEY 関数と ORDER BY 句を使用して、名前と年齢の昇順でソートしてみましょう。

+-------+------+
| name  | age  |
+-------+------+
| John  | 30   |
| Jane  | 25   |
| Peter | 40   |
| Mary  | 28   |
| David | 35   |
+-------+------+

+-------+------+
| name  | age  |
+-------+------+
| Jane  | 25   |
| John  | 30   |
| Mary  |

NATURAL_SORT_KEY 関数の代替方法

NATURAL_SORT_KEY 関数の代替方法として、以下の方法が考えられます。

  • 正規表現を使用する

正規表現を使用して、文字列内の数字を数字として扱い、アルファベットと数字を混在した文字列を正しくソートすることができます。

SELECT
  REGEXP_REPLACE(column_name, '[0-9]+', '\1\.0') AS sorted_column
FROM table_name
ORDER BY sorted_column;

例

次の表の version 列を正規表現を使用してソートしてみましょう。

+------------------------------+
| version |
+------------------------------+
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-305.19.1.el8_4.x86_64 |
| 4.18.0-358.el8.x86_64 |
| 4.18.0-348.12.2.el8_5.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
+------------------------------+

+------------------------------+
| version |
+------------------------------+
| 4.18.0-305.19.1.el8_4.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-348.12.2.el8_5.x86_64 |
| 4.18.0-358.el8.x86_64 |
+------------------------------+

**PAD 関数を使用する**

PAD 関数を使用して、文字列の長さを揃え、文字列内の数字を数字として扱い、アルファベットと数字を混在した文字列を正しくソートすることができます。

```sql
SELECT
  PAD(column_name, 20, '0') AS sorted_column
FROM table_name
ORDER BY sorted_column;

例

次の表の version 列を PAD 関数を使用してソートしてみましょう。

+------------------------------+
| version |
+------------------------------+
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-305.19.1.el8_4.x86_64 |
| 4.18.0-358.el8.x86_64 |
| 4.18.0-348.12.2.el8_5.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
+------------------------------+

+------------------------------+
| version |
+------------------------------+
| 4.18.0-305.19.1.el8_4.x86_64 |
| 4.18.0-348.12.2.el8_5.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-348.7.1.el8_5.x86_64 |
| 4.18.0-358.el8.x86_64 |
+------------------------------+

**CASE 式を使用する**

CASE 式を使用して、文字列の先頭文字列を判断し、数字の場合は数字として扱い、アルファベットの場合はアルファベットとして