What is the difference between blob and text in mysql

Difference: 1. BLOB in MySQL is used to save binary data, while TEXT is used to save character data; 2. BLOB columns have no character set, and sorting and comparison are based on the numerical value of column value bytes, while TEXT columns have a character set, and values ​​are sorted and compared according to the collation rules of the character set.

The operating environment of this tutorial: windows7 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.

The difference between blob and text

1. The main difference

The main difference between TEXT and BLOB The difference is that BLOB stores binary data and TEXT stores character data. At present, almost all pictures in blog content are not stored in binary in the database. Instead, the pictures are uploaded to the server and then referenced using the tag in the text. Such blogs can use the TEXT type. BLOB can convert pictures into binary and save them in the database.

2. Type difference

There are 4 types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. They only differ in the maximum length they can hold a value.

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

3. Character set

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

4, case

is stored in the TEXT or BLOB column or During the retrieval process, there is no case conversion, they are all the same!

5. 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 Cropped for fit. 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 intercepted with a warning.

6. Others

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

When you want to make more bytes than max_sort_length meaningful, another way to use GROUP BY or ORDER BY on a BLOB or TEXT column containing long values ​​is to convert the column value into a fixed-length object. 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 size that can actually be passed between the client and 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 client programs.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the difference between blob and text in mysql. For more information, please follow other related articles on the PHP Chinese website!