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

This article is a detailed introduction to "what is the difference between blob and text in mysql". The content is detailed, the steps are clear, and the details are properly handled. I hope this article "what is the difference between blob and text in mysql" can help you solve your doubts. Let's go deeper and learn new knowledge together with the ideas of Xiaobian.

BLOB in MySQL is used to hold binary data, while TEXT is used to hold character data; BLOB columns have no character set and sort and compare numerical values based on column value bytes, while TEXT columns have a character set and sort and compare values according to the collation rules of the character set.

Operating environment of this tutorial: Windows 7 system, MySQL8 version, Dell G3 computer.

There are two field types in MySQL that are easy to confuse, that is, TEXT and BLOB. Let's take a look at the differences between the two.

Difference between Blob and Text

1. Main differences

The main difference between TEXT and BLOB is that BLOB stores binary data, while TEXT stores character data. Currently almost all blog content images are not binary stored in the database, but uploaded to the server and then used in the text

Tag references, such blogs can use the TEXT type. BLOB can convert images into binary and save them to the database.

2. Type difference

BLOB has four types: TINY BLOB, BLOB, MEDIUM BLOB and LONG BLOB. They differ only in the maximum length of the value they can accommodate.

There are also four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. These types have the same maximum length and storage requirements as BLOB types.

3. Character set

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

4. Case

There is no case conversion during storage or retrieval of a TEXT or BLOB column, it's all the same!

5. Strict mode

When running in non-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.

6 and others.

Tail spaces are not removed when saving or retrieving values for BLOB and TEXT columns.

For indexes on BLOB and TEXT columns, you must specify the length of the index prefix.

BLOB and TEXT columns cannot have default values.

Only the first max_sort_length bytes of the column are used when sorting. The default value for max_sort_length is 1024.

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.

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.

Read here, this article "what is the difference between blob and text in mysql" has been introduced, want to master the knowledge points of this article still need to practice to understand, if you want to know more related content articles, welcome to pay attention to 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.