Dede Lukmanul Hakim Dede Lukmanul Hakim -3 years ago 220
MySQL Question

how to solve error:1365 division by 0 in mysql

I want to generate insert to table from query but this query might throw a division by zero error:

insert into rpp
select IFNULL(IF((IF(((((((`s`.`smi` + `s`.`jmb`) + `s`.`ruah`) +
`s`.`stg_jd`) + `s`.`jdw_pro`) / `p`.`rate`) <= 2),
((`p`.`rate` * 4) - ((((`s`.`smi` + `s`.`jmb`) + `s`.`ruah`)
+ `s`.`stg_jd`) + `s`.`jdw_pro`)),0) > 0),
(FLOOR((IF(((((((`s`.`smi` + `s`.`jmb`) + `s`.`ruah`)
+ `s`.`stg_jd`) + `s`.`jdw_pro`) / `p`.`rate`) <= 2),
((`p`.`rate` * 4) - ((((`s`.`smi` + `s`.`jmb`) +
`s`.`ruah`) + `s`.`stg_jd`) + `s`.`jdw_pro`)),
0) / `p`.`bets`)) * `p`.`bets`),0),0) AS `rp`
FROM (`produk` `p`LEFT JOIN `stok` `s` ON ((`p`.`kode` = `s`.`kode_produk`)))

Thanks in advance!

Answer Source

There are multiple ways to solve this.

Add logic, that you divide by 1 instead or however you want to handle it. For this there's this wonderful function IF() which works like IF(condition, then, else), for example IF(my_column = 0, 1, my_column).

Another way is by setting your sql_mode accordingly. Have a look at the sql_mode error_for_division_by_zero.

The ERROR_FOR_DIVISION_BY_ZERO mode affects handling of division by zero, which includes MOD(N,0). For data-change operations (INSERT, UPDATE), its effect also depends on whether strict SQL mode is enabled.

If this mode is not enabled, division by zero inserts NULL and produces no warning.

If this mode is enabled, division by zero inserts NULL and produces a warning.

If this mode and strict mode are enabled, division by zero produces an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, division by zero inserts NULL and produces a warning.

For SELECT, division by zero returns NULL. Enabling ERROR_FOR_DIVISION_BY_ZERO causes a warning to be produced as well, regardless of whether strict mode is enabled.

As of MySQL 5.7.4, ERROR_FOR_DIVISION_BY_ZERO is deprecated. In MySQL 5.7.4 through 5.7.7, ERROR_FOR_DIVISION_BY_ZERO does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, ERROR_FOR_DIVISION_BY_ZERO does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if ERROR_FOR_DIVISION_BY_ZERO is enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7.

Because ERROR_FOR_DIVISION_BY_ZERO is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download