如何使用 MySQL 有条件地处理除以零

How to conditionally handle division by zero with MySQL

提问by Pushpendra Kuntal

In MySQL, this query might throw a division by zero error:

在 MySQL 中，此查询可能会引发除以零错误：

SELECT ROUND(noOfBoys / noOfGirls) AS ration
FROM student;

If noOfGirlsis 0then the calculation fails.

如果noOfGirls是，0则计算失败。

What is the best way to handle this?

处理这个问题的最佳方法是什么？

I would like to conditionally change the value of noOfGirlsto 1when it is equal to 0.

我想有条件改变的值noOfGirls来1时，它等于0。

Is there a better way?

有没有更好的办法？

回答by Icarus

Yes, you can do a case:

是的，你可以做一个案例：

select case when noOfGirls=0 then noOfBoys 
       else  round(noOfBoys/noOfGirls) end as ration 
from student;

But you probably want:

但你可能想要：

select case when noOfGirls=0 then 1 
       else  round(noOfBoys/noOfGirls) end as ration 
from student;

回答by Romain

You can use this (over-expressive) way:

您可以使用这种（过度表达）方式：

select IF(noOfGirls=0, NULL, round(noOfBoys/noOfGirls)) as ration from student;

Which will put out NULL if there are no girls, which is effectively what 0/0 should be in SQL semantics.

如果没有女孩，它将输出 NULL，这实际上是 SQL 语义中的 0/0 应该是什么。

MySQL will anyway give NULLif you try to do 0/0, as the SQL meaning fo NULLis "no data", or in this case "I don't know what this value can be".

NULL如果您尝试这样做0/0，MySQL 无论如何都会给出，因为 SQL 意思NULL是“无数据”，或者在这种情况下“我不知道这个值是什么”。

回答by itsmeee

Well if you want to set noOfGirls to 1 if it's 0, then the following should work:

好吧，如果您想将 noOfGirls 设置为 1（如果它是 0），那么以下应该起作用：

SELECT ROUND(noOfBoys / if(noOfGirls, noOfGirls, 1)) as ration FROM student;

回答by michal.jakubeczy

Division by NULLactually works in MySQL server and returns NULL. So you can do:

除法NULL实际上在 MySQL 服务器中工作并返回NULL. 所以你可以这样做：

SELECT ROUND(noOfBoys / NULLIF(noOfGirls, 0)) AS ration FROM student;

I think NULLratio is the most accurate for this case. If you need the ratio to be equal to numOfBoysthen you can use:

我认为NULL对于这种情况，比率是最准确的。如果您需要比率等于，numOfBoys那么您可以使用：

SELECT COALESCE(ROUND(noOfBoys / NULLIF(noOfGirls, 0)), noOfBoys) AS ration FROM student;

回答by Raffael

check if the denominator is zero and the nominator is not NULL - if yes then use 'NaN'.

检查分母是否为零且分母不为 NULL - 如果是，则使用“NaN”。

回答by Ariful Islam

select
    case student.noOfGirls
           when 0 then 1
           else  round(noOfBoys/noOfGirls)
    end as ration
from
    `student`