mysql function for Luhn algorithm to create the check digit NOT verify

Question

This MySQL function should generate a check digit using Luhn algorithm. Given the number 345722 the function should return 3. It returns 6 which is wrong.

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;

I converted the code above from the PHP code below which works.

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);
}

Given the number on the left I am expecting the middle number but getting the number on the right.

input   expect  returned
371950  7   5
367489  2   3
367457  9   1
350684  7   2
347772  6   1
345722  3   6
343464  4   3
339403  8   5
337069  9   4
331979  5   6
330500  0   6

I using this page to validate https://simplycalc.com/luhn-calculate.php

Answer 1

Unless you are running your Luhn check-digit generation against a large dataset, these performance differences are not really relevant, but it has kept me entertained for a bit.

Some small performance enhancements to Bill's answer:

1. There's no need to subtract 1 from the length and then add it back on
2. Using 0+ to implicitly cast to integer is unnecessary, as digit is declared as int
3. Combining the multiple set statements into one is just a bit faster, albeit reducing readability

create function luhn1(myNum varchar(20))
returns int
deterministic
begin

  declare sIndex, digit, parity, sumX int;

  set sIndex = length(myNum);
  set parity = sIndex % 2;
  set sumX = 0;

  while sIndex > 0 do

    set digit = substring(myNum, sIndex, 1);

    set sumX = sumX + if(sIndex % 2 = parity, if(digit * 2 > 9, (digit * 2) - 9, digit * 2), digit);
    set sIndex = sIndex - 1;

  end while;

  return (10 - (sumX % 10)) % 10;

end

Here's another approach without using a loop, which is a bit faster:

create function `luhn2`(myNum varchar(20))
returns int
deterministic
begin

  set myNum = lpad(myNum, 20, '0');

  return (10 - ((
    + cast(substring(myNum, 1, 1) as unsigned)
    + 2 * cast(substring(myNum, 2, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 2, 1) as unsigned) % 10
    + cast(substring(myNum, 3, 1) as unsigned)
    + 2 * cast(substring(myNum, 4, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 4, 1) as unsigned) % 10
    + cast(substring(myNum, 5, 1) as unsigned)
    + 2 * cast(substring(myNum, 6, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 6, 1) as unsigned) % 10
    + cast(substring(myNum, 7, 1) as unsigned)
    + 2 * cast(substring(myNum, 8, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 8, 1) as unsigned) % 10
    + cast(substring(myNum, 9, 1) as unsigned)
    + 2 * cast(substring(myNum, 10, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 10, 1) as unsigned) % 10
    + cast(substring(myNum, 11, 1) as unsigned)
    + 2 * cast(substring(myNum, 12, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 12, 1) as unsigned) % 10
    + cast(substring(myNum, 13, 1) as unsigned)
    + 2 * cast(substring(myNum, 14, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 14, 1) as unsigned) % 10
    + cast(substring(myNum, 15, 1) as unsigned)
    + 2 * cast(substring(myNum, 16, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 16, 1) as unsigned) % 10
    + cast(substring(myNum, 17, 1) as unsigned)
    + 2 * cast(substring(myNum, 18, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 18, 1) as unsigned) % 10
    + cast(substring(myNum, 19, 1) as unsigned)
    + 2 * cast(substring(myNum, 20, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 20, 1) as unsigned) % 10
  ) % 10)) % 10;

end

And here's another version, specifically optimised for an input of 6 chars:

create function `luhn3`(myNum varchar(6))
returns int
deterministic
begin

  if length(myNum) <> 6 then
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Argument must be a string of 6 digits';
  end if;

  return (10 - ((
    + cast(substring(myNum, 1, 1) as unsigned)
    + 2 * cast(substring(myNum, 2, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 2, 1) as unsigned) % 10
    + cast(substring(myNum, 3, 1) as unsigned)
    + 2 * cast(substring(myNum, 4, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 4, 1) as unsigned) % 10
    + cast(substring(myNum, 5, 1) as unsigned)
    + 2 * cast(substring(myNum, 6, 1) as unsigned) DIV 10
    + 2 * cast(substring(myNum, 6, 1) as unsigned) % 10
  ) % 10)) % 10;

end

Unsurprisingly, they all produce the same output:

WITH toCheck (num) AS (
    VALUES
        ROW(371950), ROW(367489), ROW(367457), ROW(350684), ROW(347772), ROW(345722),
        ROW(343464), ROW(339403), ROW(337069), ROW(331979), ROW(330500)
)
SELECT num, luhnBK(num), luhn1(num), luhn2(num), luhn3(num) FROM toCheck;

num	luhnBK(num)	luhn1(num)	luhn2(num)	luhn3(num)
371950	7	7	7	7
367489	2	2	2	2
367457	9	9	9	9
350684	7	7	7	7
347772	6	6	6	6
345722	3	3	3	3
343464	4	4	4	4
339403	8	8	8	8
337069	9	9	9	9
331979	5	5	5	5
330500	0	0	0	0

Using benchmark to run 1M iterations, I got the following execution times:

mysql> SELECT BENCHMARK(1000000, luhnBK('371950'));
+--------------------------------------+
| BENCHMARK(1000000, luhnBK('371950')) |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (35.93 sec)

mysql> SELECT BENCHMARK(1000000, luhn1('371950'));
+-------------------------------------+
| BENCHMARK(1000000, luhn1('371950')) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (22.38 sec)

mysql> SELECT BENCHMARK(1000000, luhn2('371950'));
+-------------------------------------+
| BENCHMARK(1000000, luhn2('371950')) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (9.43 sec)

mysql> SELECT BENCHMARK(1000000, luhn3('371950'));
+-------------------------------------+
| BENCHMARK(1000000, luhn3('371950')) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (4.14 sec)

Answer 2

I tested this on MySQL 8.2 concurrent with the comments from other users above.

create function luhn(myNum varchar(20))
returns int
deterministic
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
    -- CHANGE sIndex to sIndex+1:
    set digit = 0+substring(myNum, sIndex+1, 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

Test:

select luhn('371950');
select luhn('367489');
select luhn('367457');
select luhn('350684');
select luhn('347772');
select luhn('345722');
select luhn('343464');
select luhn('339403');
select luhn('337069');
select luhn('331979');
select luhn('330500');

Output:

+----------------+
| luhn('371950') |
+----------------+
|              7 |
+----------------+

+----------------+
| luhn('367489') |
+----------------+
|              2 |
+----------------+

+----------------+
| luhn('367457') |
+----------------+
|              9 |
+----------------+

+----------------+
| luhn('350684') |
+----------------+
|              7 |
+----------------+

+----------------+
| luhn('347772') |
+----------------+
|              6 |
+----------------+

+----------------+
| luhn('345722') |
+----------------+
|              3 |
+----------------+

+----------------+
| luhn('343464') |
+----------------+
|              4 |
+----------------+

+----------------+
| luhn('339403') |
+----------------+
|              8 |
+----------------+

+----------------+
| luhn('337069') |
+----------------+
|              9 |
+----------------+

+----------------+
| luhn('331979') |
+----------------+
|              5 |
+----------------+

+----------------+
| luhn('330500') |
+----------------+
|              0 |
+----------------+