GNU Info

(mysql.info)BLOB

---

Next: ENUM Prev: CHAR Up: String types

---

Enter node , (file) or (file)node

The `BLOB' and `TEXT' Types
...........................

A `BLOB' is a binary large object that can hold a variable amount of
data.  The four `BLOB' types `TINYBLOB', `BLOB', `MEDIUMBLOB', and
`LONGBLOB' differ only in the maximum length of the values they can
hold.  Note: Storage requirements.

The four `TEXT' types `TINYTEXT', `TEXT', `MEDIUMTEXT', and `LONGTEXT'
correspond to the four `BLOB' types and have the same maximum lengths
and storage requirements.  The only difference between `BLOB' and
`TEXT' types is that sorting and comparison is performed in
case-sensitive fashion for `BLOB' values and case-insensitive fashion
for `TEXT' values.  In other words, a `TEXT' is a case-insensitive
`BLOB'.

If you assign a value to a `BLOB' or `TEXT' column that exceeds the
column type's maximum length, the value is truncated to fit.

In most respects, you can regard a `TEXT' column as a `VARCHAR' column
that can be as big as you like.  Similarly, you can regard a `BLOB'
column as a `VARCHAR BINARY' column.  The differences are:

   * You can have indexes on `BLOB' and `TEXT' columns with MySQL
     Version 3.23.2 and newer. Older versions of MySQL did not support
     this.

   * There is no trailing-space removal for `BLOB' and `TEXT' columns
     when values are stored, as there is for `VARCHAR' columns.

   * `BLOB' and `TEXT' columns cannot have `DEFAULT' values.

*MyODBC* defines `BLOB' values as `LONGVARBINARY' and `TEXT' values as
`LONGVARCHAR'.

Because `BLOB' and `TEXT' values may be extremely long, you may run up
against some constraints when using them:

   * If you want to use `GROUP BY' or `ORDER BY' on a `BLOB' or `TEXT'
     column, you must convert the column value into a fixed-length
     object. The standard way to do this is with the `SUBSTRING'
     function.  For example:

          mysql> select comment from tbl_name,substring(comment,20) as substr
                 ORDER BY substr;

     If you don't do this, 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
     `-O' option when starting the `mysqld' server. You can group on an
     expression involving `BLOB' or `TEXT' values by specifying the
     column position or by using an alias:

          mysql> select id,substring(blob_col,1,100) from tbl_name
                     GROUP BY 2;
          mysql> select id,substring(blob_col,1,100) as b from tbl_name
                     GROUP BY b;

   * The maximum size of a `BLOB' or `TEXT' object is determined by its
     type, but the largest value you can actually 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, but you must do so on both the server and
     client ends. Note: Server parameters.

Note that each `BLOB' or `TEXT' value is represented internally by a
separately allocated object. This is in contrast to all other column
types, for which storage is allocated once per column when the table is
opened.