How to Change Character Set of Database in MySQL

To change the character set of a MySQL database, use the ALTER DATABASE statement with CHARACTER SET and optionally COLLATE. For example, ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; changes the database character set to utf8mb4.

Syntax

The basic syntax to change a database's character set in MySQL is:

• ALTER DATABASE database_name: Specifies which database to modify.
• CHARACTER SET charset_name: Sets the new character set.
• COLLATE collation_name (optional): Sets the collation for the character set.

sql

ALTER DATABASE database_name CHARACTER SET charset_name [COLLATE collation_name];

Example

This example changes the character set of the database named mydb to utf8mb4 and sets the collation to utf8mb4_unicode_ci. This is recommended for full Unicode support including emojis.

sql

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Output

Query OK, 1 row affected (0.01 sec)

Common Pitfalls

Changing the database character set does not automatically convert existing tables or data. You must also change the character set of each table and column if needed.

Trying to change the character set without specifying a valid charset or collation will cause errors.

Also, some older MySQL versions may not support utf8mb4, so check your MySQL version.

sql

/* Wrong: Only changing database, but tables remain unchanged */
ALTER DATABASE mydb CHARACTER SET latin1;

/* Right: Change tables and columns too */
ALTER TABLE mytable CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Quick Reference

Command	Description
ALTER DATABASE db_name CHARACTER SET charset_name;	Change database default character set
ALTER DATABASE db_name COLLATE collation_name;	Change database default collation
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;	Convert table and columns to new charset
SHOW CREATE DATABASE db_name;	View current database charset and collation

Key Takeaways

Use ALTER DATABASE with CHARACTER SET and COLLATE to change database charset.

Changing database charset does not change existing tables or data automatically.

Use ALTER TABLE ... CONVERT TO CHARACTER SET to update tables and columns.

Always verify your MySQL version supports the desired character set.

Check current charset with SHOW CREATE DATABASE before making changes.