Oracle 5.0, Справочное руководство

Страница: 850 / 3232

String Type Overview
830
Note
The
TIMESTAMP
format that was used prior to MySQL 4.1 is not supported
in MySQL 5.0; see MySQL 3.23, 4.0, 4.1 Reference Manual for information
regarding the old format.
•
TIME
A time. The range is
'-838:59:59'
to
'838:59:59'
. MySQL displays
TIME
values in
'HH:MM:SS'
format, but permits assignment of 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.
YEAR(2)
or
YEAR(4)
differ
in display format, but have the same range of values. In four-digit format, values display as
1901
to
2155
, and
0000
. In two-digit format, values display as
70
to
69
, representing years from 1970 to
2069. MySQL displays
YEAR
values in
YYYY
or
YY
format, but permits assignment of values to
YEAR
columns using either strings or numbers.
For additional information about
YEAR
display format and interpretation of input values, see
Section 11.1.5.3, “The
YEAR
Type” .
The
SUM()
[972] and
AVG()
[969] aggregate functions do not work with temporal values. (They
convert the values to numbers, losing everything after the first nonnumeric character.) To work
around this problem, convert to numeric units, perform the aggregate operation, and convert back to a
temporal value. Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC( time_col ))) FROM tbl_name ;
SELECT FROM_DAYS(SUM(TO_DAYS( date_col ))) FROM tbl_name ;
Note
The MySQL server can be run with the
MAXDB
[540] SQL mode enabled. In this
case,
TIMESTAMP
is identical with
DATETIME
. If this mode is enabled 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. See
Section 5.1.7, “Server SQL Modes” .
11.1.3. String Type Overview
A summary of the string data types follows. For additional information about properties and storage
requirements of the string types, see
Section 11.1.6, “String Types” , and Section 11.2, “Data Type
Storage Requirements” .
In some cases, MySQL may change a string column to a type different from that given in a
CREATE
TABLE
or
ALTER TABLE
statement. See Section 13.1.10.3, “Silent Column Specification Changes” .
In MySQL 4.1 and up, string data types include some features that you may not have encountered in
working with versions of MySQL prior to 4.1:
• MySQL interprets length specifications in character column definitions in character units. (Before
MySQL 4.1, column lengths were interpreted in bytes.) This applies to
CHAR
,
VARCHAR
, and the
TEXT
types.
• Column definitions for many string data types can include attributes that specify the character set or
collation of the column. These attributes apply to the
CHAR
,
VARCHAR
, the
TEXT
types,
ENUM
, and
SET
data types: