Fully understand the difference between UTF8 and utf8mb4 in Mysql _mysql

First, Introduction

MySQL added this utf8mb4 code after 5.5.3, MB4 is most bytes 4, and is designed to be compatible with four-byte Unicode. Fortunately, UTF8MB4 is a superset of UTF8, and there is no need for other conversions except to change the encoding to UTF8MB4. Of course, in order to save space, the general use of UTF8 is enough.

Ii. Description of the content

It says that since UTF8 can save most Chinese characters, why use UTF8MB4? The original MySQL-supported UTF8 encoded maximum character length of 3 bytes, if encountered 4 bytes of wide characters will be inserted into the exception. The three-byte UTF-8 maximum encoded Unicode character is 0xFFFF, which is the basic Multilingual Plane (BMP) in Unicode. That is, any Unicode character that is not in the basic multiple-text plane cannot be stored with the UTF8 character set of Mysql. Including emoji expressions (emoji is a special Unicode encoding, common on iOS and Android phones), and many infrequently used Chinese characters, as well as any new Unicode characters, and so on.

Iii. source of the problem

The original UTF-8 format uses one to six bytes, and a maximum of 31-bit characters can be encoded. The latest UTF-8 specification uses only one to four bytes and can encode up to 21 bits, just to represent all 17 Unicode planes.

UTF8 is a character set in Mysql that supports only the UTF-8 characters with a maximum of three bytes, which is the basic multiple-text plane in Unicode.

Why does the UTF8 in Mysql only support UTF-8 characters that hold a maximum of three bytes? I thought for a moment, probably because Mysql is just beginning to develop that, Unicode has no auxiliary plane this say. At that time, the Unicode commission also made "65,535 characters enough for the world to use" dream. The string length in Mysql is a number of characters rather than bytes, and for the CHAR data type, it is necessary to keep the string long enough. When you use the UTF8 character set, the length you need to keep is utf8 the length of the longest character multiplied by the length of the string, so it is a matter of course to limit the UTF8 maximum length of 3, for example, CHAR Mysql will retain 300 bytes of length. As for the subsequent version why not support the 4-byte length of the UTF-8 character, I think one is for backwards compatibility considerations, and there is really little use of characters outside the basic multilingual plane.

To save 4-byte-length UTF-8 characters in Mysql, you need to use the UTF8MB4 character set, but only the 5.5.3 version will support (View version: select version ();). I think that in order to get better compatibility, you should always use UTF8MB4 rather than UTF8. For char type data, UTF8MB4 will consume more space, according to Mysql official recommendation, use VARCHAR instead of char.

The above comprehensive understanding of the MySQL UTF8 and utf8mb4 is the difference between the small series to share all the content, hope to give you a reference, but also hope that we support the cloud-dwelling community.