In MySQL, why does this IF return false?
In MySql, if the first argument of the IF () function is a string, why does it return false?
SELECT IF('string', 'string', 'not string'); -- 'not string'
Of course I could fix it if I did
IF(!ISNULL('string'), 'string', 'not string')) -- 'string'
or
IFNULL('string', 'not string'); -- 'string'
It seems somewhat contradictory that it evaluates the string as it looks like
SELECT IF(1, 'one', 'not one'); -- 'one'
and
SELECT IF('1', 'one', 'not one'); -- 'one'
appreciate how they do it ...
source to share
From MySQL
IF (expression1, expression2, expression3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), then IF () returns expr2; otherwise, it returns expr3. IF () returns a numeric string or a string value, depending on the context in which it is used.
So 1 is true because 1! = 0 and 1! = NULL. This is similar to what you will see in C.
But for a line that says 'test' evaluates to true has no real basis in the definition and has no logical meaning. It needs to be compared to something for a logical result.
source to share
The first argument given IF()
is a predicate . The string is not considered a MySQL predicate, so it defaults to false. As for your last case, many languages (C, Perl, etc.) consider nonzero integers to be true, so MySQL just supports this paradigm.
source to share
Because 'string' is neither true nor false, and the first expression must evaluate to boolean.
Why does IF (1, ...) evaluate to true? Good question. Maybe a fallback to C (i.e. if it's 1, then it's true)?
EDIT: Actually, as defined by the command http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if , expr1 is TRUE if expr1 <> 0 and expr1 < > NULL, which is the case when expr1 == 1.
source to share
Please note that there are similarities in PHP:
"string" != 0 // false
"one" != 0 // false
"1" != 0 // true
The confusion seems to be due to the fact that MySQL is comparing against 0 (and null) to get the boolean value of something. In other languages such as PHP and Javascript, a string, when pressed to boolean, returns true if not empty (or not "0").
// php
0 == true // false
"string" == 0 // true*
"string" == true // true
// mysql
if(0, "true", "false") // false
if("string", "true", "false") // false*
The two *
highlighted lines *
show equivalent comparisons, if you know what I mean.
source to share