How to Fix Garbled Chinese and Japanese Characters by Changing Database Collation?

Database Character Set and Collation Modification Guide: Restoring Character Details

This guide solves the problem that incorrect database collation settings cause Chinese and Japanese characters to display garbled characters. The goal is to modify the collation and restore character detail.

Modify database sorting rules:

<code class="language-sql">ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;</code>

Modify table sorting rules:

<code class="language-sql">ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;</code>

Modify column sorting rules:

<code class="language-sql">ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;</code>

utf8mb4_0900_ai_ci Meaning of each part:

• utf8 (3 bytes): Standard UTF-8 encoding, using 3 bytes.
• utf8mb4 (4 bytes, new): Extended UTF-8 encoding, using 4 bytes to store extended characters.
• 0900 (new): Faster sorting algorithm for version 9.0 and above.
• _ai_ci: Explicitly ignore collation of case and accents.

Additional information:

• utf8_general_ci vs. utf8_unicode_ci: utf8_general_ci does not support multi-alphabetic characters, while utf8_unicode_ci does.
• Collation Modification: For more detailed instructions on modifying collation at different levels, please refer to the provided resources. (Relevant resource links should be added here, the original text is missing)

The above is the detailed content of How to Fix Garbled Chinese and Japanese Characters by Changing Database Collation?. For more information, please follow other related articles on the PHP Chinese website!