使用SQL生成Luhn Mod N

Question

我们使用CRYPT_GEN_RANDOM生成一个15个字符的字符串(每个字符的数字加上大写= 36个选项)，并希望使用Luhn Mod N添加最后一个字符作为校验和。

我正在寻找一种方法来做到这一点使用SQL。有很多只使用数字的标准luhn formula的例子，但我找不到一个用于Luhn Mod N的。

Answer 1

当我正确解释了Luhn mod N的公式时：

CREATE DEFINER=`root`@`localhost` FUNCTION `LUHN_MOD_N`(input CHAR(15)) RETURNS char(1) CHARSET ascii
    DETERMINISTIC
BEGIN

-- https://stackoverflow.com/questions/59471115/generate-luhn-mod-n-using-sql
set @retValue =' ';

with 
  recursive codePointFromchar as (
     select 'A' cp, 0 as val
     union all
     select CHAR(ASCII('A' )+val+1), val+1 
     from codePointFromchar 
     where val < 26 
  ), 
  cte as (
    select input as x, 2 as factor, 0 as sum, 0 as codePoint
    union all
    select 
       x, 
       case when factor=2 then 1 else 2 end, 
       sum+((floor((select val from codePointFromchar where cp =MID(x,codePoint+1,1) limit 1)/15) +
             ((select val from codePointFromchar where cp =MID(x,codePoint+1,1) limit 1) % 15)
            )* factor ), 
       codePoint+1 
    from cte where codePoint<15
)
select (select cp from codePointFromchar where val = ((15 - sum%15) % 15) ) into @retValue 
        from cte order by codePoint desc limit 1 ;

return @retValue;
END

• N的值设置为固定为15。
• 输入值ABCDEFGHIJKLMNO当前也是固定的。
• codePointFromchar cte将codePoint值分配给字符。<代码>H211<代码>F212

输出：

mysql> SELECT LUHN_MOD_N('ABCDEFGHIJKLMNO');
+-------------------------------+
| LUHN_MOD_N('ABCDEFGHIJKLMNO') |
+-------------------------------+
| E                             |
+-------------------------------+