Show all locales in MariaDB

This article describes the method used to display all regional locales in MariaDB.

Posted on

MariaDB is a multilingual and multiregional database. This is helpful for our application internationalization application.

Some functions, such as DATE_FORMAT(), DAYNAME() and MONTHNAME(), depend on the locale of the current database for output unless you specify a locale.

Query the current locale

You can get the current locale use lc_time_names variable:

SELECT @@lc_time_names;

Output:

+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+

Show all locales

MariaDB does not support the SHOW LOCALES command. But you can enable this command by installing the locales plugin.

After installing the locales plugin, the locale will be loaded into the system table, then you can use the SHOW LOCALES command and the INFORMATION_SCHEMA.LOCALES table.

install locales plugin

If you have already installed this plugin, you can skip this step.

Run the following code to install the plugin:

INSTALL SONAME 'locales';

The plugin creates a LOCALES table in the INFORMATION_SCHEMA database and stores all locales available to this table.

Use SHOW LOCALES

SHOW LOCALES is the easiest command to view all locale settings.

SHOW LOCALES;

Output:

+-----+-------+-------------------------------------+------------------------+
| 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                |
+-----+-------+-------------------------------------+------------------------+
111 rows in set (0.003 sec)

If you don’t install the locales plugin, running SHOW LOCALES will return the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOCALES' at line 1

Use INFORMATION_SCHEMA.LOCALES table

In MariaDB, all locale settings are stored in the INFORMATION_SCHEMA.LOCALES table, so you can query this table directly.

SELECT *
FROM INFORMATION_SCHEMA.LOCALES;

Output:

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

Conclusion

This article shows a way to display all regional locales in MariaDB.