MySQL Character Set Configuration: Differences Between utf8 and utf8mb4 and Conversion Methods

There is a well-known pitfall in MySQL: utf8 is not complete UTF-8. It supports a maximum of 3 bytes, whereas emojis and some rare characters require 4 bytes. Therefore, databases using utf8 will throw errors or store question marks when encountering emojis.

The correct approach is to always use utf8mb4.

Differences Between the Two

Common symptoms:

Incorrect string value: 'xF0x9Fx98x80' for column 'xxx'
# Or content becomes ???? (silently replaced)

Check Existing Character Sets

# Database character set
mysql -e "SELECT schema_name, default_character_set_name, default_collation_name
          FROM information_schema.schemata;"

# Table character set
mysql -e "SELECT table_name, table_collation FROM information_schema.tables
          WHERE table_schema='dbname';"

# Column level (overrides table-level settings)
mysql -e "SELECT table_name, column_name, character_set_name, collation_name
          FROM information_schema.columns
          WHERE table_schema='dbname' AND data_type LIKE '%char%';"

# Connection layer character set (equally important)
mysql -e "SHOW VARIABLES LIKE 'character_set%';"

Configuration (New Environment)

# /etc/my.cnf
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci

systemctl restart mysqld
mysql -e "SHOW VARIABLES LIKE 'character_set_server';"

# Explicitly specify when creating a database (do not rely on defaults)
mysql -e "CREATE DATABASE dbname
          DEFAULT CHARACTER SET utf8mb4
          COLLATE utf8mb4_unicode_ci;"

How to Choose a Collation

Maintain consistent collation within the same database. Mixing them will cause errors (illegal mix of collations) during join queries or comparisons.

# Check for mixed collations
mysql -e "SELECT DISTINCT collation_name FROM information_schema.columns
          WHERE table_schema='dbname';"

Converting Existing Databases (From utf8 to utf8mb4)

# ① Backup first (conversion involves schema changes, so backup is mandatory)
mysqldump -u root -p --default-character-set=utf8mb4 
  --single-transaction --routines --triggers dbname > backup.sql

# ② Change the database default character set
mysql -e "ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# ③ Change tables (rebuilds tables, takes longer for large tables)
mysql -e "ALTER TABLE dbname.tablename
          CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Batch generate conversion statements for all tables
mysql -Nse "SELECT CONCAT('ALTER TABLE ', table_name,
            ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
            FROM information_schema.tables
            WHERE table_schema='dbname';" > convert.sql

# Review generated statements before executing
cat convert.sql

Use CONVERT TO instead of just changing the table’s default character set. The latter only affects newly added fields; existing field character sets will not change—this is the most common mistake during conversion.

Connection Layer Must Also Be Consistent

// Specify when connecting via PDO
$pdo = new PDO($dsn, $user, $pass, [
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4",
]);

// Or in the DSN
$dsn = "mysql:host=localhost;dbname=dbname;charset=utf8mb4";

// WordPress
define('DB_CHARSET', 'utf8mb4');

# Also specify when importing via command line
mysql --default-character-set=utf8mb4 dbname < db.sql

If the server uses utf8mb4 but the connection layer uses utf8, problems will still occur. All three layers (server, database/table/column, and connection) must be consistent.

Verification

# Insert an emoji to test
mysql -e "USE dbname; INSERT INTO test_table (content) VALUES ('Test😀');"
mysql -e "USE dbname; SELECT content FROM test_table ORDER BY id DESC LIMIT 1;"
# If the emoji displays normally, it is working correctly

# Confirm character sets at each layer
mysql -e "SHOW VARIABLES LIKE 'character_set%';"

Decision Tree

