BLOB is a binary large object that can
hold a variable amount of data. The four
BLOB types are
LONGBLOB. These differ only in the maximum
length of the values they can hold. The four
TEXT types are
LONGTEXT. These correspond to the four
BLOB types and have the same maximum
lengths and storage requirements. See
Section 11.2, “Data Type Storage Requirements”.
BLOB values 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
TEXT values 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
TEXT column 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.0.60, truncation of excess trailing
spaces from values to be inserted into
TEXT columns always generates a
warning, regardless of the SQL mode.
columns, there is no padding on insert and no bytes are
stripped on select.
TEXT column 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
column as a
that can be as large as you like. Similarly, you can regard a
TEXT column as a
VARCHAR in the following ways:
There is no trailing-space removal for
TEXT columns when values
are stored or retrieved. Before MySQL 5.0.3, this differs
VARCHAR, for which trailing
spaces are removed when values are stored.
If you use the
BINARY attribute with a
TEXT data type, the column is assigned the
binary collation of the column character set.
map to the
MEDIUMTEXT data type. This is a
MySQL Connector/ODBC defines
BLOB values as
values can be extremely long, you might encounter some
constraints in using them:
Only the first
max_sort_length bytes of
the column are used when sorting. The default value of
max_sort_length is 1024.
You can make more bytes significant in sorting or grouping
by increasing the value of
max_sort_length at server
startup or runtime. Any client can change the value of its
SET max_sort_length = 2000;mysql>
SELECT id, comment FROM t->
ORDER BY comment;
TEXT columns 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
MEMORY storage engine does
not support those data types (see
Section 8.8.5, “How MySQL Uses Internal Temporary Tables”). Use of disk
incurs a performance penalty, so include
in the query result only if they are really needed. For
example, avoid using
*, which selects all columns.
The maximum size of a
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
variable, but you must do so for both the server and your
client program. For example, both mysql
and mysqldump enable you to change the
See Section 8.9.2, “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 11.2, “Data Type Storage Requirements”
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
TEXT columns. You may find MySQL's string
handling functions useful for working with such data. See
Section 12.5, “String Functions”. For security and other
reasons, it is usually preferable to do so using application
code rather than giving application users the
FILE privilege. You can discuss
specifics for various languages and platforms in the MySQL
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices