The difference between a blob and text in MySQL

A blob is a binary large object that can hold a variable amount of data. There are 4 types of blobs: Tinyblob, BLOBs, Mediumblob, and Longblob. They can only accommodate the maximum length of a value differently.

There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. These correspond to 4 types of blobs, with the same maximum length and storage requirements.

The BLOB column is considered a binary string (a byte string). The text column is treated as a non-binary string (character string).

Blob columns do not have a character set, 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 according to the collation rules of the character set.

There is no case conversion during the storage or retrieval of a text or BLOB column.

When not running in strict mode, if you assign a BLOB or text column A value that exceeds the maximum length of the column type, the value is truncated to ensure that it is appropriate. If the truncated character is not a space, a warning will be generated.

With strict SQL mode, errors are generated and the values are rejected rather than intercepted and given a warning.

In most respects, you can treat a BLOB column as a varbinary column that is large enough. Similarly, you can treat the text column as a varchar column. BLOBs and text differ from varbinary and varchar in the following ways:

1. Trailing spaces are not deleted when you save or retrieve the values of the Blob and text columns. (This is the same as varbinary and varchar columns).

Note that the text is expanded with a space in comparison to fit the object to compare, just as char and varchar do.

2. For the index of the Blob and text columns, you must specify the length of the index prefix. For char and varchar, the prefix length is optional.

3. BLOBs and text columns cannot have default values.

Long and long varchar correspond to the Mediumtext data type. This is to ensure compatibility. If the text column type uses the binary attribute, the column is assigned a two-element collation of the column character set.

The MySQL connector/ODBC defines the BLOB value as LongVarBinary and defines the text value as LongVarChar.

Because the blob and text values can be very long, you may encounter some constraints when using them:

1. Use only the first max_sort_length bytes of the column when sorting. The default value for Max_sort_length is 1024, which can bechanged using the--max_sort_length option when starting MySQL D server.

Increasing the value of max_sort_length at run time can make more bytes meaningful when sorting or combining. Any client can change the value of its session max_sort_length variable:

mysql> SET max_sort_length = 2000;

mysql> SELECT ID, comment from tbl_name

ORDER by comment;

When you want to make sense for more than max_sort_length bytes, another way to use a group by or order by for 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. For example, the following statement sorts the 2000 bytes of a comment column:

mysql> SELECT ID, SUBSTRING (comment,1,2000) from Tbl_name

ORDER by SUBSTRING (comment,1,2000);

2. 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 communication buffer size. 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 the client program. For example, you can use MySQL and mysqldump to change the client's Max_allowed_packet value.

Each blob or text value is represented by an internally allocated object. This contrasts with other column types, which allocate the storage engine for every 1 columns when the table is opened.

The difference between a blob and text in MySQL