MySQL dayname()函数

转载自  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

请参阅示例数据库(yiibaid)中的以下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函数获取特定日期的工作日名称。