Solution to MySQL IFNULL judgment problem

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.

You may also be interested in:
  • Solutions to invalid is Null segment judgment and IFNULL() failure in MySql
  • Summary of knowledge points related to null (IFNULL, COALESCE and NULLIF) in MySQL
  • Detailed explanation of IFNULL() and COALESCE() functions to replace null in MySQL
  • A brief discussion on ifnull() function similar to nvl() function in MySQL
  • Detailed explanation of IFNULL, NULLIF and ISNULL usage in MySql
  • A brief discussion on the usage of SQL Server's ISNULL function and MySQL's IFNULL function
  • Introduction to the difference between IFNULL, IF, and CASE in MySQL
  • Instructions for nested use of MySQL ifnull

<<:  How to Communicate with Other Users on the Linux Command Line

>>:  Detailed explanation of how to adjust Linux command history

Recommend

Solution to the problem that Java cannot connect to MySQL 8.0

This article shares a collection of Java problems...

What is HTML?

History of HTML development: HTML means Hypertext...

Linux MySQL root password forgotten solution

When using the MySQL database, if you have not lo...

Solve the problem of OpenLayers 3 loading vector map source

1. Vector Map Vector graphics use straight lines ...

SQL-based query statements

Table of contents 1. Basic SELECT statement 1. Qu...

MySQL 5.7.18 installation and configuration tutorial under Windows

This article shares the installation and configur...

MySQL 20 high-performance architecture design principles (worth collecting)

Open Source Database Architecture Design Principl...

Detailed explanation of using MySQL where

Table of contents 1. Introduction 2. Main text 2....

How to use Vue to develop public account web pages

Table of contents Project Background start Create...

What are mysql dirty pages?

Table of contents Dirty pages (memory pages) Why ...

Optimization analysis of Limit query in MySQL optimization techniques

Preface In actual business, paging is a common bu...

Several ways to center a box in Web development

1. Record several methods of centering the box: 1...

Detailed explanation of the abbreviation of state in react

Preface What is state We all say that React is a ...