stack stack - 3 months ago 14
MySQL Question

How can I test an IF statement in MySQL?

How can I test an IF statement in MySQL ?

I need to know, what happens when a query is into a

IF
statement and it returns nothing (0 row selected) ?

In other word, what a
SELECT
query returns when the no row is selected? Will it return
NULL
? If yes, so will this throw an error?
IF ( NULL ) THEN ...
? Or in this case just the statement of that
IF
won't execute?




In reality, I have a code like this:

IF ( SELECT banned FROM users WHERE id=new.user_id ) THEN
// do stuff


Note:
banned
column contains either
0
or
1
.

All I want to know, is my code safe when that query doesn't return any row? I mean will it throw an error or nothing happens (just that
IF
statement doesn't execute)
?




Noted that I wanted to test it myselft, but I don't know how can I test a
IF
statement in MySQL. For example, I wanted to test it by this code:

BEGIN
IF ( SELECT banned FROM users WHERE id=new.user_id ) THEN
SELECT 'test';
ENDIF;
END


But code above doesn't even run. That's why I've asked this question.

Answer

An IF statement can be tested in the context of a MySQL stored program (a procedure, function or trigger.) MySQL doesn't support anonymous blocks.

To test an IF statement, you can create a procedure

DELIMITER $$

CREATE PROCEDURE foo
BEGIN
  IF ( some_condition ) THEN
     SELECT 'evaluation of some_condition returned TRUE' AS msg;
  ELSE
     SELECT 'evaluation of some_condition did NOT return TRUE' AS msg;
  END IF;
END$$    

DELIMITER ;

CALL foo;