在本教學中，您將學習如何使用MySQL DAYNAME函數來獲取給定日期的工作日的名稱。
MySQL DAYNAME函數介紹
MySQL DAYNAME函數返回指定日期的工作日的名稱。 以下說明了DAYNAME函數的語法：
DAYNAME(date);
DAYNAME函數接受1個參數，該參數是要獲取其工作日名稱的日期。
如果日期為NULL或無效，例如2017-02-30，DAYNAME函數將返回NULL。
MySQL DAYNAME函數示例
以下示例將返回2018年1月1日的工作日名稱。
mysql> SELECT DAYNAME('2018-01-01') dayname;
+---------+
| dayname |
+---------+
| Monday |
+---------+
1 row in set
默認情況下，MySQL返回由lc_time_names系統變數控制的語言中的工作日的名稱，查詢當前lc_time_names變數設置的值，如下 -
mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US |
+-----------------+
1 row in set
您可以看到，目前區域設置設置為en_US。
要在特定區域設置中獲取日期名稱一個工作日，您需要更改lc_time_names變數的值。 例如，以下語句將語言環境設置為中文：
mysql> SET @@lc_time_names = 'zh_CN';
Query OK, 0 rows affected
現在，我們來查詢2018年1月1日的工作日名稱：
mysql> SELECT DAYNAME('2018-01-01') dayname;
+---------+
| dayname |
+---------+
| 星期一 |
+---------+
1 row in set
您可以看到，工作日名稱已更改為中文：星期一。
下表顯示了MySQL所支持的lc_time_names系統變數的有效語言環境的值：
| 國家 | 編/代碼 |
|---|---|
| Albanian – Albania | sq_AL |
| Arabic – Algeria | ar_DZ |
| Arabic – Bahrain | ar_BH |
| Arabic – Egypt | ar_EG |
| Arabic – India | ar_IN |
| Arabic – Iraq | ar_IQ |
| Arabic – Jordan | ar_JO |
| Arabic – Kuwait | ar_KW |
| Arabic – Lebanon | ar_LB |
| Arabic – Libya | ar_LY |
| Arabic – Morocco | ar_MA |
| Arabic – Oman | ar_OM |
| Arabic – Qatar | ar_QA |
| Arabic – Saudi Arabia | ar_SA |
| Arabic – Sudan | ar_SD |
| Arabic – Syria | ar_SY |
| Arabic – Tunisia | ar_TN |
| Arabic – United Arab Emirates | ar_AE |
| Arabic – Yemen | ar_YE |
| Basque – Basque | eu_ES |
| Belarusian – Belarus | be_BY |
| Bulgarian – Bulgaria | bg_BG |
| Catalan – Spain | ca_ES |
| Chinese – China | zh_CN |
| Chinese – Hong Kong | zh_HK |
| Chinese – Taiwan Province of China | zh_TW |
| Croatian – Croatia | hr_HR |
| Czech – Czech Republic | cs_CZ |
| Danish – Denmark | da_DK |
| Dutch – Belgium | nl_BE |
| Dutch – The Netherlands | nl_NL |
| English – Australia | en_AU |
| English – Canada | en_CA |
| English – India | en_IN |
| English – New Zealand | en_NZ |
| English – Philippines | en_PH |
| English – South Africa | en_ZA |
| English – United Kingdom | en_GB |
| English – United States | en_US |
| English – Zimbabwe | en_ZW |
| Estonian – Estonia | et_EE |
| Faroese – Faroe Islands | fo_FO |
| Finnish – Finland | fi_FI |
| French – Belgium | fr_BE |
| French – Canada | fr_CA |
| French – France | fr_FR |
| French – Luxembourg | fr_LU |
| French – Switzerland | fr_CH |
| Galician – Spain | gl_ES |
| German – Austria | de_AT |
| German – Belgium | de_BE |
| German – Germany | de_DE |
| German – Luxembourg | de_LU |
| German – Switzerland | de_CH |
| Greek – Greece | el_GR |
| Gujarati – India | gu_IN |
| Hebrew – Israel | he_IL |
| Hindi – India | hi_IN |
| Hungarian – Hungary | hu_HU |
| Icelandic – Iceland | is_IS |
| Indonesian – Indonesia | id_ID |
| Italian – Italy | it_IT |
| Italian – Switzerland | it_CH |
| Japanese – Japan | ja_JP |
| Korean – Republic of Korea | ko_KR |
| Latvian – Latvia | lv_LV |
| Lithuanian – Lithuania | lt_LT |
| Macedonian – FYROM | mk_MK |
| Malay – Malaysia | ms_MY |
| Mongolia – Mongolian | mn_MN |
| Norwegian – Norway | no_NO |
| Norwegian(Bokmål) – Norway | nb_NO |
| Polish – Poland | pl_PL |
| Portugese – Brazil | pt_BR |
| Portugese – Portugal | pt_PT |
| Romanian – Romania | ro_RO |
| Russian – Russia | ru_RU |
| Russian – Ukraine | ru_UA |
| Serbian – Yugoslavia | sr_RS |
| Slovak – Slovakia | sk_SK |
| Slovenian – Slovenia | sl_SI |
| Spanish – Argentina | es_AR |
| Spanish – Bolivia | es_BO |
| Spanish – Chile | es_CL |
| Spanish – Columbia | es_CO |
| Spanish – Costa Rica | es_CR |
| Spanish – Dominican Republic | es_DO |
| Spanish – Ecuador | es_EC |
| Spanish – El Salvador | es_SV |
| Spanish – Guatemala | es_GT |
| Spanish – Honduras | es_HN |
| Spanish – Mexico | es_MX |
| Spanish – Nicaragua | es_NI |
| Spanish – Panama | es_PA |
| Spanish – Paraguay | es_PY |
| Spanish – Peru | es_PE |
| Spanish – Puerto Rico | es_PR |
| Spanish – Spain | es_ES |
| Spanish – United States | es_US |
| Spanish – Uruguay | es_UY |
| Spanish – Venezuela | es_VE |
| Swedish – Finland | sv_FI |
| Swedish – Sweden | sv_SE |
| Tamil – India | ta_IN |
| Telugu – India | te_IN |
| Thai – Thailand | th_TH |
| Turkish – Turkey | tr_TR |
| Ukrainian – Ukraine | uk_UA |
| Urdu – Pakistan | ur_PK |
| Vietnamese – Viet Nam vi_VN |
請參閱示例資料庫(zaixiand)中的以下orders表：
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int(11) | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| status | varchar(15) | NO | | NULL | |
| comments | text | YES | | NULL | |
| customerNumber | int(11) | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
7 rows in set
以下語句返回2014年按工作日名稱分組的訂單計數。
SELECT
DAYNAME(orderdate) weekday,
COUNT(*) total_orders
FROM
orders
WHERE
YEAR(orderdate) = 2004
GROUP BY weekday
ORDER BY total_orders DESC;
執行上面查詢語句，得到以下結果 -
+---------+--------------+
| weekday | total_orders |
+---------+--------------+
| 星期三 | 37 |
| 星期一 | 28 |
| 星期日 | 27 |
| 星期二 | 22 |
| 星期六 | 21 |
| 星期四 | 14 |
| 星期五 | 2 |
+---------+--------------+
7 rows in set
星期三的訂單數量是最高的，星期五有兩個訂單。
在本教學中，您已經學習了如何使用MySQL DAYNAME函數獲取特定日期的工作日名稱。
上一篇：
MySQL函數
下一篇：
MySQL+Node.js連接和操作