Practical tutorial on modifying MySQL character set

Preface:

In MySQL, the system supports many character sets, and there are slight differences between different character sets. The most commonly used character sets at present should be utf8 and utf8mb4. Compared with utf8, utf8mb4 supports the storage of emoji expressions and has a wider range of uses. This article will introduce how to change utf8 to utf8mb4 character set.

1. Introduction to utf8 and utf8mb4 character sets

Character is a general term for various texts and symbols, including national texts, punctuation marks, graphic symbols, numbers, etc. A character set is a collection of multiple characters.

Character sets in MySQL can be used at four levels: server level, database level, table level, and column level. The comparison rules at the server level are controlled by the character_set_server parameter. If no character set is explicitly specified when creating a database, table, or column, the character set of the previous level will be inherited.

The default character set for MySQL 5.7 and earlier versions is latin1, and the default character set for MySQL 8.0 is utf8mb4. However, using latin1 can easily lead to garbled characters, so utf8 and utf8mb4 are the most widely used. utf8 is actually an alias for utf8mb3, which uses only 1 to 3 bytes to represent characters. utf8mb4 uses 1 to 4 bytes to represent characters, and can store more emoji expressions and any new Unicode characters. utf8mb4 is compatible with utf8 and can represent more characters than utf8. It is a superset of the utf8 character set. Therefore, some new businesses now recommend setting the database character set to utf8mb4, especially when there is a need to store emoticons.

2. Modify the character set method

The current Internet business has an increasing demand for emoji storage. For example, nicknames and comments must support emojis. At this time, if the database field uses the utf8 character set, the following error will be reported:

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\x95\xF0\x9F...' for column…………

To meet business needs, we need to change the database character set to utf8mb4. Fortunately, utf8mb4 is a superset of utf8, so no other conversion is required except changing the encoding to utf8mb4. Here is a brief description of the modification method.

System parameter modification

First, you should modify the system character set parameters so that the default character set for the database tables created in the future is utf8mb4. Find the configuration file and add or modify the following parameters:

vi /etc/my.cnf

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
skip-character-set-client-handshake
#Ignore the character encoding set by the application connection and keep it consistent with the global setting [client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

Modify the database character set

For a database that has already been created, if the original character set is utf8, you can modify it as follows:

# Set the database character set encoding ALTER DATABASE `dbname` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# Examplemysql> show create database testdb;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter database `testdb` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show create database testdb;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

Modify table and field character set

Similarly, for tables that have already been created, changing the global and database character sets will not affect the character sets of the original tables and fields. The original utf8 table can be modified as follows:

# Modify the table character set alter table `tb_name` default character set utf8mb4;

# Modify the character set of a field alter table `tb_name` modify col_name varchar(20) character set utf8mb4;

# Modify the character set of the table and field at the same time alter table `tb_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# If there are many tables in a database, you can splice the batch modification statements to be executed SELECT
 CONCAT( 'ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) 
FROM
 information_schema.`TABLES` 
WHERE
 TABLE_SCHEMA = 'testdb';

3. Some suggestions

The modification method seems simple, but you still need to be extra careful in the production environment. In particular, when modifying the field character set, a lock will be added to prevent write operations. This is also very slow for large tables and may affect online business.

If your database is relatively small, the above method should not be a problem. For online environments, if you want to modify the character set, you must make a good assessment. It is best to shut down the system for modification during the off-peak period of business, and be sure to back up before modification. If there is no downtime, you can consider making changes in the backup database first and then switching between the primary and backup databases, but this will be more troublesome.

If conditions permit, you can also prepare an empty instance, import the table structure first, change it to utf8mb4 character set, and then import the data. This is also a method, but it may also require downtime and switching.

The above is the details of how to modify the character set in MySQL. For more information about modifying the character set in MySQL, please pay attention to other related articles on 123WORDPRESS.COM!