Solution to MySQL IFNULL judgment problem

Problem: The null type data returned by mybatis disappears, causing errors in the front-end display

Idea: If the query result is a null value, it should be converted into an empty string. Of course, judgment can also be made on the front end, but this function is required to be implemented in the background.

Solution:

Use the following method to query:

SELECT IFNULL(sex,'') AS sex FROM user --If the sex value is null, it will be assigned an empty string.

However, if the query statement itself is null, then what is returned to the front end is still null, which needs to be determined in the code.
for example:

SELECT IFNULL(sex,'') AS sex FROM user WHERE id=100

The return result of this statement itself is null, so it will not execute the IFNULL function, and the final return result is of course null.

So my approach is to add judgments in the code:

if(ansList == null || ansList.size() == 0){...}

MYSQL IFNULL(expr1,expr2) function

If expr1 is not NULL, IFNULL() returns expr1, otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

mysql> select IFNULL(1,0);  
           -> 1  
mysql> select IFNULL(0,10);  
           -> 0  
mysql> select IFNULL(1/0,10);  
           -> 10  
mysql> select IFNULL(1/0,yes);  
           -> yes

IF(expr1,expr2,expr3) Function

If expr1 is TRUE (expr1<>0 and expr1<>NULL), then IF() returns expr2, otherwise it returns expr3. IF() returns a number or string value, depending on the context in which it is used.

mysql> select IF(1>2,2,3);  
           -> 3  
mysql> select IF(1<2,yes,no);  
           -> yes  
mysql> select IF(strcmp(test,test1),yes,no);  
           -> no  
  
expr1 is evaluated as an integer value, which means that if you are testing a floating point or string value, you should use a comparison operator to do so.    
  
mysql> select IF(0.1,1,0);  
           -> 0  
mysql> select IF(0.1<>0,1,0);  
           -> 1

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.