(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
automatically generated byinfo2html