(mysql.info) blob

 
 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