Comparison of text and blob in MySQL

MySQL has text and BLOB:

(1) Same

• There is no case conversion during text or blob column storage or retrieval. When the mode is not running in 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 proper 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 instead of being intercepted and a warning is given.
  
• Blob and text Columns cannot have default values.
  
• When you save or retrieve the values of blob and text columns, trailing spaces are not deleted. (This is the same as the varbinary and varchar columns ).
  
• The index prefix length must be specified for index of blob and text columns. For char and varchar, the prefix length is optional.
  

(2) Differences

Text:

• The text value is case-insensitive.
  
• Text is considered as a non-binary string
  
• The text column has a character set and sorts and compares values according to the character set's checking rules.
  
• The text column can be considered as a varchar column.
  
• MySQL connectionProgram/ODBC defines the text value as longvarchar
  
• Blob can store images, but text cannot. Text can only store plain text files. Four text types, tinytext, text, mediumtext, and longtext, correspond to four blob types, and have the same maximum length and storage requirements.
  

BLOB:

• Sort and compare blob values in Upper/lower case sensitive mode;
  
• Blob is considered as a binary string;
  

• Blob columns do not have character sets, and sort and compare value values based on column value bytes.

  
  

• In most cases, blob columns can be considered as varbinary columns that are sufficiently large.

  
  

• MySQL Connection Program/ODBC defines blob value as longvarbinary
  
• A blob is a large binary object that can save a variable amount of data. Four blob types, tinyblob, blob, mediumblob, and longblob, differ only in the maximum length of values they can save.
  

(3) Others:

Varchar, blob
And the text type are variable-length, and the storage requirements depend on the actual length of the column value (expressed in l in the previous table), rather than the maximum possible size of the type. For example
The varchar (10) column can store a string with a maximum length of 10 characters. The actual storage needs to be the length of the string.
To record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.
Blob and text types require 1, 2, 3, or 4 bytes to record the length of column values, depending on the maximum possible length of the type. Varchar must be defined with a maximum size of 255. text is not required. If you assign a value that exceeds the maximum length of the column type to a blob or text column, the value is truncated to suit it.

Char (n) fixed length, up to 255 characters
Varchar (n) variable length, MySQL 4.1 and a maximum of 255 characters before, MySQL 5 after a maximum of 65535 bytes
Tinytext variable length, up to 255 characters
Variable text length, up to 65535 characters
Mediumtext variable length, up to 16777215 (2 ^ 24-1) characters
Longtext can be a variable length of up to 4294967295 (2 ^ 32-1) (4G) characters