MySQL week()

最新推荐文章于 2025-07-28 00:29:25 发布

原创最新推荐文章于 2025-07-28 00:29:25 发布 · 772 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql

cs 专栏收录该内容

5 篇文章

订阅专栏

本文详细介绍了MySQL中WEEK函数的用法，包括如何通过指定不同的mode参数来确定周数的计算方式，以及如何根据ISO8601:1988标准进行周数计算。

MySQL WEEK函数介绍

通常，一年的正常年份为365天，闰年为366天。一年又可以分为许多周，每周有7天。所以一年，我们经常有365/7 = 52周，周范围是从1到52。

要查看给定日期属于哪个周数，您可以使用WEEK函数，如下所示：

WEEK(date, mode);

SQL

WEEK函数接受两个参数：

date是要获取周数的日期。
mode是一个可选参数，用于确定周数计算的逻辑。它允许您指定本周是从星期一还是星期日开始，返回的周数应在0到52之间或0到53之间。

如果忽略mode参数，默认情况下WEEK函数将使用default_week_format系统变量的值。

要获取default_week_format变量的当前值，请使用SHOW VARIABLES语句如下：

mysql> SHOW VARIABLES LIKE 'default_week_format';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set

The following table describes how the mode argument works.

Mode	First day of week	Range	Week 1 is the first week …
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with 4 or more days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with 4 or more days this year
4	Sunday	0-53	with 4 or more days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with 4 or more days this year
7	Monday	1-53	with a Monday in this year

For mode values with a meaning of “with 4 or more days this year,” weeks are numbered according to ISO 8601:1988:

If the week containing January 1 has 4 or more days in the new year, it is week 1.
Otherwise, it is the last week of the previous year, and the next week is week 1.

WEEK函数返回一个周数，遵循ISO 8601:1988

参考文出自：1、【易百教程】作者：初生不惑 MySQL week()函数。

2、 MySQL DOC function_week