mysql中使用if或者ifnull函数将NULL值映射为其他值

Summary: in this tutorial, you will to learn how to map NULL values onto other values for a better data representation.

Dr.E.F.Codd, who is the creator of relational model for database, introduced the NULL concept in the relational database theory. According to Dr.E.F.Codd, NULL means unknown value or missing information.

MySQL supports NULL values that represent the concept of missing or inapplicable information.

In the database, we have to store data in the columns of tables that contain NULL values. When we present the data to the users in the form of reports, it doesn’t make sense to display the NULL values. In order to make the data more readable and understandable, we have to display NULL values as other values such as unknown, missing or not available (N/A). In order to do this, we can use the MySQL IF function.

The syntax of  the IF function is as follows:

IF(exp,exp_result1,exp_result2)

If the exp evaluates to TRUE (when exp <> 0 and exp <> NULL), the IF function returns the value of the exp_result1 otherwise it returns the value of exp_result2. The returned value of the IF function can be a string or a number, depending on the exp_result1 and exp_result2 expressions.

Let’s practice with some examples to get a better understanding. We will work with the customers table in the sample database.

The following is the partial data in the customers table that includes customername, state and country:

SELECT customername, state, country FROM customers ORDER BY country

From the result set above, we see that the state values are  not available for some customers therefore we can use the IF function to display NULL value as N/A:

SELECT customername,         IF(state IS NULL,"N/A",state) state,         country FROM customers ORDER BY country

MySQL also provides IFNULL function that allows you to handle NULL values directly. The following is the syntax of the IFNULL function:

IFNULL(exp,exp_result)

The IFNULL function returns the value of the exp_result expression if the exp evaluates to NULL value, otherwise it returns the value of the exp expression.

The following query demonstrates how to use IFNULL function to display NULL as N/A for state values:

SELECT customername,         IFNULL(state,"N/A") state,         country FROM customers ORDER BY country

In this tutorial, we have shown you how to use the IF and IFNULL functions to map the NULL values onto other values for presenting data in a readable manner.

原文链接：http://outofmemory.cn/mysql/tips/avoid-displaying-null-values-by-mapping-to-other-values