(mysql.info) blob
Info Catalog
(mysql.info) binary-varbinary
(mysql.info) string-types
(mysql.info) enum
11.4.3 The `BLOB' and `TEXT' Types
----------------------------------
A `BLOB' is a binary large object that can hold a variable amount of
data. The four `BLOB' types are `TINYBLOB', `BLOB', `MEDIUMBLOB', and
`LONGBLOB'. These differ only in the maximum length of the values they
can hold. The four `TEXT' types are `TINYTEXT', `TEXT', `MEDIUMTEXT',
and `LONGTEXT'. These correspond to the four `BLOB' types and have the
same maximum lengths and storage requirements. See
storage-requirements. No lettercase conversion for `TEXT' or `BLOB'
columns takes place during storage or retrieval.
`BLOB' columns are treated as binary strings (byte strings). `TEXT'
columns are treated as non-binary strings (character strings). `BLOB'
columns have no character set, and sorting and comparison are based on
the numeric values of the bytes in column values. `TEXT' columns have
a character set, and values are sorted and compared based on the
collation of the character set.
If a `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 `BLOB' columns.
When not running in strict mode, if you assign a value to a `BLOB' or
`TEXT' column that exceeds the data type's maximum length, the value is
truncated to fit. If the truncated characters are not spaces, a warning
is generated. You can cause an error to occur and the value to be
rejected rather than to be truncated with a warning by using strict SQL
mode. See server-sql-mode.
In most respects, you can regard a `BLOB' column as a `VARBINARY'
column that can be as large as you like. Similarly, you can regard a
`TEXT' column as a `VARCHAR' column. `BLOB' and `TEXT' differ from
`VARBINARY' and `VARCHAR' in the following ways:
* 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'.
* For indexes on `BLOB' and `TEXT' columns, you must specify an index
prefix length. For `CHAR' and `VARCHAR', a prefix length is
optional. See indexes.
* `BLOB' and `TEXT' columns cannot have `DEFAULT' values.
`LONG' and `LONG VARCHAR' map to the `MEDIUMTEXT' data type. This is a
compatibility feature. If you use the `BINARY' attribute with a `TEXT'
data type, the column is assigned the binary collation of the column
character set.
MySQL Connector/ODBC defines `BLOB' values as `LONGVARBINARY' and `TEXT'
values as `LONGVARCHAR'.
Because `BLOB' and `TEXT' 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. This
value can be changed using the -max_sort_length=N option when
starting the `mysqld' server. See server-system-variables.
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 t
-> 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 t
-> ORDER BY SUBSTRING(comment,1,2000);
* 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 server-parameters,
mysql, and mysqldump.
Each `BLOB' or `TEXT' value 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 `BLOB' or `TEXT' columns. You may find MySQL's string handling
functions useful for working with such data. See
string-functions. For security and other reasons, it is usually
preferable to do so using application code rather than allowing
application users the `FILE' privilege. You can discuss specifics for
various languages and platforms in the MySQL Forums
(`http://forums.mysql.com/').
Info Catalog
(mysql.info) binary-varbinary
(mysql.info) string-types
(mysql.info) enum
automatically generated byinfo2html