How to Show all Locales in MariaDB

MariaDB supports a large list of locales that are used by functions such as DAYNAME(), MONTHNAME() and DATE_FORMAT().

While these locales are available to MariaDB, by default they’re not loaded into any of the system tables, so you can’t run a query to retrieve them.

However, MariaDB provides a plugin that does load these into a table. Once the plugin has been installed, you can query that table to return the locales available in MariaDB.

Install the LOCALES Plugin

This step only needs to be done if it hasn’t been done before.

The first step in being able to view the locales is to install the LOCALES plugin. This plugin creates a LOCALES table in the INFORMATION_SCHEMA database, then populates that table with all the locales that are available in MariaDB.

Run this code to install the plugin:

INSTALL SONAME 'locales';

You can alternatively use the INSTALL PLUGIN syntax.

It’s also possible to install this plugin when the server starts up. You can do this by passing the --plugin-load or the --plugin-load-add command-line arguments to mysqld, or providing them in the relevant server option group in an option file.

View the Locales

Once the plugin has been installed, the INFORMATION_SCHEMA.LOCALES table can be queried. You can also use the SHOW LOCALES command.

The LOCALES Table

Here’s an example of querying the the INFORMATION_SCHEMA.LOCALES table:

SELECT * 
FROM INFORMATION_SCHEMA.LOCALES
LIMIT 10;

Result:

+----+-------+-------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+
| ID | NAME  | DESCRIPTION                   | MAX_MONTH_NAME_LENGTH | MAX_DAY_NAME_LENGTH | DECIMAL_POINT | THOUSAND_SEP | ERROR_MESSAGE_LANGUAGE |
+----+-------+-------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+
|  0 | en_US | English - United States       |                     9 |                   9 | .             | ,            | english                |
|  1 | en_GB | English - United Kingdom      |                     9 |                   9 | .             | ,            | english                |
|  2 | ja_JP | Japanese - Japan              |                     3 |                   3 | .             | ,            | japanese               |
|  3 | sv_SE | Swedish - Sweden              |                     9 |                   7 | ,             |              | swedish                |
|  4 | de_DE | German - Germany              |                     9 |                  10 | ,             | .            | german                 |
|  5 | fr_FR | French - France               |                     9 |                   8 | ,             |              | french                 |
|  6 | ar_AE | Arabic - United Arab Emirates |                     6 |                   8 | .             | ,            | english                |
|  7 | ar_BH | Arabic - Bahrain              |                     6 |                   8 | .             | ,            | english                |
|  8 | ar_JO | Arabic - Jordan               |                    12 |                   8 | .             | ,            | english                |
|  9 | ar_SA | Arabic - Saudi Arabia         |                    12 |                   8 | .             |              | english                |
+----+-------+-------------------------------+-----------------------+---------------------+---------------+--------------+------------------------+

The SHOW LOCALES Command

Here’s an example of running the SHOW LOCALES command:

SHOW LOCALES;

Result:

