MySQL division by zero错误：UPDATE语句中voteup与votedown均为0时的处理方案咨询

Let's tackle that division by zero error you're hitting when both voteup and votedown are 0. The root problem is straightforward: when voteup + votedown equals 0, trying to divide by it throws an error. Here are two clean, practical ways to handle this condition directly in your query:

Option 1: Use NULLIF + IFNULL for concise handling #

This approach avoids division by zero by converting a 0 denominator to NULL, then replacing that NULL with a default score value of your choice:

UPDATE pics 
SET voteup = voteup - 1, 
    score = ROUND(IFNULL((voteup * 100) / NULLIF(voteup + votedown, 0), 0))

• NULLIF(voteup + votedown, 0) returns NULL if the total votes sum to 0, otherwise it returns the sum itself.
• Dividing by NULL gives a NULL result, which IFNULL then replaces with 0 (you can swap this with another default like 50 if that makes more sense for your business logic).

Option 2: Use CASE for explicit conditional logic #

If you prefer more readable, explicit code, a CASE statement makes the condition crystal clear:

UPDATE pics 
SET voteup = voteup - 1, 
    score = CASE 
        WHEN voteup + votedown = 0 THEN 0  -- Set your preferred default score here
        ELSE ROUND((voteup * 100) / (voteup + votedown))
    END

This checks if the total votes are 0 first. If yes, it sets score to your default value. If not, it runs the normal score calculation as you originally intended.

Quick side note #

Keep in mind: if voteup was 0 before the update, subtracting 1 will make it -1. If votedown is also 0, the sum becomes -1—which won't trigger division by zero, but the score calculation will result in 100 (since -1 * 100 / -1 = 100). If negative votes don't make sense for your use case, you might want to add an extra check (like ensuring voteup doesn't drop below 0), but that's separate from your original division by zero issue.

内容的提问来源于stack exchange，提问作者TheGreatCornholio