Oracle 5.0, Reference Manual

Page: 868 / 3232

String Types
848
11.1.6. String Types
The string types are
CHAR
,
VARCHAR
,
BINARY
,
VARBINARY
,
BLOB
,
TEXT
,
ENUM
, and
SET
. This
section describes how these types work and how to use them in your queries. For string type storage
requirements, see
Section 11.2, “Data Type Storage Requirements” .
11.1.6.1. The
CHAR
and
VARCHAR
Types
The
CHAR
and
VARCHAR
types are similar, but differ in the way they are stored and retrieved. As of
MySQL 5.0.3, they also differ in maximum length and in whether trailing spaces are retained.
The
CHAR
and
VARCHAR
types are declared with a length that indicates the maximum number of
characters you want to store. For example,
CHAR(30)
can hold up to 30 characters.
The length of a
CHAR
column is fixed to the length that you declare when you create the table. The
length can be any value from 0 to 255. When
CHAR
values are stored, they are right-padded with
spaces to the specified length. When
CHAR
values are retrieved, trailing spaces are removed.
Values in
VARCHAR
columns are variable-length strings. The length can be specified as a value from 0
to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length
of a
VARCHAR
in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is
shared among all columns) and the character set used. See Section E.7.4, “Limits on Table Column
Count and Row Size” .
In contrast to
CHAR
,
VARCHAR
values are stored as a 1-byte or 2-byte length prefix plus data. The
length prefix indicates the number of bytes in the value. A column uses one length byte if values
require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
If strict SQL mode is not enabled and you assign a value to a
CHAR
or
VARCHAR
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” .
For
VARCHAR
columns, trailing spaces in excess of the column length are truncated prior to insertion
and a warning is generated, regardless of the SQL mode in use. For
CHAR
columns, truncation of
excess trailing spaces from inserted values is performed silently regardless of the SQL mode.
VARCHAR
values are not padded when they are stored. Handling of trailing spaces is version-
dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in
conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when
they are stored into a
VARCHAR
column; this means that the spaces also are absent from retrieved
values.
Before MySQL 5.0.3, if you need a data type for which trailing spaces are not removed, consider using
a
BLOB
or
TEXT
type. Also, if you want to store binary values such as results from an encryption or
compression function that might contain arbitrary byte values, use a
BLOB
column rather than a
CHAR
or
VARCHAR
column, to avoid potential problems with trailing space removal that would change data
values.
The following table illustrates the differences between
CHAR
and
VARCHAR
by showing the result of
storing various string values into
CHAR(4)
and
VARCHAR(4)
columns (assuming that the column uses
a single-byte character set such as
latin1
).
Value
CHAR(4)
Storage Required
VARCHAR(4)
Storage Required
'' ' '
4 bytes
''
1 byte
'ab' 'ab '
4 bytes
'ab'
3 bytes
'abcd' 'abcd'
4 bytes
'abcd'
5 bytes