+-----+-------+-------------------------------------+------------------------+
| Id  | Name  | Description                         | Error_Message_Language |
+-----+-------+-------------------------------------+------------------------+
|   0 | en_US | English - United States             | english                |
|   1 | en_GB | English - United Kingdom            | english                |
|   2 | ja_JP | Japanese - Japan                    | japanese               |
|   3 | sv_SE | Swedish - Sweden                    | swedish                |
|   4 | de_DE | German - Germany                    | german                 |
|   5 | fr_FR | French - France                     | french                 |
|   6 | ar_AE | Arabic - United Arab Emirates       | english                |
|   7 | ar_BH | Arabic - Bahrain                    | english                |
|   8 | ar_JO | Arabic - Jordan                     | english                |
|   9 | ar_SA | Arabic - Saudi Arabia               | english                |
|  10 | ar_SY | Arabic - Syria                      | english                |
|  11 | be_BY | Belarusian - Belarus                | english                |
|  12 | bg_BG | Bulgarian - Bulgaria                | english                |
|  13 | ca_ES | Catalan - Catalan                   | english                |
|  14 | cs_CZ | Czech - Czech Republic              | czech                  |
|  15 | da_DK | Danish - Denmark                    | danish                 |
|  16 | de_AT | German - Austria                    | german                 |
|  17 | es_ES | Spanish - Spain                     | spanish                |
|  18 | et_EE | Estonian - Estonia                  | estonian               |
|  19 | eu_ES | Basque - Basque                     | english                |
|  20 | fi_FI | Finnish - Finland                   | english                |
|  21 | fo_FO | Faroese - Faroe Islands             | english                |
|  22 | gl_ES | Galician - Galician                 | english                |
|  23 | gu_IN | Gujarati - India                    | english                |
|  24 | he_IL | Hebrew - Israel                     | english                |
|  25 | hi_IN | Hindi - India                       | hindi                  |
|  26 | hr_HR | Croatian - Croatia                  | english                |
|  27 | hu_HU | Hungarian - Hungary                 | hungarian              |
|  28 | id_ID | Indonesian - Indonesia              | english                |
|  29 | is_IS | Icelandic - Iceland                 | english                |
|  30 | it_CH | Italian - Switzerland               | italian                |
|  31 | ko_KR | Korean - Korea                      | korean                 |
|  32 | lt_LT | Lithuanian - Lithuania              | english                |
|  33 | lv_LV | Latvian - Latvia                    | english                |
|  34 | mk_MK | Macedonian - FYROM                  | english                |
|  35 | mn_MN | Mongolia - Mongolian                | english                |
|  36 | ms_MY | Malay - Malaysia                    | english                |
|  37 | nb_NO | Norwegian(Bokml) - Norway           | norwegian              |
|  38 | nl_NL | Dutch - The Netherlands             | dutch                  |
|  39 | pl_PL | Polish - Poland                     | polish                 |
|  40 | pt_BR | Portuguese - Brazil                 | portuguese             |
|  41 | pt_PT | Portuguese - Portugal               | portuguese             |
|  42 | ro_RO | Romanian - Romania                  | romanian               |
|  43 | ru_RU | Russian - Russia                    | russian                |
|  44 | ru_UA | Russian - Ukraine                   | russian                |
|  45 | sk_SK | Slovak - Slovakia                   | slovak                 |
|  46 | sl_SI | Slovenian - Slovenia                | english                |
|  47 | sq_AL | Albanian - Albania                  | english                |
|  48 | sr_RS | Serbian - Serbia                    | serbian                |
|  49 | ta_IN | Tamil - India                       | english                |
|  50 | te_IN | Telugu - India                      | english                |
|  51 | th_TH | Thai - Thailand                     | english                |
|  52 | tr_TR | Turkish - Turkey                    | english                |
|  53 | uk_UA | Ukrainian - Ukraine                 | ukrainian              |
|  54 | ur_PK | Urdu - Pakistan                     | english                |
|  55 | vi_VN | Vietnamese - Vietnam                | english                |
|  56 | zh_CN | Chinese - Peoples Republic of China | english                |
|  57 | zh_TW | Chinese - Taiwan                    | english                |
|  58 | ar_DZ | Arabic - Algeria                    | english                |
|  59 | ar_EG | Arabic - Egypt                      | english                |
|  60 | ar_IN | Arabic - Iran                       | english                |
|  61 | ar_IQ | Arabic - Iraq                       | english                |
|  62 | ar_KW | Arabic - Kuwait                     | english                |
|  63 | ar_LB | Arabic - Lebanon                    | english                |
|  64 | ar_LY | Arabic - Libya                      | english                |
|  65 | ar_MA | Arabic - Morocco                    | english                |
|  66 | ar_OM | Arabic - Oman                       | english                |
|  67 | ar_QA | Arabic - Qatar                      | english                |
|  68 | ar_SD | Arabic - Sudan                      | english                |
|  69 | ar_TN | Arabic - Tunisia                    | english                |
|  70 | ar_YE | Arabic - Yemen                      | english                |
|  71 | de_BE | German - Belgium                    | german                 |
|  72 | de_CH | German - Switzerland                | german                 |
|  73 | de_LU | German - Luxembourg                 | german                 |
|  74 | en_AU | English - Australia                 | english                |
|  75 | en_CA | English - Canada                    | english                |
|  76 | en_IN | English - India                     | english                |
|  77 | en_NZ | English - New Zealand               | english                |
|  78 | en_PH | English - Philippines               | english                |
|  79 | en_ZA | English - South Africa              | english                |
|  80 | en_ZW | English - Zimbabwe                  | english                |
|  81 | es_AR | Spanish - Argentina                 | spanish                |
|  82 | es_BO | Spanish - Bolivia                   | spanish                |
|  83 | es_CL | Spanish - Chile                     | spanish                |
|  84 | es_CO | Spanish - Columbia                  | spanish                |
|  85 | es_CR | Spanish - Costa Rica                | spanish                |
|  86 | es_DO | Spanish - Dominican Republic        | spanish                |
|  87 | es_EC | Spanish - Ecuador                   | spanish                |
|  88 | es_GT | Spanish - Guatemala                 | spanish                |
|  89 | es_HN | Spanish - Honduras                  | spanish                |
|  90 | es_MX | Spanish - Mexico                    | spanish                |
|  91 | es_NI | Spanish - Nicaragua                 | spanish                |
|  92 | es_PA | Spanish - Panama                    | spanish                |
|  93 | es_PE | Spanish - Peru                      | spanish                |
|  94 | es_PR | Spanish - Puerto Rico               | spanish                |
|  95 | es_PY | Spanish - Paraguay                  | spanish                |
|  96 | es_SV | Spanish - El Salvador               | spanish                |
|  97 | es_US | Spanish - United States             | spanish                |
|  98 | es_UY | Spanish - Uruguay                   | spanish                |
|  99 | es_VE | Spanish - Venezuela                 | spanish                |
| 100 | fr_BE | French - Belgium                    | french                 |
| 101 | fr_CA | French - Canada                     | french                 |
| 102 | fr_CH | French - Switzerland                | french                 |
| 103 | fr_LU | French - Luxembourg                 | french                 |
| 104 | it_IT | Italian - Italy                     | italian                |
| 105 | nl_BE | Dutch - Belgium                     | dutch                  |
| 106 | no_NO | Norwegian - Norway                  | norwegian              |
| 107 | sv_FI | Swedish - Finland                   | swedish                |
| 108 | zh_HK | Chinese - Hong Kong SAR             | english                |
| 109 | el_GR | Greek - Greece                      | greek                  |
| 110 | rm_CH | Romansh - Switzerland               | english                |
+-----+-------+-------------------------------------+------------------------+

Uninstall the LOCALES Plugin

If for any reason, you need to uninstall the LOCALES plugin, you can do it with the following command:

UNINSTALL SONAME 'locales';