How to change the default collation of a database?

Question

Our previous programmer set the wrong collation in a table (Mysql). He set it up with Latin collation when it should be UTF8, and now I have issues. Every record with Chinese and Japan character turn to ??? character.

Is possible to change collation and get back the detail of character?

Answer 1

You can change the database collation like this:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

You can change the table collation this way:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

And you can change the column collation like this:

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

For more information you can refer to these links:

• What are the diffrences between utf8_general_ci and utf8_unicode_ci?
• What's the difference between utf8_general_ci and utf8_unicode_ci
• How to change collation of database, table, column?