How to fix ERROR 1365: Division by zero in MySQL

The NULLIF() function returns NULL if the divisor equals zero, preventing the division operation:

SELECT 
  total_sales,
  revenue / NULLIF(units_sold, 0) AS price_per_unit
FROM sales
WHERE date >= CURDATE();

When units_sold is 0, NULLIF() returns NULL, making the division result NULL instead of an error. This is the most concise solution for most use cases.

Add explicit conditional logic to handle the zero case:

SELECT 
  total_sales,
  CASE 
    WHEN units_sold = 0 THEN NULL
    ELSE revenue / units_sold
  END AS price_per_unit
FROM sales;

CASE provides more control—you can return NULL, 0, a default value, or even log the condition. Use this when you need custom behavior beyond just returning NULL.

The IF() function provides a concise alternative to CASE for simple conditions:

SELECT 
  total_sales,
  IF(units_sold = 0, NULL, revenue / units_sold) AS price_per_unit
FROM sales;

IF() is equivalent to the CASE statement but more compact. It returns NULL when units_sold is 0, otherwise performs the division.

Exclude rows where the divisor is zero before performing calculations:

SELECT 
  total_sales,
  revenue / units_sold AS price_per_unit
FROM sales
WHERE units_sold > 0;

This approach works when you only need results for valid calculations. However, it loses rows where the divisor is zero, which may not always be acceptable.

For columns that should never be zero, add a CHECK constraint when defining the table:

CREATE TABLE sales (
  id INT PRIMARY KEY,
  units_sold INT NOT NULL CHECK (units_sold > 0),
  revenue DECIMAL(10, 2) NOT NULL
);

Or on an existing table:

ALTER TABLE sales
ADD CONSTRAINT check_units_positive CHECK (units_sold > 0);

This enforces data integrity at the database level, preventing problematic data from being inserted in the first place.

Check your current SQL mode to understand when division by zero is treated as an error:

SELECT @@sql_mode;

If ERROR_FOR_DIVISION_BY_ZERO is enabled (typically as part of STRICT_ALL_TABLES or TRADITIONAL mode), division by zero will error during INSERT/UPDATE/CREATE TABLE operations. To temporarily disable strict mode:

SET sql_mode='';

Or remove just the error flag:

SET sql_mode='STRICT_TRANS_TABLES';

Note: Changing sql_mode at runtime only affects the current session. To make permanent changes, edit your MySQL configuration file (my.cnf or my.ini) and restart the server.