Focus on the base-mysql blob type and text type

Focus on the base-mysql blob type and text type-binary storage

After a few Java projects, encountered a number of problems, in solving the problem of the basic needs of continuous review and consolidation.

Recently done projects, provided to the interface caller data synchronization interface, the transmitted data format is the JSON string. Because the JSON string structure is more hierarchical, the amount of data is also many. When designing the database, the BLOB type is selected as the field type. Everything on the start, Synchronization service normal operation, but the problem slowly exposed, the client in the temporary I provide data, the Chinese always show garbled, garbled, garbled, has been garbled.

　　Analysis Path of the problem

1. View the database connection string, CHARACTERENCODING=UTF8, configured correctly.

2. Ask the interface caller, the encoding format is consistent, UTF8.

3. View the database encoding format, UTF8, normal.

4. Check the data table encoding format, UTF8, normal.

Then the problem persists, the Chinese data through the query script to check out the results are displayed normal, but one to the Web page is still garbled, garbled. So the data exported to a local, and then observed, found in Chinese garbled place can be seen, a Chinese is 3 bytes replaced, taking into account the UTF8 encoding format, a Chinese accounted for 3 bytes, the cause of the problem can be determined initially is the data of the type of storage lattice caused. Then read the description of the MySQL blob type, blob storage is actually binary data, we look at the BLOB definition:

　　A BLOB is a binary large object that can hold a variable amount of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently. The BLOB column is considered a binary string (a byte string). Blob columns do not have a character set, and sort and compare numeric values based on column-valued bytes. Let's take a look at the definitions of the common large text type text, so that there is a comparison.

　　The text column is treated as a non-binary string (character string), the text column has a character set, and the values are sorted and compared according to the collation rules of the character set.

Usually the configuration of the MySQL character set tells MySQL to execute the query when the encoding format, take the blob stored in the binary data for example: In the MySQL console to execute the query command, the console itself to help us to complete the conversion from byte to text, the corresponding Chinese is also a normal conversion. Remember, here we emphasize the console environment of the MySQL console, or our Common shell command, the mysql-u * *-P script executes.  BYTE--The conversion of the text the MySQL console was converted according to UTF8 encoding format. This is why we logged into MySQL service after mysql-u * * * login mysql server, execute query script to normal data reason.

So why the program connection MySQL returned the result is not correct, because the Java program connected to MySQL execution query, the BLOB type itself is not a character set, will not do according to the character set of proofreading, so Java connection MySQL execution query returned byte[] Byte array is not through any character set of the school team, even if you deposit the BLOB data itself is in accordance with UTF8 encoding format, the resulting results will still be garbled, unless you have the appropriate processing logic in the program.

Well, to this finally solved the problem of Chinese garbled display, but also understand the reason for garbled appearance. The knowledge of BLOB types is also more profound.

Tip: If the data stored in your blob is originally in UTF8 encoded format, and your database and data tables are encoded in the UTF8 format, then when the field type is changed from blob to text type, the data is guaranteed to convert normally, of course, Don't forget to keep the field length to a magnitude. I tested it myself, no problem.

　　

Focus on the base-mysql blob type and text type