MySQL Reference Manual :: 11.4.3 The BLOB and TEXT Types

There is no trailing-space removal for BLOB and TEXT columns when values are stored or retrieved. Before MySQL 5.0.3, this differs from VARBINARY and VARCHAR, for which trailing spaces are removed when values are stored.

Note that TEXT is on comparison space extended to fit the compared object, exactly like CHAR and VARCHAR.

You can have indexes on BLOB and TEXT columns only as of MySQL 3.23.2 for MyISAM tables or MySQL 4.0.14 for InnoDB tables. Older versions of MySQL did not support indexing these column types.

For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional.

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; this value can be changed using the --max_sort_length option when starting the mysqld server. See Section 5.3.3, “Server System Variables”.

As of MySQL 4.0.3, you can make more bytes significant in sorting or grouping by increasing the value of max_sort_length at runtime. 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;

Another way to use GROUP BY or ORDER BY on a BLOB or TEXT column containing long values when you want more than max_sort_length bytes to be significant is to convert the column value into a fixed-length object. The standard way to do this is with the SUBSTRING function. For example, the following statement causes 2000 bytes of the comment column to be taken into account for sorting:

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name
    -> ORDER BY SUBSTRING(comment,1,2000);

Before MySQL 3.23.2, you can group on an expression involving BLOB or TEXT values by using a column alias or by specifying the column position:

mysql> SELECT id, SUBSTRING(comment,1,2000) AS b
    -> FROM tbl_name GROUP BY b;
mysql> SELECT id, SUBSTRING(comment,1,2000)
    -> FROM tbl_name GROUP BY 2;

The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program. For example, both mysql and mysqldump allow you to change the client-side max_allowed_packet value. See Section 7.5.2, “Tuning Server Parameters”, Section 8.3, “mysql, the Command-Line Tool”, and Section 8.8, “The mysqldump Database Backup Program”.