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 −

Example

Let us see another simple example with float values −

SELECT 6255.55855 MOD 987546.965;

Output

This will produce the following result −

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 −

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 −

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.

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 −