Oracle 5.0, Reference Manual

Page: 871 / 3232

String Types
851
pad bytes will result in a duplicate-key error. For example, if a table contains
'a'
, an attempt to store
'a\0'
causes a duplicate-key error.
You should consider the preceding padding and stripping characteristics carefully if you plan to use the
BINARY
data type for storing binary data and you require that the value retrieved be exactly the same
as the value stored. The following example illustrates how
0x00
-padding of
BINARY
values affects
column value comparisons:
mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t SET c = 'a';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 | 0 | 1 |
+--------+---------+-------------+
1 row in set (0.09 sec)
If the value retrieved must be the same as the value specified for storage with no padding, it might be
preferable to use
VARBINARY
or one of the
BLOB
data types instead.
11.1.6.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 Section 11.2, “Data Type Storage Requirements” .
BLOB
values are treated as binary strings (byte strings). They have no character set, and sorting and
comparison are based on the numeric values of the bytes in column values.
TEXT
values are treated as
nonbinary strings (character strings). They have a character set, and values are sorted and compared
based on the collation of the character set.
If strict SQL mode is not enabled and you assign a value to a
BLOB
or
TEXT
column that exceeds the
column's maximum length, the value is truncated to fit and a warning is generated. For truncation of
nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion
of the value by using strict SQL mode. See
Section 5.1.7, “Server SQL Modes” .
Beginning with MySQL 5.0.60, truncation of excess trailing spaces from values to be inserted into
TEXT
columns always generates a warning, regardless of the SQL mode.
For
TEXT
and
BLOB
columns, there is no padding on insert and no bytes are stripped on select.
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.
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.
On comparisons,
TEXT
is space extended to fit the compared object, exactly like
CHAR
and
VARCHAR
.