BLOBis a binary large object that can hold a variable amount of data. The four
LONGBLOB. These differ only in the maximum length of the values they can hold. The four
LONGTEXT. These correspond to the four
BLOBtypes and have the same maximum lengths and storage requirements. See Section 10.5, “Data Type Storage Requirements”.
BLOBvalues are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values.
TEXTvalues are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set.
If strict SQL mode is not enabled and you assign a value to a
TEXTcolumn that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.7, “Server SQL Modes”.
Beginning with MySQL 5.1.24, truncation of excess trailing spaces from values to be inserted into
TEXTcolumns always generates a warning, regardless of the SQL mode.
TEXTcolumn is indexed, index entry comparisons are space-padded at the end. This means that, if the index requires unique values, duplicate-key errors will occur for values that differ only in the number of trailing spaces. For example, if a table contains
'a', an attempt to store
'a 'causes a duplicate-key error. This is not true for
In most respects, you can regard a
BLOBcolumn as a
VARBINARYcolumn that can be as large as you like. Similarly, you can regard a
TEXTcolumn as a
VARCHARin the following ways:
If you use the
BINARYattribute with a
TEXTdata type, the column is assigned the binary collation of the column character set.
LONG VARCHARmap to the
MEDIUMTEXTdata type. This is a compatibility feature.
Only the first
max_sort_lengthbytes of the column are used when sorting. The default value of
max_sort_lengthis 1024. You can make more bytes significant in sorting or grouping by increasing the value of
max_sort_lengthat server startup or runtime. Any client can change the value of its session
SET max_sort_length = 2000;mysql>
SELECT id, comment FROM t->
ORDER BY comment;
Another way to use
ORDER BYon a
TEXTcolumn containing long values when you want more than
max_sort_lengthbytes 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
commentcolumn to be taken into account for sorting:
SELECT id, SUBSTRING(comment,1,2000) FROM t->
ORDER BY SUBSTRING(comment,1,2000);
TEXTcolumns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the
MEMORYstorage engine does not support those data types (see Section 7.5.10, “How MySQL Uses Internal Temporary Tables”). Use of disk incurs a performance penalty, so include
TEXTcolumns in the query result only if they are really needed. For example, avoid using
SELECT *, which selects all columns.
The maximum size of a
TEXTobject 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_packetvariable, 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_packetvalue. See Section 7.5.3, “Tuning Server Parameters”, Section 4.5.1, “mysql — The MySQL Command-Line Tool”, and Section 4.5.4, “mysqldump — A Database Backup Program”. You may also want to compare the packet sizes and the size of the data objects you are storing with the storage requirements, see Section 10.5, “Data Type Storage Requirements”
TEXTvalue is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.
In some cases, it may be desirable to store binary data such as media files in
TEXTcolumns. You may find MySQL's string handling functions useful for working with such data. See Section 11.4, “String Functions”. For security and other reasons, it is usually preferable to do so using application code rather than allowing application users the
FILEprivilege. You can discuss specifics for various languages and platforms in the MySQL Forums (http://forums.mysql.com/).