MySQL str_to_date()函数在年初为周一的年份下失效问题

我之前碰到过一模一样的MySQL周数解析坑，尤其是在新年第一天刚好是周一的年份（比如2018、2007），用str_to_date()处理%X%V %W格式时会出怪事——除了周日，其余星期几都会被错误解析到下一周的日期。

问题复现 #

异常场景（2018年，1月1日为周一） #

执行以下查询：

SELECT 
  str_to_date('201801 Monday', '%X%V %W') AS 'Monday',
  str_to_date('201801 Tuesday', '%X%V %W') AS 'Tuesday',
  str_to_date('201801 Wednesday', '%X%V %W') AS 'Wednesday',
  str_to_date('201801 Thursday', '%X%V %W') AS 'Thursday',
  str_to_date('201801 Friday', '%X%V %W') AS 'Friday',
  str_to_date('201801 Saturday', '%X%V %W') AS 'Saturday',
  str_to_date('201801 Sunday', '%X%V %W') AS 'Sunday';

异常输出：

Monday       | Tuesday     | Wednesday   | Thursday    | Friday      | Saturday    | Sunday
'2018-01-08'| '2018-01-09'| '2018-01-10'| '2018-01-11'| '2018-01-12'| '2018-01-13'| '2018-01-07'

本该返回2018年第一周（1月1日-7日）的日期，结果只有周日是对的，其余日期全跳到了第二周。

正常场景（2017年，1月1日为周日） #

执行相同逻辑的查询：

SELECT 
  str_to_date('201701 Monday', '%X%V %W') AS 'Monday',
  str_to_date('201701 Tuesday', '%X%V %W') AS 'Tuesday',
  str_to_date('201701 Wednesday', '%X%V %W') AS 'Wednesday',
  str_to_date('201701 Thursday', '%X%V %W') AS 'Thursday',
  str_to_date('201701 Friday', '%X%V %W') AS 'Friday',
  str_to_date('201701 Saturday', '%X%V %W') AS 'Saturday',
  str_to_date('201701 Sunday', '%X%V %W') AS 'Sunday';

正常输出：

Monday       | Tuesday     | Wednesday   | Thursday    | Friday      | Saturday    | Sunday
'2017-01-02'| '2017-01-03'| '2017-01-04'| '2017-01-05'| '2017-01-06'| '2017-01-07'| '2017-01-01'

问题原因 #

这个bug和MySQL对%X（周所属年份）与%V（周数，周一为一周第一天）的组合解析逻辑有关：当新年第一天恰好是周一时，MySQL错误地把该周的周一至周六归到了下一周（周数02），只把周日留在了周数01里。而且调整default_week_format系统变量也没法解决这个特定场景的问题。

解决方案 #

我们可以绕开str_to_date()的这个bug，利用周日解析始终正确的特性，反向推导其他星期几的日期：

通用单日期解析方法 #

SET @input_str = '201801 Monday'; -- 替换成你要解析的字符串

-- 拆分输入里的年份、周数、星期几
SET @year = LEFT(@input_str, 4);
SET @week = SUBSTRING(@input_str, 5, 2);
SET @weekday = TRIM(RIGHT(@input_str, LENGTH(@input_str) - 6));

-- 先拿到该周周日的正确日期
SET @sunday_date = STR_TO_DATE(CONCAT(@year, @week, ' Sunday'), '%X%V %W');

-- 根据星期几反向算出目标日期
SELECT
  CASE @weekday
    WHEN 'Monday' THEN DATE_SUB(@sunday_date, INTERVAL 6 DAY)
    WHEN 'Tuesday' THEN DATE_SUB(@sunday_date, INTERVAL 5 DAY)
    WHEN 'Wednesday' THEN DATE_SUB(@sunday_date, INTERVAL 4 DAY)
    WHEN 'Thursday' THEN DATE_SUB(@sunday_date, INTERVAL 3 DAY)
    WHEN 'Friday' THEN DATE_SUB(@sunday_date, INTERVAL 2 DAY)
    WHEN 'Saturday' THEN DATE_SUB(@sunday_date, INTERVAL 1 DAY)
    WHEN 'Sunday' THEN @sunday_date
  END AS correct_date;

整周日期批量查询示例 #

如果要一次性获取整周的正确日期，可以用这个语句：

SET @year = '2018';
SET @week = '01';
SET @sunday_date = STR_TO_DATE(CONCAT(@year, @week, ' Sunday'), '%X%V %W');

SELECT
  DATE_SUB(@sunday_date, INTERVAL 6 DAY) AS 'Monday',
  DATE_SUB(@sunday_date, INTERVAL 5 DAY) AS 'Tuesday',
  DATE_SUB(@sunday_date, INTERVAL 4 DAY) AS 'Wednesday',
  DATE_SUB(@sunday_date, INTERVAL 3 DAY) AS 'Thursday',
  DATE_SUB(@sunday_date, INTERVAL 2 DAY) AS 'Friday',
  DATE_SUB(@sunday_date, INTERVAL 1 DAY) AS 'Saturday',
  @sunday_date AS 'Sunday';

执行后会返回正确的2018年第一周日期：2018-01-01至2018-01-07。

这个方法靠周日的正确解析结果反向偏移计算，不管新年第一天是不是周一，都能得到准确的日期。

内容的提问来源于stack exchange，提问作者David Casillas