MySQL版Luhn算法校验位生成函数错误排查求助

问题概述 #

将可正常运行的PHP版Luhn算法校验位生成函数转换为MySQL函数后，出现计算结果与预期不符的情况。例如输入数字345722时，预期返回校验位3，但MySQL函数实际返回6。

代码对比 #

存在问题的MySQL函数 #

drop function if exists luhn;
delimiter //
  
create function luhn(myNum varchar(20))
    returns varchar(1)
    sql security invoker
begin
        
    declare sIndex, digit, len, parity,SumX int;
   
            set myNum=concat(myNum,'0');
            set len = length(myNum);
            set parity = len % 2;
            set SumX =0;
            set sIndex = len - 1;
 
    while sIndex >= 0 do
          set digit =  substring(myNum, sIndex,1);
             
            if(sIndex % 2 = parity) then set digit = digit * 2;
                end if;
                
                if(digit > 9) then set digit = digit - 9;
                 end if;
                
            set SumX = SumX + digit;
      set sIndex = sIndex - 1;
                
        end while;
                
                if SumX % 10 =0 then return 0;
                    else return (10 - (SumX % 10));
                end if;
         
      
end //

delimiter;

正确的PHP函数 #

function luhn_checksum($myNum) {
    $myNum = $myNum . "0";
    $len = strlen($myNum);
    $parity = $len % 2;
    $sum = 0;

    for ($index = $len - 1; $index >= 0; $index--) {
        $digit = intval($myNum[$index]);
        if ($index % 2 == $parity) {
            $digit *= 2;
        }
        if ($digit > 9) {
            $digit -= 9;
        }
        $sum += $digit;
       }
  
    if($sum % 10===0){return(0);}
   
        $finalCheckDigit= 10-($sum % 10);
            
    return($finalCheckDigit);
}

测试数据对比 #

错误原因分析 #

核心问题是字符串索引规则不一致：

• PHP的字符串访问采用0-based索引（第一个字符索引为0，最后一个字符索引为len-1）
• MySQL的SUBSTRING函数采用1-based位置（第一个字符位置为1，最后一个字符位置为len）

原MySQL函数直接照搬了PHP的索引逻辑：

1. 初始化sIndex = len - 1（对应PHP的最后一个字符索引），但用substring(myNum, sIndex, 1)获取字符时，实际取到的是MySQL中第sIndex位的字符，和PHP预期的字符位置偏移了一位。
2. 奇偶判断条件sIndex % 2 = parity基于PHP的0-based索引设计，在MySQL的1-based逻辑下，奇偶判断完全错位，导致需要乘2的数字判断错误。

修复后的MySQL函数 #

调整索引逻辑，适配MySQL的1-based规则：

drop function if exists luhn;
delimiter //
  
create function luhn(myNum varchar(20))
    returns varchar(1)
    sql security invoker
begin
    declare pos, digit, len, parity, sumX int;
   
    set myNum = concat(myNum, '0');
    set len = length(myNum);
    set parity = len % 2;
    set sumX = 0;
    set pos = len; -- 从最后一个字符的1-based位置开始遍历
 
    while pos >= 1 do
        set digit = substring(myNum, pos, 1);
        -- 转换为PHP的0-based索引进行奇偶判断
        if ((pos - 1) % 2 = parity) then 
            set digit = digit * 2;
        end if;
        
        if (digit > 9) then 
            set digit = digit - 9;
        end if;
        
        set sumX = sumX + digit;
        set pos = pos - 1;
    end while;
                
    if sumX % 10 = 0 then 
        return '0';
    else 
        return cast((10 - (sumX % 10)) as char);
    end if;
end //

delimiter;

修复说明 #

1. 将遍历起始位置改为len（MySQL字符串最后一个字符的1-based位置）
2. 奇偶判断时，将1-based的pos转换为0-based的pos-1，和PHP的判断逻辑保持一致
3. 返回值统一转为字符类型，避免隐式转换问题

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