Table of Contents
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 supports extensions for handing spatial data. Information about spatial types is provided in Chapter 19, Spatial Extensions in MySQL.
Several of the column type descriptions use these conventions:
Indicates the maximum display width. The maximum legal display width is 255.
Applies to floating-point and fixed-point types and indicates
the number of digits following the decimal point. The maximum
possible value is 30, but should be no greater than
M-2.
Square brackets (‘[’ and
‘]’) indicate parts of type
specifiers that are optional.
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.
SERIAL is an alias for BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
SERIAL DEFAULT VALUE in the definition of an
integer column is an alias for NOT NULL AUTO_INCREMENT
UNIQUE.
Warning: You should be aware
that when you use subtraction between integer values where one
is of type UNSIGNED, the result is unsigned.
See Section 12.8, “Cast Functions and Operators”.
A bit-field type. M indicates the
number of bits per value, from 1 to 64. The default is 1 if
M is omitted.
TINYINT[(
M)] [UNSIGNED]
[ZEROFILL]
A very small integer. The signed range is
-128 to 127. The
unsigned range is 0 to
255.
These are synonyms for TINYINT(1). 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]
A small integer. The signed range is
-32768 to 32767. The
unsigned range is 0 to
65535.
MEDIUMINT[(
M)]
[UNSIGNED] [ZEROFILL]
A medium-size integer. The signed range is
-8388608 to 8388607.
The unsigned range is 0 to
16777215.
INT[(
M)] [UNSIGNED]
[ZEROFILL]
A normal-size integer. The signed range is
-2147483648 to
2147483647. The unsigned range is
0 to 4294967295.
INTEGER[(
M)] [UNSIGNED]
[ZEROFILL]
This is a synonym for INT.
BIGINT[(
M)] [UNSIGNED]
[ZEROFILL]
A large integer. The signed range is
-9223372036854775808 to
9223372036854775807. The unsigned range
is 0 to
18446744073709551615.
Some things you should be aware of with respect to
BIGINT columns:
All arithmetic is done using signed
BIGINT or DOUBLE
values, so you 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 can handle BIGINT in the
following cases:
When using integers to store big unsigned values in
a BIGINT column.
In
MIN(
or
col_name)MAX(,
where col_name)col_name refers to
a BIGINT column.
When using operators (+,
-, *, and so
on) where both operands are integers.
You can always store an exact integer value in a
BIGINT column by storing it using a
string. In this case, MySQL performs a string-to-number
conversion that involves no intermediate
double-precision representation.
The -, +, and
* operators use
BIGINT arithmetic when both operands
are integer values. This means that if you multiply two
big integers (or results from functions that return
integers), you may get unexpected results when the
result is larger than
9223372036854775807.
FLOAT[(
M,D)]
[UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Allowable
values are -3.402823466E+38 to
-1.175494351E-38, 0,
and 1.175494351E-38 to
3.402823466E+38. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M is the total number of decimal
digits and D is the number of
digits following the decimal point. If
M and
D are omitted, values are stored
to the limits allowed by the hardware. A single-precision
floating-point number is accurate to approximately 7 decimal
places.
If UNSIGNED is specified, negative values
are disallowed.
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”.
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. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
M is the total number of decimal
digits and D is the number of
digits following the decimal point. If
M and
D are omitted, values are stored
to the limits allowed by the hardware. A double-precision
floating-point number is accurate to approximately 15
decimal places.
If UNSIGNED is specified, negative values
are disallowed.
DOUBLE
PRECISION[(,
M,D)]
[UNSIGNED] [ZEROFILL]REAL[(
M,D)]
[UNSIGNED] [ZEROFILL]
These are synonyms for DOUBLE. Exception:
If the server SQL mode includes the
REAL_AS_FLOAT option,
REAL is a synonym for
FLOAT rather than
DOUBLE.
FLOAT(
p) [UNSIGNED]
[ZEROFILL]
A floating-point number. p
represents the precision in bits, but MySQL uses this value
only to determine whether to give the resulting column a
data type of FLOAT or
DOUBLE. If p
is from 0 to 24, the data type becomes
FLOAT with no
M or D
values. If p is from 25 to 53,
the data type becomes DOUBLE with no
M or D
values. The range of the resulting column is the same as for
the single-precision FLOAT or
double-precision DOUBLE data types
described earlier in this section.
DECIMAL[(
M[,D])]
[UNSIGNED] [ZEROFILL]
A packed “exact” fixed-point number.
M is the total number of decimal
digits (the precision) and D is
the number of digits after the decimal point (the scale).
The decimal point and (for negative numbers) the
‘-’ sign are not counted in
M. If
D is 0, values have no decimal
point or fractional part. The maximum number of digits
(M) for
DECIMAL is 65. The maximum number of
supported decimals (D) is 30. If
D is omitted, the default is 0.
If M is omitted, the default is
10.
If UNSIGNED is specified, negative values
are disallowed.
All basic calculations (+, -, *, /) with
DECIMAL columns are done with a precision
of 65 digits.
DEC[(,
M[,D])]
[UNSIGNED] [ZEROFILL]NUMERIC[(,
M[,D])]
[UNSIGNED] [ZEROFILL]FIXED[(
M[,D])]
[UNSIGNED] [ZEROFILL]
These are synonyms for DECIMAL. The
FIXED synonym is available 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”.
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.
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.
A timestamp. The range is '1970-01-01
00:00:00' to partway through the year
2037.
A TIMESTAMP column is useful for
recording the date and time of an INSERT
or UPDATE operation. The first
TIMESTAMP column in a table is
automatically set to the date and time of the most recent
operation if you 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.
A TIMESTAMP value is returned as a string
in the format 'YYYY-MM-DD HH:MM:SS'whose
display width is fixed at 19 characters. If you want to
obtain the value as a number, you should add
+0 to the timestamp column.
Note: The
TIMESTAMP format that was used prior to
MySQL 4.1 is not supported in MySQL 5.1; see
MySQL 4.1 Reference Manual for information
regarding the old format.
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.
A year in two-digit or four-digit format. The default is
four-digit format. In four-digit format, the allowable
values are 1901 to
2155, and 0000. In
two-digit format, the allowable values are
70 to 69, representing
years from 1970 to 2069. MySQL displays
YEAR values in YYYY
format, but allows you to assign values to
YEAR columns using either strings or
numbers.
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.1.5.1, “Silent Column Specification Changes”.
MySQL 5.1 string data types include some features that you may not have encountered in working with versions of MySQL prior to 4.1:
Column definitions for many string data types can include a
CHARACTER SET attribute to specify the
character set and, optionally, a collation.
(CHARSET is a synonym for
CHARACTER SET.) These attributes apply to
CHAR, VARCHAR, the
TEXT types, ENUM, and
SET. For example:
CREATE TABLE t
(
c1 CHAR(20) CHARACTER SET utf8,
c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin
);
This table definition creates a column named
c1 that has a character set of
utf8 with the default collation for that
character set, and a column named c2 that
has a character set of latin1 and the
binary collation for the character set. The binary collation
is not case sensitive.
MySQL 5.1 interprets length specifications in character column definitions in character units. (Some earlier MySQL versions interpreted lengths in bytes.)
For CHAR, VARCHAR, and
the TEXT types, the
BINARY attribute causes the column to be
assigned the binary collation of the column character set.
Character column sorting and comparison are based on the
character set assigned to the column. For earalier versions,
sorting and comparison are based on the collation of the
server character set. For CHAR and
VARCHAR columns, you can declare the
column with the BINARY attribute to cause
sorting and comparison to use the underlying character code
values rather then a lexical ordering.
For more about character set support in MySQL 5.1, see Chapter 10, Character Set Support.
[NATIONAL] CHAR(
M)
[BINARY | ASCII | UNICODE]
A fixed-length string that is always right-padded with
spaces to the specified length when stored.
M represents the column length.
The range of M is 0 to 255
characters.
Note: Trailing spaces are
removed when CHAR values are retrieved.
If you attempt to set the length of a
CHAR greater than 255, the
CREATE TABLE or ALTER
TABLE statement in which this is done fails with
an error:
mysql>CREATE TABLE c1 (col1 INT, col2 CHAR(500));ERROR 1074 (42000): Column length too big for column 'col' (max = 255); use BLOB or TEXT instead mysql>SHOW CREATE TABLE c1;ERROR 1146 (42S02): Table 'test.c1' doesn't exist
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.
The BINARY attribute is shorthand for
specifying the binary collation of the column character set.
Sorting and comparison is based on numeric character values.
The column type CHAR BYTE is an alias for
CHAR BINARY. This is a compatibility
feature.
The ASCII attribute can be specified for
CHAR. It assigns the
latin1 character set.
The UNICODE attribute can be specified
for CHAR. It assigns the
ucs2 character set.
MySQL allows you to create a column of type
CHAR(0). This is mainly useful when you
have to be compliant with 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
This is a synonym for CHAR(1).
[NATIONAL] VARCHAR(
M)
[BINARY]
A variable-length string. M
represents the maximum column length. The range of
M is 0 to 65,535. (The maximum
actual length of a VARCHAR is determined
by the maximum row size and the character set you use. The
maximum effective length is 65,532
bytes.)
Note: MySQL 5.1 follows the standard SQL specification, and does not remove trailing spaces from VARCHAR values.
VARCHAR is shorthand for
CHARACTER VARYING.
The BINARY attribute is shorthand for
specifying the binary collation of the column character set.
Sorting and comparison is based on numeric character values.
VARCHAR is stored with a one-byte or
two-byte length prefix + data. The length prefix is two
bytes if the VARCHAR column is declared
with a length greater than 255.
The BINARY type is similar to the
CHAR type, but stores binary byte strings
rather than non-binary character strings.
The VARBINARY type is similar to the
VARCHAR type, but stores binary byte
strings rather than non-binary character strings.
A BLOB column with a maximum length of
255 (28 – 1) bytes.
A TEXT column with a maximum length of
255 (28 – 1) characters.
A BLOB column with a maximum length of
65,535 (216 – 1) bytes.
An optional length M can be given
for this type. If this is done, then MySQL will create the
column as the smallest BLOB type large
enough to hold values of M bytes.
A TEXT column with a maximum length of
65,535 (216 – 1)
characters.
An optional length M can be
given. Then MySQL will create the column as the smallest
TEXT type large enough to hold values
M characters long.
A BLOB column with a maximum length of
16,777,215 (224 – 1) bytes.
A TEXT column with a maximum length of
16,777,215 (224 – 1)
characters.
A BLOB column with a maximum length of
4,294,967,295 or 4GB (232 –
1) bytes. The maximum effective
(permitted) length of LONGBLOB columns
depends on the configured maximum packet size in the
client/server protocol and available memory.
A TEXT column with a maximum length of
4,294,967,295 or 4GB (232 –
1) characters. The maximum effective
(permitted) length of LONGTEXT columns
depends on the configured maximum packet size in the
client/server protocol and available memory.
An enumeration. A string object that can have only one
value, chosen from the list of values
',
value1'',
value2'..., NULL or the
special '' error value. An
ENUM column can have a maximum of 65,535
distinct values. ENUM values are
represented internally as integers.
A set. A string object that can have zero or more values,
each of which must be chosen from the list of values
',
value1'',
value2'... A SET column can
have a maximum of 64 members. SET values
are represented internally as integers.
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.
The BIT data type stores bit-field values and
is supported for MyISAM.
MEMORY, InnoDB, and
BDB tables.
As an extension to the SQL standard, MySQL also supports the
integer types TINYINT,
MEDIUMINT, and BIGINT. The
following table shows the required storage and range for each of
the integer types.
| Type | Bytes | Minimum Value | Maximum Value |
| (Signed/Unsigned) | (Signed/Unsigned) | ||
TINYINT | 1 | -128 | 127 |
0 | 255 | ||
SMALLINT | 2 | -32768 | 32767 |
0 | 65535 | ||
MEDIUMINT | 3 | -8388608 | 8388607 |
0 | 16777215 | ||
INT | 4 | -2147483648 | 2147483647 |
0 | 4294967295 | ||
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
0 | 18446744073709551615 |
Another extension is supported by MySQL for optionally specifying
the display width of an integer value in parentheses following the
base keyword for the type (for example,
INT(4)). This optional display width
specification is used to left-pad the display of values having a
width less than the width specified for the column.
The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.
When used in conjunction with the optional extension attribute
ZEROFILL, the default padding of spaces is
replaced with zeros. For example, for a column declared as
INT(5) ZEROFILL, a value of
4 is retrieved as 00004.
Note that if you store larger values than the display width in an
integer column, you may experience problems when MySQL generates
temporary tables for some complicated joins, because in these
cases MySQL trusts that the data did fit into the original column
width.
All integer types can have an optional (non-standard) attribute
UNSIGNED. Unsigned values can be used when you
want to allow only non-negative numbers in a column and you need a
bigger upper numeric range for the column.
Floating-point and fixed-point types also can be
UNSIGNED. As with integer types, this attribute
prevents negative values from being stored in the column. However,
unlike the integer types, the upper range of column values remains
the same.
If you specify ZEROFILL for a numeric column,
MySQL automatically adds the UNSIGNED attribute
to the column.
For floating-point 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 a
four-byte single-precision FLOAT column. A
precision from 24 to 53 results in an eight-byte double-precision
DOUBLE column.
MySQL allows a non-standard syntax:
FLOAT(
or
M,D)REAL(
or M,D)DOUBLE
PRECISION(.
Here,
“M,D)(”
means than values are displayed with up to
M,D)M digits in total, of which
D digits may be after the decimal
point. For example, a column defined as
FLOAT(7,4) will look like
-999.9999 when displayed. MySQL performs
rounding when storing values, so if you insert
999.00009 into a FLOAT(7,4)
column, the approximate result is 999.0001.
MySQL treats DOUBLE as a synonym for
DOUBLE PRECISION (a non-standard extension).
MySQL also treats REAL as a synonym for
DOUBLE PRECISION (a non-standard variation),
unless the server SQL mode includes the
REAL_AS_FLOAT option.
For maximum portability, code requiring storage of approximate
numeric data values should use FLOAT or
DOUBLE PRECISION with no specification of
precision or number of digits.
The DECIMAL and NUMERIC
types are implemented as the same type by MySQL. They are used to
store values for which it is important to preserve exact
precision, for example with monetary data. When declaring a column
of one of these types, the precision and scale can be (and usually
is) specified; for example:
salary DECIMAL(5,2)
In this example, 5 is the precision and
2 is the scale. The precision represents the
number of significant digits that are stored for values, and the
scale represents the number of digits that can be stored following
the decimal point.
MySQL 5.1 stores DECIMAL and
NUMERIC values in binary format.
Standard SQL requires that the salary column be
able to store any value with five digits and two decimals. In this
case, therefore, the range of values that can be stored in the
salary column is from
-999.99 to 999.99.
In standard SQL, the syntax
DECIMAL( is
equivalent to
M)DECIMAL(.
Similarly, the syntax M,0)DECIMAL is equivalent to
DECIMAL(, where
the implementation is allowed to decide the value of
M,0)M. Both of these variant forms of the
DECIMAL and NUMERIC data
types are supported in MySQL 5.1. The default value
of M is 10.
The maximum number of digits for DECIMAL or
NUMERIC is 65, 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.)
The BIT data type can be used to store
bit-field values. A type of
BIT( allows for
storage of M)M-bit values.
M can range from 1 to 64.
To specify bit values,
b' notation
can be used. value'value is a binary value
written using zeroes and ones. For example,
b'111' and b'100000000'
represent 7 and 128, respectively. See
Section 9.1.5, “Bit-Field Values”.
If you assign a value to a
BIT( column that
is less than M)M bits long, the value is
padded on the left with zeroes. For example, assigning a value of
b'101' to a BIT(6) column
is, in effect, the same as assigning b'000101'.
When asked to store a value in a numeric column that is outside
the column type's allowable range, MySQL's behavior depends on the
SQL mode in effect at the time. If the mode is not set, MySQL
clips the value to the appropriate endpoint of the range and
stores the resulting value instead. However, if the mode is set to
traditional (“strict mode”), a
value that is out of range is rejected with an error, and the
insert fails, in accordance with the SQL standard. See
Section 5.3.2, “The Server SQL Mode”.
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 in non-strict mode.
When a floating-point or fixed-point column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL in non-strict mode stores the value representing the corresponding end point of that range.
Conversions that occur due to clipping when MySQL is not operating
in strict mode are reported as warnings for ALTER
TABLE, LOAD DATA INFILE,
UPDATE, and multiple-row
INSERT statements. When MySQL is operating in
strict mode, these statements fail, and some or all of the values
will not be inserted or changed, depending on whether the table is
a transactional table and other factors. See
Section 5.3.2, “The Server SQL Mode” for details.
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 may be used
when you specify an illegal value that MySQL cannot represent. The
TIMESTAMP type has special automatic updating
behavior, described later on.
MySQL gives warnings or 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. This is useful
when you want to store a “possibly wrong” value which
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 do not 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 (and uses less space in data and index) 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.3.2, “The Server SQL Mode”.
Here are some general considerations to keep in mind when working with date and time types:
MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). Only the formats described in the following sections are supported. It is expected that you supply legal values. Unpredictable results may occur if you use values in other formats.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:
Year values in the range 70-99 are
converted to 1970-1999.
Year values in the range 00-69 are
converted to 2000-2069.
Although MySQL tries to interpret values in several formats,
dates always must be given in year-month-day order (for
example, '98-09-04'), rather than in the
month-day-year or day-month-year orders commonly used
elsewhere (for example, '09-04-98',
'04-09-98').
MySQL automatically converts a date or time type value to a number if the value is used in a numeric context and vice versa.
When MySQL encounters a value for a date or time type that is
out of range or otherwise illegal for the type (as described
at the beginning of this section), it converts the value to
the “zero” value for that type. The exception is
that out-of-range TIME values are clipped
to the appropriate endpoint of the TIME
range.
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 |
The “zero” values are special, but you can store
or refer to them explicitly using the values shown in the
table. You can also do this using the values
'0' or 0, which are
easier to write.
“Zero” date or time values used through MyODBC
are converted automatically to NULL in
MyODBC 2.50.12 and above, because ODBC cannot handle such
values.
The DATETIME, DATE, and
TIMESTAMP types are related. This section
describes their characteristics, how they are similar, and how
they differ.
The DATETIME type is used when you need
values that contain both date and time information. MySQL
retrieves and displays DATETIME values in
'YYYY-MM-DD HH:MM:SS' format. The supported
range is '1000-01-01 00:00:00' to
'9999-12-31 23:59:59'.
(“Supported” means that although earlier values
might work, there is no guarantee)
The DATE type is used when you need only a
date value, without a time part. MySQL retrieves and displays
DATE values in
'YYYY-MM-DD' format. The supported range is
'1000-01-01' to
'9999-12-31'.
The TIMESTAMP 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:
As a string in either 'YYYY-MM-DD
HH:MM:SS' or 'YY-MM-DD
HH:MM:SS' format. A “relaxed” syntax
is allowed: Any punctuation character may be used as the
delimiter between date parts or time parts. For example,
'98-12-31 11:30:45', '98.12.31
11+30+45', '98/12/31 11*30*45',
and '98@12@31 11^30^45' are equivalent.
As a string in either 'YYYY-MM-DD' or
'YY-MM-DD' format. A
“relaxed” syntax is allowed here, too. For
example, '98-12-31',
'98.12.31',
'98/12/31', and
'98@12@31' are equivalent.
As a string with no delimiters in either
'YYYYMMDDHHMMSS' or
'YYMMDDHHMMSS' format, provided that the
string makes sense as a date. For example,
'19970523091528' and
'970523091528' are interpreted as
'1997-05-23 09:15:28', but
'971122129015' is illegal (it has a
nonsensical minute part) and becomes '0000-00-00
00:00:00'.
As a string with no delimiters in either
'YYYYMMDD' or 'YYMMDD'
format, provided that the string makes sense as a date. For
example, '19970523' and
'970523' are interpreted as
'1997-05-23', but
'971332' is illegal (it has nonsensical
month and day parts) and becomes
'0000-00-00'.
As a number in either YYYYMMDDHHMMSS or
YYMMDDHHMMSS format, provided that the
number makes sense as a date. For example,
19830905132800 and
830905132800 are interpreted as
'1983-09-05 13:28:00'.
As a number in either YYYYMMDD or
YYMMDD format, provided that the number
makes sense as a date. For example,
19830905 and 830905
are interpreted as '1983-09-05'.
As the result of a function that returns a value that is
acceptable in a DATETIME,
DATE, or TIMESTAMP
context, such as NOW() or
CURRENT_DATE.
Illegal DATETIME, DATE, or
TIMESTAMP values are converted to the
“zero” value of the appropriate type
('0000-00-00 00:00:00',
'0000-00-00', or
00000000000000).
For values specified as strings that include date part
delimiters, it is not necessary to specify two digits for month
or day values that are less than 10.
'1979-6-9' is the same as
'1979-06-09'. Similarly, for values specified
as strings that include time part delimiters, it is not
necessary to specify two digits for hour, minute, or second
values that are less than 10.
'1979-10-30 1:2:3' is the same as
'1979-10-30 01:02:03'.
Values specified as numbers should be 6, 8, 12, or 14 digits
long. If a number is 8 or 14 digits long, it is assumed to be in
YYYYMMDD or YYYYMMDDHHMMSS
format and that the year is given by the first 4 digits. If the
number is 6 or 12 digits long, it is assumed to be in
YYMMDD or YYMMDDHHMMSS
format and that the year is given by the first 2 digits. Numbers
that are not one of these lengths are interpreted as though
padded with leading zeros to the closest length.
Values specified as non-delimited strings are interpreted using
their length as given. If the string is 8 or 14 characters long,
the year is assumed to be given by the first 4 characters.
Otherwise, the year is assumed to be given by the first 2
characters. The string is interpreted from left to right to find
year, month, day, hour, minute, and second values, for as many
parts as are present in the string. This means you should not
use strings that have fewer than 6 characters. For example, if
you specify '9903', thinking that represents
March, 1999, MySQL inserts a “zero” date value 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, you can explicitly specify a value of zero to represent
missing month or day parts. For example, you can use
'990300' to insert the value
'1999-03-00'.
You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:
If you assign a DATE value to a
DATETIME or TIMESTAMP
object, the time part of the resulting value is set to
'00:00:00' because the
DATE value contains no time information.
If you assign a DATETIME or
TIMESTAMP value to a
DATE object, the time part of the
resulting value is deleted because the
DATE type stores no time information.
Remember that although DATETIME,
DATE, and TIMESTAMP
values all can be specified using the same set of formats,
the types do not all have the same range of values. For
example, TIMESTAMP values cannot be
earlier than 1970 or later than
2037. This means that a date such as
'1968-01-01', while legal as a
DATETIME or DATE
value, is not a valid TIMESTAMP value and
is converted to 0 if assigned to such an
object.
Be aware of certain pitfalls when specifying date values:
The relaxed format allowed for values specified as strings
can be deceiving. For example, a value such as
'10:11:12' might look like a time value
because of the ‘:’ delimiter,
but if used in a date context is interpreted as the year
'2010-11-12'. The value
'10:45:15' is converted to
'0000-00-00' because
'45' is not a legal month.
In non-strict mode, the MySQL server performs only basic
checking on the validity of a date: The ranges for year,
month, and day are 1000 to 9999, 00 to 12, and 00 to 31,
respectively. Any date containing parts not within these
ranges is subject to conversion to
'0000-00-00'. Please note that this still
allows you to store invalid dates such as
'2002-04-31'. To ensure that a date is
valid when not using strict mode, you should perform a check
in your application.
In strict mode, invalid dates are not accepted, and not converted.
See Section 5.3.2, “The Server SQL Mode” for more information.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:
Year values in the range 00-69 are
converted to 2000-2069.
Year values in the range 70-99 are
converted to 1970-1999.
Note: In older versions of
MySQL (prior to 4.1), the properties of the
TIMESTAMP column type differed
significantly in many ways from what is described in this
section. If you need to convert older
TIMESTAMP data to work with MySQL
5.1, be sure to see the
MySQL 4.1 Reference Manual for details.
TIMESTAMP columns are displayed in the same
format as DATETIME columns. In other words,
the display width is fixed at 19 characters, and the format is
YYYY-MM-DD HH:MM:SS.
The MySQL server can be also 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;
MySQL does not accept timestamp values that include a zero in
the day or month column or values that are not a valid date.
The sole exception to this rule is the special value
'0000-00-00 00:00:00'.
You have considerable flexibility in determining when
automatic TIMESTAMP initialization and
updating occur and which column should have those behaviors:
You can assign the current timestamp as the default value and the auto-update value, as before. But it is possible to have just one automatic behavior or the other, or neither of them. (It is not possible to have one behavior for one column and the other for another column.)
You can specify which TIMESTAMP column
to automatically initialize or update to the current date
and time. This no longer need be the first
TIMESTAMP column.
Note that the information in the following discussion applies
to TIMESTAMP columns only for tables not
created with MAXDB mode enabled. (As noted
above, MAXDB mode causes columns to be
created as DATETIME columns.) The rules
governing initialisation and updating of
TIMESTAMP columns 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:
With both DEFAULT CURRENT_TIMESTAMP
and ON UPDATE CURRENT_TIMESTAMP
clauses, the column has the current timestamp for its
default value, and is automatically updated.
With neither DEFAULT nor
ON UPDATE clauses, it is the same
as DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP.
With a DEFAULT CURRENT_TIMESTAMP
clause and no ON UPDATE clause, the
column has the current timestamp for its default value
but is not automatically updated.
With no DEFAULT clause and with an
ON UPDATE CURRENT_TIMESTAMP clause,
the column has a default of 0 and is automatically
updated.
With a constant DEFAULT value, the
column has the given default. If the column has an
ON UPDATE CURRENT_TIMESTAMP clause,
it is automatically updated, otherwise not.
In other words, you can use the current timestamp for both
the initial value and the auto-update value, or either
one, or neither. (For example, you can specify ON
UPDATE to enable 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 so, then
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);
You can set the current time zone on a per-connection basis,
as described in Section 5.10.8, “MySQL Server Time Zone Support”.
TIMESTAMP values are stored in UTC, being
converted from the current time zone for storage, and
converted back to the current time zone upon retrieval. As
long as the time zone setting remains constant, you get back
the same value you store. If you store a
TIMESTAMP value, change the time zone and
then retrieve the value, it is different than the value you
stored. This occurs because the same time zone was not used
for conversion in both directions. The current time zone is
available as the value of the time_zone
system variable.
You can include the NULL attribute in the
definition of a TIMESTAMP column to allow
the column to contain NULL values. For
example:
CREATE TABLE t ( ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP );
If the NULL attribute is not specified,
setting the column to NULL sets it to the
current timestamp. Note that a TIMESTAMP
column which allows NULL values will
not take on the current
timestamp unless either its default value is defined as
CURRENT_TIMESTAMP,
or either
NOW() or
CURRENT_TIMESTAMP is inserted into the
column. In other words, a TIMESTAMP column
defined as NULL will auto-update only if it
is created using a definition such as the following:
CREATE TABLE t (ts NULL DEFAULT CURRENT_TIMESTAMP);
Otherwise - that is, if the TIMESTAMP
column is defined using NULL but not using
DEFAULT TIMESTAMP, as shown here...
CREATE TABLE t1 (ts NULL DEFAULT NULL); CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00');
...then you must explicitly insert a value corresponding to the current date and time. For example:
INSERT INTO t1 VALUES (NOW()); INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
MySQL retrieves and displays TIME values in
'HH:MM:SS' format (or
'HHH:MM:SS' format for large hours values).
TIME values may range from
'-838:59:59' to
'838:59:59'. The reason for which the hours
part may be so large is that the TIME type
may be used not only to represent a time of day (which must be
less than 24 hours), but 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:
As a string in 'D HH:MM:SS.fraction'
format. You can also use one of the following
“relaxed” syntaxes:
'HH:MM:SS.fraction',
'HH:MM:SS', 'HH:MM',
'D HH:MM:SS', 'D
HH:MM', 'D HH', or
'SS'. Here D
represents days and can have a value from 0 to 34. Note that
MySQL does not (yet) store the fraction.
As a string with no delimiters in
'HHMMSS' format, provided that it makes
sense as a time. For example, '101112' is
understood as '10:11:12', but
'109712' is illegal (it has a nonsensical
minute part) and becomes '00:00:00'.
As a number in HHMMSS format, provided
that it makes sense as a time. For example,
101112 is understood as
'10:11:12'. The following alternative
formats are also understood: SS,
MMSS, HHMMSS,
HHMMSS.fraction. Note that MySQL doesn't
(yet) store the fraction.
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 abbreviated values to a
TIME column. Without colons, MySQL interprets
values using the assumption that the two rightmost digits
represent seconds. (MySQL interprets TIME
valuesas 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' 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.
The YEAR type is a one-byte type used for
representing years.
MySQL retrieves and displays YEAR values in
YYYY format. The range is
1901 to 2155.
You can specify YEAR values in a variety of
formats:
As a four-digit string in the range
'1901' to '2155'.
As a four-digit number in the range 1901
to 2155.
As a two-digit string in the range '00'
to '99'. Values in the ranges
'00' to '69' and
'70' to '99' are
converted to YEAR values in the ranges
2000 to 2069 and
1970 to 1999.
As a two-digit number in the range 1 to
99. Values in the ranges
1 to 69 and
70 to 99 are converted
to YEAR values in the ranges
2001 to 2069 and
1970 to 1999. Note
that the range for two-digit numbers is slightly different
from the range for two-digit strings, because you cannot
specify zero directly as a number and have it be interpreted
as 2000. You must specify it as a string
'0' or '00' or it is
interpreted as 0000.
As the result of a function that returns a value that is
acceptable in a YEAR context, such as
NOW().
Illegal YEAR values are converted to
0000.
MySQL itself is year 2000 (Y2K) safe (see Section 1.4.5, “Year 2000 Compliance”), but input values presented to MySQL may not be. Any input containing two-digit year values is ambiguous, because the century is unknown. Such values must be interpreted into four-digit form because MySQL stores years internally using four digits.
For DATETIME, DATE,
TIMESTAMP, and YEAR types,
MySQL interprets dates with ambiguous year values using the
following rules:
Year values in the range 00-69 are
converted to 2000-2069.
Year values in the range 70-99 are
converted to 1970-1999.
Remember that these rules provide only reasonable guesses as to what your data values mean. If the heuristics used by MySQL do not produce the correct values, you should provide unambiguous input containing four-digit year values.
ORDER BY properly sorts
TIMESTAMP or YEAR values
that have two-digit years.
Some functions like MIN() and
MAX() convert a TIMESTAMP
or YEAR to a number. This means that a value
with a two-digit year does not work properly with these
functions. The fix in this case is to convert the
TIMESTAMP or YEAR to
four-digit year format or use something like
MIN(DATE_ADD(timestamp,INTERVAL 0 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.
The CHAR and VARCHAR types
are similar, but differ in the way they are stored and
retrieved. They also differ in maximum length and in whether
trailing spaces are retained. No lettercase conversion takes
place during storage or retrieval.
The CHAR and VARCHAR types
are declared with a length that indicates the maximum number of
characters you want to store. For example,
CHAR(30) can hold up to 30 characters.
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. When CHAR
values are stored, they are right-padded with spaces to the
specified length. When CHAR values are
retrieved, trailing spaces are removed. No lettercase conversion
takes place during storage or retrieval.
Values in VARCHAR columns are variable-length
strings. The length can be specified as a value from 0 to
65,535. (The maximum effective length of a
VARCHAR is determined by the maximum row size
and the character set used. The maximum length overall is 65,532
bytes.)
In contrast to CHAR,
VARCHAR values are stored using only as many
characters as are needed, plus one byte to record the length
(two bytes for columns that are declared with a length longer
than 255).
VARCHAR values are not padded when they are
stored. Trailing spaces are retained when values are stored and
retrieved, in conformance with standard SQL.
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 the truncated
characters are not spaces, a warning is generated. For
truncation of non-space characters, you can cause an error to
occur (rather than a warning) and suppress insertion of the
value by using strict SQL mode. See
Section 5.3.2, “The Server SQL Mode”.
The following table illustrates the differences between
CHAR and VARCHAR 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 |
Note that the values shown as stored in the last row of the table apply only when not using strict mode; if MySQL is running in strict mode, values that exceed the column length are not stored, and an error results.
The values retrieved from the CHAR(4) and
VARCHAR(4) columns are not always the same,
because trailing spaces are removed from CHAR
columns upon retrieval. This difference is illustrated by the
following example:
mysql>CREATE TABLE vc (v VARCHAR(4), c CHAR(4));Query OK, 0 rows affected (0.02 sec) mysql>INSERT INTO vc VALUES ('ab ', 'ab ');Query OK, 1 row affected (0.00 sec) mysql>SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;+----------------+----------------+ | CONCAT(v, '+') | CONCAT(c, '+') | +----------------+----------------+ | ab + | ab+ | +----------------+----------------+ 1 row in set (0.00 sec)
Values in CHAR and VARCHAR
columns are sorted and compared according to the character set
collation assigned to the column.
Note that all MySQL collations are of type
PADSPACE. This means that all
CHAR and VARCHAR values in
MySQL are compared without regard to any trailing spaces. For
example:
mysql>CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));Query OK, 0 rows affected (0.09 sec) mysql>INSERT INTO names VALUES ('Monty ', 'Monty ');Query OK, 1 row affected (0.00 sec) mysql>SELECT myname = 'Monty ', yourname = 'Monty ' FROM names;+--------------------+----------------------+ | myname = 'Monty ' | yourname = 'Monty ' | +--------------------+----------------------+ | 1 | 1 | +--------------------+----------------------+ 1 row in set (0.00 sec)
Note that this is true for all MySQL versions, and that it is not affected by the server SQL mode.
For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error.
CHAR BYTE is an alias for CHAR
BINARY. This is a compatibility feature.
The ASCII attribute assigns the
latin1 character set to a
CHAR column. The UNICODE
attribute assigns the ucs2 character set.
The BINARY and VARBINARY
types are similar to 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 that they
have no character set, and sorting and comparison is based on
the numeric values of the bytes in column values.
The allowable maximum length is the same for
BINARY and VARBINARY as it
is for CHAR and VARCHAR,
except that the length for BINARY and
VARBINARY is a length in bytes rather than in
characters.
The BINARY and VARBINARY
data types are distinct from the CHAR BINARY
and VARCHAR BINARY data types. For the latter
types, 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, and the column itself contains non-binary character
strings rather than binary byte strings. For example
CHAR(5) BINARY is treated as CHAR(5)
CHARACTER SET latin1 COLLATE latin1_bin, assuming that
the default character set is latin1. This
differs from BINARY(5), which stores 5-bytes
binary strings that have no character set or collation.
When BINARY values are stored, they are
right-padded with the pad value to the specified length. The pad
value is 0x00 (the zero byte). Values are
right-padded with 0x00 on insert, and no
trailing bytes are removed on select. All bytes are significant
in comparisons, including ORDER BY and
DISTINCT operations. 0x00
bytes and spaces are different in comparisons, with
0x00 < space.
Example: For a BINARY(3) column,
'a ' becomes
'a \0' when inserted.
'a\0' becomes 'a\0\0' when
inserted. Both inserted values remain unchanged when selected.
For VARBINARY, there is no padding on insert
and no bytes are stripped on select. All bytes are significant
in comparisons, including ORDER BY and
DISTINCT operations. 0x00
bytes and spaces are different in comparisons, with
0x00 < space.
For those cases where trailing pad bytes are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad bytes will result in a duplicate-key error.
You should consider the preceding padding and stripping
characteristics carefully if you plan to use these data types
for storing binary data and you require that the value retrieved
be exactly the same as the value stored. The following example
illustrates how 0x00-padding of
BINARY values affects column value
comparisons:
mysql>CREATE TABLE t (c BINARY(3));Query OK, 0 rows affected (0.01 sec) mysql>INSERT INTO t SET c = 'a';Query OK, 1 row affected (0.01 sec) mysql>SELECT HEX(c), c = 'a', c = 'a\0\0' from t;+--------+---------+-------------+ | HEX(c) | c = 'a' | c = 'a\0\0' | +--------+---------+-------------+ | 610000 | 0 | 1 | +--------+---------+-------------+ 1 row in set (0.09 sec)
If the value retrieved must be the same as the value specified
for storage with no padding, it might be preferable to use one
of the BLOB data types instead.
MySQL may silently change the type of a
BINARY or VARBINARY column
at table creation time. See
Section 13.1.5.1, “Silent Column Specification Changes”.
A BLOB is a binary large object that can hold
a variable amount of data. The four BLOB
types are TINYBLOB, BLOB,
MEDIUMBLOB, and LONGBLOB.
These differ only in the maximum length of the values they can
hold.
The four TEXT types are
TINYTEXT, TEXT,
MEDIUMTEXT, and LONGTEXT.
These correspond to the four BLOB types and
have the same maximum lengths and storage requirements.
See Section 11.5, “Column Type Storage Requirements”.
BLOB columns are treated as binary strings
(byte strings). TEXT columns are treated as
non-binary strings (character strings). BLOB
columns have no character set, and sorting and comparison is
based on the numeric values of the bytes in column values.
TEXT columns have a character set, and values
are sorted and compared based on the collation of the character
set.
No lettercase conversion for TEXT or
BLOB columns takes place during storage or
retrieval.
When not running in strict mode, 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. If the truncated characters are not spaces, a warning is
generated. You can cause an error to occur and the value to be
rejected rather than to be truncated with awarning by using
strict SQL mode. See
Section 5.3.2, “The Server SQL Mode”.
In most respects, you can regard a BLOB
column as a VARBINARY column that can be as
large as you like. Similarly, you can regard a
TEXT column as a VARCHAR
column. BLOB and TEXT
differ from VARBINARY and
VARCHAR in the following ways::
There is no trailing-space removal for
BLOB and TEXT columns
when values are stored or retrieved. (This is the same as
with VARBINARY and
VARCHAR columns.)
Note that TEXT is on comparison space
extended to fit the compared object, exactly like
CHAR and VARCHAR.
For indexes on BLOB and
TEXT columns, you must specify an index
prefix length. For CHAR and
VARCHAR, a prefix length is optional. See
Section 7.4.3, “Column Indexes”.
LONG and LONG VARCHAR map
to the MEDIUMTEXT data type. This is a
compatibility feature. If you use the BINARY
attribute with a TEXT column type, the column
is assigned the binary collation of the column character set.
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:
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 --max_sort_length
option when starting the mysqld server.
See Section 5.3.3, “Server System Variables”.
You can make more bytes significant 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_nameORDER BY comment;