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 1Use 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.