Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


12.4 制御フロー関数

表 12.6 フロー制御演算子

名前 説明
CASE CASE 演算子
IF() If/else 構文
IFNULL() Null if/else 構文
NULLIF() expr1 = expr2 の場合に NULL を返します

  • CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

    CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

    1 番目のバージョンでは、value=compare_value の場合に result が返されます。2 番目のバージョンでは、true である最初の条件の結果が返されます。一致する結果値がなかった場合は、ELSE のあとの結果が返され、ELSE 部分がない場合は、NULL が返されます。

    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
    

    CASE 式の戻り型は、すべての戻り値の互換性のある集約型ですが、使用されるコンテキストにも依存します。文字列のコンテキストで使用される場合は、結果は文字列として返されます。数値のコンテキストで使用される場合は、結果が 10 進数値、実数値、または整数値として返されます。

    注記

    ここで示す CASE の構文は、ストアドプログラム内部で使用するために、セクション13.6.5.1「CASE 構文」で説明した SQL CASE ステートメントの構文とはわずかに異なります。CASE ステートメントは ELSE NULL 句を持つことができず、END でなく、END CASE で終了します。

  • IF(expr1,expr2,expr3)

    expr1TRUE (expr1 <> 0 および expr1 <> NULL) の場合、IF()expr2 を返します。それ以外の場合は expr3 を返します。IF() は、使用されているコンテキストに応じて、数値または文字列値を返します。

    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'
    

    expr2expr3 の一方のみが明示的に NULL である場合は、IF() 関数の結果型は非 NULL 式の型になります。

    IF() のデフォルトの戻り値 (一時テーブルに格納されるときに重要となる場合があります) は、次のように計算されます。

    戻り値
    expr2 または expr3 は文字列を返す 文字列
    expr2 または expr3 は浮動小数点値を返す 浮動小数点
    expr2 または expr3 は整数を返す 整数

    expr2expr3 の両方が文字列で、どちらかの文字列で大文字と小文字が区別される場合は、結果でも大文字と小文字が区別されます。

    注記

    IF ステートメントもありますが、ここで説明されている IF() 関数とは異なります。セクション13.6.5.2「IF 構文」を参照してください。

  • IFNULL(expr1,expr2)

    expr1NULL でない場合、IFNULL()expr1 を返し、それ以外の場合は expr2 を返します。IFNULL() は、使用されているコンテキストに応じて、数値または文字列値を返します。

    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'
    

    IFNULL(expr1,expr2) のデフォルトの結果値は、STRINGREAL、または INTEGER の順に、2 つの式のよりも一般的です。式や MySQL が一時テーブルの IFNULL() で返された値を内部に格納する必要のある場所に基づいて、テーブルの大文字と小文字を考慮してください。

    
    mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
    mysql> DESCRIBE tmp;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | test  | varbinary(4) | NO   |     |         |       |
    +-------+--------------+------+-----+---------+-------+
    

    この例では、test カラムの型は VARBINARY(4) です。

  • NULLIF(expr1,expr2)

    expr1 = expr2 が true の場合は NULL を返し、それ以外の場合は expr1 を返します。これは、CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END と同じです。

    mysql> SELECT NULLIF(1,1);
            -> NULL
    mysql> SELECT NULLIF(1,2);
            -> 1
    
    注記

    引数が等しくない場合は、MySQL で expr1 が 2 回評価されます。


User Comments
  Posted by I W on July 12, 2005
Don't use IFNULL for comparisons (especially not for Joins)

(example:
select aa from a left join b ON IFNULL(a.col,1)=IFNULL(b.col,1)
)

It's terrible slow (ran for days on two tables with approx 250k rows).

Use <=> (NULL-safe comparison) instead. It did the same job in less than 15 minutes!!

  Posted by john mullee on November 9, 2005
IFNULL is like oracle's NVL function (these should help people searching for NVL() ..)
  Posted by Philip Mak on May 26, 2006
When using CASE, remember that NULL != NULL, so if you write "WHEN NULL", it will never match. (I guess you have to use IFNULL() instead...)
  Posted by Marc Grue on June 24, 2006
You can ORDER BY a dynamic column_name parameter using a CASE expression in the ORDER BY clause of the SELECT statement:

CREATE PROCEDURE `orderby`(IN _orderby VARCHAR(50))
BEGIN

SELECT id, first_name, last_name, birthday
FROM table
ORDER BY
-- numeric columns
CASE _orderby WHEN 'id' THEN id END ASC,
CASE _orderby WHEN 'desc_ id' THEN id END DESC,
-- string columns
CASE _orderby WHEN 'first_name' THEN first_name WHEN 'last_name' THEN last_name END ASC,
CASE _orderby WHEN 'desc_first_name' THEN first_name WHEN 'desc_last_name' THEN last_name END DESC,
-- datetime columns
CASE _orderby WHEN 'birthday' THEN birthday END ASC,
CASE _orderby WHEN 'desc_ birthday' THEN birthday END DESC;

