Easily change MySQL database character set to utf8mb4 with the below simple steps in this article. At Bobcares, with our MySQL Support Service, we can handle your MySQL issues.
How to change the database character set to utf8mb4 in MySQL?
In MySQL 5.5.3, a new encoding called utf8mb4 was introduced, which maps to proper UTF-8 and hence fully supports Unicode, including astral symbols. The Character Set requires a maximum of four bytes per multibyte character.
It also supports BMP and additional characters. The utf8mb4 set has identical storage characteristics like the same code values, the same encoding, and the same length for a BMP character, and utf8mb4 requires four bytes to store a supplementary character.
Let’s see the steps to change any utf8 character set to utf8mb4 database character set in MySQL
- Firstly, create a backup of all the DB on the server we’re upgrading.
- Then Upgrade the MySQL server to version 5.5.3 or higher.
- Also, change the character set and collation properties of the DBs, tables, and columns to use utf8mb4 rather than utf8.
# For each database: ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; # For each table: ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # For each column: ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # The above code is for 'VARCHAR' column. The exact statement depends on the column type, maximum length, and other properties.
- Now verify the maximum length for both index keys and columns.
The maximum length of a column or index key remains constant when going from utf8 to utf8mb4, in terms of bytes. As a result, it has fewer characters because a character can now only be up to four bytes long instead of three.
- Then update the connection, client, and server character sets.
To set the connection character set to utf8mb4 in the program code, simply replace any variants of SET NAMES utf8 with SET NAMES utf8mb4. If the old SET NAMES statement specified the collation, we have to change that too. ‘SET NAMES utf8 COLLATE utf8_unicode_ci’ will change to ‘SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci’.
To set the client and server character set, we have:
[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci
We can also verify if these settings work correctly as below. The character_set_filesystem should be binary unless we are using a file system that allows multi-byte UTF-8-encoded characters in file names, and character_set_system is always UTF-8 and cannot be modified.
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +--------------------------+--------------------+ 10 rows in set (0.00 sec)
- Repair and optimize all DBs and tables by running the below code as the root user on CLI
mysqlcheck
utility.$ mysqlcheck -u root -p --auto-repair --optimize --all-databases
[Looking for a solution to another question? Get in touch with us.]
Conclusion
It may take some time to update the databases and the code, but it will be worthwhile to change MySQL’s database character set to utf8mb4. To summarize, we have outlined the steps provided by our skilled Support team to convert from any utf8 character set to a utf8mb4 character set.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
var google_conversion_label = "owonCMyG5nEQ0aD71QM";
0 Comments