The difference between text and varchar and char in MySQL

Char type

The length of the char column is fixed to the length declared when the table was created. The length can be any value from 0 to 255. When you save the char value, fill the space on their right to reach the specified length. When a char value is retrieved, the trailing space is removed. No case conversion is made during storage or retrieval.

VARCHAR type

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

Text type

There are 4 types of text: Tinytext, text, Mediumtext, and Longtext. These correspond to 4 types of blobs, with the same maximum length and storage requirements.

A BLOB column is treated as a binary string (a byte string). The text column is treated as a non binary string (character string). Blob columns do not have character sets, and sort and compare numeric values based on column-valued bytes. The text column has a character set, and the values are sorted and compared according to the collation rules of the character set.

There is no case conversion during the storage or retrieval of a text or BLOB column.

When not running in strict mode, if you assign a BLOB or text column to a value that exceeds the maximum length of the column type, the value is truncated to ensure fit. If the truncated character is not a space, a warning is generated. Using strict SQL mode produces an error, and the value is rejected rather than intercepted and given a warning.

In most respects, you can consider a BLOB column to be a varbinary column that is large enough. Similarly, you can treat the text column as a varchar column. BLOBs and text differ from varbinary and varchar in the following ways:

Trailing spaces are not deleted when the values of BLOB and text columns are saved or retrieved. (This is the same as varbinary and varchar columns).

Note that when compared, the text is expanded with a space to fit the objects that are compared, as char and varchar do.

For the index of a BLOB and text column, 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 property, the column will be assigned a two-dollar collation for the column character set.

The MySQL connector/ODBC defines the BLOB value as LongVarBinary and the text value as LongVarChar.

Because blob and text values can be very long, you may encounter some constraints when you use 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 using the--max_sort_length option when starting the MYSQLD server. See section 5.3.3, "Server System variables."

Adding max_sort_length values at run time can make more bytes meaningful when sorting or grouping. 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;

When you want to make a byte that is more than max_sort_length meaningful, another way to use Group by or order by with a BLOB or text column with a Long value is to convert the column value to a fixed-length object. The standard method is to use the SUBSTRING function. For example, the following statement sorts 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 the client and the server is determined by the amount of available memory and the size of the communication buffer. You can change the size of the message cache by changing the value of the Max_allowed_packet variable, but you must also modify both the server and the client program. For example, you can use MySQL and mysqldump to change the Max_allowed_packet value of a client. See section 7.5.2, "Tuning server Parameters", 8.3 sections, "Mysql:mysql command-line tools" and 8.8 sections, "mysqldump: Database backup programs."

Each blob or text value is represented by an internally allocated object. This contrasts with other column types, which allocate the storage engine for each 1 columns when the table is opened.