MySQL MOD() Function

 The MOD() function in MySQL is used to calculate the remainder of the division of one number by another. It is commonly used in mathematical and logical operations where modular arithmetic is required.

Syntax:

MOD(N, M)

• N: The dividend (numerator).
• M: The divisor (denominator).

Behavior:

• The function returns the remainder of $N \div M$.
• If M is 0, the function returns NULL to avoid a division-by-zero error.
• The function works with both integer and floating-point numbers.

Examples:

1. Basic Usage with Integers:

   SELECT MOD(10, 3); -- Output: 1 (10 ÷ 3 has a remainder of 1)
   

2. With Negative Numbers:

   SELECT MOD(-10, 3); -- Output: -1
   SELECT MOD(10, -3); -- Output: 1
   

3. With Floating-Point Numbers:

   SELECT MOD(10.5, 3); -- Output: 1.5 (10.5 ÷ 3 leaves a remainder of 1.5)
   

4. When Divisor is Zero:

   SELECT MOD(10, 0); -- Output: NULL
   

Alternative Syntax:

You can also use the modulus operator % as shorthand for the MOD() function:

SELECT 10 % 3; -- Output: 1

Use Cases:

1. Identifying Even or Odd Numbers:

   SELECT MOD(10, 2); -- Output: 0 (Even)
   SELECT MOD(11, 2); -- Output: 1 (Odd)
   

2. Rotating Through Values:

   SELECT MOD(ROW_NUMBER() OVER (), 3) AS group_id FROM my_table;
   

   This can divide rows into groups cyclically.

3. Finding Remainders in Financial Calculations: For example, calculating remaining stock or dividing items evenly among groups.