Oracle 5.0, Reference Manual

Page: 880 / 3232

Storage Requirements for String Types
860
Data Type Storage Required
YEAR
1 byte
For details about internal representation of temporal values, see MySQL Internals: Important
Algorithms and Structures .
Storage Requirements for String Types
In the following table,
M
represents the declared column length in characters for nonbinary string types
and bytes for binary string types.
L
represents the actual length in bytes of a given string value.
Data Type Storage Required
CHAR(M) M
×
w
bytes, 0
<= M <=
255, where
w
is the number of bytes
required for the maximum-length character in the character
set
BINARY(M) M
bytes, 0
<= M <=
255
VARCHAR(M)
,
VARBINARY(M) L
+ 1 bytes if column values require 0 – 255 bytes,
L
+ 2
bytes if values may require more than 255 bytes
TINYBLOB
,
TINYTEXT L
+ 1 bytes, where
L
< 2
8
BLOB
,
TEXT L
+ 2 bytes, where
L
< 2
16
MEDIUMBLOB
,
MEDIUMTEXT L
+ 3 bytes, where
L
< 2
24
LONGBLOB
,
LONGTEXT L
+ 4 bytes, where
L
< 2
32
ENUM('value1','value2',...)
1 or 2 bytes, depending on the number of enumeration
values (65,535 values maximum)
SET('value1','value2',...)
1, 2, 3, 4, or 8 bytes, depending on the number of set
members (64 members maximum)
Variable-length string types are stored using a length prefix plus data. The length prefix requires from
one to four bytes depending on the data type, and the value of the prefix is
L
(the byte length of the
string). For example, storage for a
MEDIUMTEXT
value requires
L
bytes to store the value plus three
bytes to store the length of the value.
To calculate the number of bytes used to store a particular
CHAR
,
VARCHAR
, or
TEXT
column value, you
must take into account the character set used for that column and whether the value contains multi-
byte characters. In particular, when using the
utf8
Unicode character set, you must keep in mind that
not all characters use the same number of bytes and can require up to three bytes per character. For
a breakdown of the storage used for different categories of
utf8
characters, see Section 10.1.10,
“Unicode Support” .
VARCHAR
,
VARBINARY
, and the
BLOB
and
TEXT
types are variable-length types. For each, the storage
requirements depend on these factors:
• The actual length of the column value
• The column's maximum possible length
• The character set used for the column, because some character sets contain multi-byte characters
For example, a
VARCHAR(255)
column can hold a string with a maximum length of 255 characters.
Assuming that the column uses the
latin1
character set (one byte per character), the actual storage
required is the length of the string (
L
), plus one byte to record the length of the string. For the string
'abcd'
,
L
is 4 and the storage requirement is five bytes. If the same column is instead declared to use
the
ucs2
double-byte character set, the storage requirement is 10 bytes: The length of
'abcd'
is eight
bytes and the column requires two bytes to store lengths because the maximum length is greater than
255 (up to 510 bytes).