Shulou(Shulou.com)05/31 Report--

This article to share with you is about MySql Blob and Text difference is what, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after harvest, not much to say, follow Xiaobian to see it.

BLOB is a binary large object that can hold variable amounts of data. There are 4 BLOB types: TINY BLOB, BLOB, MEDIUM BLOB and LONG BLOB. They differ only in the maximum length of the value they can accommodate.

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.

BLOB columns are treated as binary strings (byte strings). TEXT columns are treated as non-binary strings (character strings). BLOB columns have no character set, and sort and compare numeric values based on column value bytes. The TEXT column has a character set, and the values are sorted and compared according to collation rules for the character set.

There is no case conversion during 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 for that column type, the value is truncated to ensure fit. If the truncated character is not a space, a warning is generated. With strict SQL mode, errors are generated and values are rejected rather than intercepted and warned.

In most respects, BLOB columns can be thought of as VARBINARY columns that can be large enough. Similarly, you can think of a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHAR in several ways:

·Tail spaces are not removed when saving or retrieving values for BLOB and TEXT columns. (This is the same as VARBINARY and VARCHAR columns).

Note that comparisons extend TEXT with spaces to fit the objects being compared, just as CHAR and VARCHAR do.

·For BLOB and TEXT column indexes, you must specify the length of the index prefix. For CHAR and VARCHAR, the prefix length is optional.

BLOB and TEXT columns cannot have default values.

LONG and LONG VARCHAR correspond to MEDIUMTEXT data types. This is to ensure compatibility. If the TEXT column type uses the BINARY attribute, the column is assigned binary collation rules for the column character set.

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

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

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

Increasing the value of max_sort_length at runtime makes 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;

Another way to use GROUP BY or ORDER BY for BLOB or TEXT columns with long values is to convert column values to fixed-length objects when you want to make sense of bytes that exceed max_sort_length. The standard approach 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 maximum value that can actually be passed between client and server is determined by the amount of available memory and the size of the communication buffer. You can change the message buffer size by changing 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 on the client side.

Each BLOB or TEXT value is represented by an internally assigned object, respectively. This is in contrast to other column types that allocate storage engines for every 1 column when the table is opened.

The above is what is the difference between Blob and Text in MySql. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.