MySQL日期函数：DAYNAME()与MONTHNAME() 详解
大家好，今天我们来深入探讨MySQL中两个非常有用的日期函数：DAYNAME()和MONTHNAME()。这两个函数的主要作用是将日期值转换为对应的星期名称和月份名称，方便我们在查询和报表中更直观地展示日期信息。
1. DAYNAME() 函数
DAYNAME()函数用于返回日期值所对应的星期名称。它的语法非常简单：
DAYNAME(date)其中，date 可以是以下几种类型：
DATE类型的值DATETIME类型的值TIMESTAMP类型的值- 表示日期或时间的字符串（MySQL会自动尝试将其转换为日期类型）
示例 1：使用 DATE 类型的值
SELECT DAYNAME('2023-10-27');
-- 输出：Friday示例 2：使用 DATETIME 类型的值
SELECT DAYNAME('2023-10-27 10:30:00');
-- 输出：Friday示例 3：使用 TIMESTAMP 类型的值
SELECT DAYNAME(CURRENT_TIMESTAMP());
-- 输出：取决于当前日期是星期几，例如：Friday示例 4：使用字符串表示的日期
SELECT DAYNAME('October 27, 2023');
-- 输出：Friday示例 5：与表字段结合使用
假设我们有一个名为 orders 的表，其中包含 order_date 字段（DATE 类型），我们可以使用 DAYNAME() 函数来获取每个订单日期的星期名称：
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
order_date DATE
);
INSERT INTO orders (order_date) VALUES
('2023-10-23'),
('2023-10-24'),
('2023-10-25'),
('2023-10-26'),
('2023-10-27');
SELECT order_id, order_date, DAYNAME(order_date) AS day_of_week
FROM orders;查询结果如下：
| order_id | order_date | day_of_week |
|---|---|---|
| 1 | 2023-10-23 | Monday |
| 2 | 2023-10-24 | Tuesday |
| 3 | 2023-10-25 | Wednesday |
| 4 | 2023-10-26 | Thursday |
| 5 | 2023-10-27 | Friday |
示例 6：在 WHERE 子句中使用 DAYNAME()
我们可以结合 DAYNAME() 函数和 WHERE 子句来筛选特定星期的订单：
SELECT order_id, order_date
FROM orders
WHERE DAYNAME(order_date) = 'Friday';这条 SQL 语句会返回所有 order_date 是星期五的订单。
示例 7：使用 DAYNAME() 进行分组统计
我们可以使用 DAYNAME() 函数和 GROUP BY 子句来统计每周的订单数量：
SELECT DAYNAME(order_date) AS day_of_week, COUNT(*) AS order_count
FROM orders
GROUP BY DAYNAME(order_date)
ORDER BY FIELD(day_of_week, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');这条 SQL 语句会返回每周每一天的订单数量，并按照星期的顺序进行排序。FIELD() 函数在这里用于自定义排序，确保结果按照我们期望的星期顺序显示。
注意事项：
DAYNAME()函数返回的星期名称是基于当前 MySQL 服务器的lc_time_names系统变量设置的。如果需要使用不同的语言，可以修改该系统变量。例如，要使用中文的星期名称，可以设置lc_time_names为zh_CN。DAYNAME()函数不区分大小写。例如，DAYNAME('2023-10-27')和DAYNAME('2023-10-27')都会返回Friday。
2. MONTHNAME() 函数
MONTHNAME() 函数用于返回日期值所对应的月份名称。它的语法与 DAYNAME() 函数类似：
MONTHNAME(date)其中，date 的类型与 DAYNAME() 函数相同，可以是 DATE、DATETIME、TIMESTAMP 类型的值，也可以是表示日期或时间的字符串。
示例 1：使用 DATE 类型的值
SELECT MONTHNAME('2023-10-27');
-- 输出：October示例 2：使用 DATETIME 类型的值
SELECT MONTHNAME('2023-10-27 10:30:00');
-- 输出：October示例 3：使用 TIMESTAMP 类型的值
SELECT MONTHNAME(CURRENT_TIMESTAMP());
-- 输出：取决于当前日期是几月，例如：October示例 4：使用字符串表示的日期
SELECT MONTHNAME('October 27, 2023');
-- 输出：October示例 5：与表字段结合使用
假设我们有一个名为 products 的表，其中包含 created_at 字段（DATE 类型），我们可以使用 MONTHNAME() 函数来获取每个产品的创建月份名称：
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
created_at DATE
);
INSERT INTO products (product_name, created_at) VALUES
('Product A', '2023-09-15'),
('Product B', '2023-10-20'),
('Product C', '2023-10-25'),
('Product D', '2023-11-05');
SELECT product_id, product_name, MONTHNAME(created_at) AS creation_month
FROM products;查询结果如下：
| product_id | product_name | creation_month |
|---|---|---|
| 1 | Product A | September |
| 2 | Product B | October |
| 3 | Product C | October |
| 4 | Product D | November |
示例 6：在 WHERE 子句中使用 MONTHNAME()
我们可以结合 MONTHNAME() 函数和 WHERE 子句来筛选特定月份的产品：
SELECT product_id, product_name
FROM products
WHERE MONTHNAME(created_at) = 'October';这条 SQL 语句会返回所有 created_at 是十月份的产品。
示例 7：使用 MONTHNAME() 进行分组统计
我们可以使用 MONTHNAME() 函数和 GROUP BY 子句来统计每个月的产品数量：
SELECT MONTHNAME(created_at) AS creation_month, COUNT(*) AS product_count
FROM products
GROUP BY MONTHNAME(created_at)
ORDER BY FIELD(creation_month, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');这条 SQL 语句会返回每月的产品数量，并按照月份的顺序进行排序。FIELD() 函数在这里用于自定义排序，确保结果按照我们期望的月份顺序显示。
注意事项：
MONTHNAME()函数返回的月份名称也是基于当前 MySQL 服务器的lc_time_names系统变量设置的。MONTHNAME()函数不区分大小写。
3. lc_time_names 系统变量
lc_time_names 系统变量控制着 DAYNAME() 和 MONTHNAME() 函数返回的星期和月份名称的语言。可以使用以下 SQL 语句查看当前 lc_time_names 的值：
SHOW VARIABLES LIKE 'lc_time_names';要修改 lc_time_names 的值，可以使用以下 SQL 语句：
SET lc_time_names = 'zh_CN';这条 SQL 语句会将 lc_time_names 设置为中文，DAYNAME() 和 MONTHNAME() 函数将会返回中文的星期和月份名称。
示例：使用中文的星期和月份名称
首先，设置 lc_time_names 为 zh_CN：
SET lc_time_names = 'zh_CN';然后，执行以下查询：
SELECT DAYNAME('2023-10-27');
-- 输出：星期五
SELECT MONTHNAME('2023-10-27');
-- 输出：十月4. DATE_FORMAT() 函数
虽然 DAYNAME() 和 MONTHNAME() 函数可以方便地获取星期和月份名称，但如果需要更灵活地格式化日期，可以使用 DATE_FORMAT() 函数。
DATE_FORMAT() 函数允许我们使用各种格式化字符串来控制日期的显示方式。它的语法如下：
DATE_FORMAT(date, format)其中，date 是要格式化的日期值，format 是格式化字符串。
以下是一些常用的格式化字符串：
| 格式化字符串 | 描述 |
|---|---|
%a |
缩写的星期名称 (Sun..Sat) |
%b |
缩写的月份名称 (Jan..Dec) |
%c |
月份，数字 (0..12) |
%D |
带有英语后缀的日期 (0th, 1st, 2nd, 3rd, …) |
%d |
月份中的日期，数字 (00..31) |
%e |
月份中的日期，数字 (0..31) |
%f |
微秒 (000000..999999) |
%H |
小时 (00..23) |
%h |
小时 (01..12) |
%I |
小时 (01..12) |
%i |
分钟 (00..59) |
%j |
年中的天数 (001..366) |
%k |
小时 (0..23) |
%l |
小时 (1..12) |
%M |
月份名称 (January..December) |
%m |
月份，数字 (00..12) |
%p |
AM 或 PM |
%r |
12 小时制时间 (hh:mm:ss AM 或 PM) |
%S |
秒 (00..59) |
%s |
秒 (00..59) |
%T |
24 小时制时间 (hh:mm:ss) |
%U |
年中的星期数，星期日是每周的第一天 (00..53) |
%u |
年中的星期数，星期一是每周的第一天 (00..53) |
%V |
年中的 ISO 星期数，星期日是每周的第一天 (01..53) |
%v |
年中的 ISO 星期数，星期一是每周的第一天 (01..53) |
%W |
星期名称 (Sunday..Saturday) |
%w |
星期几，数字 (0=星期日..6=星期六) |
%X |
ISO 年份，星期日是每周的第一天 |
%x |
ISO 年份，星期一是每周的第一天 |
%Y |
年，数字，四位数 |
%y |
年，数字，两位数 |
示例 1：格式化日期为 YYYY-MM-DD 格式
SELECT DATE_FORMAT('2023-10-27', '%Y-%m-%d');
-- 输出：2023-10-27示例 2：格式化日期为 MM/DD/YYYY 格式
SELECT DATE_FORMAT('2023-10-27', '%m/%d/%Y');
-- 输出：10/27/2023示例 3：格式化日期为 Month DD, YYYY 格式
SELECT DATE_FORMAT('2023-10-27', '%M %d, %Y');
-- 输出：October 27, 2023示例 4：格式化日期为 Weekday, Month DD, YYYY 格式
SELECT DATE_FORMAT('2023-10-27', '%W, %M %d, %Y');
-- 输出：Friday, October 27, 2023示例 5：与表字段结合使用
SELECT product_name, DATE_FORMAT(created_at, '%W, %M %d, %Y') AS formatted_date
FROM products;总结：
DAYNAME() 和 MONTHNAME() 函数是 MySQL 中用于获取星期和月份名称的便捷工具。DAYNAME() 返回日期对应的星期名称，MONTHNAME() 返回日期对应的月份名称。 结合 lc_time_names 系统变量，可以控制返回的星期和月份名称的语言。而 DATE_FORMAT() 函数则提供了更强大的日期格式化功能，可以根据需要灵活地控制日期的显示方式。选择合适的函数取决于具体的需求和场景。
日期函数灵活运用，提升数据可读性
通过今天的讲解，我们学习了 DAYNAME() 和 MONTHNAME() 函数，了解了如何使用它们来获取日期的星期和月份名称。同时，我们还学习了 lc_time_names 系统变量以及 DATE_FORMAT() 函数，它们可以帮助我们更好地控制日期的显示方式，使数据更具可读性。希望这些知识能够帮助大家在实际的 MySQL 开发中更好地处理日期数据。