Character Set Issues
├─ New Environment
│  ├─ Set utf8mb4 in config file
│  ├─ Explicitly specify when creating database (don't rely on defaults)
│  └─ Specify connection layer as well
├─ Converting Existing Database
│  ├─ ① Backup first
│  ├─ ② Change database default character set
│  ├─ ③ Use CONVERT TO to change tables (not just default)
│  └─ ④ Verify: Insert emoji and check read/write success
├─ Troubleshooting Garbled Text
│  ├─ Check three levels: database, table, column (column overrides table)
│  ├─ Check connection layer character set
│  └─ Check for mixed collations
└─ Mixed Collations
   └─ Unify to a single collation

Scenario Analysis: Same Domain, One Site Normal, One Garbled

Symptom: Two sites running on the same server, sharing two databases from the same DB instance.
One site displays content normally, while new content written by the other site appears garbled.

1. Determine where the garbled text occurs → Check the database directly;
   if storage is already incorrect, it is not a page rendering issue.
2. Compare character set settings of both databases →
   One database has a complete character set, while the other uses an incomplete legacy one.
3. Understand why the old one worked before →
   Ordinary text can be stored in both character sets; errors only occur when characters outside the old range appear.
4. Unify character sets → Convert both the database and related tables’ character sets and collations.
5. Ensure connection declarations are also consistent → Changing only the database/tables is insufficient;
   mismatched connection declarations will still trigger conversions.
6. Verify with problematic characters → Specifically test with characters that previously caused errors, rather than ordinary text.

# Compare layers for both databases
mysql -e "SELECT @@character_set_server, @@collation_server;"
mysql -e "SELECT schema_name, default_character_set_name FROM information_schema.schemata;"
mysql -e "SHOW CREATE TABLE db_name.table_nameG" | grep -i charset

# Conversion (backup务必 before operation)
mysqldump --default-character-set=utf8mb4 db_name > bak-$(date +%F).sql
# ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Verification: Read/write test with previously problematic characters
mysql -e "SELECT HEX(column_name) FROM db_name.table_name LIMIT 1;"

Root Cause: Inconsistent character sets between the two databases;
one uses an incomplete character set, leading to irreversible replacement when encountering out-of-range characters.

Two Key Takeaways:

• Check three layers for character sets: Server, Database/Table Structure, and Connection Declaration.
  Changing only one layer is insufficient; any mismatch causes implicit conversion.
• Verify using problematic characters, not ordinary text.
  Passing ordinary text does not prove conversion was successful; characters outside the old range are the true test samples.

8 Common Pitfalls

1. Using utf8 instead of utf8mb4: Cannot store 4-byte characters.
2. Changing only table default character set without converting fields: Field-level remains old.
3. Ignoring connection layer character set: Server is correct, but connection is utf8.
4. Not specifying when creating database, relying on defaults: Defaults may not be what you want.
5. Mixing collations: Causes illegal mix of collations errors during joins or comparisons.
6. Not backing up before conversion: Schema changes carry risks.
7. Not specifying character set during import: Mismatch between ends causes issues.
8. Not verifying: Silent replacements are invisible; active testing is required.

Real Customer Feedback (From Cloud Market Reviews and Consultations for Similar Products)

The following excerpts are taken from real buyer reviews and consultations for similar operations services on Alibaba Cloud / Tencent Cloud / Huawei Cloud markets (service providers include our store and peers, provided solely for reference of real feedback, not fictitious tickets from this site):

9****3 Rating: 5.0 2021-07-22 10:31

s****x Rating: 5.0 2021-06-26 10:30

10***47 2021-06-05 5.0 On-time delivery | Skilled technology | Affordable price (User did not fill in comment content) 10***15 2021-05-28 5.0 Safe and reliable | On-time delivery | Perfect after-sales service The boss is nice and answers carefully! 10***69 2021-04-27 5.0 Safe and reliable | On-time delivery | Affordable price Excellent, will come again 10***45 2021-03-22 5.0 Skilled technology | Affordable price | Patient and professional 1. The input box design for requiring customers to submit “Requirement Information” is inconvenient. The light-colored hint text inside the prompt box contains too much content for customers to remember entirely. As soon as they type one character, the light hint text disappears, leaving them unsure of what to write next. Suggestion for improvement: Write the hint text outside the box; or directly make the requirement information into a form. This would be more convenient for customer operation. 2. The email goes unattended, wasting time. It would be better to explicitly direct users to find customer service. 51***82 2021-03-11 5.0 On-time delivery | Affordable price | Patient and professional (User did not fill in comment content) 10***74 2021-01-24 5.0 Comprehensive features | Affordable price | Skilled technology Good configuration 10***32 2020-11-27 5.0 Affordable price (User did not fill in comment content)