MySQL IF Statement

Summary: in this tutorial, you will learn how to use MySQL IF statement to execute a block of SQL code based on conditions.

The MySQL IF statement allows you to execute a set of SQL statements based on a certain condition or value of an expression. To form an expression in MySQL, you can combine literals, variables, operators, and even functions. An expression can return three value TRUE, FALSE or NULL.

Mysql if 声明可以根据特定的条件或者表达式值进行执行。在mysql中构造表达式，可以是常量、变量、操作符甚至函数。表达式可以返回TRUE、FALSE或者NULL

MySQL IF statement syntax

The following illustrates the syntax of the IF statement:

1 2 3 4

IF if_expression THEN commands    [ ELSEIF elseif_expression THEN commands ]    [ ELSE commands ]    END IF ;

If the if_expression evaluates to TRUE the commands in the IF branch will execute. If it evaluates to FALSE, MySQL will check the elseif_expression and execute the commands in ELSEIF branch if theelseif_expression evaluates to TRUE.

The IF statement may have multiple ELSEIF branches to check multiple expressions.  If no expression evaluates to TRUE, the commands in the ELSE branch will execute.

MySQL IF statement examples

Let’s take a look at an example of how to use MySQL IF statements.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

DELIMITER $ $   CREATE PROCEDURE GetCustomerLevel (      in    p_customerNumber int ( 11 ) ,      out p_customerLevel    varchar ( 10 ) ) BEGIN      DECLARE creditlim double ;        SELECT creditlimit INTO creditlim      FROM customers      WHERE customerNumber = p_customerNumber ;        IF creditlim > 50000 THEN      SET p_customerLevel = 'PLATINUM' ;      ELSEIF ( creditlim <= 50000 AND creditlim >= 10000 ) THEN          SET p_customerLevel = 'GOLD' ;      ELSEIF creditlim < 10000 THEN          SET p_customerLevel = 'SILVER' ;      END IF ;   END $ $

We pass customer number to the stored procedure to get customer level based on credit limit. We use IF ELSEIF and ELSE statement to check customer credit limit against multiple values.

The following is the flow chart  that demonstrates the logic of determining customer level.

In this tutorial, you have learned how to use MySQL IF statement to execute a block of SQL code based on conditions.

Related Tutorials

• MySQL CASE Statement
• Loop in Stored Procedures