I recently came across two behaviors of the MySQL CONCAT function which caused me problems with encoding and null values. Here is a description of the problems encountered with their solutions and explanations.
I use MySQL functions, stored procedures, triggers and events daily in my work. In this context, I encountered some problems with the CONCAT function. After some research on the net, I saw that the behavior in question was normal and I had the explanation of why and how. I give you here these solutions and explanations in case you encounter the same problems.
NULL values problem with CONCAT
One of the points that often pissed me off when I had to use the CONCAT MySQL function is the fact that it only takes one NULL value in the batch for the whole string to become NULL.
It is then necessary to find out in the middle of all the fields and all the variables involved which one is at fault. Not always easy depending on the number of parameters. I recently found on this site a trick using the CONCAT_WS function with the empty string as a separator. The CONCAT_WS function (for With Separator) automatically adds a separator between each parameter of the function (defined as the first parameter). This function also has the advantage of ignoring null values.
Encoding problem with CONCAT
I ran into an encoding problem recently with the CONCAT function. The input and output fields were however of the same character set (latin1, to migrate one day to Utf8 editor’s note), but on output my accents appeared with a question mark. By doing some tests with the CHARSET function, I realized that from the moment I had a numeric in my concatenation, my character set in CONCAT output was “binary” and more latin1 (see screenshot below). The behavior is normal and documented after reading the MySQL site. One of the solutions in this case is to go through a CAST (see screenshot below).
