Column Types
============
available and summarises the storage requirements for each column type,
then provides a more detailed description of the properties of the
types in each category. The overview is intentionally brief. The more
detailed descriptions should be consulted for additional information
about particular column types, such as the allowable formats in which
you can specify values.
The column types supported by MySQL are listed below. The following
code letters are used in the descriptions:
`M'
Indicates the maximum display size. The maximum legal display
size is 255.
`D'
Applies to floating-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.
Note that if you specify `ZEROFILL' for a column, MySQL will
automatically add the `UNSIGNED' attribute to the column.
*Warning*: you should be aware that when you use subtraction between
integer values where one is of type `UNSIGNED', the result will be
unsigned! *Note Cast Functions::.
`TINYINT[(M)] [UNSIGNED] [ZEROFILL]'
A very small integer. The signed range is `-128' to `127'. The
unsigned range is `0' to `255'.
`BIT'
`BOOL'
`BOOLEAN'
These are synonyms for `TINYINT(1)'. The `BOOLEAN' synonym was
added in version 4.1.0
Full boolean type handling will be introduced in accordance with
SQL-99.
`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'.
you should be aware of with
respect to `BIGINT' columns:
If
you do that, some of the last digits in the result may be
wrong because of rounding errors when converting the `BIGINT'
to a `DOUBLE'.
MySQL 4.0 can handle `BIGINT' in the following cases:
* Use integers to store big unsigned values in a `BIGINT'
column.
* In `MIN(big_int_column)' and `MAX(big_int_column)'.
* When using operators (`+', `-', `*', etc.) where both
operands are integers.
intermediate double representation.
* `-', `+', and `*' will use `BIGINT' arithmetic when both
arguments 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(precision) [UNSIGNED] [ZEROFILL]'
A floating-point number. `precision' can be `<=24' for a
single-precision floating-point number and between 25 and 53 for a
double-precision floating-point number. These types are like the
`FLOAT' and `DOUBLE' types described immediately below.
`FLOAT(X)' has the same range as the corresponding `FLOAT' and
`DOUBLE' types, but the display size and number of decimals are
undefined.
In MySQL Version 3.23, this is a true floating-point value. In
earlier MySQL versions, `FLOAT(precision)' always has 2 decimals.
Note that using `FLOAT' may give you some unexpected problems as
all calculations in MySQL are done with double precision. *Note
No matching rows::.
This syntax is provided for ODBC compatibility.
If `UNSIGNED' is
specified, negative values are disallowed. The `M' is the display
width and `D' is the number of decimals. `FLOAT' without
arguments or `FLOAT(X)' where `X' <= 24 stands for a
single-precision floating-point number.
`DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]'
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'. If `UNSIGNED' is specified, negative
values are disallowed. The `M' is the display width and `D' is
the number of decimals. `DOUBLE' without arguments or `FLOAT(X)'
where 25 <= `X' <= 53 stands for a double-precision floating-point
number.
`DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]'
`REAL[(M,D)] [UNSIGNED] [ZEROFILL]'
These are synonyms for `DOUBLE'.
digit of the value. The decimal point and, for
negative numbers, the `-' sign, are not counted in `M' (but space
for these is reserved). If `D' is 0, values will 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 `UNSIGNED' is specified, negative values are disallowed.
If `D' is omitted, the default is 0. If `M' is omitted, the
default is 10.
Prior to MySQL Version 3.23, the `M' argument must include the
space needed for the sign and the decimal point.
`DEC[(M[,D])] [UNSIGNED] [ZEROFILL]'
`NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]'
`FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]'
These are synonyms for `DECIMAL'.
The `FIXED' alias was added in version 4.1.0 for compatibility
with other servers.
`DATE'
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. *Note DATETIME::.
`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.
*Note DATETIME::.
`TIMESTAMP[(M)]'
A timestamp. The range is `'1970-01-01 00:00:00'' to sometime in
the year `2037'.
`6',
but allows you to assign values to `TIMESTAMP' columns using
either strings or numbers.
From MySQL 4.1, `TIMESTAMP' is returned as a string with the format
`'YYYY-MM-DD HH:MM:SS''. If you want to have this as a number you
should add +0 to the timestamp column. Different timestamp lengths
are not supported. From version 4.0.12, the `--new' option can be
used to make the server behave as in version 4.1.
A `TIMESTAMP' column is useful for recording the date and time of
an `INSERT' or `UPDATE' operation because it is automatically set
to the date and time of the most recent operation if you don't
give it a value yourself. You can also set it to the current date
and time by assigning it a `NULL' value. *Note Date and time
types::.
The `M' argument affects only how a `TIMESTAMP' column is
displayed; its values always are stored using 4 bytes each.
dump and
restore the table with these types! *Note `DATETIME': DATETIME.
`TIME'
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.
*Note `TIME': TIME.
`YEAR[(2|4)]'
A year in 2- or 4-digit format (default is 4-digit). The
allowable values are `1901' to `2155', `0000' in the 4-digit year
format, and 1970-2069 if you use the 2-digit format (70-69).
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 Version 3.22.)
*Note `YEAR': YEAR.
`[NATIONAL] CHAR(M) [BINARY]'
A fixed-length string that is always right-padded with spaces to
the specified length when stored. The range of `M' is 0 to 255
characters (1 to 255 prior to MySQL Version 3.23). Trailing
spaces are removed when the value is retrieved. `CHAR' values are
sorted and compared in case-insensitive fashion according to the
default character set unless the `BINARY' keyword is given.
From version 4.1.0, if the `M' value specified is greater than 255,
the column type is converted to `TEXT'. This is a compatibility
feature.
`NATIONAL CHAR' (or its equivalent short form, `NCHAR') is the
SQL-99 way to define that a `CHAR' column should use the default
CHARACTER set. This is the default in MySQL.
`CHAR' is a shorthand for `CHARACTER'.
MySQL allows you to create a column of type `CHAR(0)'. This is
mainly useful when you have to be compliant with some old
applications that depend on the existence of a column but that do
not actually use the value. This is also quite nice when you need
a column that only can take 2 values: A `CHAR(0)', that is not
defined as `NOT NULL', will occupy only one bit and can take only
2 values: `NULL' or `""'. *Note `CHAR': CHAR.
`CHAR'
This is a synonym for `CHAR(1)'.
`[NATIONAL] VARCHAR(M) [BINARY]'
A variable-length string. *Note*: trailing spaces are removed when
the value is stored (this differs from the SQL-99 specification).
The range of `M' is 0 to 255 characters (1 to 255 prior to MySQL
Version 4.0.2). `VARCHAR' values are sorted and compared in
case-insensitive fashion unless the `BINARY' keyword is given.
*Note Silent column changes::.
From version 4.1.0, if the `M' value specified is greater than 255,
the column type is converted to `TEXT'. This is a compatibility
feature.
`VARCHAR' is a shorthand for `CHARACTER VARYING'. *Note CHAR::.
`TINYBLOB'
`TINYTEXT'
A `BLOB' or `TEXT' column with a maximum length of 255 (2^8 - 1)
characters. *Note Silent column changes::. *Note BLOB::.
`BLOB'
`TEXT'
A `BLOB' or `TEXT' column with a maximum length of 65535 (2^16 - 1)
characters. *Note Silent column changes::. *Note BLOB::.
`MEDIUMBLOB'
`MEDIUMTEXT'
A `BLOB' or `TEXT' column with a maximum length of 16777215 (2^24
- 1) characters. *Note Silent column changes::. *Note BLOB::.
`LONGBLOB'
`LONGTEXT'
A `BLOB' or `TEXT' column with a maximum length of 4294967295 or
4G (2^32 - 1) characters. *Note Silent column changes::. Upto
MySQL version 3.23 the server/client protocol and MyISAM tables had
a limit of 16M per communication packet / table row, from version
4.x the maximum allowed length of `LONGTEXT' or `LONGBLOB' columns
depends on the configured maximum packet size in the client/server
protocol and available memory. *Note BLOB::.
`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' can have a
maximum of 65535 distinct values. *Note ENUM::.
`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' can have a maximum of 64 members. *Note
SET::.
Numeric Types
-------------
MySQL supports all of the SQL-92 numeric datatypes. These types
include the exact numeric datatypes (`NUMERIC', `DECIMAL', `INTEGER',
and `SMALLINT'), as well as the approximate numeric datatypes (`FLOAT',
`REAL', and `DOUBLE PRECISION'). The keyword `INT' is a synonym for
`INTEGER', and the keyword `DEC' is a synonym for `DECIMAL'.
The `NUMERIC' and `DECIMAL' types are implemented as the same type by
MySQL, as permitted by the SQL-92 standard. They are used for 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)
the
decimal point. In this case, therefore, the range of values that can
be stored in the `salary' column is from `-99.99' to `99.99'. (MySQL
can actually store numbers up to `999.99' in this column because it
doesn't have to store the sign for positive numbers)
In SQL-92, the syntax `DECIMAL(p)' is equivalent to `DECIMAL(p,0)'.
Similarly, the syntax `DECIMAL' is equivalent to `DECIMAL(p,0)', where
the implementation is allowed to decide the value of `p'. MySQL does
not currently support either of these variant forms of the
`DECIMAL'/`NUMERIC' datatypes. This is not generally a serious
problem, as the principal benefits of these types derive from the
ability to control both precision and scale explicitly.
`DECIMAL' and `NUMERIC' values are stored as strings, rather than as
binary floating-point numbers, in order to preserve the decimal
precision of those values. One character is used for each digit of the
value, the decimal point (if `scale' > 0), and the `-' sign (for
negative numbers). If `scale' is 0, `DECIMAL' and `NUMERIC' values
contain no decimal point or fractional part.
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 rounded to that `scale'. When a `DECIMAL' or `NUMERIC'
column is assigned a value whose magnitude exceeds the range implied by
the specified (or defaulted) `precision' and `scale', MySQL stores the
value representing the corresponding end point of that range.
As an extension to the SQL-92 standard, MySQL also supports the integer
types `TINYINT', `MEDIUMINT', and `BIGINT' as listed in the tables
above. 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 width specification is used to left-pad the display of values
whose width is less than the width specified for the column, but does
not constrain the range of values that can be stored in the column, nor
the number of digits that will be displayed for values whose width
exceeds that specified for the column. When used in conjunction with
the optional extension attribute `ZEROFILL', the default padding of
spaces is replaced with zeroes. 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, as in these cases MySQL trusts that the data
did fit into the original column width.
range
for the column.
As of MySQL 4.0.2, floating-point types also can be `UNSIGNED'. As
with integer types, this attribute prevents negative values from being
stored in the column. Unlike the integer types, the upper range of
column values remains the same.
The `FLOAT' type is used to represent approximate numeric datatypes.
The SQL-92 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. When the keyword `FLOAT' is used for
a column type without a precision specification, MySQL uses four bytes
to store the values. A variant syntax is also supported, with two
numbers given in parentheses following the `FLOAT' keyword. With this
option, the first number continues to represent the storage
requirements for the value in bytes, and the second number specifies
the number of digits to be stored and displayed following the decimal
point (as with `DECIMAL' and `NUMERIC'). When MySQL is asked to store
a number for such a column with more decimal digits following the
decimal point than specified for the column, the value is rounded to
eliminate the extra digits when the value is stored.
The `REAL' and `DOUBLE PRECISION' types do not accept precision
specifications. As an extension to the SQL-92 standard, MySQL
recognises `DOUBLE' as a synonym for the `DOUBLE PRECISION' type. In
contrast with the standard's requirement that the precision for `REAL'
be smaller than that used for `DOUBLE PRECISION', MySQL implements both
as 8-byte double-precision floating-point values (when not running in
"ANSI mode"). 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 decimal
points.
When asked to store a value in a numeric column that is outside the
column 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,
the value is clipped to the lower endpoint of the range, and
`-2147483648' is stored instead. Similarly, if you try to insert
`9999999999', `2147483647' is stored 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 become `0' and `4294967296'.
Conversions that occur due to clipping are reported as "warnings" for
`ALTER TABLE', `LOAD DATA INFILE', `UPDATE', and multi-row `INSERT'
statements.
*Type* *Bytes* *From* *To*
`TINYINT' 1 -128 127
`SMALLINT' 2 -32768 32767
`MEDIUMINT' 3 -8388608 8388607
`INT' 4 -2147483648 2147483647
`BIGINT' 8 -9223372036854775808 9223372036854775807
Date and Time Types
-------------------
The date and time types are `DATETIME', `DATE', `TIMESTAMP', `TIME',
and `YEAR'. Each of these has a range of legal values, as well as a
"zero" value that is used when you specify a really illegal value.
Note that MySQL allows you to store certain 'not strictly' legal date
values, for example `1999-11-31'. The reason for this is that we think
it's the responsibility of the application to handle date checking, not
the SQL servers. To make the date checking 'fast', MySQL only checks
that the month is in the range of 0-12 and the day is in the range of
0-31. The above ranges are defined this way because MySQL allows you to
store, in a `DATE' or `DATETIME' column, dates where the day or
month-day is zero. This is extremely useful for applications that need
to store a birth-date for which you don't know the exact date. In this
case you simply store the date like `1999-00-00' or `1999-01-00'. (You
cannot expect to get a correct value from functions like `DATE_SUB()'
or `DATE_ADD' for dates like these.)
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
format, but it attempts to interpret a variety of formats for
values that you supply (for example, when you specify a value to
be assigned to or compared to a date or time type). Nevertheless,
only the formats described in the following sections are
supported. It is expected that you will supply legal values, and
unpredictable results may occur if you use values in other formats.
* Although MySQL tries to interpret values in several formats, it
always expects the year part of date values to be leftmost. Dates
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 (see the start 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.) The following table
shows the format of the "zero" value for each type:
*Column *"Zero" value*
type*
`DATETIME' `'0000-00-00 00:00:00''
`DATE' `'0000-00-00''
`TIMESTAMP'`00000000000000' (length depends on
display size)
`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' Version 2.50.12 and above,
because ODBC can't handle such values.
Y2K Issues and Date Types
.........................
values
must be interpreted into 4-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'.
4-digit
year values.
`ORDER BY' will sort 2-digit `YEAR/DATE/DATETIME' types properly.
Note also that some functions like `MIN()' and `MAX()' will convert a
`TIMESTAMP/DATE' to a number. This means that a timestamp with a
2-digit year will not work properly with these functions. The fix in
this case is to convert the `TIMESTAMP/DATE' to 4-digit year format or
use something like `MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS))'.
The `DATETIME', `DATE', and `TIMESTAMP' Types
.............................................
The `DATETIME', `DATE', and `TIMESTAMP' types are related. This
section describes their characteristics, how they are similar, and how
they differ.
`'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 that they
will.)
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' column 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, only the first
one is updated automatically.
Automatic updating of the first `TIMESTAMP' column occurs under any of
the following conditions:
* 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. (Note that an `UPDATE' that
sets a column to the value it already has will not cause the
`TIMESTAMP' column to be updated, because if you set a column to
its current value, MySQL ignores the update for efficiency.)
* You explicitly set the `TIMESTAMP' column to `NULL'.
`TIMESTAMP' columns other than the first may also be set to the current
date and time. Just set the column to `NULL' or to `NOW()'.
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 will
initialise 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.
On the other hand, you may find it just as easy to use a `DATETIME'
column that you initialise to `NOW()' when the row is created and leave
alone for subsequent updates.
`TIMESTAMP' values may range from the beginning of 1970 to sometime in
the year 2037, with a resolution of one second. Values are displayed as
numbers.
may be
created with shorter display sizes:
`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.
*Note*: From version 4.1, `TIMESTAMP' is returned as a string with the
format `'YYYY-MM-DD HH:MM:SS'' and different timestamp lengths are no
longer supported.
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.
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').
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''.
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 will
represent March, 1999, you will find that 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 missing (zero), so the value is not a
legal date.
`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 will not
be a legal date and `0' will be stored.
* If you use `ALTER TABLE' to widen a narrow `TIMESTAMP' column,
information will be 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.
* 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 functions 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.
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
will be converted to `0' if assigned to such an object.
Be aware of certain pitfalls when specifying date values:
in a
date context will be interpreted as the year `'2010-11-12''. The
value `'10:45:15'' will be converted to `'0000-00-00'' because
`'45'' is not a legal month.
* The MySQL server only performs basic checking on the validity of a
date: days `00-31', months `00-12', years `1000-9999'. Any date
not within this range will revert to `0000-00-00'. Please note
that this still allows you to store invalid dates such as
`2002-04-31'. It allows web applications to store data from a form
without further checking. To ensure a date is valid, perform a
check in your application.
* Year values specified as two digits are ambiguous, because the
century is unknown. MySQL interprets 2-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'.
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 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 also elapsed time
or a time interval between two events (which may be much greater than
24 hours, or even negative).
You can specify `TIME' values in a variety of formats:
`HH:MM:SS.fraction', `HH:MM:SS', `HH:MM', `D HH:MM:SS', `D HH:MM',
`D HH' or `SS'. Here `D' is days between 0-33.
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 doesn't yet
store the fraction 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'' will mean `'11:12:00'', not `'00:11:12''.
Values that lie outside the `TIME' range but are otherwise legal are
clipped to the appropriate 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.
The `YEAR' Type
...............
The `YEAR' type is a 1-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'.
`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 will be
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'.
String Types
------------
The string types are `CHAR', `VARCHAR', `BLOB', `TEXT', `ENUM', and
`SET'. This section describes how these types work, their storage
requirements, and how to use them in your queries.
*Type* *Max.size* *Bytes*
`TINYTEXT' or `TINYBLOB' 2^8-1 255
`TEXT' or `BLOB' 2^16-1 (64K-1) 65535
`MEDIUMTEXT' or `MEDIUMBLOB' 2^24-1 (16M-1) 16777215
`LONGBLOB' 2^32-1 (4G-1) 4294967295
The `CHAR' and `VARCHAR' Types
..............................
The `CHAR' and `VARCHAR' types are similar, but differ in the way they
are stored and retrieved.
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 between 1 and
255. (As of MySQL Version 3.23, the length of `CHAR' may be 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. You can
declare a `VARCHAR' column to be any length between 1 and 255, just as
for `CHAR' columns. However, in contrast to `CHAR', `VARCHAR' values
are stored using only as many characters as are needed, plus one byte
to record the length. Values are not padded; instead, trailing spaces
are removed when values are stored. (This space removal differs from
the SQL-99 specification.) No case conversion takes place during
storage or retrieval.
If you assign a value to a `CHAR' or `VARCHAR' column that exceeds the
column's maximum length, the value is truncated to fit.
The following table illustrates the differences between the two types
of columns by showing the result of storing various string values into
`CHAR(4)' and `VARCHAR(4)' columns:
*Value* `CHAR(4)' *Storage `VARCHAR(4)'*Storage
required* required*
`''' `' '' 4 bytes `''' 1 byte
`'ab'' `'ab '' 4 bytes `'ab'' 3 bytes
`'abcd'' `'abcd'' 4 bytes `'abcd'' 5 bytes
`'abcdefgh''`'abcd'' 4 bytes `'abcd'' 5 bytes
The values retrieved from the `CHAR(4)' and `VARCHAR(4)' columns will
be the same in each case, because trailing spaces are removed from
`CHAR' columns upon retrieval.
Values in `CHAR' and `VARCHAR' columns are sorted and compared in
case-insensitive fashion, unless the `BINARY' attribute was specified
when the table was created. The `BINARY' attribute means that column
values are sorted and compared in case-sensitive fashion according to
the ASCII order of the machine where the MySQL server is running.
`BINARY' doesn't affect how the column is stored or retrieved.
From version 4.1.0, column type `CHAR BYTE' is an alias for `CHAR
BINARY'. This is a compatibility feature.
The `BINARY' attribute is sticky. This means that if a column marked
`BINARY' is used in an expression, the whole expression is compared as a
`BINARY' value.
MySQL may silently change the type of a `CHAR' or `VARCHAR' column at
table creation time. *Note Silent column changes::.
The `BLOB' and `TEXT' Types
...........................
A `BLOB' is a binary large object that can hold a variable amount of
data. The four `BLOB' types `TINYBLOB', `BLOB', `MEDIUMBLOB', and
`LONGBLOB' differ only in the maximum length of the values they can
hold. *Note Storage requirements::.
The four `TEXT' types `TINYTEXT', `TEXT', `MEDIUMTEXT', and `LONGTEXT'
correspond to the four `BLOB' types and have the same maximum lengths
and storage requirements. The only difference between `BLOB' and
`TEXT' types is that sorting and comparison is performed in
case-sensitive fashion for `BLOB' values and case-insensitive fashion
for `TEXT' values. In other words, a `TEXT' is a case-insensitive
`BLOB'. No case conversion takes place during storage or retrieval.
If you assign a value to a `BLOB' or `TEXT' column that exceeds the
column type's maximum length, the value is truncated to fit.
* You can have indexes on `BLOB' and `TEXT' columns with MySQL
Version 3.23.2 and newer. Older versions of MySQL did not support
this.
* There is no trailing-space removal for `BLOB' and `TEXT' columns
when values are stored, as there is for `VARCHAR' columns.
* `BLOB' and `TEXT' columns cannot have `DEFAULT' values.
From version 4.1.0, `LONG' and `LONG VARCHAR' map to the `MEDIUMTEXT'
data type. This is a compatibility feature.
`MyODBC' defines `BLOB' values as `LONGVARBINARY' and `TEXT' values as
`LONGVARCHAR'.
Because `BLOB' and `TEXT' values may be extremely long, you may run up
against some constraints when using them:
function. For example:
mysql> SELECT comment FROM tbl_name,SUBSTRING(comment,20) AS substr
-> ORDER BY substr;
If you don't do this, only the first `max_sort_length' bytes of the
column are used when sorting. The default value of
`max_sort_length' is 1024; this value can be changed using the
`-O' option when starting the `mysqld' server. You can group on an
expression involving `BLOB' or `TEXT' values by specifying the
column position or by using an alias:
mysql> SELECT id,SUBSTRING(blob_col,1,100) FROM tbl_name GROUP BY 2;
mysql> SELECT id,SUBSTRING(blob_col,1,100) AS b FROM tbl_name GROUP BY b;
* The maximum size of a `BLOB' or `TEXT' object is determined by its
type, but the largest value you can actually transmit between the
client and server is determined by the amount of available memory
and the size of the communications buffers. You can change the
message buffer size (`max_allowed_packet'), but you must do so on
both the server and client ends. *Note Server parameters::.
Note that each `BLOB' or `TEXT' value is represented internally by a
separately allocated object. This is in contrast to all other column
types, for which storage is allocated once per column when the table is
opened.
The `ENUM' Type
...............
An `ENUM' is a string object whose value normally is chosen from a list
of allowed values that are enumerated explicitly in the column
specification at table creation time.
The value may also be the empty string (`""') or `NULL' under certain
circumstances:
be
distinguished from a 'normal' empty string by the fact that this
string has the numerical value 0. More about this later.
* If an `ENUM' is declared `NULL', `NULL' is also a legal value for
the column, and the default value is `NULL'. If an `ENUM' is
declared `NOT NULL', the default value is the first element of the
list of allowed values.
Each enumeration value has an index:
* Values from the list of allowable elements in the column
specification are numbered beginning with 1.
* The index value of the empty string error value is 0. This means
that you can use the following `SELECT' statement to find rows
into which invalid `ENUM' values were assigned:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
* The index of the `NULL' value is `NULL'.
For example, a column specified as `ENUM("one", "two", "three")' can
have any of the values shown here. The index of each value is also
shown:
*Value* *Index*
`NULL' `NULL'
`""' 0
`"one"' 1
`"two"' 2
`"three"'3
An enumeration can have a maximum of 65535 elements.
Starting from 3.23.51 trailing spaces are automatically deleted from
`ENUM' values when the table is created.
at
table creation time.
If you retrieve an `ENUM' in a numeric context, the column value's
index is returned. For example, you can retrieve numeric values from
an `ENUM' column like this:
mysql> SELECT enum_col+0 FROM tbl_name;
If you store a number into an `ENUM', the number is treated as an
index, and the value stored is the enumeration member with that index.
(However, this will not work with `LOAD DATA', which treats all input
as strings.) It's not advisable to store numbers in an `ENUM' string
because it will make things confusing.
`ENUM' values are sorted according to the order in which the enumeration
members were listed in the column specification. (In other words,
`ENUM' values are sorted according to their index numbers.) For
example, `"a"' sorts before `"b"' for `ENUM("a", "b")', but `"b"' sorts
before `"a"' for `ENUM("b", "a")'. The empty string sorts before
non-empty strings, and `NULL' values sort before all other enumeration
values. To prevent unexpected results, specify the `ENUM' list in
alphabetical order. You can also use `GROUP BY CONCAT(col)' to make
sure the column is sorted alphabetically rather than by index number.
If you want to get all possible values for an `ENUM' column, you should
use: `SHOW COLUMNS FROM table_name LIKE enum_column_name' and parse the
`ENUM' definition in the second column.
The `SET' Type
..............
set
members are specified with members separated by commas (`,'). A
consequence of this is that `SET' member values cannot themselves
contain commas.
For example, a column specified as `SET("one", "two") NOT NULL' can have
any of these values:
""
"one"
"two"
"one,two"
A `SET' can have a maximum of 64 different members.
Starting from 3.23.51 trailing spaces are automatically deleted from
`SET' values when the table is created.
MySQL stores `SET' values numerically, with the low-order bit of the
stored value corresponding to the first set member. If you retrieve a
`SET' value in a numeric context, the value retrieved has bits set
corresponding to the set members that make up the column value. For
example, you can retrieve numeric values from a `SET' column like this:
mysql> SELECT set_col+0 FROM tbl_name;
If a number is stored into a `SET' column, the bits that are set in the
binary representation of the number determine the set members in the
column value. Suppose a column is specified as `SET("a","b","c","d")'.
Then the members have the following bit values:
`1000'
If you assign a value of `9' to this column, that is `1001' in binary,
so the first and fourth `SET' value members `"a"' and `"d"' are
selected and the resulting value is `"a,d"'.
For a value containing more than one `SET' element, it does not matter
what order the elements are listed in when you insert the value. It
also does not matter how many times a given element is listed in the
value. When the value is retrieved later, each element in the value
will appear once, with elements listed according to the order in which
they were specified at table creation time. For example, if a column
is specified as `SET("a","b","c","d")', then `"a,d"', `"d,a"', and
`"d,a,a,d,d"' will all appear as `"a,d"' when retrieved.
If you set a `SET' column to an unsupported value, the value will be
ignored.
`SET' values are sorted numerically. `NULL' values sort before
non-`NULL' `SET' values.
Normally, you perform a `SELECT' on a `SET' column using the `LIKE'
operator or the `FIND_IN_SET()' function:
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
But the following will also work:
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;
The first of these statements looks for an exact match. The second
looks for values containing the first set member.
If you want to get all possible values for a `SET' column, you should
use: `SHOW COLUMNS FROM table_name LIKE set_column_name' and parse the
`SET' definition in the second column.
Choosing the Right Type for a Column
------------------------------------
is the best
type.
Accurate representation of monetary values is a common problem. In
MySQL, you should use the `DECIMAL' type. This is stored as a string,
so no loss of accuracy should occur. If accuracy is not too important,
the `DOUBLE' type may also be good enough.
For high precision, you can always convert to a fixed-point type stored
in a `BIGINT'. This allows you to do all calculations with integers and
convert results back to floating-point values only when necessary.
Using Column Types from Other Database Engines
----------------------------------------------
other database
engines to MySQL:
*Other vendor *MySQL type*
type*
`BINARY(NUM)' `CHAR(NUM) BINARY'
`CHAR `VARCHAR(NUM)'
VARYING(NUM)'
`FLOAT4' `FLOAT'
`FLOAT8' `DOUBLE'
`INT1' `TINYINT'
`INT2' `SMALLINT'
`INT3' `MEDIUMINT'
`INT4' `INT'
`INT8' `BIGINT'
`LONG VARBINARY' `MEDIUMBLOB'
`LONG VARCHAR' `MEDIUMTEXT'
`MIDDLEINT' `MEDIUMINT'
`VARBINARY(NUM)' `VARCHAR(NUM) BINARY'
Column type mapping occurs at table creation time. If you create a
table with types used by other vendors and then issue a `DESCRIBE
tbl_name' statement, MySQL reports the table structure using the
equivalent MySQL types.
Column Type Storage Requirements
--------------------------------
The storage requirements for each of the column types supported by
MySQL are listed by category.
Storage Requirements for Numeric Types
......................................
*Column type* *Storage required*
`TINYINT' 1 byte
`SMALLINT' 2 bytes
`MEDIUMINT' 3 bytes
`INT' 4 bytes
`INTEGER' 4 bytes
`BIGINT' 8 bytes
`FLOAT(X)' 4 if X <= 24 or 8 if 25 <= X <= 53
`FLOAT' 4 bytes
`DOUBLE' 8 bytes
`DOUBLE PRECISION' 8 bytes
`REAL' 8 bytes
`DECIMAL(M,D)' `M+2' bytes if D > 0, `M+1' bytes if D = 0
(`D'+2, if `M < D')
`NUMERIC(M,D)' `M+2' bytes if D > 0, `M+1' bytes if D = 0
(`D'+2, if `M < D')
Storage Requirements for Date and Time Types
............................................
*Column type* *Storage required*
`DATE' 3 bytes
`DATETIME' 8 bytes
`TIMESTAMP' 4 bytes
`TIME' 3 bytes
`YEAR' 1 byte
Storage Requirements for String Types
.....................................
*Column type* *Storage required*
`CHAR(M)' `M' bytes, `1 <= M <= 255'
`VARCHAR(M)' `L'+1 bytes, where `L <= M' and `1 <= M <= 255'
`TINYBLOB', `TINYTEXT' `L'+1 bytes, where `L' < 2^8
`BLOB', `TEXT' `L'+2 bytes, where `L' < 2^16
`MEDIUMBLOB', `L'+3 bytes, where `L' < 2^24
`MEDIUMTEXT'
`LONGBLOB', `LONGTEXT' `L'+4 bytes, where `L' < 2^32
`ENUM('value1','value2',...)'1 or 2 bytes, depending on the number of
enumeration values (65535 values maximum)
`SET('value1','value2',...)'1, 2, 3, 4 or 8 bytes, depending on the number
of set members (64 members maximum)
`VARCHAR' and the `BLOB' and `TEXT' types are variable-length types,
for which the storage requirements depend on the actual length of
column values (represented by `L' in the preceding table), rather than
on the type's maximum possible size. For example, a `VARCHAR(10)'
column can hold a string with a maximum length of 10 characters. The
actual storage required is the length of the string (`L'), plus 1 byte
to record the length of the string. For the string `'abcd'', `L' is 4
and the storage requirement is 5 bytes.
The `BLOB' and `TEXT' types require 1, 2, 3, or 4 bytes to record the
length of the column value, depending on the maximum possible length of
the type. *Note BLOB::.
If a table includes any variable-length column types, the record format
will also be variable-length. Note that when a table is created, MySQL
may, under certain conditions, change a column from a variable-length
type to a fixed-length type, or vice-versa. *Note Silent column
changes::.
up to 65535
values. *Note ENUM::.
The size of a `SET' object is determined by the number of different set
members. If the set size is `N', the object occupies `(N+7)/8' bytes,
rounded up to 1, 2, 3, 4, or 8 bytes. A `SET' can have a maximum of 64
members. *Note SET::.
The maximum size of a row in a `MyISAM' table is 65534 bytes. Each
`BLOB' and `TEXT' column accounts for only 5-9 bytes toward this size.
[Назад] [Содержание] [Вперед]
| Главная |