1

I have a mediumtext column in a mysql database with a utf8mb4 collation:

comments mediumtext utf8mb4_unicode_ci

When I perform a mysql ENCODE the text is saved to the database column:

INSERT INTO mytable VALUES (ENCODE('test', 'abc'));

However, when I try to DECODE the text it's output is garbled:

SELECT DECODE(comments,'abc') FROM mytable WHERE 1

This example would display:

�1�= g3���

If I change the column to latin1_swedish_ci then everything works correctly as it should and "test" is displayed. Note, I am saving inputting the "test" text through a php form but not processing the data in any way.

1 - Any ideas as to why this is occurring?

2 - Is there anyway to decode the garbled text? I tried several methods to no avail.

Thanks!

Matt
  • 11
  • 1

2 Answers2

0

Maybe the string literal is defaulting to latin1. Try this:

INSERT INTO mytable VALUES (ENCODE(_utf8mb4'test', 'abc'));
Marlin Pierce
  • 9,931
  • 4
  • 30
  • 52
0

Do not put non-text in a text column.

ENCODE generates binary information, so put it into a binary column, such as MEDIUMBLOB.

Rick James
  • 135,179
  • 13
  • 127
  • 222