convert a json column to utf8mb4

Question

in a mysql insert request i used ($text being sent by $_POST and column MESSAGE being of JSON format) :

$data = '{"mytext":".'$text'."}';

INSERT INTO xxxxx (MESSAGE) VALUES('$mytext');

i was stuck with the following problem :

Some smiley are displaying ok, but some with a "?"

I searched here for an answer and found that utf8mb4 must be set on the database, tables and columns.

The problem is when i try the following which contains my registered message:

ALTER TABLE xxxxx CHANGE column_name column_name JSON CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Result of the above

You have an error in your SQL syntax;

I assume that it is only possible on Varchar() column.

Do you have any idea on how i can make this work ?

Thank you

Answer 1

Some smiley are displaying ok, but some with a "?"

Start by finding the problems that cause "question mark" from Trouble with UTF-8 characters; what I see is not what I stored

In particular:

• The bytes to be stored are not encoded as utf8mb4. Fix this.
• The column in the database is not CHARACTER SET utf8mb4. Fix this. (Use SHOW CREATE TABLE to see what it is currently.)
• Also, check that the connection during reading is UTF-8.

To discuss further, please provide

• the SHOW CREATE TABLE before attempting the ALTER. (I suspect the VARCHAR is currently CHARACTER SET utf8.)
• the full error message. (I guess it says "near 'CHARACTER...'".)
• what version of MySQL you are using.

The manual say "MySQL handles strings used in JSON context using the utf8mb4 character set and utf8mb4_bin collation.", I deduce that you do not need to specify either charset or collation for datatype JSON.