MySQL 流程控制函式
-
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSEresult] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.
mysql> SELECT CASE 1 WHEN 1 THEN 'one' -> WHEN 2 THEN 'two' ELSE 'more' END; -> 'one' mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; -> 'true' mysql> SELECT CASE BINARY 'B' -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; -> NULL
The return type of a CASE expression is the compatible aggregated type of all return values, but also depends on the context in which it is used. If used in a string context, the result is returned as a string. If used in a numeric context, the result is returned as a decimal, real, or integer value.
NoteThe syntax of the CASE expression shown here differs slightly from that of the SQL CASE statement described inSection 13.6.5.1, “CASE Syntax”, for use inside stored programs. The CASE statement cannot have an ELSE NULLclause, and it is terminated with END CASE instead of END.
-
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF()returns a numeric 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'),'no','yes'); -> 'no'
If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULLexpression.
The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows.
Expression Return Value expr2 or expr3 returns a string string expr2 or expr3 returns a floating-point value floating-point expr2 or expr3 returns an integer integer If expr2 and expr3 are both strings, the result is case sensitive if either string is case sensitive.
NoteThere is also an IF statement, which differs from the IF() function described here. See Section 13.6.5.2, “IFSyntax”.
-
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(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'yes'); -> 'yes'
The default result value of IFNULL(expr1,expr2) is the more “general” of the two expressions, in the orderSTRING, REAL, or INTEGER. Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:
mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test; mysql> DESCRIBE tmp; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | test | varbinary(4) | NO | | | | +-------+--------------+------+-----+---------+-------+
In this example, the type of the test column is VARBINARY(4).
-
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 =expr2 THEN NULL ELSE expr1 END.
mysql> SELECT NULLIF(1,1); -> NULL mysql> SELECT NULLIF(1,2); -> 1
Note that MySQL evaluates expr1 twice if the arguments are not equal.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/195110/viewspace-1084370/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 非常有用的MySQL控制流程函式薦MySql函式
- MySQL 控制流函式MySql函式
- 【Mysql 學習】流程函式MySql函式
- Scala(一):函式、流程控制、引數函式
- shell系統函式和流程控制函式
- JavaScript的流程控制語句以及函式JavaScript函式
- php易錯筆記-流程控制,函式PHP筆記函式
- 2. 流程控制 與 函式 |《 刻意學習 Golang 》函式Golang
- Go語言流程控制結構和函式解析Go函式
- Python 3 快速入門 2 —— 流程控制與函式Python函式
- mysql函式MySql函式
- (13)mysql 中的流程控制MySql
- 《MySQL 入門教程》第 13 篇 CASE 表示式與控制流函式MySql函式
- 控制make的函式函式
- Mysql 常用函式(15)- upper 函式MySql函式
- 【Mysql 學習】日期函式函式MySql函式
- MySQL 常用函式MySql函式
- MySQL 常用函式。MySql函式
- MySQL函式(一)MySql函式
- MySQL常用函式MySql函式
- mysql拼接函式MySql函式
- MySQL函式大全(字串函式,數學函式,日期函式,系統級函式,聚合函式)MySql函式字串
- MySQL函式學習(一)-----字串函式MySql函式字串
- MySQL函式-條件判斷函式MySql函式
- 現代 JS 流程控制:從回撥函式到 Promises 再到 Async/AwaitJS函式PromiseAI
- Mysql 常用函式(1)- 常用函式彙總MySql函式
- mysql FIND_IN_SET函式、INSTR函式MySql函式
- MySQL 內建函式MySql函式
- Mysql視窗函式MySql函式
- Mysql內建函式MySql函式
- Mysql 的trim() 函式MySql函式
- 7mysql函式MySql函式
- MySQL時間函式MySql函式
- MYSQL的字串函式MySql字串函式
- MySQL 聚合函式大全MySql函式
- MySQL 字串函式大全MySql字串函式
- FreeRTOS-04-核心控制函式+時間管理函式函式
- 理解zip函式的工作流程函式