1

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?

1
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Aug 31, 2021 at 0:02

1 Answer 1

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.)

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.