(mysql.info.gz) BLOB

Info Catalog

(mysql.info.gz) BINARY VARBINARY

(mysql.info.gz) String types

(mysql.info.gz) 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, `TINYBLOB', `BLOB', `MEDIUMBLOB', and
 `LONGBLOB', differ only in the maximum length of the values they can
 hold.   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.
 
 `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 is 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 assigned to the column as of MySQL 4.1.
 Before 4.1, `TEXT' sorting and comparison are based on the collation of
 the server character set.
 
 No lettercase conversion takes place during storage or retrieval.
 
 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 ways in which `BLOB' and
 `TEXT' differ from `CHAR' and `VARCHAR' are:
 
    * There is no trailing-space removal for `BLOB' and `TEXT' columns
      when values are stored or retrieved. This differs from `CHAR'
      columns (trailing spaces are removed when values are retrieved)
      and from `VARCHAR' columns (trailing spaces are removed when
      values are stored).
 
    * You can have indexes on `BLOB' and `TEXT' columns only as of MySQL
      3.23.2 for `MyISAM' tables or MySQL 4.0.14 for `InnoDB' tables.
      Older versions of MySQL did not support indexing these column
      types.
 
    * For indexes on `BLOB' and `TEXT' columns, you must specify an index
      prefix length. For `CHAR' and `VARCHAR', a prefix length is
      optional.
 
    * `BLOB' and `TEXT' columns cannot have `DEFAULT' values.
 
 From MySQL 4.1.0 on, `LONG' and `LONG VARCHAR' map to the `MEDIUMTEXT'
 data type. This is a compatibility feature. If you use the `BINARY'
 attribute with a `TEXT' column 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 may be extremely long, you may
 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' option when
      starting the `mysqld' server.   Server system variables.
 
      As of MySQL 4.0.3, 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 TBL_NAME
               -> 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 TBL_NAME
               -> ORDER BY SUBSTRING(comment,1,2000);
 
      Before MySQL 3.23.2, you can group on an expression involving
      `BLOB' or `TEXT' values by using a column alias or by specifying
      the column position:
 
           mysql> SELECT id, SUBSTRING(comment,1,2000) AS b
               -> FROM TBL_NAME GROUP BY b;
           mysql> SELECT id, SUBSTRING(comment,1,2000)
               -> FROM TBL_NAME GROUP BY 2;
 
    * 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' mysql, and  `mysqldump' mysqldump.
 
 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.
 

Info Catalog

(mysql.info.gz) BINARY VARBINARY

(mysql.info.gz) String types

(mysql.info.gz) ENUM