I write function for MySql. There are lines in the function body

SELECT `id`, `val` INTO @CACHEID, @OLDVAL FROM `shop_ch_cache` WHERE `prodid` = PRODID AND `nameid` = NAMEID LIMIT 1; IF @CACHEID THEN INSERT INTO `_db_log`(`log`) VALUES(CONCAT('id isset ', @CACHEID)); RETURN 1; END IF; INSERT INTO `_db_log`(`log`) VALUES('id don`t isset '); RETURN 0;

Gives an error message

SQLSTATE [23000]: Integrity constraint violation: 1048 Column 'log' cannot be null

How can it be NULL if there is an IF @CACHEID condition?

  • And if you change the condition from IF @CACHEID THEN to IF @CHACHEID IS NOT NULL THEN will the error remain or everything will work fine? - ApInvent
  • @ApInvent, thanks, earned ... - Sergey Derevyanko

1 answer 1

Reply from comment:

NULL not equivalent to 0. If the @CHACHEID variable is NULL and the IF condition does not work, you should resort to the special operator IS NOT NULL , which returns 1 if the variable is not NULL and 0 otherwise.

 SELECT `id`, `val` INTO @CACHEID, @OLDVAL FROM `shop_ch_cache` WHERE `prodid` = PRODID AND `nameid` = NAMEID LIMIT 1; IF @CACHEID IS NOT NULL THEN INSERT INTO `_db_log`(`log`) VALUES(CONCAT('id isset ', @CACHEID)); RETURN 1; END IF; INSERT INTO `_db_log`(`log`) VALUES('id don`t isset '); RETURN 0;