How the DIV operator works in Mariadb?

The DIV operator is a built-in operator in Mariadb that performs integer division, which means it divides the dividend by the divisor and returns the integer result of the division with any decimal places truncated from the value.

Posted on

The DIV operator is a built-in operator in Mariadb that performs integer division, which means it divides the dividend by the divisor and returns the integer result of the division with any decimal places truncated from the value. The DIV operator is similar to the FLOOR() function, but is safe with BIGINT values. If the ERROR_ON_DIVISION_BY_ZERO SQL_MODE is used, a division by zero produces an error. Otherwise, it returns NULL. The remainder of a division can be obtained using the MOD operator or the % operator.

Syntax

The syntax of the DIV operator is as follows:

dividend DIV divisor

The operands dividend and divisor are numeric values. The operator returns an integer value that is the result of the integer division. If either dividend or divisor is NULL or not a valid numeric value, the operator returns NULL.

Examples

Here are some examples of using the DIV operator with different types of numeric values.

Example 1: Integer values

The DIV operator returns the same result as the FLOOR() function when both operands are integer values. For example, the following query returns 2, because 7 divided by 3 is 2 with a remainder of 1.

SELECT 7 DIV 3;

The output is:

+---------+
| 7 DIV 3 |
+---------+
|       2 |
+---------+

However, the following query returns -3, because -7 divided by 3 is -2 with a remainder of -1, and the FLOOR() function rounds down to the nearest integer.

SELECT -7 DIV 3;

The output is:

+----------+
| -7 DIV 3 |
+----------+
|       -2 |
+----------+

Example 2: Decimal values

The DIV operator returns the integer part of the division when one or both operands are decimal values. For example, the following query returns 3, because 10.5 divided by 3.5 is 3 with a decimal part of 0.142857.

SELECT 10.5 DIV 3.5;

The output is:

+--------------+
| 10.5 DIV 3.5 |
+--------------+
|            3 |
+--------------+

However, the following query returns 0, because 3.5 divided by 10.5 is 0.333333 with a decimal part of 0.333333.

SELECT 3.5 DIV 10.5;

The output is:

+--------------+
| 3.5 DIV 10.5 |
+--------------+
|            0 |
+--------------+

Example 3: BIGINT values

The DIV operator is safe with BIGINT values, which means it can handle values that exceed the range of INT values. For example, the following query returns 2147483648, which is the result of dividing the maximum BIGINT value by the maximum INT value.

SELECT 9223372036854775807 DIV 4294967295;

The output is:

+------------------------------------+
| 9223372036854775807 DIV 4294967295 |
+------------------------------------+
|                         2147483648 |
+------------------------------------+

However, the following query returns NULL, because the divisor is zero and the ERROR_ON_DIVISION_BY_ZERO SQL_MODE is not used.

SELECT 9223372036854775807 DIV 0;

The output is:

+---------------------------+
| 9223372036854775807 DIV 0 |
+---------------------------+
|                      NULL |
+---------------------------+

There are some other functions that are related to the DIV operator in Mariadb. Here are some of them:

  • FLOOR(): This function returns the largest integer value that is less than or equal to a given numeric value. It is similar to the DIV operator when both operands are integer values, but may produce different results when one or both operands are decimal values.
  • MOD(): This function returns the remainder of a division. It is equivalent to the % operator, but can also handle string arguments that are converted to numbers.
  • DIVINT(): This function performs integer division and returns the integer result of the division. It is equivalent to the DIV operator, but can also handle string arguments that are converted to numbers.
  • /: This operator performs decimal division and returns the decimal result of the division. It may produce different results from the DIV operator when one or both operands are decimal values.

For example, the following query shows the results of different division functions and operators for two decimal values.

SELECT 10.5 DIV 3.5, FLOOR(10.5 / 3.5), MOD(10.5, 3.5), 10.5 / 3.5;

The output is:

+--------------+-------------------+----------------+------------+
| 10.5 DIV 3.5 | FLOOR(10.5 / 3.5) | MOD(10.5, 3.5) | 10.5 / 3.5 |
+--------------+-------------------+----------------+------------+
| 3            | 3                 | 0.5            | 3.0000     |
+--------------+-------------------+----------------+------------+

Conclusion

The DIV operator is a useful operator in Mariadb that performs integer division, which means it divides the dividend by the divisor and returns the integer result of the division with any decimal places truncated from the value. The DIV operator is similar to the FLOOR() function, but is safe with BIGINT values. If the ERROR_ON_DIVISION_BY_ZERO SQL_MODE is used, a division by zero produces an error. Otherwise, it returns NULL. The remainder of a division can be obtained using the MOD operator or the % operator. There are some other functions that are related to the DIV operator, such as FLOOR(), MOD(), and DIVINT(). These functions can help us to perform different types of division operations in Mariadb.