Two examples of if and ifnull in MySQL

1.IFNULL statement: Ifnull (EXP1, EXP2), return EXP2 if EXP1 is null, return EXP1 if not null

The code is as follows

Copy Code

mysql> SELECT ifnull (null, 100); +-------------------+ | Ifnull (NULL, 100) | +-------------------+ | 100 | +-------------------+ mysql> SELECT ifnull (0, 100); +----------------+ | Ifnull (0, 100) | +----------------+ | 0 | +----------------+ Mysql> SELECT Ifnull (-10, 100); +------------------+ | Ifnull (-10, 100) | +------------------+ | -10 | +------------------+ Mysql> SELECT ifnull (10, 100); +-----------------+ | Ifnull (10, 100) | +-----------------+ | 10 | +-----------------+ mysql> SELECT ifnull (' null ', 100); +---------------------+ | Ifnull (' null ', 100) | +---------------------+ | null | +---------------------+ Mysql> SELECT Ifnull (false, 100); +--------------------+ | Ifnull (False, 100) | +--------------------+ | 0 | +--------------------+ Mysql> SELECT Ifnull (true, 100); +-------------------+ | Ifnull (True, 100) | +-------------------+ | 1 | +-------------------+

2.IF statement: if (EXP1, EXP2, EXP3) If EXP1 is true (exp1 <> 0 && exp1 <> null)

Return to EXP2 or return EXP3

The code is as follows

Copy Code

Mysql> SELECT IF (STRCMP (' str ', ' str1 '), ' yes ', ' no '); +----------------------------------------+ | IF (STRCMP (' str ', ' str1 '), ' yes ', ' no ') | +----------------------------------------+ | Yes | +----------------------------------------+ mysql> SELECT IF (0, ' yes ', ' www.111cn.net '); +--------------------+ | IF (0, ' yes ', ' no ') | +--------------------+ | No | +--------------------+ mysql> SELECT IF (null, ' yes ', ' no '); +-----------------------+ | IF (null, ' yes ', ' no ') | +-----------------------+ | No | +-----------------------+ mysql> SELECT IF (' null ', ' yes ', ' no '); +-------------------------+ | IF (' null ', ' yes ', ' no ') | +-------------------------+ | No | +-------------------------+ Mysql> SELECT IF (false, ' yes ', ' no '); +------------------------+ | IF (False, ' yes ', ' no ') | +------------------------+ | No | +------------------------+ Mysql> SELECT IF ( -10, ' yes ', ' no '); +----------------------+ | IF ( -10, ' yes ', ' no ') | +----------------------+ | Yes | +----------------------+ Mysql> SELECT IF (Ten, ' Yes ', ' no '); +---------------------+ | IF (+, ' yes ', ' no ') | +---------------------+ | Yes | +---------------------+ mysql> SELECT IF (' 0 ', ' yes ', ' no '); +----------------------+ | IF (' 0 ', ' yes ', ' no ') | +----------------------+ | No | +----------------------+