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

How to import js configuration file on Vue server

Table of contents background accomplish Supplemen...

Methods and problems encountered in installing mariadb in centos under mysql

Delete the previously installed mariadb 1. Use rp...

MySQL uses binlog logs to implement data recovery

MySQL binlog is a very important log in MySQL log...

CSS setting div background image implementation code

Adding background image control to a component re...

mysql8.0.23 linux (centos7) installation complete and detailed tutorial

Table of contents What is a relational database? ...

Vue template configuration and webstorm code format specification settings

Table of contents 1. Compiler code format specifi...

How to create, save, and load Docker images

There are three ways to create an image: creating...

How to implement rounded corners with CSS3 using JS

I found an example when I was looking for a way t...

Analysis of MySQL multi-table joint query operation examples

This article describes the MySQL multi-table join...

Advanced explanation of javascript functions

Table of contents Function definition method Func...

Vue interpretation of responsive principle source code analysis

Table of contents initialization initState() init...

How to install MySQL 8.0 and log in to MySQL on MacOS

Follow the official tutorial, download the instal...

Solution to MySQL being unable to start due to excessive memory configuration

Problem Description MySQL reports an error when s...

HTML Basics Must-Read - Comprehensive Understanding of CSS Style Sheets

CSS (Cascading Style Sheet) is used to beautify H...