Upgrade mysql db character set to utf8mb4

Why upgrade?

Recently, because the database of the business side supports utf8mb4, the datax synchronization tool is still on the support for utf8 character sets. As a result, some synchronization tasks cannot correctly synchronize emoticon fields such as emoji, resulting in a large number of garbled characters.

Mysql 5.5.3 and later versions, utf8 encoding supports up to three bytes, that is, the BMP encoding area, in the range of 0000 ~ FFFF.

Utf8mb4 is added in versions later than mysql 5.5.3. It is officially introduced to be backward compatible with the utf8 character set and can mark more characters than utf8.

Currently, most mobile projects require utf8mb4 support because they need to store a large number of emojis, such as emoji, to store users' texts.

Upgrade compatibility

Utf8mb4 is used as the superset of utf8 and is backward compatible with utf8, so you don't have to worry about character compatibility.

However, after the switch takes effect, you need to restart the mysql server, which will have a certain impact on the business database. (The official document says it is about dynamic configuration modification. I have not tested it. However, refer to the documentation provided in this article, it takes effect only after restart .)

After the upgrade, I re-ran a test set of mysql-related plug-ins without any exceptions. It can be proved that the compatibility of datax mysql plug-in after upgrade is not affected.

Upgrade procedure

Check the mysql version. If the version is earlier than 5.5.3, upgrade mysql server first.
Modify/etc/my. cnf

[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
Init_connect = 'set NAMES utf8mb4'
  
Modify the database, table, and column character sets (depending on your own situation)

Alter database database_name character set = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Alter table table_name convert to character set utf8mb4 COLLATE utf8mb4_unicode_ci;
Alter table table_name CHANGE column_name VARCHAR (191) character set utf8mb4 COLLATE utf8mb4_unicode_ci;
  
Check the character'

Restart: sudo/etc/init. d/mysql restart
Enter the mysql command line: mysql-h localhost-uroot-proot

Check character set

Mysql> show variables where Variable_name LIKE 'character \ _ set \ _ % 'OR Variable_name LIKE 'colation % ';
+ -------------------------- + -------------------- +
| 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.01 sec)
  
Note:

Must be guaranteed
Charactersetclient/charactersetconnection/charactersetdatabase/charactersetresults/charactersetserver is utf8mb4.

The meanings of these variables are as follows:

-Charactersetserver: default internal operation character set

-Charactersetclient: character set used by the client source data

-Charactersetconnection: connection layer character set

-Charactersetresults: character set of the query result

-Charactersetdatabase: default character set of the currently selected database

-Charactersetsystem: system metadata (field name, etc.) character set

Mysql character set settings:

MySQL character set settings

• System variables:

-Character_set_server: default internal operation character set

-Character_set_client: character set used by the client source data

-Character_set_connection: connection layer character set

-Character_set_results: query result character set

-Character_set_database: default character set of the currently selected database

-Character_set_system: system metadata (field name, etc.) character set

-There are also variables starting with collation _, which are used to describe the collation.

• Use introducer to specify the character set of the text string:

-Format: [_ charset] 'string' [COLLATE collation]

-Example:

• SELECT _ latin1 'string ';

• SELECT _ utf8 'Hello 'COLLATE utf8_general_ci;

-Text strings modified by introducer are directly converted to internal character sets for processing without extra transcoding during the request.

Character Set conversion process in MySQL

1. MySQL Server converts the request data from character_set_client to character_set_connection when receiving the request;

2. Before performing internal operations, convert the request data from character_set_connection to the internal operation character set. The method is as follows:

• SET the character set value for each data field;

• If the preceding value does not exist, use the default character set value of the corresponding data table (MySQL extension, non-SQL standard );

• If the preceding value does not exist, use the default character set value of the corresponding database;

• If the preceding value does not exist, use character_set_server to set the value.

3. Convert the operation result from the internal character set to character_set_results.

Check usage configuration

Check mysql-connector version, which must be later than 5.1.3; otherwise, utf8mb4 cannot be used.
If you use jdbc-url for connection, note that characterEncoding = utf8 can be automatically recognized as utf8mb4 (of course compatible with the original utf8 ).

Jdbc. url = jdbc: mysql: // localhost: 3306/database? UseUnicode = true & characterEncoding = utf8 & autoReconnect = true & rewriteBatchedStatements = TRUE