MySQL中实现字符串对应映射字符数值求和的方法

嘿，这个需求我之前也碰到过，刚好有两种实用的解决方案，你可以根据自己的MySQL版本和使用场景来选：

方案一：纯SQL语句（适合MySQL 8.0+，支持CTE） #

首先假设你的表结构是这样的（我先建个示例表方便演示）：

CREATE TABLE char_values (
    char_col CHAR(1) PRIMARY KEY,
    num_col INT NOT NULL
);

-- 插入你提到的示例数据
INSERT INTO char_values (char_col, num_col) VALUES
('A', 1), ('B', 2), ('C', 3), ('D', 4), ('E', 5);

接下来用递归CTE把指定字符串拆分成单个字符，再和值表关联求和。比如计算BAD的总和：

WITH RECURSIVE split_chars AS (
    SELECT 
        1 AS pos,
        SUBSTRING('BAD', 1, 1) AS single_char,
        LENGTH('BAD') AS total_length
    UNION ALL
    SELECT 
        pos + 1,
        SUBSTRING('BAD', pos + 1, 1),
        total_length
    FROM split_chars
    WHERE pos < total_length
)
SELECT SUM(cv.num_col) AS total_sum
FROM split_chars sc
JOIN char_values cv ON sc.single_char = cv.char_col;

小说明： #

• 递归CTE会把字符串逐位拆成多行，每一行对应一个字符
• 如果字符串里有不在你的值表中的字符，这个查询会自动忽略它（如果想把这类字符算0，可以改成LEFT JOIN，然后用COALESCE(cv.num_col, 0)求和）

方案二：存储过程（适合重复调用，兼容所有MySQL版本） #

如果需要频繁计算不同字符串的总和，写个存储过程会更方便：

DELIMITER //

CREATE PROCEDURE CalculateCharTotal(IN input_string VARCHAR(255), OUT result_sum INT)
BEGIN
    DECLARE str_length INT;
    DECLARE current_position INT DEFAULT 1;
    DECLARE current_char CHAR(1);
    DECLARE current_num INT;
    SET result_sum = 0;
    SET str_length = LENGTH(input_string);
    
    -- 循环遍历字符串的每个字符
    WHILE current_position <= str_length DO
        SET current_char = SUBSTRING(input_string, current_position, 1);
        -- 查找当前字符对应的数值
        SELECT num_col INTO current_num FROM char_values WHERE char_col = current_char;
        -- 如果找到数值就累加，没找到则跳过（也可以改成加0）
        IF current_num IS NOT NULL THEN
            SET result_sum = result_sum + current_num;
        END IF;
        SET current_position = current_position + 1;
    END WHILE;
END //

DELIMITER ;

调用方式很简单：

SET @total = 0;
CALL CalculateCharTotal('BAD', @total);
SELECT @total AS total_sum; -- 输出7

兼容MySQL 5.x的纯SQL方案 #

如果你的MySQL版本不支持CTE（比如5.7及以下），可以用数字辅助表来拆分字符串：

SELECT SUM(cv.num_col) AS total_sum
FROM (
    SELECT SUBSTRING('BAD', nums.n, 1) AS single_char
    FROM (
        -- 这里的数字要覆盖你可能用到的最大字符串长度
        SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
        UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
    ) nums
    WHERE nums.n <= LENGTH('BAD')
) sc
JOIN char_values cv ON sc.single_char = cv.char_col;

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