Understand the difference between TEXT and BLOB field types in MySQL

There are two field types in MySQL that are easy to confuse, that is TEXT and BLOB, especially bloggers who write their own blog programs do not know to change to their own Select TEXT or BLOB type for the blog text field.
The following are some differences:
1. Main differences
The main difference between TEXT and BLOB is that BLOB stores binary data and TEXT stores character data. At present, almost all pictures in blog content are not stored in binary in the database. Instead, the pictures are uploaded to the server and then referenced using the tag in the text. Such blogs can use the TEXT type. BLOB can convert pictures into binary and save them in the database.
2. Type differences
There are 4 types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. They only differ in the maximum length they can hold a value.
TEXT also has 4 types: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. These types have the same maximum length and storage requirements as the BLOB types.
3. Character set
BLOB columns do not have a character set, and sorting and comparison are based on the numerical value of the column value bytes. The TEXT column has a character set, and the values ​​are sorted and compared according to the collation rules of the character set
4. Case
does not exist during the storage or retrieval process of TEXT or BLOB columns Case conversion is the same!
5. Strict Mode
When running in non-strict mode, if you assign a value to a BLOB or TEXT column that exceeds the maximum length of the column type, the value is truncated to ensure it fits. If the truncated character is not a space, a warning will be generated. Using strict SQL mode, an error will be generated and the value will be rejected instead of intercepted with a warning.
6. Others
Do not delete trailing spaces when saving or retrieving BLOB and TEXT column values.
For indexes on BLOB and TEXT columns, the length of the index prefix must be specified.
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 of max_sort_length is 1024.
When you want to make more bytes than max_sort_length meaningful, another way to use GROUP BY or ORDER BY on a BLOB or TEXT column containing long values ​​is to convert the column value into a fixed-length object. The standard approach is to use the SUBSTRING function.
The maximum size of a BLOB or TEXT object is determined by its type, but the maximum size that can actually be passed between the client and server is determined by the amount of available memory and the size of the communication cache area. You can change the size of the message buffer by changing the value of the max_allowed_packet variable, but you must modify both the server and client programs.

The above is the detailed content of Understand the difference between TEXT and BLOB field types in MySQL. For more information, please follow other related articles on the PHP Chinese website!