Differences between text and varchar and char in mysql

The length of a char column is fixed to the length declared during table creation. The length can be any value from 0 to 255. When saving CHAR values, fill in spaces on the right of them to reach the specified length. When the CHAR value is retrieved, the trailing space is deleted. Case-insensitive conversion is not performed during storage or retrieval. The value in the varchar column of the VARCHAR type is variable length.

The length of a char column is fixed to the length declared during table creation. The length can be any value from 0 to 255. When saving CHAR values, fill in spaces on the right of them to reach the specified length. When the CHAR value is retrieved, the trailing space is deleted. Case-insensitive conversion is not performed during storage or retrieval. The value in the varchar column of the VARCHAR type is variable length.

Char type

The length of the CHAR column is fixed to the length declared during table creation. The length can be any value from 0 to 255. When saving CHAR values, fill in spaces on the right of them to reach the specified length. When the CHAR value is retrieved, the trailing space is deleted. Case-insensitive conversion is not performed during storage or retrieval.

Varchar type

The value in the VARCHAR column is a variable-length string. Length can be set to a value between 0 and 65,535. (The maximum valid length of a VARCHAR is determined by the maximum row size and the character set used. The total length is 65,532 bytes ).

Text Type

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 index of 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. See section 5.3.3 "server system variables ".

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> 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,) FROM tbl_name-> order by substring (comment );·

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. See section 7.5.2, "Adjusting server Parameters", section 8.3, "mysql: MySQL command line tool", section 8.8, and "mysqldump: database backup program ".

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.