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



  • 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?



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2