Skip to main content
Documents
Share
LinkedIn
Facebook
Twitter
Copy
ClausesStatementsKeywordsExpressionsFunctionsIndexesPerformance Optimization

MySQL MOD() Function

The `MOD()` function in MySQL computes the remainder of the division of two numbers. It is commonly used for operations requiring modulo arithmetic, such as determining if a number is even or odd.

Usage

The `MOD()` function is used to find the remainder when one numeric expression is divided by another. It is particularly useful in scenarios involving cyclic operations or determining divisibility.

MOD(N, M);

In this syntax, `N` is the dividend, and `M` is the divisor; the function returns the remainder of `N` divided by `M`.

Examples

1. Basic Modulo Operation

SELECT MOD(10, 3);

This query returns `1`, which is the remainder of `10` divided by `3`.

2. Using MOD() with Table Data

SELECT order_id, MOD(order_id, 2) AS remainder
FROM orders;

This example calculates the remainder of each `order_id` divided by `2`, useful for identifying even or odd order IDs.

3. Conditional Logic with MOD()

SELECT employee_id
FROM employees
WHERE MOD(employee_id, 5) = 0;

This example selects `employee_id`s that are multiples of 5, using the `MOD()` function to filter results.

4. Handling Negative Numbers

SELECT MOD(-10, 3);

This query returns `-1`, illustrating how `MOD()` handles negative dividends by returning a remainder with the same sign as the dividend. Similarly, `SELECT MOD(10, -3);` returns `1`, reflecting the sign of the divisor.

Tips and Best Practices

  • Use for cyclic operations. Utilize `MOD()` for tasks like distributing items evenly across groups.
  • Check for zero divisor. Ensure the divisor is not zero to prevent errors in execution. Attempting `MOD(N, 0)` results in a `NULL` value, as division by zero is undefined.
  • Combine with other functions. Pair `MOD()` with functions like `FLOOR()` or `CEIL()` for complex arithmetic operations.
  • Optimize performance. Use `MOD()` in `WHERE` clauses cautiously, as it can impact query performance if not indexed properly. Consider creating indexes on columns involved in `MOD()` operations or using database-specific optimizations to enhance performance.