MySQL and UTF-8

I recently had a problem in a web application that I created where the UTF-8 characters were not interpreted correctly by browsers.  The biggest issue was this did not happen in all instances of presenting these UTF-8 strings.

After tracing the strings through several libraries, I found the culprit.  The following SQL statement was the source of the text.

SELECT ID, CONCAT(sDescription, ' (', ID, ')') FROM ProdFamily;

Where ID is a integer key for the table and sDescription is a varchar column.  The result of the CONCAT function is a “binary string” because of the integer column as one of the operands.  The end result is that this “binary string” was treated differently than a regular UTF-8 string and characters outside the normal ASCII ones were not display correctly.  To fix this issue the CAST function must be added to set the type of the ID column to string as follows.

SELECT ID, CONCAT(sDescription, ' (', CAST(ID AS CHAR), ')') FROM ProdFamily

See MySQL CONCAT or CAST function documentation for more information.