MySQL supports a number of data types in several categories: numeric
types, date and time types, and string (character) types. This
chapter first gives an overview of these data types, and then
provides a more detailed description of the properties of the types
in each category, and a summary of the data type storage
requirements. The overview is intentionally brief. The more detailed
descriptions should be consulted for additional information about
particular data types, such as the allowable formats in which you
can specify values.
MySQL versions 4.1 and up support extensions for handing spatial
data. Information about spatial types is provided in
Chapter 17, Spatial Extensions in MySQL.
Several of the data type descriptions use these conventions:
M
Indicates the maximum display width. The maximum legal display
width is 255.
D
Applies to floating-point and fixed-point types and indicates
the number of digits following the decimal point. The maximum
possible value is 30, but should be no greater than
M-2.
Square brackets (‘[’ and
‘]’) indicate parts of type
specifiers that are optional.
M indicates the maximum display
width. The maximum legal display width is 255. Display width is
unrelated to the storage size or range of values a type can
contain, as described in Section 11.2, “Numeric Types”.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED
attribute to the column.
SERIAL is an alias for BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL DEFAULT VALUE in the definition of an
integer column is an alias for NOT NULL AUTO_INCREMENT
UNIQUE.
Warning: You should be aware
that when you use subtraction between integer values where one
is of type UNSIGNED, the result is unsigned.
See Section 12.8, “Cast Functions and Operators”.
BIT
In versions of MySQL up to and lincluding 4.1,
BIT is a synonym for
TINYINT(1).
TINYINT[(M)] [UNSIGNED]
[ZEROFILL]
A very small integer. The signed range is
-128 to 127. The
unsigned range is 0 to
255.
BOOL, BOOLEAN
These are synonyms for TINYINT(1). The
BOOLEAN synonym was added in MySQL 4.1.0.
A value of zero is considered false. Non-zero values are
considered true.
SMALLINT[(M)] [UNSIGNED]
[ZEROFILL]
A small integer. The signed range is
-32768 to 32767. The
unsigned range is 0 to
65535.
MEDIUMINT[(M)]
[UNSIGNED] [ZEROFILL]
A medium-size integer. The signed range is
-8388608 to 8388607.
The unsigned range is 0 to
16777215.
INT[(M)] [UNSIGNED]
[ZEROFILL]
A normal-size integer. The signed range is
-2147483648 to
2147483647. The unsigned range is
0 to 4294967295.
INTEGER[(M)] [UNSIGNED]
[ZEROFILL]
This is a synonym for INT.
BIGINT[(M)] [UNSIGNED]
[ZEROFILL]
A large integer. The signed range is
-9223372036854775808 to
9223372036854775807. The unsigned range
is 0 to
18446744073709551615.
Some things you should be aware of with respect to
BIGINT columns:
All arithmetic is done using signed
BIGINT or DOUBLE
values, so you should not use unsigned big integers
larger than 9223372036854775807 (63
bits) except with bit functions! If you do that, some of
the last digits in the result may be wrong because of
rounding errors when converting a
BIGINT value to a
DOUBLE.
MySQL 4.0 can handle BIGINT in the
following cases:
When using integers to store large unsigned values
in a BIGINT column.
In
MIN(col_name)
or
MAX(col_name),
where col_name refers to
a BIGINT column.
When using operators (+,
-, *, and so
on) where both operands are integers.
You can always store an exact integer value in a
BIGINT column by storing it using a
string. In this case, MySQL performs a string-to-number
conversion that involves no intermediate
double-precision representation.
The -, +, and
* operators use
BIGINT arithmetic when both operands
are integer values. This means that if you multiply two
big integers (or results from functions that return
integers), you may get unexpected results when the
result is larger than
9223372036854775807.
FLOAT[(M,D)]
[UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Allowable
values are -3.402823466E+38 to
-1.175494351E-38, 0,
and 1.175494351E-38 to
3.402823466E+38. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M is the total number of decimal
digits and D is the number of
digits following the decimal point. If
M and
D are omitted, values are stored
to the limits allowed by the hardware. A single-precision
floating-point number is accurate to approximately 7 decimal
places.
If UNSIGNED is specified, negative values
are disallowed.
A normal-size (double-precision) floating-point number.
Allowable values are
-1.7976931348623157E+308 to
-2.2250738585072014E-308,
0, and
2.2250738585072014E-308 to
1.7976931348623157E+308. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M is the total number of decimal
digits and D is the number of
digits following the decimal point. If
M and
D are omitted, values are stored
to the limits allowed by the hardware. A double-precision
floating-point number is accurate to approximately 15
decimal places.
If UNSIGNED is specified, negative values
are disallowed.
These are synonyms for DOUBLE. Exception:
If the server SQL mode includes the
REAL_AS_FLOAT option,
REAL is a synonym for
FLOAT rather than
DOUBLE.
FLOAT(p) [UNSIGNED]
[ZEROFILL]
A floating-point number. p
represents the precision in bits, but MySQL uses this value
only to determine whether to give the resulting column a
data type of FLOAT or
DOUBLE. If p
is from 0 to 24, the data type becomes
FLOAT with no
M or D
values. If p is from 25 to 53,
the data type becomes DOUBLE with no
M or D
values. The range of the resulting column is the same as for
the single-precision FLOAT or
double-precision DOUBLE data types
described earlier in this section.
As of MySQL 3.23, this data type holds true floating-point
values. In earlier MySQL versions,
FLOAT(p)
always has two decimals.
FLOAT(p)
syntax is provided for ODBC compatibility.
DECIMAL[(M[,D])]
[UNSIGNED] [ZEROFILL]
An unpacked fixed-point number. Behaves like a
CHAR column; “unpacked”
means the number is stored as a string, using one character
for each digit of the value. M is
the total number of digits and D
is the number of digits after the decimal point. The decimal
point and (for negative numbers) the
‘-’ sign are not counted in
M, although space for them is
reserved. If D is 0, values have
no decimal point or fractional part. The maximum range of
DECIMAL values is the same as for
DOUBLE, but the actual range for a given
DECIMAL column may be constrained by the
choice of M and
D. If
D is omitted, the default is 0.
If M is omitted, the default is
10.
If UNSIGNED is specified, negative values
are disallowed.
Note: Before MySQL 3.23,
the value of M must be large
enough to include the space needed for the sign and the
decimal point characters.
A date. The supported range is
'1000-01-01' to
'9999-12-31'. MySQL displays
DATE values in
'YYYY-MM-DD' format, but allows you to
assign values to DATE columns using
either strings or numbers.
DATETIME
A date and time combination. The supported range is
'1000-01-01 00:00:00' to
'9999-12-31 23:59:59'. MySQL displays
DATETIME values in 'YYYY-MM-DD
HH:MM:SS' format, but allows you to assign values
to DATETIME columns using either strings
or numbers.
TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01
00:00:00' to partway through the year
2037.
A TIMESTAMP column is useful for
recording the date and time of an INSERT
or UPDATE operation. The first
TIMESTAMP column in a table is
automatically set to the date and time of the most recent
operation if you do not assign it a value yourself. You can
also set any TIMESTAMP column to the
current date and time by assigning it a
NULL value.
In MySQL 4.1, TIMESTAMP is returned as a
string with the format 'YYYY-MM-DD
HH:MM:SS'. Display widths (used as described in
the following paragraphs) are no longer supported; the
display width is fixed at 19 characters. If you want to
obtain the value as a number, you should add
+0 to the timestamp column.
In MySQL 4.0 and earlier, TIMESTAMP
values are displayed in YYYYMMDDHHMMSS,
YYMMDDHHMMSS,
YYYYMMDD, or YYMMDD
format, depending on whether M is
14 (or missing), 12, 8, or 6, but allows you to assign
values to TIMESTAMP columns using either
strings or numbers. The M
argument affects only how a TIMESTAMP
column is displayed, not storage. Its values always are
stored using four bytes each. From MySQL 4.0.12, the
--new option can be used to make the server
behave as in MySQL 4.1.
Note that
TIMESTAMP(M)
columns where M is 8 or 14 are
reported to be numbers, whereas other
TIMESTAMP(M)
columns are reported to be strings. This is just to ensure
that you can reliably dump and restore the table with these
types.
A time. The range is '-838:59:59' to
'838:59:59'. MySQL displays
TIME values in
'HH:MM:SS' format, but allows you to
assign values to TIME columns using
either strings or numbers.
YEAR[(2|4)]
A year in two-digit or four-digit format. The default is
four-digit format. In four-digit format, the allowable
values are 1901 to
2155, and 0000. In
two-digit format, the allowable values are
70 to 69, representing
years from 1970 to 2069. MySQL displays
YEAR values in YYYY
format, but allows you to assign values to
YEAR columns using either strings or
numbers. The YEAR type is unavailable
prior to MySQL 3.22.
As of MySQL 4.1, several changes affect string data types:
Column definitions for many string data types can include a
CHARACTER SET attribute to specify the
character set and, optionally, a collation.
(CHARSET is a synonym for
CHARACTER SET.) These attributes apply to
CHAR, VARCHAR, the
TEXT types, ENUM, and
SET. For example:
CREATE TABLE t
(
c1 CHAR(20) CHARACTER SET utf8,
c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin
);
This table definition creates a column named
c1 that has a character set of
utf8 with the default collation for that
character set, and a column named c2 that
has a character set of latin1 and the
binary collation for the character set. The binary collation
is not case sensitive.
Note: In MySQL 4.1, MySQL
interprets length specifications in character column
definitions in character units. Earlier versions interpret
lengths in byte units.
For CHAR, VARCHAR, and
the TEXT types, the
BINARY attribute causes the column to be
assigned the binary collation of the column character set.
For earlier versions, BINARY causes
CHAR and VARCHAR to be
treated as binary strings and is disallowed for the
TEXT types.
Character column sorting and comparison are based on the
character set assigned to the column. For earlier versions,
sorting and comparison are based on the collation of the
server character set. For CHAR and
VARCHAR columns, you can declare the
column with the BINARY attribute to cause
sorting and comparison to use the underlying character code
values rather then a lexical ordering.
A fixed-length string that is always right-padded with
spaces to the specified length when stored.
M represents the column length.
The range of M is 0 to 255
characters (1 to 255 prior to MySQL 3.23).
Note: Trailing spaces are
removed when CHAR values are retrieved.
In MySQL 4.1, a CHAR column with a length
specification greater than 255 is converted to the smallest
TEXT type that can hold values of the
given length. For example, CHAR(500) is
converted to TEXT, and
CHAR(200000) is converted to
MEDIUMTEXT. This is a compatibility
feature. However, this conversion causes the column to
become a variable-length column, and also affects
trailing-space removal.
CHAR is shorthand for
CHARACTER. NATIONAL
CHAR (or its equivalent short form,
NCHAR) is the standard SQL way to define
that a CHAR column should use the default
character set. This is the default in MySQL.
As of MySQL 4.1.2, the BINARY attribute
is shorthand for specifying the binary collation of the
column character set. Sorting and comparison is based on
numeric character values. Before 4.1.2,
BINARY attribute causes the column to be
treated as a binary string. Sorting and comparison is based
on numeric byte values.
From MySQL 4.1.0 on, the data type CHAR
BYTE is an alias for CHAR
BINARY. This is a compatibility feature.
From MySQL 4.1.0 on, the ASCII attribute
can be specified for CHAR. It assigns the
latin1 character set.
From MySQL 4.1.1 on, the UNICODE
attribute can be specified for CHAR. It
assigns the ucs2 character set.
MySQL allows you to create a column of type
CHAR(0). This is mainly useful when you
have to be compliant with old applications that depend on
the existence of a column but that do not actually use the
value. This can also be useful when you need a column that
can take only two values: A CHAR(0)
column that is not defined as NOT NULL
occupies only one bit and can take only the values
NULL and '' (the empty
string).
CHAR
This is a synonym for CHAR(1).
[NATIONAL] VARCHAR(M)
[BINARY]
A variable-length string. M
represents the maximum column length. The range of
M is 1 to 255 before MySQL 4.0.2,
and 0 to 255 as of MySQL 4.0.2.
Note: Trailing spaces are
removed when VARCHAR values are stored.
This differs from the standard SQL specification.
In MySQL 4.1, a VARCHAR column with a
length specification greater than 255 is converted to the
smallest TEXT type that can hold values
of the given length. For example,
VARCHAR(500) is converted to
TEXT, and
VARCHAR(200000) is converted to
MEDIUMTEXT. This is a compatibility
feature. However, this conversion affects trailing-space
removal.
VARCHAR is shorthand for
CHARACTER VARYING.
As of MySQL 4.1.2, the BINARY attribute
is shorthand for specifying the binary collation of the
column character set. Sorting and comparison is based on
numeric character values. Before 4.1.2,
BINARY attribute causes the column to be
treated as a binary string. Sorting and comparison is based
on numeric byte values.
BINARY(M)
The BINARY type is similar to the
CHAR type, but stores binary byte strings
rather than non-binary character strings.
This type was added in MySQL 4.1.2.
VARBINARY(M)
The VARBINARY type is similar to the
VARCHAR type, but stores binary byte
strings rather than non-binary character strings.
This type was added in MySQL 4.1.2.
TINYBLOB
A BLOB column with a maximum length of
255 (28 – 1) bytes.
TINYTEXT
A TEXT column with a maximum length of
255 (28 – 1) characters.
BLOB[(M)]
A BLOB column with a maximum length of
65,535 (216 – 1) bytes.
Beginning with MySQL 4.1, an optional length
M can be given. MySQL will create
the column as the smallest BLOB type
largest enough to hold values M
bytes long.
TEXT[(M)]
A TEXT column with a maximum length of
65,535 (216 – 1)
characters.
Beginning with MySQL 4.1, an optional length
M can be given. MySQL will create
the column as the smallest TEXT type
largest enough to hold values M
characters long.
MEDIUMBLOB
A BLOB column with a maximum length of
16,777,215 (224 – 1) bytes.
MEDIUMTEXT
A TEXT column with a maximum length of
16,777,215 (224 – 1)
characters.
LONGBLOB
A BLOB column with a maximum length of
4,294,967,295 or 4GB (232 –
1) bytes. Up to MySQL 3.23, the client/server protocol and
MyISAM tables had a limit of 16MB per
communication packet or table row. From MySQL 4.0, the
maximum allowed length of LONGBLOB
columns depends on the configured maximum packet size in the
client/server protocol and available memory.
LONGTEXT
A TEXT column with a maximum length of
4,294,967,295 or 4GB (232 –
1) characters. Up to MySQL 3.23, the client/server protocol
and MyISAM tables had a limit of 16MB per
communication packet or table row. From MySQL 4.0, the
maximum allowed length of LONGTEXT
columns depends on the configured maximum packet size in the
client/server protocol and available memory.
ENUM('value1','value2',...)
An enumeration. A string object that can have only one
value, chosen from the list of values
'value1',
'value2',
..., NULL or the
special '' error value. An
ENUM column can have a maximum of 65,535
distinct values. ENUM values are
represented internally as integers.
SET('value1','value2',...)
A set. A string object that can have zero or more values,
each of which must be chosen from the list of values
'value1',
'value2',
... A SET column can
have a maximum of 64 members. SET values
are represented internally as integers.
11.2. Numeric Types
MySQL supports all of the standard SQL numeric data types. These
types include the exact numeric data types
(INTEGER, SMALLINT,
DECIMAL, and NUMERIC), as
well as the approximate numeric data types
(FLOAT, REAL, and
DOUBLE PRECISION). The keyword
INT is a synonym for
INTEGER, and the keyword DEC
is a synonym for DECIMAL.
As an extension to the SQL standard, MySQL also supports the
integer types TINYINT,
MEDIUMINT, and BIGINT. The
following table shows the required storage and range for each of
the integer types.
Type
Bytes
Minimum Value
Maximum Value
(Signed/Unsigned)
(Signed/Unsigned)
TINYINT
1
-128
127
0
255
SMALLINT
2
-32768
32767
0
65535
MEDIUMINT
3
-8388608
8388607
0
16777215
INT
4
-2147483648
2147483647
0
4294967295
BIGINT
8
-9223372036854775808
9223372036854775807
0
18446744073709551615
Another extension is supported by MySQL for optionally specifying
the display width of an integer value in parentheses following the
base keyword for the type (for example,
INT(4)). This optional display width
specification is used to left-pad the display of values having a
width less than the width specified for the column.
The display width does not constrain the range of values that can
be stored in the column, nor the number of digits that are
displayed for values having a width exceeding that specified for
the column.
When used in conjunction with the optional extension attribute
ZEROFILL, the default padding of spaces is
replaced with zeros. For example, for a column declared as
INT(5) ZEROFILL, a value of
4 is retrieved as 00004.
Note that if you store larger values than the display width in an
integer column, you may experience problems when MySQL generates
temporary tables for some complicated joins, because in these
cases MySQL trusts that the data did fit into the original column
width.
All integer types can have an optional (non-standard) attribute
UNSIGNED. Unsigned values can be used when you
want to allow only non-negative numbers in a column and you need a
bigger upper numeric range for the column.
As of MySQL 4.0.2, floating-point and fixed-point types also can
be UNSIGNED. As with integer types, this
attribute prevents negative values from being stored in the
column. However, unlike the integer types, the upper range of
column values remains the same.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED attribute
to the column.
For floating-point data types, MySQL uses four bytes for
single-precision values and eight bytes for double-precision
values.
The FLOAT type is used to represent approximate
numeric data types. The SQL standard allows an optional
specification of the precision (but not the range of the exponent)
in bits following the keyword FLOAT in
parentheses. The MySQL implementation also supports this optional
precision specification, but the precision value is used only to
determine storage size. A precision from 0 to 23 results in a
four-byte single-precision FLOAT column. A
precision from 24 to 53 results in an eight-byte double-precision
DOUBLE column.
MySQL allows a non-standard syntax:
FLOAT(M,D)
or
REAL(M,D)
or DOUBLE
PRECISION(M,D).
Here,
“(M,D)”
means than values are displayed with up to
M digits in total, of which
D digits may be after the decimal
point. For example, a column defined as
FLOAT(7,4) will look like
-999.9999 when displayed. MySQL performs
rounding when storing values, so if you insert
999.00009 into a FLOAT(7,4)
column, the approximate result is 999.0001.
MySQL treats DOUBLE as a synonym for
DOUBLE PRECISION (a non-standard extension).
MySQL also treats REAL as a synonym for
DOUBLE PRECISION (a non-standard variation),
unless the server SQL mode includes the
REAL_AS_FLOAT option.
For maximum portability, code requiring storage of approximate
numeric data values should use FLOAT or
DOUBLE PRECISION with no specification of
precision or number of digits.
The DECIMAL and NUMERIC
types are implemented as the same type by MySQL. They are used to
store values for which it is important to preserve exact
precision, for example with monetary data. When declaring a column
of one of these types, the precision and scale can be (and usually
is) specified; for example:
salary DECIMAL(5,2)
In this example, 5 is the precision and
2 is the scale. The precision represents the
number of significant digits that are stored for values, and the
scale represents the number of digits that can be stored following
the decimal point.
Through version 4.1, MySQL stores DECIMAL and
NUMERIC values as strings, rather than in
binary. One character is used for each digit of the value, the
decimal point (if the scale is greater than 0), and the
‘-’ sign (for negative numbers). If
the scale is 0, DECIMAL and
NUMERIC values contain no decimal point or
fractional part.
Standard SQL requires that the salary column be
able to store any value with five digits and two decimals. In this
case, therefore, the range of values that can be stored in the
salary column is from
-999.99 to 999.99. In
versions up to and including 4.1, MySQL varies from this limit in
two ways due to the use of string format for value storage:
On the positive end of the range, the column actually can
store numbers up to 9999.99. For positive
numbers, MySQL uses the byte reserved for the sign to extend
the upper end of the range.
DECIMAL columns in MySQL before 3.23 are
stored differently and cannot represent all the values
required by standard SQL. This is because for a type of
DECIMAL(M,D),
the value of M includes the bytes
for the sign and the decimal point. The range of the
salary column before MySQL 3.23 would be
-9.99 to 99.99.
In standard SQL, the syntax
DECIMAL(M) is
equivalent to
DECIMAL(M,0).
Similarly, the syntax DECIMAL is equivalent to
DECIMAL(M,0), where
the implementation is allowed to decide the value of
M. As of MySQL 3.23.6, both of these
variant forms of the DECIMAL and
NUMERIC data types are supported. The default
value of M is 10. Before 3.23.6,
M and D both
must be specified explicitly.
The maximum range of DECIMAL and
NUMERIC values is the same as for
DOUBLE, but the actual range for a given
DECIMAL or NUMERIC column
can be constrained by the precision or scale for a given column.
When such a column is assigned a value with more digits following
the decimal point than are allowed by the specified scale, the
value is converted to that scale. (The precise behavior is
operating system-specific, but generally the effect is truncation
to the allowable number of digits.)
When asked to store a value in a numeric column that is outside
the data type's allowable range, MySQL clips the value to the
appropriate endpoint of the range and stores the resulting value
instead.
For example, the range of an INT column is
-2147483648 to 2147483647.
If you try to insert -9999999999 into an
INT column, MySQL clips the value to the lower
endpoint of the range and stores -2147483648
instead. Similarly, if you try to insert
9999999999, MySQL clips the value to the upper
endpoint of the range and stores 2147483647
instead.
If the INT column is
UNSIGNED, the size of the column's range is the
same but its endpoints shift up to 0 and
4294967295. If you try to store
-9999999999 and 9999999999,
the values stored in the column are 0 and
4294967296.
When a floating-point or fixed-point column is assigned a value
that exceeds the range implied by the specified (or default)
precision and scale, MySQL stores the value representing the
corresponding end point of that range.
Conversions that occur due to clipping are reported as
“warnings” for ALTER TABLE,
LOAD DATA INFILE, UPDATE,
and multiple-row INSERT statements.
The date and time types for representing temporal values are
DATETIME, DATE,
TIMESTAMP, TIME, and
YEAR. Each temporal type has a range of legal
values, as well as a “zero” value that is used when
you specify an illegal value that MySQL cannot represent. The
TIMESTAMP type has special automatic updating
behavior, described later on.
MySQL version through 4.1 accept certain “illegal”
values for dates, such as '1999-11-31'. This is
useful when you want to store a possibly incorrect value specified
by a user (for example, in a web form) in the database for future
processing. MySQL verifies only that the month is in the range
from 0 to 12 and that the day is in the range from 0 to 31. These
ranges are defined to include zero because MySQL allows you to
store dates where the day or month and day are zero in a
DATE or DATETIME column.
This is extremely useful for applications that need to store a
birthdate for which you do not know the exact date. In this case,
you simply store the date as '1999-00-00' or
'1999-01-00'. If you store dates such as these,
you should not expect to get correct results for functions such as
DATE_SUB() or DATE_ADD that
require complete dates.
MySQL also allows you to store '0000-00-00' as
a “dummy date”. This is in some cases more
convenient, and uses less data and index space, than storing
NULL values.
Here are some general considerations to keep in mind when working
with date and time types:
MySQL retrieves values for a given date or time type in a
standard output format, but it attempts to interpret a variety
of formats for input values that you supply (for example, when
you specify a value to be assigned to or compared to a date or
time type). Only the formats described in the following
sections are supported. It is expected that you supply legal
values, and unpredictable results may occur if you use values
in other formats.
Dates containing two-digit year values are ambiguous because
the century is unknown. MySQL interprets two-digit year values
using the following rules:
Year values in the range 00-69 are
converted to 2000-2069.
Year values in the range 70-99 are
converted to 1970-1999.
Although MySQL tries to interpret values in several formats,
dates always must be given in year-month-day order (for
example, '98-09-04'), rather than in the
month-day-year or day-month-year orders commonly used
elsewhere (for example, '09-04-98',
'04-09-98').
MySQL automatically converts a date or time type value to a
number if the value is used in a numeric context and vice
versa.
When MySQL encounters a value for a date or time type that is
out of range or otherwise illegal for the type (as described
at the beginning of this section), it converts the value to
the “zero” value for that type. The exception is
that out-of-range TIME values are clipped
to the appropriate endpoint of the TIME
range.
Data Type
“Zero” Value
DATETIME
'0000-00-00 00:00:00'
DATE
'0000-00-00'
TIMESTAMP
00000000000000
TIME
'00:00:00'
YEAR
0000
The “zero” values are special, but you can store
or refer to them explicitly using the values shown in the
table. You can also do this using the values
'0' or 0, which are
easier to write.
“Zero” date or time values used through MyODBC
are converted automatically to NULL in
MyODBC 2.50.12 and above, because ODBC cannot handle such
values.
The DATETIME, DATE, and
TIMESTAMP types are related. This section
describes their characteristics, how they are similar, and how
they differ.
The DATETIME type is used when you need
values that contain both date and time information. MySQL
retrieves and displays DATETIME values in
'YYYY-MM-DD HH:MM:SS' format. The supported
range is '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'.
(“Supported” means that although earlier values
might work, there is no guarantee)
The DATE type is used when you need only a
date value, without a time part. MySQL retrieves and displays
DATE values in
'YYYY-MM-DD' format. The supported range is
'1000-01-01' to
'9999-12-31'.
The TIMESTAMP data type has varying
properties, depending on the MySQL version. These properties are
described later in this section.
You can specify DATETIME,
DATE, and TIMESTAMP values
using any of a common set of formats:
As a string in either 'YYYY-MM-DD
HH:MM:SS' or 'YY-MM-DD
HH:MM:SS' format. A “relaxed” syntax
is allowed: Any punctuation character may be used as the
delimiter between date parts or time parts. For example,
'98-12-31 11:30:45', '98.12.31
11+30+45', '98/12/31 11*30*45',
and '98@12@31 11^30^45' are equivalent.
As a string in either 'YYYY-MM-DD' or
'YY-MM-DD' format. A
“relaxed” syntax is allowed here, too. For
example, '98-12-31',
'98.12.31',
'98/12/31', and
'98@12@31' are equivalent.
As a string with no delimiters in either
'YYYYMMDDHHMMSS' or
'YYMMDDHHMMSS' format, provided that the
string makes sense as a date. For example,
'19970523091528' and
'970523091528' are interpreted as
'1997-05-23 09:15:28', but
'971122129015' is illegal (it has a
nonsensical minute part) and becomes '0000-00-00
00:00:00'.
As a string with no delimiters in either
'YYYYMMDD' or 'YYMMDD'
format, provided that the string makes sense as a date. For
example, '19970523' and
'970523' are interpreted as
'1997-05-23', but
'971332' is illegal (it has nonsensical
month and day parts) and becomes
'0000-00-00'.
As a number in either YYYYMMDDHHMMSS or
YYMMDDHHMMSS format, provided that the
number makes sense as a date. For example,
19830905132800 and
830905132800 are interpreted as
'1983-09-05 13:28:00'.
As a number in either YYYYMMDD or
YYMMDD format, provided that the number
makes sense as a date. For example,
19830905 and 830905
are interpreted as '1983-09-05'.
As the result of a function that returns a value that is
acceptable in a DATETIME,
DATE, or TIMESTAMP
context, such as NOW() or
CURRENT_DATE.
Illegal DATETIME, DATE, or
TIMESTAMP values are converted to the
“zero” value of the appropriate type
('0000-00-00 00:00:00',
'0000-00-00', or
00000000000000).
For values specified as strings that include date part
delimiters, it is not necessary to specify two digits for month
or day values that are less than 10.
'1979-6-9' is the same as
'1979-06-09'. Similarly, for values specified
as strings that include time part delimiters, it is not
necessary to specify two digits for hour, minute, or second
values that are less than 10.
'1979-10-30 1:2:3' is the same as
'1979-10-30 01:02:03'.
Values specified as numbers should be 6, 8, 12, or 14 digits
long. If a number is 8 or 14 digits long, it is assumed to be in
YYYYMMDD or YYYYMMDDHHMMSS
format and that the year is given by the first 4 digits. If the
number is 6 or 12 digits long, it is assumed to be in
YYMMDD or YYMMDDHHMMSS
format and that the year is given by the first 2 digits. Numbers
that are not one of these lengths are interpreted as though
padded with leading zeros to the closest length.
Values specified as non-delimited strings are interpreted using
their length as given. If the string is 8 or 14 characters long,
the year is assumed to be given by the first 4 characters.
Otherwise, the year is assumed to be given by the first 2
characters. The string is interpreted from left to right to find
year, month, day, hour, minute, and second values, for as many
parts as are present in the string. This means you should not
use strings that have fewer than 6 characters. For example, if
you specify '9903', thinking that represents
March, 1999, MySQL inserts a “zero” date into your
table. This is because the year and month values are
99 and 03, but the day
part is completely missing, so the value is not a legal date.
However, as of MySQL 3.23, you can explicitly specify a value of
zero to represent missing month or day parts. For example, you
can use '990300' to insert the value
'1999-03-00'.
You can to some extent assign values of one date type to an
object of a different date type. However, there may be some
alteration of the value or loss of information:
If you assign a DATE value to a
DATETIME or TIMESTAMP
object, the time part of the resulting value is set to
'00:00:00' because the
DATE value contains no time information.
If you assign a DATETIME or
TIMESTAMP value to a
DATE object, the time part of the
resulting value is deleted because the
DATE type stores no time information.
Remember that although DATETIME,
DATE, and TIMESTAMP
values all can be specified using the same set of formats,
the types do not all have the same range of values. For
example, TIMESTAMP values cannot be
earlier than 1970 or later than
2037. This means that a date such as
'1968-01-01', while legal as a
DATETIME or DATE
value, is not a valid TIMESTAMP value and
is converted to 0 if assigned to such an
object.
Be aware of certain pitfalls when specifying date values:
The relaxed format allowed for values specified as strings
can be deceiving. For example, a value such as
'10:11:12' might look like a time value
because of the ‘:’ delimiter,
but if used in a date context is interpreted as the year
'2010-11-12'. The value
'10:45:15' is converted to
'0000-00-00' because
'45' is not a legal month.
The MySQL server performs only basic checking on the
validity of a date: The ranges for year, month, and day are
1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date
containing parts not within these ranges is subject to
conversion to '0000-00-00'. Please note
that this still allows you to store invalid dates such as
'2002-04-31'. To ensure that a date is
valid, perform a check in your application.
Dates containing two-digit year values are ambiguous because
the century is unknown. MySQL interprets two-digit year
values using the following rules:
Year values in the range 00-69 are
converted to 2000-2069.
Year values in the range 70-99 are
converted to 1970-1999.
11.3.1.1. TIMESTAMP Properties Prior to MySQL 4.1
The TIMESTAMP data type provides a type
that you can use to automatically mark
INSERT or UPDATE
operations with the current date and time. If you have
multiple TIMESTAMP columns in a table, only
the first one is updated automatically. (From MySQL 4.1.2 on,
you can specify which TIMESTAMP column
updates; see Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.)
Automatic updating of the first TIMESTAMP
column in a table occurs under any of the following
conditions:
You explicitly set the column to NULL.
The column is not specified explicitly in an
INSERT or LOAD DATA
INFILE statement.
The column is not specified explicitly in an
UPDATE statement and some other column
changes value. An UPDATE that sets a
column to the value it does not cause the
TIMESTAMP column to be updated; if you
set a column to its current value, MySQL ignores the
update for efficiency.
A TIMESTAMP column other than the first
also can be assigned the current date and time by setting it
to NULL or to any function that produces
the current date and time (NOW(),
CURRENT_TIMESTAMP).
You can set any TIMESTAMP column to a value
different from the current date and time by setting it
explicitly to the desired value. This is true even for the
first TIMESTAMP column. You can use this
property if, for example, you want a
TIMESTAMP to be set to the current date and
time when you create a row, but not to be changed whenever the
row is updated later:
Let MySQL set the column when the row is created. This
initializes it to the current date and time.
When you perform subsequent updates to other columns in
the row, set the TIMESTAMP column
explicitly to its current value:
Another way to maintain a column that records row-creation
time is to use a DATETIME column that you
initialize to NOW() when the row is created
and do not modify for subsequent updates.
TIMESTAMP values may range from the
beginning of 1970 to partway through the year 2037, with a
resolution of one second. Values are displayed as numbers.
When you store a value in a TIMESTAMP
column, it is assumed to be represented in the current time
zone, and is converted to UTC for storage. When you retrieve
the value, it is converted from UTC back to the local time
zone for display. Before MySQL 4.1.3, the server has a single
time zone. As of 4.1.3, clients can set their own time zones
on a per-connection basis, as described in
Section 5.9.8, “MySQL Server Time Zone Support”.
Prior to version 4.1, the format in which MySQL retrieves and
displays TIMESTAMP values depends on the
display size, as illustrated in the following table. The
“full” TIMESTAMP format is 14
digits, but TIMESTAMP columns may be
created with shorter display sizes:
Data Type
Display Format
TIMESTAMP(14)
YYYYMMDDHHMMSS
TIMESTAMP(12)
YYMMDDHHMMSS
TIMESTAMP(10)
YYMMDDHHMM
TIMESTAMP(8)
YYYYMMDD
TIMESTAMP(6)
YYMMDD
TIMESTAMP(4)
YYMM
TIMESTAMP(2)
YY
All TIMESTAMP columns have the same storage
size, regardless of display size. The most common display
sizes are 6, 8, 12, and 14. You can specify an arbitrary
display size at table creation time, but values of 0 or
greater than 14 are coerced to 14. Odd-valued sizes in the
range from 1 to 13 are coerced to the next higher even number.
TIMESTAMP columns store legal values using
the full precision with which the value was specified,
regardless of the display size. This has several implications:
Always specify year, month, and day, even if your column
types are TIMESTAMP(4) or
TIMESTAMP(2). Otherwise, the value is
not a legal date and 0 is stored.
If you use ALTER TABLE to widen a
narrow TIMESTAMP column, information is
displayed that previously was “hidden.”
Similarly, narrowing a TIMESTAMP column
does not cause information to be lost, except in the sense
that less information is shown when the values are
displayed.
If you are planning to use mysqldump
for the database, do not use
TIMESTAMP(4) or
TIMESTAMP(2). The display format for
these data types are not legal dates and
0 will be stored instead. This
inconsistency is fixed starting with MySQL 4.1, where
display width is ignored. To prepare for transition to
versions after 4.0, you should change to use display
widths of 6 or more, which will produce a legal display
format. You can change the display width of
TIMESTAMP data types, without losing
any information, by using ALTER TABLE
as indicated above.
If you need to print the timestamps for external
applications, you can use MID() to
extract the relevant part of the timestamp: for example,
to imitate the TIMESTAMP(4) display
format.
Although TIMESTAMP values are stored to
full precision, the only function that operates directly
on the underlying stored value is
UNIX_TIMESTAMP(). Other functions
operate on the formatted retrieved value. This means you
cannot use a function such as HOUR() or
SECOND() unless the relevant part of
the TIMESTAMP value is included in the
formatted value. For example, the HH
part of a TIMESTAMP column is not
displayed unless the display size is at least 10, so
trying to use HOUR() on shorter
TIMESTAMP values produces a meaningless
result.
In MySQL 4.1, TIMESTAMP display format
changes to be the same as DATETIME, that
is, as a string in 'YYYY-MM-DD HH:MM:SS'
format rather than as a number in
YYYYMMDDHHMMSS format. To test applications
written for MySQL 4.0 for compatibility with this change, you
can set the new system variable to 1. This
variable is available beginning with MySQL 4.0.12. It can be
set at server startup by specifying the --new
option to mysqld. At runtime, a user who
has the SUPER privilege can set the global
value with a SET statement:
mysql> SET GLOBAL new = 1;
Any client can set its session value of new
as follows:
mysql> SET new = 1;
The general effect of setting new to 1 is
that values for a TIMESTAMP column display
as strings rather than as numbers. Also,
DESCRIBE displays the column definition as
timestamp(19), rather than as
timestamp(14).
However, the effect differs somewhat for
TIMESTAMP columns that are created while
new is set to 1. In this case, column
values display as strings and DESCRIBE
shows the definition as timestamp(19),
regardless of the current value of new.
In other words, with new=1, all
TIMESTAMP values display as strings and
DESCRIBE shows a display width of 19. For
columns created while new=1, they continue
to display as strings and to have a display width of 19 even
if new is set to 0.
For a TIMESTAMP column that displays as a
string, you can display it as a number by retrieving it as
col_name+0.
11.3.1.2. TIMESTAMP Properties as of MySQL 4.1
In MySQL 4.1 and up, the properties of the
TIMESTAMP data type change in the ways
described in this section.
From MySQL 4.1.0 on, TIMESTAMP display
format differs from that of earlier MySQL releases:
TIMESTAMP columns are displayed in the
same format as DATETIME columns. In
other words, the display width is fixed at 19 characters,
and the format is YYYY-MM-DD HH:MM:SS.
Display widths (used as described in the preceding
section) are no longer supported. In other words, for
declarations such as TIMESTAMP(2),
TIMESTAMP(4), and so on, the display
width is ignored.
Beginning with MySQL 4.1.1, the MySQL server can be run in
MAXDB mode. When the server runs in this
mode, TIMESTAMP is identical with
DATETIME. That is, if the server is running
in MAXDB mode at the time that a table is
created, TIMESTAMP columns are created as
DATETIME columns. As a result, such columns
use DATETIME display format, have the same
range of values, and there is no automatic initialization or
updating to the current date and time.
To enable MAXDB mode, set the server SQL
mode to MAXDB at startup using the
--sql-mode=MAXDB server option or by setting
the global sql_mode variable at runtime:
mysql> SET GLOBAL sql_mode=MAXDB;
A client can cause the server to run in
MAXDB mode for its own connection as
follows:
mysql> SET SESSION sql_mode=MAXDB;
Beginning with MySQL 4.1.2, you have more flexible control
over when automatic TIMESTAMP
initialization and updating occur and which column should have
those behaviors:
You can assign the current timestamp as the default value
and the auto-update value, as before. But it is possible
to have just one automatic behavior or the other, or
neither of them. (It is not possible to have one behavior
for one column and the other for another column.)
You can specify which TIMESTAMP column
to automatically initialize or update to the current date
and time. This no longer need be the first
TIMESTAMP column.
The following discussion describes the revised syntax and
behavior. Note that this information applies only to
TIMESTAMP columns for tables not created
with MAXDB mode enabled. As noted earlier
in this section, MAXDB mode causes columns
to be created as DATETIME columns.
The following items summarize the pre-4.1.2 properties for
TIMESTAMP initialization and updating:
The first TIMESTAMP column in table row
automatically is set to the current timestamp when the record
is created if the column is set to NULL or
is not specified at all.
The first TIMESTAMP column in table row
automatically is updated to the current timestamp when the
value of any other column in the row is changed, unless the
TIMESTAMP column explicitly is assigned a
value other than NULL.
If a DEFAULT value is specified for the
first TIMESTAMP column when the table is
created, it is silently ignored.
Other TIMESTAMP columns in the table can be
set to the current TIMESTAMP by assigning
NULL to them, but they do not update
automatically.
As of 4.1.2, you have more flexibility in deciding which
TIMESTAMP column automatically is
initialized and updated to the current timestamp. The rules
are as follows:
If a DEFAULT value is specified for the
first TIMESTAMP column in a table, it is
not ignored. The default can be
CURRENT_TIMESTAMP or a constant date and
time value.
DEFAULT NULL is the same as
DEFAULT CURRENT_TIMESTAMP for the
firstTIMESTAMP
column. For any other TIMESTAMP column,
DEFAULT NULL is treated as DEFAULT
0.
Any single TIMESTAMP column in a table can
be set to be the one that is initialized to the current
timestamp and/or updated automatically.
In a CREATE TABLE statement, the first
TIMESTAMP column can be declared in any of
the following ways:
With both DEFAULT CURRENT_TIMESTAMP and
ON UPDATE CURRENT_TIMESTAMP clauses,
the column has the current timestamp for its default
value, and is automatically updated.
With neither DEFAULT nor ON
UPDATE clauses, it is the same as
DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP.
With a DEFAULT CURRENT_TIMESTAMP clause
and no ON UPDATE clause, the column has
the current timestamp for its default value but is not
automatically updated.
With no DEFAULT clause and with an
ON UPDATE CURRENT_TIMESTAMP clause, the
column has a default of 0 and is automatically updated.
With a constant DEFAULT value, the
column has the given default. If the column has an
ON UPDATE CURRENT_TIMESTAMP clause, it
is automatically updated, otherwise not.
In other words, you can use the current timestamp for both the
initial value and the auto-update value, or either one, or
neither. (For example, you can specify ON
UPDATE to get auto-update without also having the
column auto-initialized.)
Any of CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP(), or
NOW() can be used in the
DEFAULT and ON UPDATE
clauses. They all have the same effect.
The order of the two attributes does not matter. If both
DEFAULT and ON UPDATE
are specified for a TIMESTAMP column,
either can precede the other.
Example. These statements are equivalent:
CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
To specify automatic default or updating for a
TIMESTAMP column other than the first one,
you must suppress the automatic initialization and update
behaviors for the first TIMESTAMP column by
explicitly assigning it a constant DEFAULT
value (for example, DEFAULT 0 or
DEFAULT '2003-01-01 00:00:00'). Then for
the other TIMESTAMP column, the rules are
the same as for the first TIMESTAMP column,
except that you cannot omit both of the
DEFAULT and ON UPDATE
clauses. If you do that, no automatic initialization or
updating occurs.
Example. These statements are equivalent:
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);
Beginning with MySQL 4.1.3, you can set the current time zone
on a per-connection basis, as described in
Section 5.9.8, “MySQL Server Time Zone Support”.
TIMESTAMP values still are stored in UTC,
but are converted from the current time zone for storage, and
converted back to the current time zone for retrieval. As long
as the time zone setting remains the same, you get back the
same value you store. If you store a
TIMESTAMP value, then change the time zone
and retrieve the value, it is different than the value you
stored. This occurs because the same time zone is not used for
conversion in both directions. The current time zone is
available as the value of the time_zone
system variable.
Beginning with MySQL 4.1.6, you can include the
NULL attribute in the definition of a
TIMESTAMP column to allow the column to
contain NULL values. For example:
Before MySQL 4.1.6 (and even as of 4.1.6 if the
NULL attribute is not specified), setting
the column to NULL sets it to the current
timestamp. Note that a TIMESTAMP column
which allows NULL values does
not take on the current timestamp unless
either its default value is defined as
CURRENT_TIMESTAMP, or
either NOW() or
CURRENT_TIMESTAMP is inserted into the
column. In other words, a TIMESTAMP column
defined as NULL auto-updates only if it
defined using a definition such as the following:
CREATE TABLE t (ts NULL DEFAULT CURRENT_TIMESTAMP);
Otherwise — that is, if the TIMESTAMP
column is defined using NULL but not using
DEFAULT TIMESTAMP, as shown here:
— then you must explicitly insert a value corresponding
to the current date and time, for example:
INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
11.3.2. The TIME Type
MySQL retrieves and displays TIME values in
'HH:MM:SS' format (or
'HHH:MM:SS' format for large hours values).
TIME values may range from
'-838:59:59' to
'838:59:59'. The reason for which the hours
part may be so large is that the TIME type
may be used not only to represent a time of day (which must be
less than 24 hours), but elapsed time or a time interval between
two events as well. (Note that this interval may be much greater
than 24 hours, or even negative.)
You can specify TIME values in a variety of
formats:
As a string in 'D HH:MM:SS.fraction'
format. You can also use one of the following
“relaxed” syntaxes:
'HH:MM:SS.fraction',
'HH:MM:SS', 'HH:MM',
'D HH:MM:SS', 'D
HH:MM', 'D HH', or
'SS'. Here D
represents days and can have a value from 0 to 34. Note that
MySQL does not yet store the fraction part.
As a string with no delimiters in
'HHMMSS' format, provided that it makes
sense as a time. For example, '101112' is
understood as '10:11:12', but
'109712' is illegal (it has a nonsensical
minute part) and becomes '00:00:00'.
As a number in HHMMSS format, provided
that it makes sense as a time. For example,
101112 is understood as
'10:11:12'. The following alternative
formats are also understood: SS,
MMSS, HHMMSS,
HHMMSS.fraction. Note that MySQL does not
store the fractional part.
As the result of a function that returns a value that is
acceptable in a TIME context, such as
CURRENT_TIME.
For TIME values specified as strings that
include a time part delimiter, it is not necessary to specify
two digits for hours, minutes, or seconds values that are less
than 10. '8:3:2' is the
same as '08:03:02'.
Be careful about assigning “short”
TIME values to a TIME
column. Without colons, MySQL interprets values using the
assumption that the rightmost digits represent seconds. (MySQL
interprets TIME values as elapsed time rather
than as time of day.) For example, you might think of
'1112' and 1112 as meaning
'11:12:00' (12 minutes after 11 o'clock), but
MySQL interprets them as '00:11:12' (11
minutes, 12 seconds). Similarly, '12' and
12 are interpreted as
'00:00:12'. TIME values
with colons, by contrast, are always treated as time of the day.
That is '11:12' means
'11:12:00', not
'00:11:12'.
Values that lie outside the TIME range but
are otherwise legal are clipped to the closest endpoint of the
range. For example, '-850:00:00' and
'850:00:00' are converted to
'-838:59:59' and
'838:59:59'.
Illegal TIME values are converted to
'00:00:00'. Note that because
'00:00:00' is itself a legal
TIME value, there is no way to tell, from a
value of '00:00:00' stored in a table,
whether the original value was specified as
'00:00:00' or whether it was illegal.
11.3.3. The YEAR Type
The YEAR type is a one-byte type used for
representing years.
MySQL retrieves and displays YEAR values in
YYYY format. The range is
1901 to 2155.
You can specify YEAR values in a variety of
formats:
As a four-digit string in the range
'1901' to '2155'.
As a four-digit number in the range 1901
to 2155.
As a two-digit string in the range '00'
to '99'. Values in the ranges
'00' to '69' and
'70' to '99' are
converted to YEAR values in the ranges
2000 to 2069 and
1970 to 1999.
As a two-digit number in the range 1 to
99. Values in the ranges
1 to 69 and
70 to 99 are converted
to YEAR values in the ranges
2001 to 2069 and
1970 to 1999. Note
that the range for two-digit numbers is slightly different
from the range for two-digit strings, because you cannot
specify zero directly as a number and have it be interpreted
as 2000. You must specify it as a string
'0' or '00' or it is
interpreted as 0000.
As the result of a function that returns a value that is
acceptable in a YEAR context, such as
NOW().
Illegal YEAR values are converted to
0000.
11.3.4. Y2K Issues and Date Types
MySQL itself is year 2000 (Y2K) safe (see
Section 1.4.5, “Year 2000 Compliance”), but input values
presented to MySQL may not be. Any input containing two-digit
year values is ambiguous, because the century is unknown. Such
values must be interpreted into four-digit form because MySQL
stores years internally using four digits.
For DATETIME, DATE,
TIMESTAMP, and YEAR types,
MySQL interprets dates with ambiguous year values using the
following rules:
Year values in the range 00-69 are
converted to 2000-2069.
Year values in the range 70-99 are
converted to 1970-1999.
Remember that these rules provide only reasonable guesses as to
what your data values mean. If the heuristics used by MySQL do
not produce the correct values, you should provide unambiguous
input containing four-digit year values.
ORDER BY properly sorts
TIMESTAMP or YEAR values
that have two-digit years.
Some functions like MIN() and
MAX() convert a TIMESTAMP
or YEAR to a number. This means that a value
with a two-digit year does not work properly with these
functions. The fix in this case is to convert the
TIMESTAMP or YEAR to
four-digit year format or use something like
MIN(DATE_ADD(timestamp,INTERVAL 0 DAY)).
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.
11.4.1. The CHAR and VARCHAR Types
The CHAR and VARCHAR types
are similar, but differ in the way they are stored and
retrieved. No lettercase conversion takes place during storage
or retrieval.
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.
(Before MySQL 4.1, the length is interpreted as number of
bytes.)
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. (Before MySQL 3.23, the length
of CHAR may be from 1 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 1 to 255
before MySQL 4.0.2 and 0 to 255 as of MySQL 4.0.2.
