-
CASE
value
WHEN [compare_value
] THENresult
[WHEN [compare_value
] THENresult
...] [ELSEresult
] ENDCASE WHEN [
condition
] THENresult
[WHEN [condition
] THENresult
...] [ELSEresult
] END1 番目のバージョンでは、
の場合に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 構文」で説明した SQLCASE
ステートメントの構文とはわずかに異なります。CASE
ステートメントはELSE NULL
句を持つことができず、END
でなく、END CASE
で終了します。 -
expr1
がTRUE
(
およびexpr1
<> 0
) の場合、expr1
<> NULLIF()
は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'
expr2
とexpr3
の一方のみが明示的にNULL
である場合は、IF()
関数の結果型は非NULL
式の型になります。IF()
のデフォルトの戻り値 (一時テーブルに格納されるときに重要となる場合があります) は、次のように計算されます。式 戻り値 expr2
またはexpr3
は文字列を返す文字列 expr2
またはexpr3
は浮動小数点値を返す浮動小数点 expr2
またはexpr3
は整数を返す整数 expr2
とexpr3
の両方が文字列で、どちらかの文字列で大文字と小文字が区別される場合は、結果でも大文字と小文字が区別されます。注記IF
ステートメントもありますが、ここで説明されているIF()
関数とは異なります。セクション13.6.5.2「IF 構文」を参照してください。 -
expr1
がNULL
でない場合、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
)STRING
、REAL
、または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)
です。 -
が true の場合はexpr1
=expr2
NULL
を返し、それ以外の場合はexpr1
を返します。これは、CASE WHEN
と同じです。expr1
=expr2
THEN NULL ELSEexpr1
ENDmysql> SELECT NULLIF(1,1); -> NULL mysql> SELECT NULLIF(1,2); -> 1
注記引数が等しくない場合は、MySQL で
expr1
が 2 回評価されます。
(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!!
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.
Default Behavior:
mysql> select CAST(1*-1 as UNSIGNED);
1 row in set (0.00 sec)
CAST expr2 as UNSIGNED:
mysql> SELECT IF((1 != 0), CAST(1*-1 as UNSIGNED), 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));
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);
1 row in set (0.00 sec)
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
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:
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.
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'
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
SELECT * FROM table WHERE IFNULL(NULL, field = 1)
Milan Zdimal
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.
For a nice PHP function to handle this see here:
http://www.edmondscommerce.co.uk/mysql/compare-two-columns-in-mysql/
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;