MySQL - Modulus Operator(%, MOD)

Quiz

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

Print Page