What is the difference between TEXT and BLOB Field Types in MySQL? mysqltextblob Field

What is the difference between TEXT and BLOB Field Types in MySQL? mysqltextblob Field

There are two Field Types in MySQL that are confusing, namely TEXT and BLOB. In particular, the bloggers who write their own blog programs do not know whether to change their blog body fields to TEXT or BLOB.

The following are some differences:

I. Main differences

The main difference between TEXT and BLOB is that BLOB stores binary data and TEXT stores character data. Currently, images in almost all blogs are not stored in the database in binary format. Instead, they are uploaded to the server and then referenced using the label, such a blog can use the TEXT type. BLOB can convert the image into binary and save it to the database.

Ii. Differences of Types

BLOB has four types: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They only have different maximum lengths that can hold values.

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.

Iii. Character Set

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.

Iv. Case sensitivity

There is no case conversion during TEXT or BLOB column storage or retrieval. They are all the same!

V. Strict Mode

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

Vi. Others

When you save or retrieve the values of BLOB and TEXT columns, trailing spaces are not deleted.

The index prefix length must be specified for index of BLOB and TEXT columns.

BLOB and TEXT Columns cannot have default values.

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

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.

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.

In mysql, the types of text, longtext, and mediumtext fields are different.

MySQL supports a large number of column types, which can be divided into three types: Number type, date and time type, and string (character) type. This section first provides an overview of available types, summarizes the storage requirements for each column type, and then provides a more detailed description of the type nature in each class. The overview is intended to be simplified. More detailed descriptions should take into account additional information about specific column types, such as the allowed format for which you can specify values.

The column types supported by MySQL are listed below. The following code is used to describe:

M
Specifies the maximum display size. The max valid display size is 255.
D
Applicable to the floating point type and the number of digits following the decimal point. The maximum possible value is 30, but should not be greater than the M-2.
Square brackets ("[" and "]") indicate the part of the optional type modifier.

Note: If you specify ZEROFILL, MySQL automatically adds the UNSIGNED attribute to this column.

TINYINT [(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is-128 to 127, and the unsigned range is 0 to 255.

SMALLINT [(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is-32768 to 32767, And the unsigned range is 0 to 65535.

MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL]
An integer of medium size. The signed range is-8388608 to 8388607, And the unsigned range is 0 to 16777215.

INT [(M)] [UNSIGNED] [ZEROFILL]
A normal integer. The signed range is-2147483648 to 2147483647, And the unsigned range is 0 to 4294967295.

INTEGER [(M)] [UNSIGNED] [ZEROFILL]
This is a synonym for INT.

BIGINT [(M)] [UNSIGNED] [ZEROFILL]

A large integer. The signed range is-9223372036854775808 to 9223372036854775807, And the unsigned range is 0

18446744073709551615. Note that all arithmetic operations are completed with signed BIGINT or DOUBLE values. Therefore, you should not use signed big integers greater than 9223372036854775807 (63 bits), except for bitwise functions! Note that when the two parameters are INTEGER values,-, +, and * use BIGINT operations! This means that if you take two big integers (or from the function that returns integers) and the result is greater than 9223372036854775807, you can get unexpected results. A floating-point number cannot be unsigned. For a single-precision floating-point number, its precision can be <= 24. For a double-precision floating-point number, it is between 25 and 53, these types, such as FLOAT and DOUBLE, are immediately described below. FLOAT (X) has the same FLOAT and DOUBLE ranges, but the display size and decimal places are undefined. In MySQL3.23, this is a real floating point value. In earlier MySQL versions, FLOAT (precision) always has two decimal places. This syntax is provided for ODBC compatibility.

FLOAT [(M, D)] [ZEROFILL]
A small (single precision) floating point number. It cannot be unsigned. The allowed values are-3.402823466E + 38 to-1.175494351E-38,0 and 1.175494351E-38 to 3.402823466E + 38. M indicates the display width, and D indicates the decimal digits. FLOAT without parameters or a parameter with <24 represents a single precision floating point number.

DOUBLE [(M, D)] [ZEROFILL]
A normal-sized (double-precision) floating point number. It cannot be unsigned. The allowed value is-1.7976931348623157E + 3 ...... the remaining full text>

What is the range of the Text field in Mysql? And usage

In mysql, the maximum text length is 65,535 (2 to the power of 16-1) characters in the TEXT column.
If you think the text length is not enough, you can choose
MEDIUMTEXT can be up to 16,777,215 characters in length.
The maximum LONGTEXT length is 4,294,967,295.
Text is mainly used to store non-binary Text, such as Forum posts, questions, or questions and answers that Baidu knows.
You need to understand the differences between the text and char varchar blob types.
For detailed usage, see the manual.
Dev.mysql.com/..erview