END

Since the CASE expression returns the "compatible aggregated type of all return values", you need to isolate each column type in a separate CASE expression to get the desired result.

If you mixed the columns like

CASE _orderby
WHEN 'id' THEN id
WHEN 'first_name' THEN first_name
...etc...
END ASC

.. both the id and first_name would be returned as a *string value*, and ids would be sorted as a string to '1,12,2,24,5' and not as integers to '1,2,5,12,24'.

Note that you don't need a "ELSE null" in the CASE expressions, since the CASE expression automatically returns null if there's no match. In that case, you get a "null ASC" in your ORDER BY clause which doesn't affect the sort order. If for instance _orderby is 'desc_first_name', the ORDER BY clause evaluates to:

ORDER BY null ASC, null DESC, null ASC, first_name DESC, null ASC, null DESC

Effectively the same as "ORDER BY first_name DESC". You could even add a new set of CASE expressions for a second order column (or more..) if you like.
  Posted by Matthew Montgomery on July 3, 2006
An IF() clause will have a SIGNED integer return type if either expr2 or expr3 are integers. This is the case even when you directly CAST one of the expresions as UNSIGNED.

Default Behavior:

mysql> select CAST(1*-1 as UNSIGNED);
+------------------------+
| CAST(1*-1 as UNSIGNED) |
+------------------------+
| 18446744073709551615 |
+------------------------+
1 row in set (0.00 sec)

CAST expr2 as UNSIGNED:

mysql> SELECT IF((1 != 0), CAST(1*-1 as UNSIGNED), 1);
+-----------------------------------------+
| IF((1 != 0), CAST(1*-1 as UNSIGNED), 1) |
+-----------------------------------------+
| -1 |
+-----------------------------------------+
1 row in set (0.02 sec)

CAST both expr2 and expr3 as UNSIGNED:

mysql> SELECT IF(1 != 0, CAST(1*-1 as UNSIGNED), CAST(1 as UNSIGNED));
+---------------------------------------------------------+ 
| IF(1 != 0, CAST(1*-1 as UNSIGNED), CAST(1 as UNSIGNED)) |
+---------------------------------------------------------+
| -1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Solution:

If you know that both values that should be returned by the IF() clause should be (UN)SIGNED you can CAST() the entire IF() clause as such. Currently there appears to be no way to CAST one expression and not the other.

mysql> SELECT CAST(IF((1 != 0), 1*-1, 1) AS UNSIGNED);
+-----------------------------------------+
| CAST(IF((1 != 0), 1*-1, 1) AS UNSIGNED) |
+-----------------------------------------+
| 18446744073709551615 |
+-----------------------------------------+
1 row in set (0.00 sec)
  Posted by Mark Callaghan on November 4, 2006
In MySQL 4.0.26, 'select ifnull(FloatColumn, 1) from foo' returns expressions with type float, while 'create table bar as select ifnull(FloatColumn, 1) from foo' creates a table with a double column. This has been fixed in 5.1 so that the behavior matches the documentation -- the type of the expression returned by the select statement and the column created in the table are both double.
  Posted by Robert Glover on February 13, 2007
There is a simple way to convert the following Oracle usage of decode into MySql. The reason for mentioning it here is that the conventional wisdom would be to convert the Oracle decode function into MySql Case statements.

Oracle version:

select BU, count(line_number) total,
sum(decode(RECERTIFY_FLAG,'Y',1,0)) needed,
sum(decode(RECERTIFY_FLAG,'N',1,0)) not_needed,
sum(decode(RECERTIFY_FLAG,'Y',0,'N',0,1)) not_processed
from isf.isf_analog_line group by bu order by bu

MySql version that gives same results:

select BU, count(line_number) total,
sum(FIND_IN_SET(RECERTIFY_FLAG,'Y')) needed,
sum(FIND_IN_SET(RECERTIFY_FLAG,'N')) not_needed,
sum(FIND_IN_SET(RECERTIFY_FLAG,' ')) not_processed
from isf.isf_analog_line group by bu order by bu
  Posted by Christian Marcotte on August 31, 2007
Validate and Format a date:

Someone was commenting that there is no built-in date validation. I found that using:

LAST_DAY('2007-02-25') does a good job of it.
It returns NULL if the date is invalid and the date of the last day of the month if it is valid.

I love the fact that those functions will accept all sorts of
goofy / mixed date notation and it will give you a clean
YYYY-MM-DD one in return.

