How can I see utf8mbf collate for a column in MySQL?

Question

Here is the command I use:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

It works well. Now I need to set utf8mb4_unicode_ci for a column (since currently characters are shown as ???). Anyway here is my new command:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;

But sadly MySQL throws:

ERROacR 1253 (42000): COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER

Any idea?

Answer 1

The first part of the COLLATION name must match the CHARACTER SET name.

CHARACTER SET utf8mb4 is needed for Emoji and some Chinese characters.

Let's back up to the 'real' problem -- of question marks.

COLLATION refers to the rules of ordering and sorting, not encoding.

CHARACTER SET refers to the encoding. This should be consistent at all stages. Question Marks come from inconsistencies.

Trouble with UTF-8 characters; what I see is not what I stored points out that these are the likely suspects for Question Marks:

• The bytes to be stored are not encoded as utf8/utf8mb4. Fix this.
• The column in the database is not CHARACTER SET utf8mb4. Fix this if you need 4-byte UTF-8. (Use SHOW CREATE TABLE.)
• Also, check that the connection during reading is UTF-8. The details depend on the application doing the connecting.

Answer 2

This worked for me:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;