MySQL高级函数之：DAYOFWEEK() 和 WEEKDAY()：获取星期几的应用
大家好，今天我们来深入探讨MySQL中两个常用的日期函数：DAYOFWEEK() 和 WEEKDAY()。这两个函数都用于获取给定日期的星期几，但它们返回的值略有不同，理解它们的区别对于编写准确的SQL查询至关重要。
1. DAYOFWEEK() 函数
DAYOFWEEK() 函数返回给定日期的星期几，返回值是一个介于 1 到 7 之间的整数，其中 1 代表星期日，2 代表星期一，以此类推，7 代表星期六。
语法：
DAYOFWEEK(date)其中 date 可以是日期类型的列名、日期字符串或日期表达式。
示例：
假设我们有一个名为 orders 的表，其中包含订单信息，包括 order_date 列，存储订单的日期。
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE
);
INSERT INTO orders (order_id, order_date) VALUES
(1, '2023-10-26'), -- 星期四
(2, '2023-10-27'), -- 星期五
(3, '2023-10-28'), -- 星期六
(4, '2023-10-29'), -- 星期日
(5, '2023-10-30'), -- 星期一
(6, '2023-10-31'), -- 星期二
(7, '2023-11-01'); -- 星期三现在，我们可以使用 DAYOFWEEK() 函数来获取每个订单的星期几：
SELECT order_id, order_date, DAYOFWEEK(order_date) AS day_of_week
FROM orders;查询结果如下：
| order_id | order_date | day_of_week |
|---|---|---|
| 1 | 2023-10-26 | 5 |
| 2 | 2023-10-27 | 6 |
| 3 | 2023-10-28 | 7 |
| 4 | 2023-10-29 | 1 |
| 5 | 2023-10-30 | 2 |
| 6 | 2023-10-31 | 3 |
| 7 | 2023-11-01 | 4 |
可以看到，DAYOFWEEK() 函数返回了与日期对应的星期几的数字表示。
应用场景：
- 统计特定星期几的订单数量：
SELECT DAYOFWEEK(order_date) AS day_of_week, COUNT(*) AS order_count
FROM orders
GROUP BY DAYOFWEEK(order_date)
ORDER BY DAYOFWEEK(order_date);- 筛选特定星期几的订单：
SELECT *
FROM orders
WHERE DAYOFWEEK(order_date) = 1; -- 筛选星期日的订单2. WEEKDAY() 函数
WEEKDAY() 函数也返回给定日期的星期几，但返回值是一个介于 0 到 6 之间的整数，其中 0 代表星期一，1 代表星期二，以此类推，6 代表星期日。
语法：
WEEKDAY(date)其中 date 的含义与 DAYOFWEEK() 函数相同。
示例：
使用与之前相同的 orders 表，我们可以使用 WEEKDAY() 函数来获取每个订单的星期几：
SELECT order_id, order_date, WEEKDAY(order_date) AS weekday
FROM orders;查询结果如下：
| order_id | order_date | weekday |
|---|---|---|
| 1 | 2023-10-26 | 3 |
| 2 | 2023-10-27 | 4 |
| 3 | 2023-10-28 | 5 |
| 4 | 2023-10-29 | 6 |
| 5 | 2023-10-30 | 0 |
| 6 | 2023-10-31 | 1 |
| 7 | 2023-11-01 | 2 |
可以看到，WEEKDAY() 函数返回了与日期对应的星期几的数字表示，但星期一从 0 开始计数。
应用场景：
- 统计特定星期几的订单数量：
SELECT WEEKDAY(order_date) AS weekday, COUNT(*) AS order_count
FROM orders
GROUP BY WEEKDAY(order_date)
ORDER BY WEEKDAY(order_date);- 筛选特定星期几的订单：
SELECT *
FROM orders
WHERE WEEKDAY(order_date) = 6; -- 筛选星期日的订单3. DAYNAME() 函数
虽然不是数字表示，但 DAYNAME() 函数也经常与 DAYOFWEEK() 和 WEEKDAY() 一起使用，它可以直接返回日期的星期几的英文名称。
语法：
DAYNAME(date)示例：
SELECT order_id, order_date, DAYNAME(order_date) AS day_name
FROM orders;查询结果如下：
| order_id | order_date | day_name |
|---|---|---|
| 1 | 2023-10-26 | Thursday |
| 2 | 2023-10-27 | Friday |
| 3 | 2023-10-28 | Saturday |
| 4 | 2023-10-29 | Sunday |
| 5 | 2023-10-30 | Monday |
| 6 | 2023-10-31 | Tuesday |
| 7 | 2023-11-01 | Wednesday |
4. DATE_FORMAT() 函数
DATE_FORMAT() 函数可以将日期格式化成各种字符串表示形式，其中也包括星期几的名称。
语法：
DATE_FORMAT(date, format)其中 format 是一个字符串，用于指定日期的格式。常用的星期几格式符包括：
%W: 星期几的全名 (Sunday, Monday, …)%a: 星期几的缩写 (Sun, Mon, …)
示例：
SELECT order_id, order_date, DATE_FORMAT(order_date, '%W') AS day_name_full, DATE_FORMAT(order_date, '%a') AS day_name_short
FROM orders;查询结果如下：
| order_id | order_date | day_name_full | day_name_short |
|---|---|---|---|
| 1 | 2023-10-26 | Thursday | Thu |
| 2 | 2023-10-27 | Friday | Fri |
| 3 | 2023-10-28 | Saturday | Sat |
| 4 | 2023-10-29 | Sunday | Sun |
| 5 | 2023-10-30 | Monday | Mon |
| 6 | 2023-10-31 | Tuesday | Tue |
| 7 | 2023-11-01 | Wednesday | Wed |
5. TO_DAYS() 和 FROM_DAYS() 函数
这两个函数可以将日期转换为一个表示自公元0年1月1日以来的天数的整数，反之亦然。虽然它们本身不直接返回星期几，但可以用来计算两个日期之间的天数差，从而间接推导出星期几。 不过，这种方法通常不如直接使用 DAYOFWEEK() 或 WEEKDAY() 方便。
语法：
TO_DAYS(date)
FROM_DAYS(N)示例：
SELECT TO_DAYS('2023-11-01'); -- 返回 739182
SELECT FROM_DAYS(739182); -- 返回 2023-11-016. 区别和选择：DAYOFWEEK() vs WEEKDAY()
| 特性 | DAYOFWEEK() |
WEEKDAY() |
|---|---|---|
| 返回值范围 | 1-7 (1 代表星期日) | 0-6 (0 代表星期一) |
| 星期日 | 1 | 6 |
| 星期一 | 2 | 0 |
| 使用场景 | 需要以星期日作为一周的开始时 | 需要以星期一作为一周的开始时 |
如何选择：
- 如果你的业务逻辑需要以星期日作为一周的开始（例如，某些国家的日历习惯），则应该使用
DAYOFWEEK()。 - 如果你的业务逻辑需要以星期一作为一周的开始（例如，ISO 8601 标准），则应该使用
WEEKDAY()。 - 如果仅仅需要星期几的英文名称，使用
DAYNAME()或者DATE_FORMAT()。
7. 结合其他日期函数进行更复杂的操作
这些函数可以与其他日期函数结合使用，实现更复杂的日期计算和分析。
示例1：计算每个月第一个星期一的日期
SELECT
DATE_ADD(
LAST_DAY(DATE_SUB('2024-01-15', INTERVAL (DAYOFWEEK('2024-01-15')-2) DAY)), -- 找到上个月的最后一个星期日
INTERVAL 1 DAY
) AS first_monday;
-- 更通用的方法，计算任意日期所在月份的第一个星期一
SELECT
DATE_ADD(
LAST_DAY(DATE_SUB(your_date_column, INTERVAL (DAYOFWEEK(your_date_column)-2) DAY)),
INTERVAL 1 DAY
) AS first_monday;解释：
your_date_column替换为你需要计算的日期列或具体的日期。DAYOFWEEK(your_date_column)返回your_date_column是星期几（1-7，星期日为1）。DAYOFWEEK(your_date_column)-2计算出your_date_column距离星期一有多少天。 如果结果是负数，表示your_date_column本身就在星期一之前。DATE_SUB(your_date_column, INTERVAL (DAYOFWEEK(your_date_column)-2) DAY)从your_date_column减去计算出的天数，得到最接近your_date_column的上一个星期日。LAST_DAY(...)返回上一步计算出的星期日所在月份的最后一天。DATE_ADD(..., INTERVAL 1 DAY)在上一步的结果上加一天，得到下个月的第一天。因为第五步已经确保了是上个月的最后一个星期日，加一天后必然是下个月的第一个星期一。
示例2：计算某个日期所在的周的起始日期（星期一）
SELECT DATE(your_date_column - INTERVAL (WEEKDAY(your_date_column)) DAY) AS week_start;解释：
WEEKDAY(your_date_column)返回your_date_column是星期几（0-6，星期一为0）。your_date_column - INTERVAL (WEEKDAY(your_date_column)) DAY从your_date_column减去相应的天数，得到该周的星期一。DATE(...)提取日期部分，去除时间。
示例3：计算某个月有多少个星期五
SELECT SUM(CASE WHEN DAYOFWEEK(date) = 6 THEN 1 ELSE 0 END) AS friday_count
FROM (
SELECT CURDATE() + INTERVAL (n - 1) DAY AS date
FROM (
SELECT a.n + b.n * 10 AS n
FROM
(SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS a,
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS b
) numbers
WHERE MONTH(CURDATE() + INTERVAL (n - 1) DAY) = MONTH(CURDATE())
) AS dates_in_month;解释：
- 内层SELECT生成一个数字序列，范围从1到31（足以覆盖一个月的所有天数）。
- 中间的SELECT生成一个日期序列，从当前日期开始，依次加上天数，形成一个包含一个月所有日期的表。
- 外层SELECT使用
DAYOFWEEK()函数判断每个日期是否为星期五，如果是，则计数加1。 SUM()函数计算所有星期五的数量。
这些示例仅仅是冰山一角，你可以根据自己的需求，灵活运用这些日期函数，实现各种复杂的日期计算和分析。
总结：选择合适的函数来获取星期几
DAYOFWEEK() 和 WEEKDAY() 都是用于获取星期几的函数，但它们的返回值不同。DAYOFWEEK() 从星期日开始计数，WEEKDAY() 从星期一开始计数。根据你的业务需求选择合适的函数，可以更方便地进行日期计算和分析。DAYNAME() 和 DATE_FORMAT() 可以直接返回星期几的名称。