Can I set in MariaDB the collation based on a IETF language tag?

Question

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?

Answer 1

(There is no direct way to do what you hypothecate.)

Build a Stored procedure that takes "de" as an argument and constructs and executes

SELECT word FROM `words` order by word collate utf8mb4_german2_ci

It would probably involve a relatively static lookup table that maps "de" and "de-DE" to "utf8mb4_german2_ci", etc.

See the docs on defining Stored Procedures and how to use CONCAT(), PREPARE and EXECUTE in such.

Run SHOW COLLATION LIKE 'utf8mb4%'; to see which collations are available. (It varies with the MySQL/MariaDB version.)