EX:
select last_day('2007:02%25');
or
select last_day('2007/02-25');
or even
select last_day('2007+02=25');

all return a nice clean:
+------------------------+
| 2007-02-28 |
+------------------------+

With that in mind, I now have a one query approach to both validate a user input date AND format it nicely:

SELECT IF(LAST_DAY('2007-02-25'),
CONCAT(YEAR('07-08-25'), '-', month('2007-08-25'), '-', day('2007-08-25')),
NULL
) AS my_valid_formatted_date;

it returns NULL if the date is invalid and YYYY-MM-DD if it is valid.

  Posted by Dave Joyce on January 8, 2009
I was using a nested select statement and tried ifnull to force a return from one of the selects. If the select statement is an empty set then ifnull still returns null.

Example
select pra,(select phone from phones where prax=pra) from members where pra=1111;

if pra 1111 doesn't have a phone in phones then the return columns will be 1111 and null.

To force a return on the second select I used count(*)

select pra,(select if(count(*)=0,'no phone',phone) from phones where prax=pra) from members where pra=1111;

Now without a phone the returned values will be

1111 and 'no phone'
  Posted by Marcin Szalowicz on May 6, 2009
there is one thing with the case statement it took me a while to figure this out...
when we do:
select name from employee order by
case "john"
when "john" then name
when "sam" then surrname
else id end

mysql will not work with that and will always use the last condition, in this example this will be "else id"...

to solve this we need to add brackets...

here is the solution:

select name from employee order by
(case "john"
when "john" then name
when "sam" then surrname
else id end)

after that everything will be ok
  Posted by Milan Zdimal on July 6, 2010
Sometimes it's also useful to use IFNULL in situations where you need "append" a condition to the WHERE clause:

SELECT * FROM table WHERE IFNULL(NULL, field = 1)

Milan Zdimal
  Posted by sebastian nielsen on September 12, 2010
Milan: How is it useful to run:

SELECT * FROM table WHERE IFNULL(NULL, field = 1)

instead of:

SELECT * FROM table WHERE field = 1

the IFNULL(NULL, field = 1) would always return field = 1.

But the IFNULL() is good in cases you want to do a insert and clamp a value to a value that is already in record, else set it at maximum.

For example this:

INSERT INTO users (type,class,username,userid,userip,usermobile,useremail,daysleft,pin1,pin2,pin3,active,schoolname,schoolsite) SELECT 'A','A','sebastian',1000,'192.168.0.1','none','email@server.se',(SELECT IFNULL(MIN(daysleft),30) from users WHERE userip='192.168.0.1' AND type='A'),'12345','12345','00000','N','none','none' FROM dual WHERE ((SELECT COUNT(*) FROM users WHERE userip='192.168.0.1' AND type='A' AND daysleft = 0) = 0);

The interesting part is this:
(SELECT IFNULL(MIN(daysleft),30) from users WHERE userip='192.168.0.1' AND type='A')

If theres a record in the table for the user's IP, upon registrering for a account, it will take the smallest daysleft found, else it will give the user 30 days in daysleft.

This is good for example if you want to give 30 days trial period, and still allow members in a household to register for their own accounts, but not allowing longer trial priod by registrering multiple own accounts.

In this case, if a household has a user with lets say 10 trial days left, and a another user in the household registers himself for a account, that user will too get 10 trial days left.
If theres no accounts on a specific household, the user will get a full 30 days trial period.
  Posted by Joseph Edmonds on January 19, 2012
Note using the case syntax you can update many rows with one query. This can have a really dramatic postitive impact on performance if you have a large number of updates to do.

For a nice PHP function to handle this see here:
http://www.edmondscommerce.co.uk/mysql/compare-two-columns-in-mysql/

  Posted by Mike Jonsson on February 28, 2014
This does not work to validate that all actually fall within as some can have > 1 five while still being within 20 sum and then discards second statement after OR

Thus, MySQL reads the passed if clause in the statement from LEFT TO RIGHT and not from RIGHT TO LEFT as some say...

-- This fails
IF ((@r7q0+@r7q1+@r7q2+@r7q3+@r7q4+@r7q5) < 20) OR
OR (@r7q5 > 1) THEN
SET @res = 0;
END IF;

-- This works
IF ((@r7q6+@r7q7+@r7q8+@r7q9+@r7q10) > 0) OR (@r7q5 > 1) THEN
SET @res = 0;
END IF;

-- This would work in some cases then
IF (@r7q5 > 1) OR
((@r7q0+@r7q1+@r7q2+@r7q3+@r7q4+@r7q5) < 20) THEN
SET @res = 0;
END IF;

Sign Up Login You must be logged in to post a comment.