MySQL supports a number of column types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these column types, and then provides a more detailed description of the properties of the types in each category, and a summary of the column type storage requirements. 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.
MySQL versions 4.1 and up support extensions for handing spatial data. Information about spatial types is provided in section 18 Spatial Extensions in MySQL.
Several of the column type descriptions use these conventions:
M
D
A summary of the numeric column types follows. For additional information, see section 11.2 Numeric Types. Column storage requirements are given in section 11.5 Column Type Storage Requirements.
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.
Warning: You should be aware that when you use subtraction
between integer values where one is of type UNSIGNED, the result
will be unsigned! See section 12.7 Cast Functions and Operators.
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
-128 to 127. The
unsigned range is 0 to 255.
BIT
BOOL
BOOLEAN
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.
In the future,
full boolean type handling will be introduced in accordance with standard SQL.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
-32768 to 32767. The
unsigned range is 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
-8388608 to
8388607. The unsigned range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
-2147483648 to
2147483647. The unsigned range is 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
-9223372036854775808 to
9223372036854775807. The unsigned range is 0 to
18446744073709551615.
Some things you should be aware of with respect to BIGINT columns:
BIGINT or DOUBLE
values, so you shouldn't 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:
BIGINT column.
MIN(col_name) or MAX(col_name), where
col_name refers to a BIGINT column.
+, -, *, and so on) where
both operands are integers.
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.
-, +, and * operators will 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(p) [UNSIGNED] [ZEROFILL]
FLOAT
and DOUBLE types described immediately following. FLOAT(p)
has the same range as the corresponding FLOAT and DOUBLE
types, but the display width and number of decimals are undefined.
As of MySQL 3.23, this is a true floating-point value. In
earlier MySQL versions, FLOAT(p) always has two decimals.
This syntax is provided for ODBC compatibility.
Using FLOAT might give you some unexpected problems because
all calculations in MySQL are done with double precision.
See section A.5.7 Solving Problems with No Matching Rows.
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
-3.402823466E+38 to -1.175494351E-38, 0,
and 1.175494351E-38 to 3.402823466E+38. If
UNSIGNED is specified, negative values are disallowed. M
is the display width and D is the number of decimals. FLOAT
without arguments or FLOAT(p) (where p is in the range from
0 to 24) stands for a single-precision floating-point number.
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
-1.7976931348623157E+308 to
-2.2250738585072014E-308, 0, and
2.2250738585072014E-308 to 1.7976931348623157E+308. If
UNSIGNED is specified, negative values are disallowed.
M is the display width and D is the number of decimals.
DOUBLE without arguments or FLOAT(p) (where
p is in the
range from 25 to 53) stands for a double-precision floating-point number.
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
REAL[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE.
Exception: If the server SQL mode includes the REAL_AS_FLOAT option,
REAL is a synonym for FLOAT rather than DOUBLE.
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
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 decimals. 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
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 3.23, the M argument must be large enough to
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]
DECIMAL.
The FIXED synonym was added in MySQL 4.1.0 for compatibility
with other servers.
A summary of the temporal column types follows. For additional information, see section 11.3 Date and Time Types. Column storage requirements are given in section 11.5 Column Type Storage Requirements.
DATE
'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
'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)]
'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 don't assign it a value yourself. You can also set any
TIMESTAMP column to the current date and time by assigning it a
NULL value.
From MySQL 4.1 on, TIMESTAMP is returned as a string with the format
'YYYY-MM-DD HH:MM:SS'. If you want to obtain the value as a number,
you should add +0 to the timestamp column. Different timestamp
display widths are not supported.
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.
TIME
'-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)]
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.
A summary of the string column types follows. For additional information, see section 11.4 String Types. Column storage requirements are given in section 11.5 Column 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.2.6.1 Silent Column Specification Changes.
A change that affects many string column types is that, as of MySQL 4.1,
character column definitions can include a CHARACTER SET attribute to
specify the character set and, optionally, a collation. This
applies 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.
Character column sorting and comparison are based on the character set
assigned to the column. Before MySQL 4.1, 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 be case sensitive using the
underlying character code values rather then a lexical ordering.
For more details, see section 10 Character Set Support.
Also as of 4.1, MySQL interprets length specifications in character column definitions in characters. (Earlier versions interpret them in bytes.)
[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]
CHAR values are
retrieved.
From MySQL 4.1.0, 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. Before 4.1.2, BINARY
attribute causes the column to be treated as a binary string. In either
case, sorting and comparisons become case sensitive.
From MySQL 4.1.0 on, the ASCII attribute can be specified. It
assigns the latin1 character set to a CHAR column.
From MySQL 4.1.1 on, the UNICODE attribute can be specified. It
assigns the ucs2 character set to a CHAR column.
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 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
CHAR(1).
[NATIONAL] VARCHAR(M) [BINARY]
65535 bytes. (The exact number of
characters is depending on the character set).
Note: Before 5.0.3, trailing spaces where removed when
VARCHAR values was stored, which differs from the standard SQL
specification.
From MySQL 4.1.0 - 5.0.2 on, 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. Before 4.1.2, BINARY
attribute causes the column to be treated as a binary string. In either
case, sorting and comparisons become case sensitive.
Starting from MySQL 5.0.3, VARCHAR is stored with a 1 byte or 2
byte length prefix + data. The length prefix is 1 byte if the storage
size for the VARCHAR column is less than 256.
BINARY(M)
BINARY type is similar to the CHAR type, but stores
binary strings rather than non-binary strings.
This type was added in MySQL 4.1.2.
VARBINARY(M)
VARBINARY type is similar to the VARCHAR type, but stores
binary strings rather than non-binary strings.
This type was added in MySQL 4.1.2.
TINYBLOB
TINYTEXT
BLOB or TEXT column with a maximum length of 255 (2^8
- 1)
characters.
BLOB
TEXT
BLOB or TEXT column with a maximum length of 65,535 (2^16
-1)
characters.
MEDIUMBLOB
MEDIUMTEXT
BLOB or TEXT column with a maximum length of 16,777,215
(2^24 - 1) characters.
LONGBLOB
LONGTEXT
BLOB or TEXT column with a maximum length of 4,294,967,295 or
4GB (2^32 - 1) characters. Up to MySQL
3.23, the client/server protocol and MyISAM tables had a limit
of 16MB per communication packet / table row. From MySQL 4.0, the maximum
allowed length of LONGBLOB or LONGTEXT columns depends on the
configured maximum packet size in the client/server protocol and available
memory.
ENUM('value1','value2',...)
'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',...)
'value1', 'value2',
... A SET column can have a maximum of 64 members.
SET values are represented internally as integers.
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 as listed in
the following table.
| 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 will be 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.
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 decimal digits that will be
stored for values, and the scale represents the number of digits that will
be stored following the decimal point.
MySQL stores DECIMAL and NUMERIC values 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 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. MySQL varies from this in
two ways:
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 a DECIMAL or NUMERIC 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.
For floating-point column 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 four-byte
single-precision FLOAT column. A precision from 24 to 53 results
in eight-byte double-precision DOUBLE column.
When the keyword
FLOAT is used for a column type without a precision specification,
MySQL uses four bytes to store the values. MySQL also supports variant
syntax with two numbers given in parentheses following the FLOAT
keyword. The first number represents the display width
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.
In standard SQL, the REAL and DOUBLE PRECISION types do not accept
precision specifications. MySQL supports a variant syntax with two numbers
given in parentheses following the type name. The first number represents
the display width and the second number specifies the number of digits to
be stored and displayed following the decimal point.
As an extension to the SQL standard, MySQL recognizes 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 eight-byte
double-precision floating-point values (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 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, 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.
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.
Starting from MySQL 5.0.2, MySQL will give warnings/errors if you try to insert
an illegal date. You can get MySQL to accept certain dates, such as
'1999-11-31', by using the ALLOW_INVALID_DATES SQL mode.
(Before 5.0.2, this mode was the default behavior for MySQL).
This is useful when you want to store the ``possibly wrong'' value the user has
specified (for example, in a web form) in the database for future processing.
Under this mode, 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 don't 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. (If you
don't want to allow zero in dates, you can use the NO_ZERO_IN_DATE
SQL mode).
MySQL also allows you to store '0000-00-00' as a ``dummy date''
(if you are not using the NO_ZERO_DATE SQL mode). This is in some
cases is more convenient than using NULL values.
By setting the sql_mode system variable to the appropriate mode
values, You can more exactly what kind of dates you want MySQL to support.
See section 5.2.2 The Server SQL Mode.
Here are some general considerations to keep in mind when working with date and time types:
00-69 are converted to 2000-2069.
70-99 are converted to 1970-1999.
'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').
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. Note that the use of these values produces warnings if the
NO_ZERO_DATE SQL mode is enabled.
| Column Type | ``Zero'' Value |
DATETIME | '0000-00-00 00:00:00'
|
DATE | '0000-00-00'
|
TIMESTAMP | 00000000000000
|
TIME | '00:00:00'
|
YEAR | 0000
|
'0' or 0, which are easier to write.
NULL in MyODBC 2.50.12 and above,
because ODBC can't handle such values.
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.
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 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 has varying properties,
depending on the MySQL version and the SQL mode the server is running in.
These properties are described later in this section.
You can specify DATETIME, DATE, and TIMESTAMP values using
any of a common set of formats:
'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.
'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.
'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'.
'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'.
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'.
YYYYMMDD or YYMMDD
format, provided that the number makes sense as a date. For example,
19830905 and 830905 are interpreted as '1983-09-05'.
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 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 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:
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.
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.
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:
'10:11:12' might look like a time value
because of the `:' delimiter, but if used 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.
'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.
00-69 are converted to 2000-2069.
70-99 are converted to 1970-1999.
TIMESTAMP Properties Prior to MySQL 4.1
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 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:
NULL.
INSERT or
LOAD DATA INFILE statement.
UPDATE statement and some
other column changes value. An UPDATE that sets a column
to the value it already has does not cause the TIMESTAMP column to be
updated; if you set a column to its current value, MySQL
ignores the update for efficiency.
TIMESTAMP columns other than the first can also be set to the current
date and time. Just set the column 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:
TIMESTAMP column explicitly to its current value:
UPDATE tbl_name
SET timestamp_col = timestamp_col,
other_col1 = new_value1,
other_col2 = new_value2, ...
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 leave alone 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 time zone on
a per-connection basis, as described in Time zone support.
The format in which MySQL retrieves and displays TIMESTAMP
values depends on the display size, as illustrated by the following table. The
``full'' TIMESTAMP format is 14 digits, but TIMESTAMP columns may
be created with shorter display sizes:
| Column 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:
TIMESTAMP(4) or TIMESTAMP(2). Otherwise, the value is not
a legal date and 0 will be stored.
ALTER TABLE to widen a narrow TIMESTAMP column,
information will be displayed that previously was ``hidden.''
TIMESTAMP column does not cause information to
be lost, except in the sense that less information is shown when the values
are displayed.
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.
TIMESTAMP Properties as of MySQL 4.1
In MySQL 4.1 and up, the properties of the TIMESTAMP column 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.
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;
As of MySQL 5.0.2, MySQL will not accept timestamp values that includes a zero in the day or month column or values that are not a valid date. (The execption is the special value '0000-00-00 00:00:00'.)
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:
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 first TIMESTAMP 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:
DEFAULT CURRENT_TIMESTAMP and ON UPDATE
CURRENT_TIMESTAMP clauses, the column has the current timestamp for its
default value, and is automatically updated.
DEFAULT nor ON UPDATE clauses, it is the same
as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE
clause, the column has the current timestamp for its default value but is
not automatically updated.
DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP
clause, the column has a default of 0 and is automatically updated.
DEFAULT value and with ON UPDATE
CURRENT_TIMESTAMP clause, the column has the given default and is
automatically updated.
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 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 will get back the same value you store. If you
store a TIMESTAMP value, then change the time zone and
retrieve the value, it will be 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. 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.
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:
'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 doesn't yet store the fraction part.
'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'.
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.
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 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.
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:
'1901' to '2155'.
1901 to 2155.
'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.
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.
YEAR context, such as NOW().
Illegal YEAR values are converted to 0000.
MySQL itself is year 2000 (Y2K) safe (see section 1.2.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:
00-69 are converted to 2000-2069.
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() will convert a
TIMESTAMP or YEAR to a number. This means that a value with a
two-digit year will 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
DAYS)).
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.
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 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. You can
declare a VARCHAR column to be any length from 0 to 255, just as
for CHAR columns. (Before MySQL 4.0.2, the length of VARCHAR
may be from 1 to 255.) 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
standard SQL specification.
No lettercase 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.
If you need a column for which trailing spaces are not removed, consider using
a BLOB or TEXT type.
If you want to store binary values such as results from an encryption
or compression function that might contain arbitrary byte values, use a
BLOB column rather than a CHAR or VARCHAR column, to
avoid potential problems with trailing space removal that would change
data values.
The following table illustrates the differences between 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 Required | VARCHAR(4) | Storage 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
are the same in each case, because trailing spaces are removed from
CHAR columns upon retrieval.
As of MySQL 4.1, values in CHAR and VARCHAR columns are sorted
and compared according to the collation of the character set assigned to
the column. Before MySQL 4.1, sorting and comparison are based on
the collation of the server character set; you can declare the column
with the BINARY attribute to cause sorting and comparison to be
case sensitive using the underlying character code values rather then a
lexical ordering. BINARY doesn't affect how the column is stored
or retrieved.
From MySQL 4.1.0 on, 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 treated as a
BINARY value.
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 may silently change the type of a CHAR or VARCHAR
column at table creation time.
See section 13.2.6.1 Silent Column Specification Changes.
BINARY and VARBINARY Types
The BINARY and VARBINARY types are like CHAR and
VARCHAR, except that they contain binary strings rather than
non-binary strings. That is, they contain byte strings rather than character
strings. This means they have no character set and compare in case sensitive
fashion.
Before MySQL 4.1.2, BINARY(M) and VARBINARY(M) are
treated as CHAR(M) BINARY and VARCHAR(M) BINARY.
As of MySQL 4.1.2, BINARY and VARBINARY are available as distinct
data types, and for CHAR(M) BINARY and VARCHAR(M)
BINARY, the BINARY attribute does not cause the column to be treated
as a binary string column. Instead, it causes the binary collation for the
column character set to be used, but the column itself contains non-binary
character strings rather than binary byte strings. For example, in 4.1
and up, CHAR(5) BINARY is treated as CHAR(5) CHARACTER SET
latin1 COLLATE latin1_bin, assuming that the default character set is
latin1.
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.
See section 11.5 Column Type 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.
BLOB columns are treated as binary strings, whereas TEXT
columns are treated according to their character set. For BLOB
columns, sorting and comparison is case sensitive. For TEXT columns,
values are sorted and compared based on the collation of the character set
assigned to the column as of MySQL 4.1. Before 4.1, TEXT sorting and
comparison are based on the collation of the server character set.
No lettercase 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.
In most respects, you can regard a TEXT column as a VARCHAR
column that can be as big as you like. Similarly, you can regard a
BLOB column as a VARCHAR BINARY column. The ways in which
BLOB and TEXT differ from CHAR and VARCHAR are:
BLOB and TEXT
columns when values are stored or retrieved. This differs from CHAR
columns (trailing spaces are removed when values are retrieved) and from
VARCHAR columns (trailing spaces are removed when values are stored).
BLOB and TEXT columns only as of MySQL
3.23.2 for MyISAM tables or MySQL 4.0.14 for InnoDB tables.
Older versions of MySQL did not support indexing these column types.
BLOB and TEXT columns, you must specify an index
prefix length. For CHAR and VARCHAR, a prefix length is
optional.
BLOB and TEXT columns cannot have DEFAULT values.
From MySQL 4.1.0 on, LONG and LONG VARCHAR map to the
MEDIUMTEXT data type. This is a compatibility feature.
MySQL Connector/ODBC defines BLOB values as LONGVARBINARY and
TEXT values as LONGVARCHAR.
Because BLOB and TEXT values may be extremely long, you
may encounter some constraints in using them:
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 --max_sort_length
option when starting the mysqld server.
See section 5.2.3 Server System Variables.
As of MySQL 4.0.3, you can make more bytes signficant in sorting or grouping
by increasing the value of max_sort_length at runtime. Any client
can change the value of its session max_sort_length variable:
mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM tbl_name
-> ORDER BY comment;
Another way to use GROUP BY or ORDER BY on a BLOB or
TEXT column containing long values when you want more than
max_sort_length bytes to be significant is to convert the column
value into a fixed-length object. The standard way to do this is with the
SUBSTRING function. For example, the following statement causes
2000 bytes of the comment column to be taken into account for sorting:
mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name
-> ORDER BY SUBSTRING(comment,1,2000);
Before MySQL 3.23.2, you can group on an expression involving BLOB or
TEXT values by using a column alias or by specifying the column
position:
mysql> SELECT id, SUBSTRING(comment,1,2000) AS b
-> FROM tbl_name GROUP BY b;
mysql> SELECT id, SUBSTRING(comment,1,2000)
-> FROM tbl_name GROUP BY 2;
BLOB or TEXT object is determined by its
type, but the largest value you actually can 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 by changing
the value of the
max_allowed_packet variable, but you must
do so for both the server and your client program.
For example, both mysql and mysqldump allow you to change the
client-side
max_allowed_packet value.
See
section 7.5.2 Tuning Server Parameters,
section 8.3 mysql, the Command-Line Tool,
and
section 8.8 The mysqldump Database Backup Program.
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.
ENUM Type
An ENUM is a string object with a value 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:
ENUM (that is, a string not
present in the list of allowed values), the empty string is inserted
instead as a special error value. This string can be distinguished from a
``normal'' empty string by the fact that this string has the numerical value
0. More about this later.
ENUM column is declared to allow NULL, the NULL
value is a legal value for the column, and the default value is NULL.
If an ENUM column is declared NOT NULL, its default value
is the first element of the list of allowed values.
Each enumeration value has an index:
SELECT statement to find rows into which invalid
ENUM values were assigned:
mysql> SELECT * FROM tbl_name WHERE enum_col=0;
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 65,535 elements.
Starting from MySQL 3.23.51, trailing spaces are automatically deleted from
ENUM member values when the table is created.
Lettercase is irrelevant when you assign values to an ENUM column.
However, values retrieved from the column later are displayed using the
lettercase that was used in the column definition.
If you retrieve an ENUM value 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 column, 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 define an ENUM column with enumeration values
that look like numbers, because this can easily become confusing. For example,
the following column has enumeration members with string values of
'0', '1', and '2',
but numeric index values of
1, 2, and 3:
numbers ENUM('0','1','2')
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 CAST(col AS VARCHAR) or
GROUP BY CONCAT(col) to make sure that the column
is sorted lexically rather than by index number.
If you want to determine all possible values for an ENUM column,
use SHOW COLUMNS FROM tbl_name LIKE enum_col and parse
the ENUM definition in the second column of the output.
SET Type
A SET is a string object that can have zero or more values, each of
which must be chosen from a list of allowed values specified when the table
is created. SET column values that consist of multiple 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 MySQL 3.23.51, trailing spaces are automatically deleted from
SET member 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. For a column specified as
SET('a','b','c','d'), the members have the following decimal and
binary values:
SET Member | Decimal Value | Binary Value |
'a' | 1 | 0001
|
'b' | 2 | 0010
|
'c' | 4 | 0100
|
'd' | 8 | 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. If a column is specified as
SET('a','b','c','d'), then 'a,d', 'd,a', and
'd,a,a,d,d' all will 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 search for SET values using
the FIND_IN_SET() function or the LIKE operator:
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
The first statement finds rows where set_col contains the value set member. The second is similar, but not the same: It finds rows where set_col contains value anywhere, even as a substring of another set member.
The following statements also are legal:
mysql> SELECT * FROM tbl_name WHERE set_col & 1; mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
The first of these statements looks for values containing the first set
member. The second looks for an exact match. Be careful with comparisons
of the second type. Comparing set values to 'val1,val2' will
return different results than comparing values to 'val2,val1'.
You should specify the values in the same order they are listed in the
column definition.
If you want to determine all possible values for a SET column,
use SHOW COLUMNS FROM tbl_name LIKE set_col and parse
the SET definition in the second column of the output.
The storage requirements for each of the column types supported by MySQL are listed by category.
The maximum size of a row in a MyISAM table is 65,534 bytes. Each
BLOB and TEXT column accounts for only five to nine bytes
toward this size.
If a MyISAM or ISAM table includes any variable-length column
types, the record format will also be variable length. 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. See section 13.2.6.1 Silent Column Specification Changes.
| Column Type | Storage Required |
TINYINT | 1 byte |
SMALLINT | 2 bytes |
MEDIUMINT | 3 bytes |
INT, INTEGER | 4 bytes |
BIGINT | 8 bytes |
FLOAT(p) | 4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53 |
FLOAT | 4 bytes |
DOUBLE [PRECISION], item REAL | 8 bytes |
DECIMAL(M,D), NUMERIC(M,D) | M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
|
| Column Type | Storage Required |
DATE | 3 bytes |
DATETIME | 8 bytes |
TIMESTAMP | 4 bytes |
TIME | 3 bytes |
YEAR | 1 byte |
| Column Type | Storage Required |
CHAR(M) | M bytes, 0 <= M <= 255
|
VARCHAR(M) | L+1 bytes, where L <= M and 0 <= M <= 255
|
TINYBLOB, TINYTEXT | L+1 bytes, where L < 2^8 |
BLOB, TEXT | L+2 bytes, where L < 2^16 |
MEDIUMBLOB, MEDIUMTEXT | L+3 bytes, where L < 2^24 |
LONGBLOB, LONGTEXT | L+4 bytes, where L < 2^32 |
ENUM('value1','value2',...) | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
SET('value1','value2',...) | 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) |
VARCHAR and the BLOB and TEXT types are variable-length
types. For each, 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. See section 11.4.3 The BLOB and TEXT Types.
The size of an ENUM object is determined by the number of
different enumeration values. One byte is used for enumerations with up
to 255 possible values. Two bytes are used for enumerations with up to
65,535 values. See section 11.4.4 The ENUM Type.
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. See section 11.4.5 The SET Type.
For the most efficient use of storage, try to use the most precise type in
all cases. For example, if an integer column will be used for values in the
range from 1 to 99999, MEDIUMINT UNSIGNED is the
best type. Of the types that represent all the required values, it uses
the least amount of storage.
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. (Calculations on DECIMAL values may
still be done using double-precision operations, however.) 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.
To make it easier to use code written for SQL implementations from other vendors, MySQL maps column types as shown in the following table. These mappings make it easier to import table definitions from other database engines into MySQL:
| Other Vendor Type | MySQL Type |
BINARY(M) | CHAR(M) BINARY (before MySQL 4.1.2)
|
CHAR VARYING(M) | VARCHAR(M)
|
FLOAT4 | FLOAT
|
FLOAT8 | DOUBLE
|
INT1 | TINYINT
|
INT2 | SMALLINT
|
INT3 | MEDIUMINT
|
INT4 | INT
|
INT8 | BIGINT
|
LONG VARBINARY | MEDIUMBLOB
|
LONG VARCHAR | MEDIUMTEXT
|
LONG | MEDIUMTEXT (MySQL 4.1.0 on)
|
MIDDLEINT | MEDIUMINT
|
VARBINARY(M) | VARCHAR(M) BINARY (before MySQL 4.1.2)
|
As of MySQL 4.1.2, BINARY and VARBINARY are distinct data types
and are not converted to CHAR BINARY and VARCHAR BINARY.
Column type mapping occurs at table creation time, after which the original
type specifications are discarded. 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.
Go to the first, previous, next, last section, table of contents.