mysql test IF variable IS NULL (or empty)

Question

we are selecting a row in mysql/mariadb and storing chosen columns in variables. After this, using IF we would like to test one of the variables and see if it has been set or is null, if it is then we assign it a value and continue on.

Using IS NULL does not seem to work on a non expression.

select id,history,active,jsonorder
INTO @id,@history,@active,@jsonorder
from myTable where uid = myUid
delimiter |
IF @jsonorder IS NULL THEN
  @myNewVal="zzz";
ELSE
  @myNewVal="yyy";
END IF|
insert into otherTable (colA) VALUES (@myNewVar);

What is the correct way to test if the select has provided a value into a variable such as @jsonorder?

what you mean doesnt seem to work? are you working inside a function? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jun 26, 2019 at 15:57

spencer7593 · Accepted Answer · 2019-06-26 17:04:05Z

1

We could use an expression in the SELECT list:

SELECT t.id
     , t.history
     , t.active
     , IFNULL(t.order,1) AS `order`
  INTO @id
     , @history
     , @active
     , @order
  FROM `myTable` t
 WHERE t.uid = ...

Note that DELIMITER is not a SQL statement; it's command recognized by the mysql command line client (and some other clients).

documented here in MySQL Reference Manual: https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

It's not clear in what context this script is being run; is this part of a MySQL stored program, or being called application code. What we are actually trying to achieve?

There are several other expressions we could use in the SELECT list, e.g.

CASE WHEN t.order IS NULL THEN 1 ELSE t.order END`

or

IF(t.order IS NULL,1,t.order)

et al.

FOLLOWUP

If we don't want to modify the original SQL statement; if we execute this:

SELECT t.id
     , t.history
     , t.active
     , t.jsonorder
  INTO @id
     , @history
     , @active
     , @jsonorder
  FROM `myTable` t
 WHERE t.uid = ...

And then we want to perform an assignment to another user defined variable, based on a conditional test, we can achieve that in another SELECT or a SET statement.

For example:

SELECT @myNewVal := IF(@jsonorder IS NULL,'zzz','yyy') ;
                      ELSE 'yyy'

-or-

SELECT CASE
         WHEN @jsonorder IS NULL 
         THEN 'zzz'
         ELSE 'yyy'
       END
  INTO @myNewVal

-or-

SET @myNewVal := IF(@jsonorder IS NULL,'zzz','yyy')

edited Jun 26, 2019 at 17:04

answered Jun 26, 2019 at 16:02

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

art vanderlay Over a year ago

I have updated the sql query to reflect a post IF insert action on a different table and a different variable. This statement is read by php and or bash scripts to be run.

Rick James · Accepted Answer · 2019-06-27 03:02:16Z

1

The IF statement can only be used in Stored Routines. The IF function can be used virtually anywhere an expression can be put:

select id,history,active,jsonorder
    INTO @id,@history,@active,@jsonorder
    from myTable
    where uid = myUid;
insert into otherTable (colA)
    VALUES (IF(@jsonorder IS NULL, "zzz", "yyy"));

Where does myUid come from?

Is otherTable only one column wide? Or do all the other columns have defaults?

(I'm worried that you over-sanitized the question.)

answered Jun 27, 2019 at 3:02

Rick James

144k15 gold badges144 silver badges255 bronze badges

Comments

Juan Carlos Oropeza · Accepted Answer · 2019-06-26 15:59:56Z

0

This solve for cases where order is null, but not for when myUid doesnt exist in your table

SELECT id,history,active, COALESCE(order,1)
INTO @id,@history,@active,@order
FROM myTable 
WHERE uid = myUid

answered Jun 26, 2019 at 15:59

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

mysql test IF variable IS NULL (or empty)

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related