Differences between TEXT and BLOB Field Types in MySQL

There are two Field Types in MySQL that are confusing, namely TEXT and BLOB. In particular, the bloggers who write their own blog programs do not know whether to change their blog body fields to TEXT or BLOB. The following are some differences: 1. The main difference between TEXT and BLOB is that BLOB stores binary data and TEXT stores character data. Currently

There are two Field Types in MySQL that are confusing, namely TEXT and BLOB. In particular, the bloggers who write their own blog programs do not know whether to change their blog body fields to TEXT or BLOB. The following are some differences: 1. The main difference between TEXT and BLOB is that BLOB stores binary data and TEXT stores character data. Currently

There are two Field Types in MySQL that are confusing, namely TEXT and BLOB. In particular, the bloggers who write their own blog programs do not know whether to change their blog body fields to TEXT or BLOB.

The following are some differences:

I. Main differences

The main difference between TEXT and BLOB is that BLOB stores binary data and TEXT stores character data. Currently, images in almost all blogs are not stored in the database in binary format. Instead, they are uploaded to the server and then referenced using tags in the body. In this way, blogs can use the TEXT type. BLOB can convert the image into binary and save it to the database.

Ii. Differences of Types

BLOB has four types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They only have different maximum lengths that can hold values.

There are also four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These types have the same maximum length and storage requirements as BLOB types.

Iii. Character Set

BLOB columns do not have character sets, and sort and compare value values based on column value bytes. The TEXT column has a character set and sorts and compares values according to the character set's checking rules.

Iv. Case sensitivity

There is no case conversion during TEXT or BLOB column storage or retrieval. They are all the same!

V. Strict Mode

When running in non-strict mode, if you assign a value that exceeds the maximum length of the column type to the BLOB or TEXT column, the value is truncated to ensure the fit. If the truncated characters are not spaces, a warning is generated. When strict SQL mode is used, errors are generated, and the value is denied, rather than truncated, and a warning is given.

Vi. Others

When you save or retrieve the values of BLOB and TEXT columns, trailing spaces are not deleted.

The index prefix length must be specified for index of BLOB and TEXT columns.

BLOB and TEXT Columns cannot have default values.

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

When you want to make the byte that exceeds max_sort_length meaningful, another way to use group by or order by for long BLOB or TEXT columns is to convert column values to fixed-length objects. 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 actual maximum value that can be passed between the client and the server is determined by the amount of available memory and the size of the Communication cache. You can change the size of the message cache by changing the value of the max_allowed_packet variable, but you must modify both the server and client programs.