The difference between text and blob field types in Mysql _mysql

In MySQL, there are two field types that are confusing, that is, text and blobs, especially bloggers who write their own blog do not know to change to their own blog positive text segment select text or blob type.

Here are a few differences:

I. Main differences

The main difference between the text and the Blob is that the BLOB holds the binary data and the text holds the character data. At present, almost all the images in the blog content is not stored in the database in binary, but upload the picture to the server and then use the text of the tag reference, such a blog can use the text type. A blob can convert a picture into a binary system and save it to a database.

Ii. Types of differences

There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They are only the maximum lengths that can accommodate the values.

There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. These types, like blob types, have the same maximum length and storage requirements.

Third, Character set

Blob columns do not have character sets, and sort and compare numeric values based on column-valued bytes. The text column has a character set, and the values are sorted and compared based on the collation rules of the character set

Four, case

In the storage or retrieval of a text or BLOB column, there is no case conversion, all the same!

Five, Strict mode

When running in a non strict mode, if you assign a BLOB or text column A value that exceeds the maximum length of the column type, the value is intercepted to ensure fit. If the truncated character is not a space, a warning is generated. Using strict SQL mode produces an error, and the value is rejected rather than intercepted and given a warning.

Six, other

Trailing spaces are not deleted when the values of BLOB and text columns are saved or retrieved.

For the index of a BLOB and text column, you must specify the length of the index prefix.

Blob and text columns cannot have default values.

Only the first max_sort_length bytes of the column are used when sorting. The default value for Max_sort_length is 1024.

When you want to make a byte that is more than max_sort_length meaningful, another way to use Group by or order by with a BLOB or text column with a Long value is to convert the column value to a fixed-length object. The standard method is to use the SUBSTRING function.

The maximum size of a blob or text object is determined by its type, but the maximum value that can actually be passed between the client and the server is determined by the amount of available memory and the size of the communication buffer. You can change the size of the message cache by changing the value of the Max_allowed_packet variable, but you must also modify both the server and the client program.