MySQL integer operation null value handling note points

Clevercode recently when exporting a report, when the integer is subtracted, the integer minus null is found to be null. This is a matter of detail, I hope you will pay attention later.

1 The data total,used in the table are all shaped and agreed to be empty.

2 problematic arithmetic statement subtraction problem

Select Id,total,used, (total-used) as has a from test_table

3 correct wording using Ifnull. To handle null.

Select Id,ifnull (total,0) as Total,ifnull (used,0) as used, (Ifnull (total,0)-ifnull (used,0)) as has from test_table

4 recommendations
1) in the design of the table, it is assumed that this column is required to operate the column, it is recommended to set the default value, for example 0. 2) in the operation of the Shaping column, whether it is addition, subtraction, multiplication, and so on. You need to use Ifnull to handle the column. Otherwise, the report data will be wrong.
These are the small details of the problem, we can be clear at a glance, we hope that we pay attention to the details. Otherwise, a financial statement was calculated in error. The consequences are very serious!

Copyright Notice:1) original works, from the "Clevercode blog", please do not reprint, otherwise hold the copyright legal responsibility.
2) Original address: http://blog.csdn.net/clevercode/article/details/47035975.

3) category address (MySQL database summary): http://blog.csdn.net/clevercode/article/category/3262205 (blog continued to add, concern please bookmark)
4) Welcome everyone to pay attention to my blog many other wonderful content: Http://blog.csdn.net/CleverCode.

4 recommendations

MySQL integer operation null value handling note points