MySQL中如何筛选小于指定Excel式列名的X轴坐标值

问题根源 #

MySQL默认的字符串比较是字典序，会逐个字符对比ASCII值：比如'B'的首字符ASCII码（66）大于'AA'的首字符（65），所以会判定'B' > 'AA'，导致WHERE x_axis < 'AZ'错误排除B到Z这些单字符列名，和Excel的列序逻辑完全不符。

解决方案：将列名转为数值后比较 #

Excel列名的数值转换逻辑是：A=1，B=2…Z=26，AA=27（26×1+1），AB=28（26×1+2）…AZ=52（26×1+26）。我们可以通过自定义函数或递归CTE将列名转为对应数值，再进行筛选。

方法1：创建自定义转换函数（推荐，性能更优）

先创建一个将Excel列名转为数值的函数：

DELIMITER //
CREATE FUNCTION excel_col_to_num(col VARCHAR(255)) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE num INT DEFAULT 0;
    DECLARE len INT DEFAULT LENGTH(col);
    DECLARE i INT DEFAULT 1;
    WHILE i <= len DO
        SET num = num * 26 + (ORD(SUBSTRING(col, i, 1)) - ORD('A') + 1);
        SET i = i + 1;
    END WHILE;
    RETURN num;
END //
DELIMITER ;

然后修改查询语句，用函数转换后筛选：

SELECT x_axis, y_axis
FROM coordinates
WHERE excel_col_to_num(SUBSTRING_INDEX(x_axis, ' ', 1)) < excel_col_to_num('AZ')
-- 若需要包含AZ，将<改为<=
ORDER BY LENGTH(SUBSTRING_INDEX(x_axis, ' ', 1)), SUBSTRING_INDEX(x_axis, ' ', 1);

方法2：使用递归CTE（无需创建函数）

如果无法创建函数，可通过递归CTE计算列名对应的数值：

WITH RECURSIVE col_calc AS (
    SELECT 
        x_axis,
        y_axis,
        SUBSTRING_INDEX(x_axis, ' ', 1) AS col_name,
        LENGTH(SUBSTRING_INDEX(x_axis, ' ', 1)) AS col_len,
        1 AS current_pos,
        0 AS col_num
    FROM coordinates
    UNION ALL
    SELECT 
        x_axis,
        y_axis,
        col_name,
        col_len,
        current_pos + 1,
        col_num * 26 + (ORD(SUBSTRING(col_name, current_pos, 1)) - ORD('A') + 1)
    FROM col_calc
    WHERE current_pos <= col_len
)
SELECT x_axis, y_axis
FROM col_calc
WHERE current_pos = col_len 
  AND col_num < (
      SELECT 
          SUM((ORD(SUBSTRING('AZ', pos, 1)) - ORD('A') + 1) * POWER(26, 2 - pos))
      FROM (SELECT 1 AS pos UNION SELECT 2) AS pos_list
  )
-- 若需要包含AZ，将<改为<=
ORDER BY col_len, col_name;

补充说明 #

• 两种方法都先提取x_axis中的列名部分（通过SUBSTRING_INDEX(x_axis, ' ', 1)），若你的x_axis字段没有空格，可直接用x_axis替代该部分简化语句。

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