Mysql Or condition needs to be check only if first condition fail-CodePudding

scenario:

I need to show static user message if user not exist I tried below Query, Or condition needs to be check only if first condition fail but i get both user message.

SELECT message_id, to_user, status FROM message_status USE INDEX (from_user,to_user) 
WHERE (from_user = '0e005822-8001-4a4d-be47-d078f3a5e4f6'
and (to_user = '90e0e182-a4fb-11ec-b909-0242ac120002'
 OR to_user = 'static123456789'));

CodePudding user response：

Try this.

SELECT message_id, to_user, status FROM message_status USE INDEX (from_user,to_user)
WHERE from_user = '0e005822-8001-4a4d-be47-d078f3a5e4f6'
AND (CASE WHEN (to_user = '90e0e182-a4fb-11ec-b909-0242ac120002')
THEN TRUE
ELSE (to_user = 'static123456789') END)

CodePudding user response：

Use a not exists test to test if there are tousers other than static for a message and union

DROP table if exists t;

create table t(fromuser varchar(3), message_id int,touser varchar(10));

insert into t values
('aaa','1','bbb'),
('aaa','1','static'),
('aaa','2','bbb');

select fromuser,message_id,touser 
from t where not exists (select 1 from t t1 where t1.message_id = t.message_id and t1.touser <> 'static' limit 1)
union all
select fromuser,message_id,touser from t where touser <> 'static';



 ---------- ------------ -------- 
| fromuser | message_id | touser |
 ---------- ------------ -------- 
| aaa      |          1 | bbb    |
| aaa      |          2 | bbb    |
 ---------- ------------ -------- 
2 rows in set (0.002 sec)