• notice
  • Congratulations on the launch of the Sought Tech site

Difference between TEXT and BLOB field type in mysql

There are two field types in MySQL that make people feel confused, that is, TEXT and BLOB, especially bloggers who write their own blog programs do not know whether to choose TEXT or BLOB for their own blog body fields.

The differences are given below:

I.Main differences

The main difference between TEXT and BLOB is that BLOB stores binary data, and TEXT stores character data.At present, almost all the pictures in the blog content are not stored in the database in binary, but upload the pictures to the server and use the tag in the text to quote, so that the blog can use the TEXT type.And BLOB can convert the picture into binary and save it in the database.

Two, type difference

There are 4 types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB.They differ only in the maximum length of the value that can be accommodated.

TEXT also has 4 types: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT.These types have the same maximum length and storage requirements as BLOB types.

Three.Character set

BLOB columns do not have a character set, and sorting and comparison are based on the numeric value of the column value byte.The TEXT column has a character set, and the values ​​are sorted and compared according to the collation rules of the character set


In the process of storing or retrieving TEXT or BLOB columns, there is no case conversion, it's all the same!

Five.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 intercepted to ensure that 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 being intercepted and a warning will be given.


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 use the first max_sort_length bytes of the column when sorting.The default value of max_sort_length is 1024.

When you want to make bytes exceeding max_sort_length meaningful, another way to use GROUP BY or ORDER BY for BLOB or TEXT columns with long values ​​is to convert the column values ​​into 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 transferred 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 buffer by changing the value of the max_allowed_packet variable, but you must modify the server and client programs at the same time.


Technical otaku

Sought technology together

Related Topic


Leave a Reply