Cuidado com a função Contat() do MySql e campos a NULL

Sexta-Feira, 11 de Fevereiro de 2011
Omega Paw - Roll N'Clean Litter Box PROBLEMA

A função Mysql CONCAT(str1,str2,...) devolve a concatenação dos seus argumentos. Por exemplo:
SELECT concat(‘Psantos’, ’.’, ‘com.pt’) devolve ‘Psantos.com.pt’.

Basta que um dos seus argumentos seja nulo (valor NULL), para que o valor devolvido seja nulo. Por exemplo:
SELECT CONCAT(‘Psantos’, ’.’, NULL, ‘com’) devolve NULL.

Este comportamento da função sugere que se tenha especial atenção quando se usam campos de tabelas como argumentos da função, pois todos os registo que tenham um campo a NULL, virão a NULL, o que pode ser indesejado. Por exemplo, para a tabela:

+----+-------+------------+
| Id | Nome  | Qtd
+----+-------+------------+
| 1  | Ovos  | 12 unid. 
| 2  | Uvas  | NULL       
| 3  | Maça  | 2 kg      
+----+-------+------------+

A instrução sql “SELECT CONCAT(Id,’,’, Nome, ‘, ‘, Qtd) FROM Produtos”, devolve:

1, Ovos, 12 unidades
NULL
3, Maça, 2 kg

SOLUÇÃO:
Para precaver este comportamento, pode-se usar a função COALESCE() que devolve o primeiro argumento não NULL, para transformar os NULLs em strings vazias ou qualquer outra string. Por exemplo:
“SELECT CONCAT(Id, ’,’, COALESCE(Nome, ‘*sem nome*’), ‘, ‘,COALESCE(Qtd, ‘*sem quantidade*’)) FROM Produtos”, devolve:

1, Ovos, 12 unidades
1, Uvas, *sem quantidade*
3, Maça, 2 kg

OUTRAS INFORMAÇÕES E DESABAFO:
- Para além da função COALESCE() é possível usar a função IFNULL().

- As funções LTRIM, RTRIM, TRIM têm o mesmo comportamento que a função CONCAT(), que pode ser precavido da mesma forma.

- Se eu me tivesse precavido, por exemplo usando a função COALESCE(), quando usei a função CONCAT() num projecto, teria poupado algumas dores de cabeça.

- As informações aqui presentes foram testadas na versão MySQL 5.1.33-community.

FONTES:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
http://forums.mysql.com/read.php?97,109881,110052#msg-110052