MySQL字段数字转星期缩写：如何实现任意组合的前端展示？

这个问题我之前也碰到过，用CASE枚举几百种组合确实不现实，完全是浪费时间！下面给你两个实用的MySQL方案，不管字符串长度是1到7都能轻松处理，而且不用依赖应用层：

方案一：适合MySQL 8.0+（递归CTE版） #

利用递归CTE生成1到7的位置序号，逐个提取数字串中的每个字符，映射为对应星期缩写后再拼接：

WITH RECURSIVE seq AS (
    SELECT 1 AS pos
    UNION ALL
    SELECT pos + 1 FROM seq WHERE pos < 7
)
SELECT 
    t.week_num,
    GROUP_CONCAT(
        ELT(CAST(SUBSTRING(t.week_num, s.pos, 1) AS UNSIGNED), 'Mo', 'Tu', 'We', 'Th', 'Fr', 'Sa', 'Su')
        SEPARATOR ' '
    ) AS week_abbr
FROM your_table t
JOIN seq s ON s.pos <= LENGTH(t.week_num)
GROUP BY t.week_num;

逻辑说明： #

• 递归CTE seq 生成1到7的数字序列，对应数字串的第1到第7位
• SUBSTRING(t.week_num, s.pos, 1) 提取数字串中第pos位的字符
• CAST(...) AS UNSIGNED 将字符转为整数，供ELT函数使用
• ELT(N, str1, str2,...) 根据数字N返回对应的星期缩写（1→Mo，2→Tu...7→Su）
• GROUP_CONCAT 按原顺序将所有缩写用空格拼接起来

方案二：兼容旧版本MySQL（无CTE版） #

如果你的MySQL版本低于8.0，不支持CTE，可以用CONCAT_WS结合条件判断来实现：

SELECT 
    week_num,
    CONCAT_WS(' ',
        IF(LENGTH(week_num)>=1, ELT(CAST(SUBSTRING(week_num,1,1) AS UNSIGNED), 'Mo','Tu','We','Th','Fr','Sa','Su'), ''),
        IF(LENGTH(week_num)>=2, ELT(CAST(SUBSTRING(week_num,2,1) AS UNSIGNED), 'Mo','Tu','We','Th','Fr','Sa','Su'), ''),
        IF(LENGTH(week_num)>=3, ELT(CAST(SUBSTRING(week_num,3,1) AS UNSIGNED), 'Mo','Tu','We','Th','Fr','Sa','Su'), ''),
        IF(LENGTH(week_num)>=4, ELT(CAST(SUBSTRING(week_num,4,1) AS UNSIGNED), 'Mo','Tu','We','Th','Fr','Sa','Su'), ''),
        IF(LENGTH(week_num)>=5, ELT(CAST(SUBSTRING(week_num,5,1) AS UNSIGNED), 'Mo','Tu','We','Th','Fr','Sa','Su'), ''),
        IF(LENGTH(week_num)>=6, ELT(CAST(SUBSTRING(week_num,6,1) AS UNSIGNED), 'Mo','Tu','We','Th','Fr','Sa','Su'), ''),
        IF(LENGTH(week_num)>=7, ELT(CAST(SUBSTRING(week_num,7,1) AS UNSIGNED), 'Mo','Tu','We','Th','Fr','Sa','Su'), '')
    ) AS week_abbr
FROM your_table;

逻辑说明： #

• LENGTH(week_num)>=N 判断数字串是否足够长到第N位
• 每个IF语句只在对应位置存在时返回缩写，否则返回空字符串
• CONCAT_WS(' ', ...) 自动忽略空字符串，用空格拼接有效缩写，避免多余空格

示例效果 #

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