How the MOD() function works in Mariadb?

The MOD operator is a useful tool for performing the modulo operation, which returns the remainder of a division. It can be used for various purposes, such as checking for divisibility, finding the last digit, or cycling through a sequence.

Syntax

The syntax of the MOD operator is as follows:

dividend MOD divisor

The operator takes two arguments:

dividend: A numeric value that represents the dividend of the division. It can be any valid expression that returns a numeric value, such as a column name, a literal, or a function.
divisor: A numeric value that represents the divisor of the division. It can be any valid expression that returns a numeric value, such as a column name, a literal, or a function.

The operator returns a numeric value that represents the remainder of the division, or NULL if any of the arguments are NULL or invalid. The data type of the return value depends on the data type of the arguments. If both arguments are integers, the return value is an integer. If either argument is a decimal or a floating-point value, the return value is a decimal value.

Examples

In this section, we will show some examples of how to use the MOD operator in different scenarios.

Example 1: Checking for divisibility

Suppose you want to check if a number is divisible by another number, such as 15 by 3, or 17 by 5. You can use the MOD operator to do so. For example, you can execute the following statements:

SELECT 15 MOD 3, 17 MOD 5;

This will return the remainder of the division, which is 0 if the number is divisible, or a positive value if the number is not divisible. For example, the result might look like this:

+----------+----------+
| 15 MOD 3 | 17 MOD 5 |
+----------+----------+
|        0 |        2 |
+----------+----------+

Note that the MOD operator returns a positive value even if the dividend or the divisor is negative. For example, the remainder of -15 divided by 3 is 0, and the remainder of 17 divided by -5 is 2.

Example 2: Finding the last digit

Suppose you want to find the last digit of a number, such as 1234 or 5678. You can use the MOD operator to do so. For example, you can execute the following statements:

SELECT 1234 MOD 10, 5678 MOD 10;

This will return the last digit of the number, which is the same as the remainder of the division by 10. For example, the result might look like this:

+-------------+-------------+
| 1234 MOD 10 | 5678 MOD 10 |
+-------------+-------------+
|           4 |           8 |
+-------------+-------------+

Note that the MOD operator returns the same value as the number if the divisor is 1. For example, the remainder of 1234 divided by 1 is 1234.

Example 3: Cycling through a sequence

Suppose you want to cycle through a sequence of values, such as 0, 1, 2, 3, 0, 1, 2, 3, and so on. You can use the MOD operator to do so. For example, you can execute the following statement:

SELECT (n MOD 4) AS seq FROM numbers;

This will return a sequence of values that repeats every 4 values, based on the value of n in the numbers table. For example, the result might look like this:

+-----+
| seq |
+-----+
| 0   |
| 1   |
| 2   |
| 3   |
| 0   |
| 1   |
| 2   |
| 3   |
+-----+

Note that the MOD operator returns the same value as the dividend if the divisor is greater than the dividend. For example, the remainder of 3 divided by 4 is 3.

There are some other functions that are related to the MOD operator and can be used to perform other arithmetic operations in Mariadb. Here are some of them:

DIV: This operator performs the integer division, which returns the quotient of a division. For example, 15 DIV 3 returns 5, and 17 DIV 5 returns 3.
+: This operator performs the addition, which returns the sum of two values. For example, 3 + 2 returns 5, and 10 + 10 returns 20.
-: This operator performs the subtraction, which returns the difference of two values. For example, 5 - 2 returns 3, and 10 - 20 returns -10.
*: This operator performs the multiplication, which returns the product of two values. For example, 3 * 2 returns 6, and 10 * 10 returns 100.
/: This operator performs the division, which returns the result of a division. For example, 6 / 2 returns 3, and 10 / 3 returns 3.3333333333333335.

Conclusion

The MOD operator is a powerful and flexible operator that can help you perform the modulo operation, which returns the remainder of a division. It can be used for various purposes, such as checking for divisibility, finding the last digit, or cycling through a sequence. You can also use some other related operators to perform other arithmetic operations, such as DIV, +, -, *, /, or ^. By using these operators, you can achieve a better calculation and manipulation of your numeric data.