Differences between Blob and Text in MySql _ MySQL

Differences between Blob and Text in MySql bitsCN.com

BLOB is a large binary object that can hold a variable amount of data. There are four BLOB types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They only have different maximum lengths that can hold values.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to four BLOB types, with the same maximum length and storage requirements.

The BLOB column is considered as a binary string (byte string ). The TEXT column is considered as a non-binary string (character string ). 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.

There is no case conversion during TEXT or BLOB column storage or retrieval.

When 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 that it is suitable. 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.

In most cases, BLOB columns can be considered as VARBINARY columns that are sufficiently large. Similarly, the TEXT column can be considered as a VARCHAR column. BLOB and TEXT are different from VARBINARY and VARCHAR in the following aspects:

· 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 ).

Note that during comparison, TEXT will be expanded with spaces to fit the comparison objects, just as CHAR and VARCHAR.

· The Index prefix length must be specified for BLOB and TEXT columns. For CHAR and VARCHAR, the prefix length is optional.

· BLOB 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, BINARY proofreaders of the column character set are assigned to the column.

MySQL Connector/ODBC defines BLOB as LONGVARBINARY and TEXT as LONGVARCHAR.

Because BLOB and TEXT values may be very long, you may encounter some constraints when using them:

· Only the first max_sort_length bytes of the column are used for sorting. The default value of max_sort_length is 1024. this value can be changed using the -- max_sort_length option when the mysqld server is started.

The value of max_sort_length added during running can make more bytes meaningful during sorting or combination. Any client can change the value of the max_sort_length variable of its session:

Mysql & gt; SET max_sort_length = 2000;

Mysql> SELECT id, comment FROM tbl_name

-> Order by comment;

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. For example, the following statement sorts the 2000 bytes of the comment column:

Mysql> SELECT id, SUBSTRING (comment, 1,2000) FROM tbl_name

-> Order by substring (comment, 1,2000 );

· 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. For example, you can use mysql and mysqldump to change the max_allowed_packet value of the client.

Each BLOB or TEXT value is represented by an internally assigned object. This is a comparison with other column types. The latter allocates a storage engine for each column when the table is opened.

BitsCN.com