MySQL - Modulus Operator(%, MOD)

The MySQL modulus operator (% or MOD) returns the remainder obtained from the division operation (right operator divided by the left operator) performed on the data stored in MySQL.

The modulus operator is also a part of arithmetic operators as it is performed on numbers only. It can be used with various SQL statements like SELECT, DELETE, UPDATE etc.

Syntax

Following is the syntax of MySQL MOD operator −

[SELECT|DELETE|UPDATE] x MOD y;

Where,

  • 'x' is the dividend, which is the number you want to find the remainder of.
  • 'y' is the divisor, which is the number by which you want to divide x.

Example

Following is an example of the "MOD" operator on two ordinary numbers. These numbers are not stored in the MySQL database tables, but the result is displayed as a result-set of SELECT statement −

SELECT 62555 MOD 59;

Output

This will produce the following result −

62555 MOD 59
15

Example

Let us see another simple example with float values −

SELECT 6255.55855 MOD 987546.965;

Output

This will produce the following result −

6255.55855 MOD 987546.965
6255.55855

Example

Let us create a table named CUSTOMERS using the CREATE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query adds 7 new records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Execute the below command to retrieve all the records from CUSTOMERS −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Here, we are using the "%" operator to display whether the AGE of the CUSTOMERS is even or odd −

SELECT NAME, IF(AGE%2 = 1, 'Odd', 'Even') as OddOrEven
FROM CUSTOMERS;

Output

This will produce the following result −

NAME OddOrEven
Ramesh Even
Khilan Odd
Kaushik Odd
Chaitali Odd
Hardik Odd
Komal Even
Muffy Even

Example

In this example, we are using modulus "%" operator with the DELETE statement to delete records from CUSTOMERS table that has even value in the AGE column −

DELETE FROM CUSTOMERS WHERE AGE%2 = 0;

Output

The output for the query above is produced as given below −

Query OK, 3 rows affected (0.01 sec)

Verification

Execute the below query to verify whether the above performed operation has been reflected or not −

SELECT * FROM CUSTOMERS;

As we can see in the output below, the Record with even AGE has been deleted.

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00

Example

Along with SELECT and DELETE statements, we can also use this operator with the UPDATE statement.

The following query updates the 'AGE' column in the previosly created CUSTOMERS table. It sets the AGE to the result of the modulo operation (123 % 10), which is 3. This update is only applied to rows where the 'NAME' column is 'Khilan' −

UPDATE CUSTOMERS
SET AGE = (123%10)
WHERE NAME = 'Khilan';

Output

This will produce the following result −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Execute the below query to verify the changes has been reflected or not −

Select * From CUSTOMERS;

Following is the updated CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
2 Khilan 3 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
Advertisements