Mariadb 10 uncompress () return blob instead of string
According to mysql and mariadb docs uncompress () returns a string if the input string was compressed using compress ().
Also indicated in the examples:
SELECT UNCOMPRESS(COMPRESS('a string'));
+----------------------------------+
| UNCOMPRESS(COMPRESS('a string')) |
+----------------------------------+
| a string |
+----------------------------------+
However, on mysql workbench and navicat, I only get blobs in the result. Only using
SELECT CONVERT(UNCOMPRESS(COMPRESS('a string')) USING utf8);
returns the original string. Where am I going wrong? Are the docs wrong or am I just misinterpreting here?
source to share
The result type UNCOMPRESS()
was LONG_BLOB
from at least MySQL 5.1, before that it was VARBINARY
.
It is supposed to COMPRESS()
only compress the contents of the string, but not preserve metadata such as character set or collation. Thus, UNCOMPRESS()
returning uncompressed data as a binary data stream is the only safe option. The conversion of the returned binary data to a specific character set and collation is user-dependent, for example. storing this information out of the lane in a separate column and then using it as an argument for CONVERT()
later.
source to share