MySQL MOD() function

MOD() function

MySQL MOD() returns the remainder of a number divided by another number. This function also works on fractional values and returns the exact remainder. The function returns NULL when the value of divisor is 0.

This function is useful in -

• It is frequently used to determine if a number is even or odd.
• When dealing with calendar operations, MOD() can be used to calculate day of the week, recurring events, or cyclic patterns.
• MOD() is valuable for partitioning large datasets across multiple servers or partitions.
• In database management, It helps determine which partition a record should be stored in.
• MOD() is often used in hash functions, which are fundamental in data structures like hash tables and hash maps.
• MOD() can be used in error handling to determine if a specific condition or count is within expected ranges.
• In some cases, MOD() can be used as part of cryptographic algorithms or operations involving prime numbers.

Syntax:

MOD(N,M), N % M, N MOD M;

Arguments:

MySQL Version: 8.0

Pictorial presentation of MySQL MOD() function

Example of MySQL MOD() function

Code:

SELECT MOD(17,5);

Explanation:

The above statement returns the remainder of 17 divided by 5.

Output:

mysql> SELECT MOD(17,5);
+-----------+
| MOD(17,5) |
+-----------+
|         2 | 
+-----------+
1 row in set (0.00 sec)

Example : MOD() function using MOD keyword

Code:

SELECT 17 MOD 5;

Explanation:

The above MySQL statement returns the remainder of 17 divided by 5. Notice that MOD keyword is used.

Output:

mysql> SELECT 17 MOD 5;
+----------+
| 17 MOD 5 |
+----------+
|        2 | 
+----------+
1 row in set (0.00 sec)

Example : MOD() function using fractional value

Code:

SELECT MOD( 4.567, 2.42 );

Explanation:

The above MySQL statement returns the remainder of the fractional number 4.567 divided by the fractional number 2.42.

Output:

mysql> SELECT MOD( 4.567, 2.42 );
+--------------------+
| MOD( 4.567, 2.42 ) |
+--------------------+
|              2.147 | 
+--------------------+
1 row in set (0.00 sec)

Example : MOD() function using 0(zero) divisor

Code:

SELECT MOD( 13,0 );

Explanation:

The above MySQL statement returns NULL because the value of the divisor is 0.

Output:

mysql> SELECT MOD( 13,0 );
+-------------+
| MOD( 13,0 ) |
+-------------+
|        NULL | 
+-------------+
1 row in set (0.00 sec)

All Mathematical Functions

Previous: LOG10()
Next: OCT()