如何在 MySQL 查询中编写 IF ELSE 语句

如何在 MySQL 查询中编写 IF ELSE 语句？

就像这样:

mysql_query("...(irrelevant code).. IF(action==2&&state==0){state=1}");

然后在我的数组中，我应该可以这样做:

 $row['state']
//this should equal 1, the query should not change anything in the database,
//just the variable for returning the information

402081 次浏览

小开

You're looking for case:

case when action = 2 and state = 0 then 1 else 0 end as state

MySQL has an if syntax (if(action=2 and state=0, 1, 0)), but case is more universal.

Note that the as state there is just aliasing the column. I'm assuming this is in the column list of your SQL query.

小开

you must write it in SQL not it C/PHP style

IF( action = 2 AND state = 0, 1, 0 ) AS state

for use in query

IF ( action = 2 AND state = 0 ) THEN SET state = 1

for use in stored procedures or functions

小开

最佳答案

You probably want to use a CASE expression.

They look like this:

SELECT col1, col2, (case when (action = 2 and state = 0)
THEN
1
ELSE
0
END)
as state from tbl1;

小开

SELECT col1, col2, IF( action = 2 AND state = 0, 1, 0 ) AS state from tbl1;

SELECT col1, col2, (case when (action = 2 and state = 0) then 1 else 0 end) as state from tbl1;

both results will same....

小开

according to the mySQL reference manual this the syntax of using if and else statement :

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

So regarding your query :

x = IF((action=2)&&(state=0),1,2);

or you can use

IF ((action=2)&&(state=0)) then
state = 1;
ELSE
state = 2;
END IF;