I have an application where each user account can set their preferred application language. I store those language preferences as IETF BCP47 language tag (e.g. "de", "en-US"). Now, for queries for that user, I want to sort the results based on their language preference.
Imagine me having the data AAA, TTT, OOO, ÖÖÖ in a table with utf8mb4_general_ci collation.
If I want them to be sorted for a German user, I can do
SELECT word FROM `words` order by word collate utf8mb4_german2_ci
Which would give me the order
AAA, ÖÖÖ, OOO, TTT
For an Icelandic user however, using the collation utf8mb4_icelandic_ci
I would correctly get
AAA, OOO, TTT, ÖÖÖ
Now, in my application I don't save utf8mb4_icelandic_ci
or utf8mb4_german2_ci
for that user, but I save is
or de-DE
.
Is there a way that I can send this is
or de-DE
BCP47 language tag directly to MariaDB to specify the collation instead of the collation name? Or would I have to have a mapping between the language tag and the collation name in my application to send the correct collation information for the user?