Friday, June 5, 2009

MySql GROUP_CONCAT() tip

Recently I found myself into a silly problem because of misunderstaning / confusion relating to data type of variable set in SELECT commmand using GROUP_CONCAT()

I wanted to get all integer ids as comma separated string to be used in other update query

my query:

SELECT GROUP_CONCAT(spm_ids) INTO @csvIds FROM someTable WHERE is_active = 1;

Whenever my select executed correctly it gave e.g. '2,5,6' as a string and when it failed it gave me '0' as a string.

before updating I checked

IF @csvIds <> 0 THEN
// perform some update on some other table
END IF;


I changed to below and my Stored procedure worked perfectly fine.

IF @csvIds <> '0' THEN
// perform some update on some other table
END IF;

Some of mysql String comparison
> select if('2' <> 0, 1, 0)
> 1

> select if('0,2' <> 0, 1, 0) //my problematic condition
> 0

> select if('0' <> 0, 1, 0)
> 0

> select if('2,5,6' <> 0, 1, 0)
> 1





MySql tip 2:
If you are writing too many Stored Procedures(SP) and also including below code in your SPs

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 2 AS `error_code`, 'Fail to update ' AS `error_message` ;
END;

Then I would suggest that you first create your SP entirely run it many times under different conditions, if all working fine than include above code and test it again for failure.
This is because you will save some of your precious time by knowing exactly where the query failed.

No comments: