About the pitfalls of implementing specified encoding in MySQL

Written in front

Environment: MySQL 5.7+, MySQL database character encoding is utf8, table is also utf8

Scenario: WeChat authorizes the acquisition of user information (including nickname) and saves it to the database. Some users succeed, but a few fail.

So why did it fail?

Paste the error log

Incorrect string value: '\xF0\x9F\x98\x98\xF0\x9F...' for column 'nickname' at row 1

This is because the failed user's WeChat nickname contains emoji symbols.

Analysis:

MySQL supports multiple character encoding sets, such as UTF-8, GB2312, GBK, etc.

You can view it through the SHOW CHARSET command.

We usually recommend using UTF-8 as the default character encoding.

From the above figure we can see that the MySQL database has two sets of UTF-8 encoding implementations.

• utf8: utf8 encoding only supports up to three bytes. In utf8 encoding, Chinese characters take up 3 bytes, and other English characters, numbers, and symbols all take up 1 byte. However, emoji symbols take up 4 bytes, and some more complex text and traditional Chinese characters also take up 4 bytes.
• utf8mb4: A complete implementation of the UTF-8 encoding. It supports up to 4 bytes, so it can be used to store emoji symbols.

Check the information, official explanation:

at last

Tips: If you have a field that needs to store emoji symbols, remember to specify the encoding as utf8mb4.

This is the end of this article about a small pit in the implementation of MySQL specified encoding. For more relevant MySQL specified encoding content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!