MySQL CONCAT("string",longtext) results in hex string

Question

I'm experiencing a weird hex string result when trying to concat a string with a column that should be of LONGTEXT type.

The query goes like this:

SELECT concat("abc",t.LONGTEXT_VALUE,"cde") FROM mytable t

61626354657374696e67636465

The hex string 61626354657374696e67636465 is the correct value, just in hexadecimal form.

A SELECT on the column itself will return the normal string:

SELECT t.LONGTEXT_VALUE FROM mytable t

Testing

Answer 1

When you concat a number without a cast it returns as a blob. This is intended functionality of MySQL as far as I can tell since, it was reported in this bug thread and they closed it and confirmed it was returning a Blob.

Answer 2

Have you tried casting? Usually works pretty well for me. Example:

SELECT CONCAT("abc",CAST(t.LONGTEXT_VALUE AS CHAR),"cde") FROM mytable t