[MariaDB] ORDER BY črke, številke in potem znaki

Dobro jutro.

Evo, spet smo tu.

Tokrat me zanima, če MariaDB ponuja kak način, da bi ORDER BY razvrstil po sledečem vrstnem redu:

- najprej črke po abecedi
- potem številke od 0 do 9
- potem pa še znake

Trenutno testiram na stolpcu VARCHAR, ki ima COLLATION utf8mb4_bin, in za ORDER BY dobim sledeč rezultat:

keyword|
-------+
test   |
test'  |
test-  |
test1  |
test2  |
testa  |
testb  |

Glih kontra od tega, kar bi rada.

Super zanimivo vprašanje! Gremo lepo po vrsti.

Trenutno imaš v MariaDB stolpec VARCHAR z utf8mb4_bin collation.
utf8mb4_bin pomeni "binary" primerjava -- torej strežnik gleda surode bajte Unicode znakov, nič posebne logike za črke, številke, simbole.
Zato tudi vidiš, da na primer ' (apostrof) pride pred 1 (številko), ker v Unicode/Binary ima ' nižjo vrednost.

Ti pa želiš naslednje pravilo v ORDER BY:

Najprej črke A-Ž (normalna abeceda),

Nato številke 0-9,

Nato drugi znaki (npr. -, ', ipd.).

Tak vrstni red ni standardni vrstni red nobenega Collation-a v MariaDB -- zato ga moramo sami umetno "zgraditi" v ORDER BY.
Rešitev je: preračunati "prioriteto" za vsak vnos.


Rešitev: uporabimo CASE v ORDER BY
Lahko napišeš nekaj takega:

SELECT keyword
FROM your_table
ORDER BY
  CASE
    WHEN keyword REGEXP '^[a-zA-Z]' THEN 1  -- začne se s črko
    WHEN keyword REGEXP '^[0-9]' THEN 2      -- začne se s številko
    ELSE 3                                  -- vse ostalo (znaki ipd.)
  END,
  keyword COLLATE utf8mb4_unicode_ci;         -- potem normalno po abecedi

Kaj ta poizvedba naredi:

CASE določi "skupino":

1 za črke

2 za številke

3 za vse ostalo

Nato znotraj vsake skupine še normalno sortiraš po abecedi.

Opomba: uporabim utf8mb4_unicode_ci, da črke sortirajo normalno ne glede na binarno vrednost.

? Če želiš še bolj strogo, da NE gleda samo prvega znaka, ampak vse:
Recimo, če želiš, da:

vse kar je čisto črkovno (npr. testa) pride pred test1 -> potem moraš malo bolj zapleteno.

Takrat bi rabil več CASE pogojev ali celo dodatno pomočno kolumno (recimo tip vnosa).


sauce: ChatGPT... use it!

Khm.
Pravi način je custom collation, vse drugo bo delovalo mnogo mnogo počasneje, sploh če bo Burek implementiral to na textovnih poljih "poljubne" velikosti z miljoni na miljone vnosov.

Custom collation se naredi tako, da se definira/extenda charset v /usr/share/mysql/charsets/Index.xml (ali kjerkoli že imaš mariadb inštaliran - pazi, vseeno je mapa mysql), tako da se doda custom collation. Nato se ta custom collation naredi v LDML sintaksi (tule je snippet za delček tega).
Ampak tole ni za tiste faint of heart. Je treba znati dejansko brati (in razumeti) dokumentacijo in kompleksna navodila. 95% "programerjev" tega ne sfolga.