<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 10. Data Types</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="prev" href="internationalization-localization.html" title="Chapter 9. Internationalization and Localization"><link rel="next" href="functions.html" title="Chapter 11. Functions and Operators"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 10. Data Types</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="internationalization-localization.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="functions.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="data-types"></a>Chapter 10. Data Types</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="data-types.html#data-type-overview">10.1. Data Type Overview</a></span></dt><dd><dl><dt><span class="section"><a href="data-types.html#numeric-type-overview">10.1.1. Overview of Numeric Types</a></span></dt><dt><span class="section"><a href="data-types.html#date-and-time-type-overview">10.1.2. Overview of Date and Time Types</a></span></dt><dt><span class="section"><a href="data-types.html#string-type-overview">10.1.3. Overview of String Types</a></span></dt><dt><span class="section"><a href="data-types.html#data-type-defaults">10.1.4. Data Type Default Values</a></span></dt></dl></dd><dt><span class="section"><a href="data-types.html#numeric-types">10.2. Numeric Types</a></span></dt><dt><span class="section"><a href="data-types.html#date-and-time-types">10.3. Date and Time Types</a></span></dt><dd><dl><dt><span class="section"><a href="data-types.html#datetime">10.3.1. The <code class="literal">DATETIME</code>,
<code class="literal">DATE</code>, and
<code class="literal">TIMESTAMP</code> Types</a></span></dt><dt><span class="section"><a href="data-types.html#time">10.3.2. The <code class="literal">TIME</code> Type</a></span></dt><dt><span class="section"><a href="data-types.html#year">10.3.3. The <code class="literal">YEAR</code> Type</a></span></dt><dt><span class="section"><a href="data-types.html#y2k-issues">10.3.4. Year 2000 Issues and Date Types</a></span></dt></dl></dd><dt><span class="section"><a href="data-types.html#string-types">10.4. String Types</a></span></dt><dd><dl><dt><span class="section"><a href="data-types.html#char">10.4.1. The <code class="literal">CHAR</code> and
<code class="literal">VARCHAR</code> Types</a></span></dt><dt><span class="section"><a href="data-types.html#binary-varbinary">10.4.2. The <code class="literal">BINARY</code> and
<code class="literal">VARBINARY</code> Types</a></span></dt><dt><span class="section"><a href="data-types.html#blob">10.4.3. The <code class="literal">BLOB</code> and
<code class="literal">TEXT</code> Types</a></span></dt><dt><span class="section"><a href="data-types.html#enum">10.4.4. The <code class="literal">ENUM</code> Type</a></span></dt><dt><span class="section"><a href="data-types.html#set">10.4.5. The <code class="literal">SET</code> Type</a></span></dt></dl></dd><dt><span class="section"><a href="data-types.html#storage-requirements">10.5. Data Type Storage Requirements</a></span></dt><dt><span class="section"><a href="data-types.html#choosing-types">10.6. Choosing the Right Type for a Column</a></span></dt><dt><span class="section"><a href="data-types.html#other-vendor-data-types">10.7. Using Data Types from Other Database Engines</a></span></dt></dl></div><p>
MySQL supports a number of data types in several categories: numeric
types, date and time types, and string (character) types. This
chapter first gives an overview of these data types, and then
provides a more detailed description of the properties of the types
in each category, and a summary of the data type storage
requirements. The initial overview is intentionally brief. The more
detailed descriptions later in the chapter should be consulted for
additional information about particular data types, such as the
allowable formats in which you can specify values.
</p><p>
MySQL also supports extensions for handing spatial data.
<a href="functions.html#spatial-extensions" title="11.13. Spatial Extensions">Section 11.13, “Spatial Extensions”</a>, provides information about
these data types.
</p><a class="indexterm" name="id4327038"></a><a class="indexterm" name="id4327047"></a><a class="indexterm" name="id4327059"></a><a class="indexterm" name="id4327072"></a><p>
Data type descriptions use these conventions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id4327094"></a>
<a class="indexterm" name="id4327103"></a>
<a class="indexterm" name="id4327116"></a>
<a class="indexterm" name="id4327124"></a>
<em class="replaceable"><code>M</code></em> indicates the maximum display width
for integer types. For floating-point and fixed-point types,
<em class="replaceable"><code>M</code></em> is the total number of digits that
can be stored. For string types, <em class="replaceable"><code>M</code></em> is
the maximum length. The maximum allowable value of
<em class="replaceable"><code>M</code></em> depends on the data type.
</p></li><li><p>
<a class="indexterm" name="id4327159"></a>
<a class="indexterm" name="id4327168"></a>
<em class="replaceable"><code>D</code></em> 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 <em class="replaceable"><code>M</code></em>–2.
</p></li><li><p>
<a class="indexterm" name="id4327192"></a>
<a class="indexterm" name="id4327204"></a>
Square brackets (“<span class="quote"><code class="literal">[</code></span>” and
“<span class="quote"><code class="literal">]</code></span>”) indicate optional parts of
type definitions.
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="data-type-overview"></a>10.1. Data Type Overview</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="data-types.html#numeric-type-overview">10.1.1. Overview of Numeric Types</a></span></dt><dt><span class="section"><a href="data-types.html#date-and-time-type-overview">10.1.2. Overview of Date and Time Types</a></span></dt><dt><span class="section"><a href="data-types.html#string-type-overview">10.1.3. Overview of String Types</a></span></dt><dt><span class="section"><a href="data-types.html#data-type-defaults">10.1.4. Data Type Default Values</a></span></dt></dl></div><a class="indexterm" name="id4327239"></a><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="numeric-type-overview"></a>10.1.1. Overview of Numeric Types</h3></div></div></div><a class="indexterm" name="id4327259"></a><a class="indexterm" name="id4327268"></a><a class="indexterm" name="id4327277"></a><p>
A summary of the numeric data types follows. For additional
information about properties of the numeric types, see
<a href="data-types.html#numeric-types" title="10.2. Numeric Types">Section 10.2, “Numeric Types”</a>. Storage requirements are given
in <a href="data-types.html#storage-requirements" title="10.5. Data Type Storage Requirements">Section 10.5, “Data Type Storage Requirements”</a>.
</p><p>
<em class="replaceable"><code>M</code></em> indicates the maximum display width
for integer types. The maximum legal display width is 255.
Display width is unrelated to the range of values a type can
contain, as described in <a href="data-types.html#numeric-types" title="10.2. Numeric Types">Section 10.2, “Numeric Types”</a>. For
floating-point and fixed-point types,
<em class="replaceable"><code>M</code></em> is the total number of digits that
can be stored.
</p><p>
If you specify <code class="literal">ZEROFILL</code> for a numeric column,
MySQL automatically adds the <code class="literal">UNSIGNED</code>
attribute to the column.
</p><p>
Numeric data types that allow the <code class="literal">UNSIGNED</code>
attribute also allow <code class="literal">SIGNED</code>. However, these
data types are signed by default, so the
<code class="literal">SIGNED</code> attribute has no effect.
</p><p>
<code class="literal">SERIAL</code> is an alias for <code class="literal">BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE</code>.
</p><p>
<code class="literal">SERIAL DEFAULT VALUE</code> in the definition of an
integer column is an alias for <code class="literal">NOT NULL AUTO_INCREMENT
UNIQUE</code>.
</p><div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Warning</h3><p>
When you use subtraction between integer values where one is
of type <code class="literal">UNSIGNED</code>, the result is unsigned
unless the
<a href="server-administration.html#sqlmode_no_unsigned_subtraction"><code class="literal">NO_UNSIGNED_SUBTRACTION</code></a> SQL
mode is enabled. See <a href="functions.html#cast-functions" title="11.9. Cast Functions and Operators">Section 11.9, “Cast Functions and Operators”</a>.
</p></div><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id4327430"></a>
<a class="indexterm" name="id4327439"></a>
<code class="literal">BIT[(<em class="replaceable"><code>M</code></em>)]</code>
</p><p>
A bit-field type. <em class="replaceable"><code>M</code></em> indicates the
number of bits per value, from 1 to 64. The default is 1 if
<em class="replaceable"><code>M</code></em> is omitted.
</p></li><li><p>
<a class="indexterm" name="id4327500"></a>
<a class="indexterm" name="id4327508"></a>
<code class="literal">TINYINT[(<em class="replaceable"><code>M</code></em>)] [UNSIGNED]
[ZEROFILL]</code>
</p><p>
A very small integer. The signed range is
<code class="literal">-128</code> to <code class="literal">127</code>. The
unsigned range is <code class="literal">0</code> to
<code class="literal">255</code>.
</p></li><li><p>
<a class="indexterm" name="id4327585"></a>
<a class="indexterm" name="id4327594"></a>
<a class="indexterm" name="id4327603"></a>
<a class="indexterm" name="id4327615"></a>
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BOOL</code></a>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BOOLEAN</code></a>
</p><p>
These types are synonyms for <code class="literal">TINYINT(1)</code>.
A value of zero is considered false. Nonzero values are
considered true:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT IF(0, 'true', 'false');</code></strong>
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false |
+------------------------+
mysql> <strong class="userinput"><code>SELECT IF(1, 'true', 'false');</code></strong>
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true |
+------------------------+
mysql> <strong class="userinput"><code>SELECT IF(2, 'true', 'false');</code></strong>
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true |
+------------------------+
</pre><p>
However, the values <code class="literal">TRUE</code> and
<code class="literal">FALSE</code> are merely aliases for
<code class="literal">1</code> and <code class="literal">0</code>, respectively,
as shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT IF(0 = FALSE, 'true', 'false');</code></strong>
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true |
+--------------------------------+
mysql> <strong class="userinput"><code>SELECT IF(1 = TRUE, 'true', 'false');</code></strong>
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true |
+-------------------------------+
mysql> <strong class="userinput"><code>SELECT IF(2 = TRUE, 'true', 'false');</code></strong>
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false |
+-------------------------------+
mysql> <strong class="userinput"><code>SELECT IF(2 = FALSE, 'true', 'false');</code></strong>
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false |
+--------------------------------+
</pre><p>
The last two statements display the results shown because
<code class="literal">2</code> is equal to neither
<code class="literal">1</code> nor <code class="literal">0</code>.
</p><p>
We intend to implement full boolean type handling, in
accordance with standard SQL, in a future MySQL release.
</p></li><li><p>
<a class="indexterm" name="id4327803"></a>
<a class="indexterm" name="id4327812"></a>
<code class="literal">SMALLINT[(<em class="replaceable"><code>M</code></em>)] [UNSIGNED]
[ZEROFILL]</code>
</p><p>
A small integer. The signed range is
<code class="literal">-32768</code> to <code class="literal">32767</code>. The
unsigned range is <code class="literal">0</code> to
<code class="literal">65535</code>.
</p></li><li><p>
<a class="indexterm" name="id4327889"></a>
<a class="indexterm" name="id4327898"></a>
<code class="literal">MEDIUMINT[(<em class="replaceable"><code>M</code></em>)]
[UNSIGNED] [ZEROFILL]</code>
</p><p>
A medium-sized integer. The signed range is
<code class="literal">-8388608</code> to <code class="literal">8388607</code>.
The unsigned range is <code class="literal">0</code> to
<code class="literal">16777215</code>.
</p></li><li><p>
<a class="indexterm" name="id4327975"></a>
<a class="indexterm" name="id4327984"></a>
<code class="literal">INT[(<em class="replaceable"><code>M</code></em>)] [UNSIGNED]
[ZEROFILL]</code>
</p><p>
A normal-size integer. The signed range is
<code class="literal">-2147483648</code> to
<code class="literal">2147483647</code>. The unsigned range is
<code class="literal">0</code> to <code class="literal">4294967295</code>.
</p></li><li><p>
<a class="indexterm" name="id4328055"></a>
<a class="indexterm" name="id4328064"></a>
<code class="literal">INTEGER[(<em class="replaceable"><code>M</code></em>)] [UNSIGNED]
[ZEROFILL]</code>
</p><p>
This type is a synonym for
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT</code></a>.
</p></li><li><p>
<a class="indexterm" name="id4328124"></a>
<a class="indexterm" name="id4328133"></a>
<a class="indexterm" name="id4328146"></a>
<code class="literal">BIGINT[(<em class="replaceable"><code>M</code></em>)] [UNSIGNED]
[ZEROFILL]</code>
</p><p>
A large integer. The signed range is
<code class="literal">-9223372036854775808</code> to
<code class="literal">9223372036854775807</code>. The unsigned range
is <code class="literal">0</code> to
<code class="literal">18446744073709551615</code>.
</p><p>
<code class="literal">SERIAL</code> is an alias for <code class="literal">BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE</code>.
</p><p>
Some things you should be aware of with respect to
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a> columns:
</p><div class="itemizedlist"><ul type="circle"><li><p>
<a class="indexterm" name="id4328230"></a>
All arithmetic is done using signed
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a> or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> values, so you
should not use unsigned big integers larger than
<code class="literal">9223372036854775807</code> (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
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a> value to a
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a>.
</p><p>
MySQL can handle <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a>
in the following cases:
</p><div class="itemizedlist"><ul type="square"><li><p>
When using integers to store large unsigned values
in a <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a> column.
</p></li><li><p>
In
<a href="functions.html#function_min"><code class="literal">MIN(<em class="replaceable"><code>col_name</code></em>)</code></a>
or
<a href="functions.html#function_max"><code class="literal">MAX(<em class="replaceable"><code>col_name</code></em>)</code></a>,
where <em class="replaceable"><code>col_name</code></em> refers to
a <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a> column.
</p></li><li><p>
When using operators
(<a href="functions.html#operator_plus"><code class="literal">+</code></a>,
<a href="functions.html#operator_minus"><code class="literal">-</code></a>,
<a href="functions.html#operator_times"><code class="literal">*</code></a>,
and so on) where both operands are integers.
</p></li></ul></div></li><li><p>
You can always store an exact integer value in a
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a> column by storing
it using a string. In this case, MySQL performs a
string-to-number conversion that involves no
intermediate double-precision representation.
</p></li><li><p>
The <a href="functions.html#operator_minus"><code class="literal">-</code></a>,
<a href="functions.html#operator_plus"><code class="literal">+</code></a>, and
<a href="functions.html#operator_times"><code class="literal">*</code></a>
operators use <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a>
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
<code class="literal">9223372036854775807</code>.
</p></li></ul></div></li><li><p>
<a class="indexterm" name="id4328457"></a>
<a class="indexterm" name="id4328466"></a>
<code class="literal">FLOAT[(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)]
[UNSIGNED] [ZEROFILL]</code>
</p><p>
A small (single-precision) floating-point number. Allowable
values are <code class="literal">-3.402823466E+38</code> to
<code class="literal">-1.175494351E-38</code>, <code class="literal">0</code>,
and <code class="literal">1.175494351E-38</code> to
<code class="literal">3.402823466E+38</code>. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
</p><p>
<em class="replaceable"><code>M</code></em> is the total number of digits
and <em class="replaceable"><code>D</code></em> is the number of digits
following the decimal point. If <em class="replaceable"><code>M</code></em>
and <em class="replaceable"><code>D</code></em> 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.
</p><p>
<code class="literal">UNSIGNED</code>, if specified, disallows
negative values.
</p><p>
Using <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> might give you
some unexpected problems because all calculations in MySQL
are done with double precision. See
<a href="error-handling.html#no-matching-rows" title="B.1.5.7. Solving Problems with No Matching Rows">Section B.1.5.7, “Solving Problems with No Matching Rows”</a>.
</p></li><li><p>
<a class="indexterm" name="id4328599"></a>
<a class="indexterm" name="id4328608"></a>
<a class="indexterm" name="id4328617"></a>
<a class="indexterm" name="id4328629"></a>
<code class="literal">DOUBLE[(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)]
[UNSIGNED] [ZEROFILL]</code>
</p><p>
A normal-size (double-precision) floating-point number.
Allowable values are
<code class="literal">-1.7976931348623157E+308</code> to
<code class="literal">-2.2250738585072014E-308</code>,
<code class="literal">0</code>, and
<code class="literal">2.2250738585072014E-308</code> to
<code class="literal">1.7976931348623157E+308</code>. These are the
theoretical limits, based on the IEEE standard. The actual
range might be slightly smaller depending on your hardware
or operating system.
</p><p>
<em class="replaceable"><code>M</code></em> is the total number of digits
and <em class="replaceable"><code>D</code></em> is the number of digits
following the decimal point. If <em class="replaceable"><code>M</code></em>
and <em class="replaceable"><code>D</code></em> 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.
</p><p>
<code class="literal">UNSIGNED</code>, if specified, disallows
negative values.
</p></li><li><p>
<a class="indexterm" name="id4328741"></a>
<a class="indexterm" name="id4328750"></a>
<a class="indexterm" name="id4328759"></a>
<a class="indexterm" name="id4328771"></a>
<code class="literal">DOUBLE
PRECISION[(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)]
[UNSIGNED] [ZEROFILL]</code>,
<code class="literal">REAL[(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)]
[UNSIGNED] [ZEROFILL]</code>
</p><p>
These types are synonyms for
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a>. Exception: If the
<a href="server-administration.html#sqlmode_real_as_float"><code class="literal">REAL_AS_FLOAT</code></a> SQL mode is
enabled, <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">REAL</code></a> is a synonym
for <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> rather than
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a>.
</p></li><li><p>
<a class="indexterm" name="id4328860"></a>
<a class="indexterm" name="id4328868"></a>
<a class="indexterm" name="id4328877"></a>
<code class="literal">FLOAT(<em class="replaceable"><code>p</code></em>) [UNSIGNED]
[ZEROFILL]</code>
</p><p>
A floating-point number. <em class="replaceable"><code>p</code></em>
represents the precision in bits, but MySQL uses this value
only to determine whether to use
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> for the resulting data
type. If <em class="replaceable"><code>p</code></em> is from 0 to 24, the
data type becomes <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> with
no <em class="replaceable"><code>M</code></em> or
<em class="replaceable"><code>D</code></em> values. If
<em class="replaceable"><code>p</code></em> is from 25 to 53, the data type
becomes <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> with no
<em class="replaceable"><code>M</code></em> or <em class="replaceable"><code>D</code></em>
values. The range of the resulting column is the same as for
the single-precision <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> or
double-precision <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> data
types described earlier in this section.
</p><p>
<a class="indexterm" name="id4328976"></a>
<a class="indexterm" name="id4328985"></a>
<code class="literal">FLOAT(<em class="replaceable"><code>p</code></em>)</code>
syntax is provided for ODBC compatibility.
</p></li><li><p>
<a class="indexterm" name="id4329030"></a>
<a class="indexterm" name="id4329039"></a>
<code class="literal">DECIMAL[(<em class="replaceable"><code>M</code></em>[,<em class="replaceable"><code>D</code></em>])]
[UNSIGNED] [ZEROFILL]</code>
</p><p>
A packed “<span class="quote">exact</span>” fixed-point number.
<em class="replaceable"><code>M</code></em> is the total number of digits
(the precision) and <em class="replaceable"><code>D</code></em> is the
number of digits after the decimal point (the scale). The
decimal point and (for negative numbers) the
“<span class="quote"><code class="literal">-</code></span>” sign are not counted in
<em class="replaceable"><code>M</code></em>. If
<em class="replaceable"><code>D</code></em> is 0, values have no decimal
point or fractional part. The maximum number of digits
(<em class="replaceable"><code>M</code></em>) for
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> is 65. The maximum
number of supported decimals (<em class="replaceable"><code>D</code></em>)
is 30. If <em class="replaceable"><code>D</code></em> is omitted, the
default is 0. If <em class="replaceable"><code>M</code></em> is omitted,
the default is 10.
</p><p>
<code class="literal">UNSIGNED</code>, if specified, disallows
negative values.
</p><p>
All basic calculations (<code class="literal">+, -, *, /</code>) with
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> columns are done with
a precision of 65 digits.
</p></li><li><p>
<a class="indexterm" name="id4329171"></a>
<a class="indexterm" name="id4329180"></a>
<a class="indexterm" name="id4329189"></a>
<a class="indexterm" name="id4329198"></a>
<a class="indexterm" name="id4329210"></a>
<a class="indexterm" name="id4329222"></a>
<code class="literal">DEC[(<em class="replaceable"><code>M</code></em>[,<em class="replaceable"><code>D</code></em>])]
[UNSIGNED] [ZEROFILL]</code>,
<code class="literal">NUMERIC[(<em class="replaceable"><code>M</code></em>[,<em class="replaceable"><code>D</code></em>])]
[UNSIGNED] [ZEROFILL]</code>,
<code class="literal">FIXED[(<em class="replaceable"><code>M</code></em>[,<em class="replaceable"><code>D</code></em>])]
[UNSIGNED] [ZEROFILL]</code>
</p><p>
These types are synonyms for
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a>. The
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FIXED</code></a> synonym is available
for compatibility with other database systems.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="date-and-time-type-overview"></a>10.1.2. Overview of Date and Time Types</h3></div></div></div><p>
A summary of the temporal data types follows. For additional
information about properties of the temporal types, see
<a href="data-types.html#date-and-time-types" title="10.3. Date and Time Types">Section 10.3, “Date and Time Types”</a>. Storage requirements are
given in <a href="data-types.html#storage-requirements" title="10.5. Data Type Storage Requirements">Section 10.5, “Data Type Storage Requirements”</a>. Functions that
operate on temporal values are described at
<a href="functions.html#date-and-time-functions" title="11.6. Date and Time Functions">Section 11.6, “Date and Time Functions”</a>.
</p><p>
For the <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> and
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> range descriptions,
“<span class="quote">supported</span>” means that although earlier values
might work, there is no guarantee.
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id4329373"></a>
<a class="indexterm" name="id4329382"></a>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>
</p><p>
A date. The supported range is
<code class="literal">'1000-01-01'</code> to
<code class="literal">'9999-12-31'</code>. MySQL displays
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> values in
<code class="literal">'YYYY-MM-DD'</code> format, but allows
assignment of values to <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>
columns using either strings or numbers.
</p></li><li><p>
<a class="indexterm" name="id4329460"></a>
<a class="indexterm" name="id4329468"></a>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>
</p><p>
A date and time combination. The supported range is
<code class="literal">'1000-01-01 00:00:00'</code> to
<code class="literal">'9999-12-31 23:59:59'</code>. MySQL displays
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> values in
<code class="literal">'YYYY-MM-DD HH:MM:SS'</code> format, but allows
assignment of values to
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> columns using either
strings or numbers.
</p></li><li><p>
<a class="indexterm" name="id4329552"></a>
<a class="indexterm" name="id4329561"></a>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a>
</p><p>
A timestamp. The range is <code class="literal">'1970-01-01
00:00:01'</code> UTC to <code class="literal">'2038-01-19
03:14:07'</code> UTC.
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> values are stored
as the number of seconds since the epoch
(<code class="literal">'1970-01-01 00:00:00'</code> UTC). A
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> cannot represent
the value <code class="literal">'1970-01-01 00:00:00'</code> because
that is equivalent to 0 seconds from the epoch and the value
0 is reserved for representing <code class="literal">'0000-00-00
00:00:00'</code>, the “<span class="quote">zero</span>”
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> value.
</p><p>
A <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column is useful
for recording the date and time of an
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> or
<a href="sql-syntax.html#update" title="12.2.11. UPDATE Syntax"><code class="literal">UPDATE</code></a> operation. By default,
the first <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column in
a table is automatically set to the date and time of the
most recent operation if you do not assign it a value
yourself. You can also set any
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column to the
current date and time by assigning it a
<code class="literal">NULL</code> value. Variations on automatic
initialization and update properties are described in
<a href="data-types.html#timestamp" title="10.3.1.1. TIMESTAMP Properties">Section 10.3.1.1, “<code class="literal">TIMESTAMP</code> Properties”</a>.
</p><p>
A <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> value is returned
as a string in the format <code class="literal">'YYYY-MM-DD
HH:MM:SS'</code> with a display width fixed at 19
characters. To obtain the value as a number, you should add
<code class="literal">+0</code> to the timestamp column.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
The <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> format that
was used prior to MySQL 4.1 is not supported in MySQL
5.1; see <em class="citetitle">MySQL 3.23, 4.0, 4.1
Reference Manual</em> for information regarding the
old format.
</p></div></li><li><p>
<a class="indexterm" name="id4329765"></a>
<a class="indexterm" name="id4329774"></a>
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a>
</p><p>
A time. The range is <code class="literal">'-838:59:59'</code> to
<code class="literal">'838:59:59'</code>. MySQL displays
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> values in
<code class="literal">'HH:MM:SS'</code> format, but allows assignment
of values to <a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> columns
using either strings or numbers.
</p></li><li><p>
<a class="indexterm" name="id4329851"></a>
<a class="indexterm" name="id4329860"></a>
<code class="literal">YEAR[(2|4)]</code>
</p><p>
A year in two-digit or four-digit format. The default is
four-digit format. In four-digit format, the allowable
values are <code class="literal">1901</code> to
<code class="literal">2155</code>, and <code class="literal">0000</code>. In
two-digit format, the allowable values are
<code class="literal">70</code> to <code class="literal">69</code>, representing
years from 1970 to 2069. MySQL displays
<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> values in
<code class="literal">YYYY</code> format, but allows you to assign
values to <a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> columns using
either strings or numbers.
</p></li></ul></div><p>
The <a href="functions.html#function_sum"><code class="literal">SUM()</code></a> and
<a href="functions.html#function_avg"><code class="literal">AVG()</code></a> aggregate functions do not
work with temporal values. (They convert the values to numbers,
which loses the part after the first nonnumeric character.) To
work around this problem, you can convert to numeric units,
perform the aggregate operation, and convert back to a temporal
value. Examples:
</p><pre class="programlisting">SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(<em class="replaceable"><code>time_col</code></em>))) FROM <em class="replaceable"><code>tbl_name</code></em>;
SELECT FROM_DAYS(SUM(TO_DAYS(<em class="replaceable"><code>date_col</code></em>))) FROM <em class="replaceable"><code>tbl_name</code></em>;
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="string-type-overview"></a>10.1.3. Overview of String Types</h3></div></div></div><p>
A summary of the string data types follows. For additional
information about properties of the string types, see
<a href="data-types.html#string-types" title="10.4. String Types">Section 10.4, “String Types”</a>. Storage requirements are given
in <a href="data-types.html#storage-requirements" title="10.5. Data Type Storage Requirements">Section 10.5, “Data Type Storage Requirements”</a>.
</p><p>
In some cases, MySQL may change a string column to a type
different from that given in a <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TABLE</code></a> or <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>
statement. See <a href="sql-syntax.html#silent-column-changes" title="12.1.17.1. Silent Column Specification Changes">Section 12.1.17.1, “Silent Column Specification Changes”</a>.
</p><p>
MySQL interprets length specifications in character column
definitions in character units. This applies to
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a>,
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>, and the
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> types.
</p><p>
Column definitions for many string data types can include
attributes that specify the character set or collation of the
column. These attributes apply to the
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a>,
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>, the
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> types,
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>, and
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> data types:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">CHARACTER SET</code> attribute specifies the
character set, and the <code class="literal">COLLATE</code> attribute
specifies a collation for the character set. For example:
</p><pre class="programlisting">CREATE TABLE t
(
c1 VARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);
</pre><p>
This table definition creates a column named
<code class="literal">c1</code> that has a character set of
<code class="literal">utf8</code> with the default collation for that
character set, and a column named <code class="literal">c2</code> that
has a character set of <code class="literal">latin1</code> and a
case-sensitive collation.
</p><p>
The rules for assigning the character set and collation when
either or both of the <code class="literal">CHARACTER SET</code> and
<code class="literal">COLLATE</code> attributes are missing are
described in <a href="internationalization-localization.html#charset-column" title="9.1.3.4. Column Character Set and Collation">Section 9.1.3.4, “Column Character Set and Collation”</a>.
</p><p>
<code class="literal">CHARSET</code> is a synonym for
<code class="literal">CHARACTER SET</code>.
</p></li><li><p>
Specifying the <code class="literal">CHARACTER SET binary</code>
attribute for a character data type causes the column to be
created as the corresponding binary data type:
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> becomes
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a>,
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> becomes
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a>, and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> becomes
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a>. For the
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> and
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> data types, this does not
occur; they are created as declared. Suppose that you
specify a table using this definition:
</p><pre class="programlisting">CREATE TABLE t
(
c1 VARCHAR(10) CHARACTER SET binary,
c2 TEXT CHARACTER SET binary,
c3 ENUM('a','b','c') CHARACTER SET binary
);
</pre><p>
The resulting table has this definition:
</p><pre class="programlisting">CREATE TABLE t
(
c1 VARBINARY(10),
c2 BLOB,
c3 ENUM('a','b','c') CHARACTER SET binary
);
</pre></li><li><p>
The <code class="literal">ASCII</code> attribute is shorthand for
<code class="literal">CHARACTER SET latin1</code>.
</p></li><li><p>
The <code class="literal">UNICODE</code> attribute is shorthand for
<code class="literal">CHARACTER SET ucs2</code>.
</p></li><li><p>
The <code class="literal">BINARY</code> attribute is shorthand for
specifying the binary collation of the column character set.
In this case, sorting and comparison are based on numeric
character values.
</p></li></ul></div><p>
Character column sorting and comparison are based on the
character set assigned to the column. For the
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a>,
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a>,
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>, and
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> data types, you can declare a
column with a binary collation or the <code class="literal">BINARY</code>
attribute to cause sorting and comparison to use the underlying
character code values rather than a lexical ordering.
</p><p>
<a href="internationalization-localization.html#charset" title="9.1. Character Set Support">Section 9.1, “Character Set Support”</a>, provides additional information about
use of character sets in MySQL.
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id4330408"></a>
<a class="indexterm" name="id4330417"></a>
<a class="indexterm" name="id4330426"></a>
<a class="indexterm" name="id4330435"></a>
<a class="indexterm" name="id4330444"></a>
<a class="indexterm" name="id4330456"></a>
<a class="indexterm" name="id4330469"></a>
<a class="indexterm" name="id4330481"></a>
<code class="literal">[NATIONAL] CHAR[(<em class="replaceable"><code>M</code></em>)]
[CHARACTER SET <em class="replaceable"><code>charset_name</code></em>]
[COLLATE
<em class="replaceable"><code>collation_name</code></em>]</code>
</p><p>
A fixed-length string that is always right-padded with
spaces to the specified length when stored.
<em class="replaceable"><code>M</code></em> represents the column length in
characters. The range of <em class="replaceable"><code>M</code></em> is 0
to 255. If <em class="replaceable"><code>M</code></em> is omitted, the
length is 1.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Trailing spaces are removed when
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> values are retrieved
unless the
<a href="server-administration.html#sqlmode_pad_char_to_full_length"><code class="literal">PAD_CHAR_TO_FULL_LENGTH</code></a>
SQL mode is enabled.
</p></div><p>
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> is shorthand for
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHARACTER</code></a>.
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">NATIONAL CHAR</code></a> (or its
equivalent short form, <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">NCHAR</code></a>)
is the standard SQL way to define that a
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> column should use some
predefined character set. MySQL 4.1 and up uses
<code class="literal">utf8</code> as this predefined character set.
<a href="internationalization-localization.html#charset-national" title="9.1.3.6. National Character Set">Section 9.1.3.6, “National Character Set”</a>.
</p><p>
The <a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">CHAR BYTE</code></a> data type is an
alias for the <a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> data
type. This is a compatibility feature.
</p><p>
MySQL allows you to create a column of type
<code class="literal">CHAR(0)</code>. This is useful primarily when
you have to be compliant with old applications that depend
on the existence of a column but that do not actually use
its value. <code class="literal">CHAR(0)</code> is also quite nice
when you need a column that can take only two values: A
column that is defined as <code class="literal">CHAR(0) NULL</code>
occupies only one bit and can take only the values
<code class="literal">NULL</code> and <code class="literal">''</code> (the empty
string).
</p></li><li><p>
<a class="indexterm" name="id4330703"></a>
<a class="indexterm" name="id4330712"></a>
<a class="indexterm" name="id4330721"></a>
<a class="indexterm" name="id4330730"></a>
<a class="indexterm" name="id4330739"></a>
<a class="indexterm" name="id4330748"></a>
<a class="indexterm" name="id4330757"></a>
<a class="indexterm" name="id4330770"></a>
<a class="indexterm" name="id4330782"></a>
<a class="indexterm" name="id4330794"></a>
<a class="indexterm" name="id4330807"></a>
<a class="indexterm" name="id4330819"></a>
<code class="literal">[NATIONAL] VARCHAR(<em class="replaceable"><code>M</code></em>)
[CHARACTER SET <em class="replaceable"><code>charset_name</code></em>]
[COLLATE
<em class="replaceable"><code>collation_name</code></em>]</code>
</p><p>
A variable-length string. <em class="replaceable"><code>M</code></em>
represents the maximum column length in characters. The
range of <em class="replaceable"><code>M</code></em> is 0 to 65,535. The
effective maximum length of a
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> is subject to the
maximum row size (65,535 bytes, which is shared among all
columns) and the character set used. For example,
<code class="literal">utf8</code> characters can require up to three
bytes per character, so a
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> column that uses the
<code class="literal">utf8</code> character set can be declared to be
a maximum of 21,844 characters.
</p><p>
MySQL stores <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> values
as a one-byte or two-byte length prefix plus data. The
length prefix indicates the number of bytes in the value. A
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> column uses one
length byte if values require no more than 255 bytes, two
length bytes if values may require more than 255 bytes.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
MySQL 5.1 follows the standard SQL
specification, and does <span class="emphasis"><em>not</em></span> remove
trailing spaces from
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> values.
</p></div><p>
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> is shorthand for
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHARACTER VARYING</code></a>.
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">NATIONAL VARCHAR</code></a> is the
standard SQL way to define that a
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> column should use
some predefined character set. MySQL 4.1 and up uses
<code class="literal">utf8</code> as this predefined character set.
<a href="internationalization-localization.html#charset-national" title="9.1.3.6. National Character Set">Section 9.1.3.6, “National Character Set”</a>.
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">NVARCHAR</code></a> is shorthand for
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">NATIONAL VARCHAR</code></a>.
</p></li><li><p>
<a class="indexterm" name="id4331007"></a>
<a class="indexterm" name="id4331016"></a>
<code class="literal">BINARY(<em class="replaceable"><code>M</code></em>)</code>
</p><p>
The <a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> type is similar to
the <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> type, but stores
binary byte strings rather than nonbinary character strings.
<em class="replaceable"><code>M</code></em> represents the column length in
bytes.
</p></li><li><p>
<a class="indexterm" name="id4331081"></a>
<a class="indexterm" name="id4331090"></a>
<code class="literal">VARBINARY(<em class="replaceable"><code>M</code></em>)</code>
</p><p>
The <a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> type is similar
to the <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> type, but
stores binary byte strings rather than nonbinary character
strings. <em class="replaceable"><code>M</code></em> represents the maximum
column length in bytes.
</p></li><li><p>
<a class="indexterm" name="id4331156"></a>
<a class="indexterm" name="id4331165"></a>
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TINYBLOB</code></a>
</p><p>
A <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> column with a maximum
length of 255 (2<sup>8</sup> – 1)
bytes. Each <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TINYBLOB</code></a> value is
stored using a one-byte length prefix that indicates the
number of bytes in the value.
</p></li><li><p>
<a class="indexterm" name="id4331228"></a>
<a class="indexterm" name="id4331237"></a>
<code class="literal">TINYTEXT [CHARACTER SET
<em class="replaceable"><code>charset_name</code></em>] [COLLATE
<em class="replaceable"><code>collation_name</code></em>]</code>
</p><p>
A <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column with a maximum
length of 255 (2<sup>8</sup> – 1)
characters. The effective maximum length is less if the
value contains multi-byte characters. Each
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TINYTEXT</code></a> value is stored
using a one-byte length prefix that indicates the number of
bytes in the value.
</p></li><li><p>
<a class="indexterm" name="id4331309"></a>
<a class="indexterm" name="id4331318"></a>
<code class="literal">BLOB[(<em class="replaceable"><code>M</code></em>)]</code>
</p><p>
A <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> column with a maximum
length of 65,535 (2<sup>16</sup> – 1)
bytes. Each <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> value is
stored using a two-byte length prefix that indicates the
number of bytes in the value.
</p><p>
An optional length <em class="replaceable"><code>M</code></em> can be given
for this type. If this is done, MySQL creates the column as
the smallest <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> type large
enough to hold values <em class="replaceable"><code>M</code></em> bytes
long.
</p></li><li><p>
<a class="indexterm" name="id4331403"></a>
<a class="indexterm" name="id4331412"></a>
<code class="literal">TEXT[(<em class="replaceable"><code>M</code></em>)] [CHARACTER SET
<em class="replaceable"><code>charset_name</code></em>] [COLLATE
<em class="replaceable"><code>collation_name</code></em>]</code>
</p><p>
A <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column with a maximum
length of 65,535 (2<sup>16</sup> – 1)
characters. The effective maximum length is less if the
value contains multi-byte characters. Each
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> value is stored using a
two-byte length prefix that indicates the number of bytes in
the value.
</p><p>
An optional length <em class="replaceable"><code>M</code></em> can be given
for this type. If this is done, MySQL creates the column as
the smallest <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> type large
enough to hold values <em class="replaceable"><code>M</code></em>
characters long.
</p></li><li><p>
<a class="indexterm" name="id4331506"></a>
<a class="indexterm" name="id4331515"></a>
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMBLOB</code></a>
</p><p>
A <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> column with a maximum
length of 16,777,215 (2<sup>24</sup> –
1) bytes. Each <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMBLOB</code></a>
value is stored using a three-byte length prefix that
indicates the number of bytes in the value.
</p></li><li><p>
<a class="indexterm" name="id4331579"></a>
<a class="indexterm" name="id4331588"></a>
<code class="literal">MEDIUMTEXT [CHARACTER SET
<em class="replaceable"><code>charset_name</code></em>] [COLLATE
<em class="replaceable"><code>collation_name</code></em>]</code>
</p><p>
A <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column with a maximum
length of 16,777,215 (2<sup>24</sup> –
1) characters. The effective maximum length is less if the
value contains multi-byte characters. Each
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMTEXT</code></a> value is stored
using a three-byte length prefix that indicates the number
of bytes in the value.
</p></li><li><p>
<a class="indexterm" name="id4331666"></a>
<a class="indexterm" name="id4331674"></a>
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGBLOB</code></a>
</p><p>
A <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> column with a maximum
length of 4,294,967,295 or 4GB
(2<sup>32</sup> – 1) bytes. The
effective maximum length of
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGBLOB</code></a> columns depends on
the configured maximum packet size in the client/server
protocol and available memory. Each
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGBLOB</code></a> value is stored
using a four-byte length prefix that indicates the number of
bytes in the value.
</p></li><li><p>
<a class="indexterm" name="id4331747"></a>
<a class="indexterm" name="id4331756"></a>
<code class="literal">LONGTEXT [CHARACTER SET
<em class="replaceable"><code>charset_name</code></em>] [COLLATE
<em class="replaceable"><code>collation_name</code></em>]</code>
</p><p>
A <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column with a maximum
length of 4,294,967,295 or 4GB
(2<sup>32</sup> – 1) characters. The
effective maximum length is less if the value contains
multi-byte characters. The effective maximum length of
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGTEXT</code></a>
columns also depends on the configured maximum packet size
in the client/server protocol and available memory. Each
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGTEXT</code></a>
value is stored using a four-byte length prefix that
indicates the number of bytes in the value.
</p></li><li><p>
<a class="indexterm" name="id4331837"></a>
<a class="indexterm" name="id4331846"></a>
<code class="literal">ENUM('<em class="replaceable"><code>value1</code></em>','<em class="replaceable"><code>value2</code></em>',...)
[CHARACTER SET <em class="replaceable"><code>charset_name</code></em>]
[COLLATE
<em class="replaceable"><code>collation_name</code></em>]</code>
</p><p>
An enumeration. A string object that can have only one
value, chosen from the list of values
<code class="literal">'<em class="replaceable"><code>value1</code></em>'</code>,
<code class="literal">'<em class="replaceable"><code>value2</code></em>'</code>,
<code class="literal">...</code>, <code class="literal">NULL</code> or the
special <code class="literal">''</code> error value. An
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> column can have a
maximum of 65,535 distinct values.
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> values are represented
internally as integers.
</p></li><li><p>
<a class="indexterm" name="id4331956"></a>
<a class="indexterm" name="id4331965"></a>
<code class="literal">SET('<em class="replaceable"><code>value1</code></em>','<em class="replaceable"><code>value2</code></em>',...)
[CHARACTER SET <em class="replaceable"><code>charset_name</code></em>]
[COLLATE
<em class="replaceable"><code>collation_name</code></em>]</code>
</p><p>
A set. A string object that can have zero or more values,
each of which must be chosen from the list of values
<code class="literal">'<em class="replaceable"><code>value1</code></em>'</code>,
<code class="literal">'<em class="replaceable"><code>value2</code></em>'</code>,
<code class="literal">...</code> A <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a>
column can have a maximum of 64 members.
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> values are represented
internally as integers.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="data-type-defaults"></a>10.1.4. Data Type Default Values</h3></div></div></div><a class="indexterm" name="id4332056"></a><a class="indexterm" name="id4332065"></a><a class="indexterm" name="id4332078"></a><a class="indexterm" name="id4332087"></a><a class="indexterm" name="id4332099"></a><a class="indexterm" name="id4332108"></a><p>
The <code class="literal">DEFAULT <em class="replaceable"><code>value</code></em></code>
clause in a data type specification indicates a default value
for a column. With one exception, the default value must be a
constant; it cannot be a function or an expression. This means,
for example, that you cannot set the default for a date column
to be the value of a function such as
<a href="functions.html#function_now"><code class="literal">NOW()</code></a> or
<a href="functions.html#function_current-date"><code class="literal">CURRENT_DATE</code></a>. The exception is
that you can specify
<a href="functions.html#function_current-timestamp"><code class="literal">CURRENT_TIMESTAMP</code></a> as the default
for a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column. See
<a href="data-types.html#timestamp" title="10.3.1.1. TIMESTAMP Properties">Section 10.3.1.1, “<code class="literal">TIMESTAMP</code> Properties”</a>.
</p><p>
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> columns cannot be assigned a
default value.
</p><p>
If a column definition includes no explicit
<code class="literal">DEFAULT</code> value, MySQL determines the default
value as follows:
</p><p>
If the column can take <code class="literal">NULL</code> as a value, the
column is defined with an explicit <code class="literal">DEFAULT
NULL</code> clause.
</p><p>
If the column cannot take <code class="literal">NULL</code> as the value,
MySQL defines the column with no explicit
<code class="literal">DEFAULT</code> clause. For data entry, if an
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> or
<a href="sql-syntax.html#replace" title="12.2.7. REPLACE Syntax"><code class="literal">REPLACE</code></a> statement includes no
value for the column, or an
<a href="sql-syntax.html#update" title="12.2.11. UPDATE Syntax"><code class="literal">UPDATE</code></a> statement sets the column
to <code class="literal">NULL</code>, MySQL handles the column according
to the SQL mode in effect at the time:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If strict SQL mode is not enabled, MySQL sets the column to
the implicit default value for the column data type.
</p></li><li><p>
If strict mode is enabled, an error occurs for transactional
tables and the statement is rolled back. For
nontransactional tables, an error occurs, but if this
happens for the second or subsequent row of a multiple-row
statement, the preceding rows will have been inserted.
</p></li></ul></div><p>
Suppose that a table <code class="literal">t</code> is defined as follows:
</p><pre class="programlisting">CREATE TABLE t (i INT NOT NULL);
</pre><p>
In this case, <code class="literal">i</code> has no explicit default, so
in strict mode each of the following statements produce an error
and no row is inserted. When not using strict mode, only the
third statement produces an error; the implicit default is
inserted for the first two statements, but the third fails
because <a href="functions.html#function_default"><code class="literal">DEFAULT(i)</code></a> cannot produce
a value:
</p><pre class="programlisting">INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));
</pre><p>
See <a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p><p>
For a given table, you can use the <a href="sql-syntax.html#show-create-table" title="12.5.5.12. SHOW CREATE TABLE Syntax"><code class="literal">SHOW
CREATE TABLE</code></a> statement to see which columns have an
explicit <code class="literal">DEFAULT</code> clause.
</p><p>
Implicit defaults are defined as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
For numeric types, the default is <code class="literal">0</code>, with
the exception that for integer or floating-point types
declared with the <code class="literal">AUTO_INCREMENT</code>
attribute, the default is the next value in the sequence.
</p></li><li><p>
For date and time types other than
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a>, the default is the
appropriate “<span class="quote">zero</span>” value for the type. For the
first <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column in a
table, the default value is the current date and time. See
<a href="data-types.html#date-and-time-types" title="10.3. Date and Time Types">Section 10.3, “Date and Time Types”</a>.
</p></li><li><p>
For string types other than
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>, the default value is
the empty string. For <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>,
the default is the first enumeration value.
</p></li></ul></div><a class="indexterm" name="id4332420"></a><p>
<code class="literal">SERIAL DEFAULT VALUE</code> in the definition of an
integer column is an alias for <code class="literal">NOT NULL AUTO_INCREMENT
UNIQUE</code>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="numeric-types"></a>10.2. Numeric Types</h2></div></div></div><a class="indexterm" name="id4332455"></a><a class="indexterm" name="id4332464"></a><a class="indexterm" name="id4332472"></a><p>
MySQL supports all of the standard SQL numeric data types. These
types include the exact numeric data types
(<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INTEGER</code></a>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">SMALLINT</code></a>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a>, and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a>), as well as the
approximate numeric data types
(<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">REAL</code></a>, and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE PRECISION</code></a>). The keyword
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT</code></a> is a synonym for
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INTEGER</code></a>, and the keyword
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DEC</code></a> is a synonym for
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a>. For numeric type storage
requirements, see <a href="data-types.html#storage-requirements" title="10.5. Data Type Storage Requirements">Section 10.5, “Data Type Storage Requirements”</a>.
</p><p>
The numeric types used for the results of calculations depends on
the operations being performed and the numeric types of the
operands; for more information, see
<a href="functions.html#arithmetic-functions" title="11.5.1. Arithmetic Operators">Section 11.5.1, “Arithmetic Operators”</a>.
</p><p>
The <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIT</code></a> data type stores bit-field
values and is supported for <code class="literal">MyISAM</code>,
<code class="literal">MEMORY</code>, <code class="literal">InnoDB</code>, and
<code class="literal">NDBCLUSTER</code> tables.
</p><p>
As an extension to the SQL standard, MySQL also supports the
integer types <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">TINYINT</code></a>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">MEDIUMINT</code></a>, and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a>. The following table shows
the required storage and range for each of the integer types.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Type</strong></span></td><td><span class="bold"><strong>Bytes</strong></span></td><td><span class="bold"><strong>Minimum Value</strong></span></td><td><span class="bold"><strong>Maximum Value</strong></span></td></tr><tr><td> </td><td> </td><td><span class="bold"><strong>(Signed/Unsigned)</strong></span></td><td><span class="bold"><strong>(Signed/Unsigned)</strong></span></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">TINYINT</code></a></td><td>1</td><td><code class="literal">-128</code></td><td><code class="literal">127</code></td></tr><tr><td> </td><td> </td><td><code class="literal">0</code></td><td><code class="literal">255</code></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">SMALLINT</code></a></td><td>2</td><td><code class="literal">-32768</code></td><td><code class="literal">32767</code></td></tr><tr><td> </td><td> </td><td><code class="literal">0</code></td><td><code class="literal">65535</code></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">MEDIUMINT</code></a></td><td>3</td><td><code class="literal">-8388608</code></td><td><code class="literal">8388607</code></td></tr><tr><td> </td><td> </td><td><code class="literal">0</code></td><td><code class="literal">16777215</code></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT</code></a></td><td>4</td><td><code class="literal">-2147483648</code></td><td><code class="literal">2147483647</code></td></tr><tr><td> </td><td> </td><td><code class="literal">0</code></td><td><code class="literal">4294967295</code></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a></td><td>8</td><td><code class="literal">-9223372036854775808</code></td><td><code class="literal">9223372036854775807</code></td></tr><tr><td> </td><td> </td><td><code class="literal">0</code></td><td><code class="literal">18446744073709551615</code></td></tr></tbody></table></div><p>
Another extension is supported by MySQL for optionally specifying
the display width of integer data types in parentheses following
the base keyword for the type (for example,
<code class="literal">INT(4)</code>). This optional display width may be
used by applications to display integer values having a width less
than the width specified for the column by left-padding them with
spaces. (That is, this width is present in the metadata returned
with result sets. Whether it is used or not is up to the
application.)
</p><p>
The display width does <span class="emphasis"><em>not</em></span> 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. For example, a column specified as
<code class="literal">SMALLINT(3)</code> has the usual
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">SMALLINT</code></a> range of
<code class="literal">-32768</code> to <code class="literal">32767</code>, and values
outside the range allowed by three characters are displayed using
more than three characters.
</p><p>
When used in conjunction with the optional extension attribute
<code class="literal">ZEROFILL</code>, the default padding of spaces is
replaced with zeros. For example, for a column declared as
<code class="literal">INT(5) ZEROFILL</code>, a value of
<code class="literal">4</code> is retrieved as <code class="literal">00004</code>.
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 assumes that the data fits into the original column
width.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
The <code class="literal">ZEROFILL</code> attribute is ignored when a
column is involved in expressions or
<a href="sql-syntax.html#union" title="12.2.8.3. UNION Syntax"><code class="literal">UNION</code></a> queries.
</p></div><p>
All integer types can have an optional (nonstandard) attribute
<code class="literal">UNSIGNED</code>. Unsigned values can be used when you
want to allow only nonnegative numbers in a column and you need a
larger upper numeric range for the column. For example, if an
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT</code></a> column is
<code class="literal">UNSIGNED</code>, the size of the column's range is the
same but its endpoints shift from <code class="literal">-2147483648</code>
and <code class="literal">2147483647</code> up to <code class="literal">0</code> and
<code class="literal">4294967295</code>.
</p><p>
Floating-point and fixed-point types also can be
<code class="literal">UNSIGNED</code>. 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.
</p><p>
If you specify <code class="literal">ZEROFILL</code> for a numeric column,
MySQL automatically adds the <code class="literal">UNSIGNED</code> attribute
to the column.
</p><p>
Integer or floating-point data types can have the additional
attribute <code class="literal">AUTO_INCREMENT</code>. When you insert a
value of <code class="literal">NULL</code> (recommended) or
<code class="literal">0</code> into an indexed
<code class="literal">AUTO_INCREMENT</code> column, the column is set to the
next sequence value. Typically this is
<code class="literal"><em class="replaceable"><code>value</code></em>+1</code>, where
<em class="replaceable"><code>value</code></em> is the largest value for the
column currently in the table. <code class="literal">AUTO_INCREMENT</code>
sequences begin with <code class="literal">1</code>.
</p><p>
For floating-point data types, MySQL uses four bytes for
single-precision values and eight bytes for double-precision
values.
</p><p>
The <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> data types are used to
represent approximate numeric data values. For
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a>, the SQL standard allows an
optional specification of the precision (but not the range of the
exponent) in bits following the keyword
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> in parentheses. MySQL 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
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> column. A precision from 24
to 53 results in an eight-byte double-precision
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> column.
</p><p>
MySQL allows a nonstandard syntax:
<code class="literal">FLOAT(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>
or
<code class="literal">REAL(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>
or <code class="literal">DOUBLE
PRECISION(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>.
Here,
“<span class="quote"><code class="literal">(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code></span>”
means than values can be stored with up to
<em class="replaceable"><code>M</code></em> digits in total, of which
<em class="replaceable"><code>D</code></em> digits may be after the decimal
point. For example, a column defined as
<code class="literal">FLOAT(7,4)</code> will look like
<code class="literal">-999.9999</code> when displayed. MySQL performs
rounding when storing values, so if you insert
<code class="literal">999.00009</code> into a <code class="literal">FLOAT(7,4)</code>
column, the approximate result is <code class="literal">999.0001</code>.
</p><p>
MySQL treats <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> as a synonym
for <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE PRECISION</code></a> (a nonstandard
extension). MySQL also treats <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">REAL</code></a>
as a synonym for <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE PRECISION</code></a>
(a nonstandard variation), unless the
<a href="server-administration.html#sqlmode_real_as_float"><code class="literal">REAL_AS_FLOAT</code></a> SQL mode is
enabled.
</p><p>
For maximum portability, code requiring storage of approximate
numeric data values should use
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE PRECISION</code></a> with no
specification of precision or number of digits.
</p><p>
The <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a> data types are used to
store exact numeric data values. In MySQL,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a> is implemented as
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a>. These types are used to
store values for which it is important to preserve exact
precision, for example with monetary data.
</p><p>
MySQL 5.1 stores
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a> values in binary format.
Before MySQL 5.0.3, they were stored as strings. See
<a href="functions.html#precision-math" title="11.14. Precision Math">Section 11.14, “Precision Math”</a>.
</p><p>
When declaring a <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a> column, the precision and
scale can be (and usually is) specified; for example:
</p><pre class="programlisting">salary DECIMAL(5,2)
</pre><p>
In this example, <code class="literal">5</code> is the precision and
<code class="literal">2</code> 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. If the scale is 0,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a> values contain no decimal
point or fractional part.
</p><p>
Standard SQL requires that the <code class="literal">salary</code> 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
<code class="literal">salary</code> column is from
<code class="literal">-999.99</code> to <code class="literal">999.99</code>.
</p><p>
In standard SQL, the syntax
<code class="literal">DECIMAL(<em class="replaceable"><code>M</code></em>)</code> is
equivalent to
<code class="literal">DECIMAL(<em class="replaceable"><code>M</code></em>,0)</code>.
Similarly, the syntax <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> is
equivalent to
<code class="literal">DECIMAL(<em class="replaceable"><code>M</code></em>,0)</code>, where
the implementation is allowed to decide the value of
<em class="replaceable"><code>M</code></em>. MySQL supports both of these variant
forms of the <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a> syntax. The default value
of <em class="replaceable"><code>M</code></em> is 10.
</p><p>
The maximum number of digits for
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a> is 65, but the actual range
for a given <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a> 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.)
</p><p>
The <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIT</code></a> data type is used to store
bit-field values. A type of
<code class="literal">BIT(<em class="replaceable"><code>M</code></em>)</code> allows for
storage of <em class="replaceable"><code>M</code></em>-bit values.
<em class="replaceable"><code>M</code></em> can range from 1 to 64.
</p><p>
To specify bit values,
<code class="literal">b'<em class="replaceable"><code>value</code></em>'</code> notation
can be used. <em class="replaceable"><code>value</code></em> is a binary value
written using zeros and ones. For example,
<code class="literal">b'111'</code> and <code class="literal">b'10000000'</code>
represent 7 and 128, respectively. See
<a href="language-structure.html#bit-field-values" title="8.1.5. Bit-Field Values">Section 8.1.5, “Bit-Field Values”</a>.
</p><p>
If you assign a value to a
<code class="literal">BIT(<em class="replaceable"><code>M</code></em>)</code> column that
is less than <em class="replaceable"><code>M</code></em> bits long, the value is
padded on the left with zeros. For example, assigning a value of
<code class="literal">b'101'</code> to a <code class="literal">BIT(6)</code> column
is, in effect, the same as assigning <code class="literal">b'000101'</code>.
</p><p>
When asked to store a value in a numeric column that is outside
the data type's allowable range, MySQL's behavior depends on the
SQL mode in effect at the time. For example, if no restrictive
modes are enabled, MySQL clips the value to the appropriate
endpoint of the range and stores the resulting value instead.
However, if strict SQL mode is enabled, MySQL rejects a value that
is out of range with an error, and the insert fails, in accordance
with the SQL standard.
</p><p>
In nonstrict mode, when an out-of-range value is assigned to an
integer column, MySQL stores the value representing the
corresponding endpoint of the column data type range. If you store
256 into a <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">TINYINT</code></a> or
<code class="literal">TINYINT UNSIGNED</code> column, MySQL stores 127 or
255, respectively. When a floating-point or fixed-point column is
assigned a value that exceeds the range implied by the specified
(or default) precision and scale, MySQL stores the value
representing the corresponding endpoint of that range.
</p><p>
Subtraction between integer values, where one is of type
<code class="literal">UNSIGNED</code>, produces an unsigned result by
default. If the result would otherwise have been negative, it
becomes the maximum integer value. If the
<a href="server-administration.html#sqlmode_no_unsigned_subtraction"><code class="literal">NO_UNSIGNED_SUBTRACTION</code></a> SQL mode
is enabled, the result is negative.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET SQL_MODE = '';</code></strong>
mysql> <strong class="userinput"><code>SELECT CAST(0 AS UNSIGNED) - 1;</code></strong>
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| 18446744073709551615 |
+-------------------------+
mysql> <strong class="userinput"><code>SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';</code></strong>
mysql> <strong class="userinput"><code>SELECT CAST(0 AS UNSIGNED) - 1;</code></strong>
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| -1 |
+-------------------------+
</pre><p>
If the result of such an operation is used to update an
<code class="literal">UNSIGNED</code> integer column, the result is clipped
to the maximum value for the column type, or clipped to 0 if
<a href="server-administration.html#sqlmode_no_unsigned_subtraction"><code class="literal">NO_UNSIGNED_SUBTRACTION</code></a> is
enabled. If strict SQL mode is enabled, an error occurs and the
column remains unchanged.
</p><p>
Conversions that occur due to clipping when MySQL is not operating
in strict mode are reported as warnings for
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>,
<a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax"><code class="literal">LOAD DATA
INFILE</code></a>, <a href="sql-syntax.html#update" title="12.2.11. UPDATE Syntax"><code class="literal">UPDATE</code></a>, and
multiple-row <a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> 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. For details, see <a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="date-and-time-types"></a>10.3. Date and Time Types</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="data-types.html#datetime">10.3.1. The <code class="literal">DATETIME</code>,
<code class="literal">DATE</code>, and
<code class="literal">TIMESTAMP</code> Types</a></span></dt><dt><span class="section"><a href="data-types.html#time">10.3.2. The <code class="literal">TIME</code> Type</a></span></dt><dt><span class="section"><a href="data-types.html#year">10.3.3. The <code class="literal">YEAR</code> Type</a></span></dt><dt><span class="section"><a href="data-types.html#y2k-issues">10.3.4. Year 2000 Issues and Date Types</a></span></dt></dl></div><a class="indexterm" name="id4333879"></a><a class="indexterm" name="id4333892"></a><p>
The date and time types for representing temporal values are
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a>,
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a>, and
<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a>. Each temporal type has a
range of legal values, as well as a “<span class="quote">zero</span>” value that
may be used when you specify an illegal value that MySQL cannot
represent. The <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> type has
special automatic updating behavior, described later on. For
temporal type storage requirements, see
<a href="data-types.html#storage-requirements" title="10.5. Data Type Storage Requirements">Section 10.5, “Data Type Storage Requirements”</a>.
</p><p>
MySQL gives warnings or errors if you try to insert an illegal
date. By setting the SQL mode to the appropriate value, you can
specify more exactly what kind of dates you want MySQL to support.
(See <a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.) You can get MySQL to
accept certain dates, such as <code class="literal">'2009-11-31'</code>, by
using the <a href="server-administration.html#sqlmode_allow_invalid_dates"><code class="literal">ALLOW_INVALID_DATES</code></a>
SQL mode. This is useful when you want to store a “<span class="quote">possibly
wrong</span>” 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
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> column. This is extremely
useful for applications that need to store a birthdate for which
you do not know the exact date. In this case, you simply store the
date as <code class="literal">'2009-00-00'</code> or
<code class="literal">'2009-01-00'</code>. If you store dates such as these,
you should not expect to get correct results for functions such as
<a href="functions.html#function_date-sub"><code class="literal">DATE_SUB()</code></a> or
<a href="functions.html#function_date-add"><code class="literal">DATE_ADD()</code></a> that require complete
dates. (If you do <span class="emphasis"><em>not</em></span> want to allow zero in
dates, you can use the
<a href="server-administration.html#sqlmode_no_zero_in_date"><code class="literal">NO_ZERO_IN_DATE</code></a> SQL mode).
</p><p>
Prior to MySQL 5.1.18, when <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>
values are compared with <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>
values, the time portion of the
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> value is ignored, or the
comparison could be performed as a string compare. Starting from
MySQL 5.1.18, a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> value is
coerced to the <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> type by
adding the time portion as <code class="literal">'00:00:00'</code>. To mimic
the old behavior, use the <a href="functions.html#function_cast"><code class="literal">CAST()</code></a>
function to cause the comparison operands to be treated as
previously. For example:
</p><pre class="programlisting"><em class="replaceable"><code>date_col</code></em> = CAST(NOW() AS DATE)
</pre><p>
MySQL also allows you to store <code class="literal">'0000-00-00'</code> as
a “<span class="quote">dummy date</span>” (if you are not using the
<a href="server-administration.html#sqlmode_no_zero_date"><code class="literal">NO_ZERO_DATE</code></a> SQL mode). This is
in some cases more convenient (and uses less data and index space)
than using <code class="literal">NULL</code> values.
</p><p>
Here are some general considerations to keep in mind when working
with date and time types:
</p><div class="itemizedlist"><ul type="disc"><li><p>
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.
</p></li><li><p>
Dates containing two-digit year values are ambiguous because
the century is unknown. MySQL interprets two-digit year values
using the following rules:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Year values in the range <code class="literal">70-99</code> are
converted to <code class="literal">1970-1999</code>.
</p></li><li><p>
Year values in the range <code class="literal">00-69</code> are
converted to <code class="literal">2000-2069</code>.
</p></li></ul></div></li><li><p>
Although MySQL tries to interpret values in several formats,
dates always must be given in year-month-day order (for
example, <code class="literal">'98-09-04'</code>), rather than in the
month-day-year or day-month-year orders commonly used
elsewhere (for example, <code class="literal">'09-04-98'</code>,
<code class="literal">'04-09-98'</code>).
</p></li><li><p>
MySQL automatically converts a date or time type value to a
number if the value is used in a numeric context and vice
versa.
</p></li><li><p>
By default, 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 “<span class="quote">zero</span>” value for that type. The
exception is that out-of-range
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> values are clipped to the
appropriate endpoint of the
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> range.
</p><p>
The following table shows the format of the
“<span class="quote">zero</span>” value for each type. Note that the use of
these values produces warnings if the
<a href="server-administration.html#sqlmode_no_zero_date"><code class="literal">NO_ZERO_DATE</code></a> SQL mode is
enabled.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Data Type</strong></span></td><td><span class="bold"><strong>“<span class="quote">Zero</span>” Value</strong></span></td></tr><tr><td><a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a></td><td><code class="literal">'0000-00-00 00:00:00'</code></td></tr><tr><td><a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a></td><td><code class="literal">'0000-00-00'</code></td></tr><tr><td><a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a></td><td><code class="literal">'0000-00-00 00:00:00'</code></td></tr><tr><td><a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a></td><td><code class="literal">'00:00:00'</code></td></tr><tr><td><a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a></td><td><code class="literal">0000</code></td></tr></tbody></table></div></li><li><p>
The “<span class="quote">zero</span>” 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
<code class="literal">'0'</code> or <code class="literal">0</code>, which are
easier to write.
</p></li><li><p>
“<span class="quote">Zero</span>” date or time values used through MyODBC
are converted automatically to <code class="literal">NULL</code> in
MyODBC 2.50.12 and above, because ODBC cannot handle such
values.
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="datetime"></a>10.3.1. The <code class="literal">DATETIME</code>,
<code class="literal">DATE</code>, and
<code class="literal">TIMESTAMP</code> Types</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="data-types.html#timestamp">10.3.1.1. <code class="literal">TIMESTAMP</code> Properties</a></span></dt></dl></div><a class="indexterm" name="id4334466"></a><a class="indexterm" name="id4334475"></a><a class="indexterm" name="id4334484"></a><a class="indexterm" name="id4334493"></a><a class="indexterm" name="id4334505"></a><a class="indexterm" name="id4334517"></a><p>
The <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>, and
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> types are related. This
section describes their characteristics, how they are similar,
and how they differ.
</p><p>
The <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> type is used when
you need values that contain both date and time information.
MySQL retrieves and displays
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> values in
<code class="literal">'YYYY-MM-DD HH:MM:SS'</code> format. The supported
range is <code class="literal">'1000-01-01 00:00:00'</code> to
<code class="literal">'9999-12-31 23:59:59'</code>.
</p><p>
The <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> type is used when you
need only a date value, without a time part. MySQL retrieves and
displays <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> values in
<code class="literal">'YYYY-MM-DD'</code> format. The supported range is
<code class="literal">'1000-01-01'</code> to
<code class="literal">'9999-12-31'</code>.
</p><p>
For the <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> and
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> range descriptions,
“<span class="quote">supported</span>” means that although earlier values
might work, there is no guarantee.
</p><p>
The <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> data type has a
range of <code class="literal">'1970-01-01 00:00:01'</code> UTC to
<code class="literal">'2038-01-19 03:14:07'</code> UTC. It 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.
</p><p>
You can specify <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>, and
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> values using any of a
common set of formats:
</p><div class="itemizedlist"><ul type="disc"><li><p>
As a string in either <code class="literal">'YYYY-MM-DD
HH:MM:SS'</code> or <code class="literal">'YY-MM-DD
HH:MM:SS'</code> format. A “<span class="quote">relaxed</span>” syntax
is allowed: Any punctuation character may be used as the
delimiter between date parts or time parts. For example,
<code class="literal">'98-12-31 11:30:45'</code>, <code class="literal">'98.12.31
11+30+45'</code>, <code class="literal">'98/12/31 11*30*45'</code>,
and <code class="literal">'98@12@31 11^30^45'</code> are equivalent.
</p></li><li><p>
As a string in either <code class="literal">'YYYY-MM-DD'</code> or
<code class="literal">'YY-MM-DD'</code> format. A
“<span class="quote">relaxed</span>” syntax is allowed here, too. For
example, <code class="literal">'98-12-31'</code>,
<code class="literal">'98.12.31'</code>,
<code class="literal">'98/12/31'</code>, and
<code class="literal">'98@12@31'</code> are equivalent.
</p></li><li><p>
As a string with no delimiters in either
<code class="literal">'YYYYMMDDHHMMSS'</code> or
<code class="literal">'YYMMDDHHMMSS'</code> format, provided that the
string makes sense as a date. For example,
<code class="literal">'20070523091528'</code> and
<code class="literal">'070523091528'</code> are interpreted as
<code class="literal">'2007-05-23 09:15:28'</code>, but
<code class="literal">'071122129015'</code> is illegal (it has a
nonsensical minute part) and becomes <code class="literal">'0000-00-00
00:00:00'</code>.
</p></li><li><p>
As a string with no delimiters in either
<code class="literal">'YYYYMMDD'</code> or <code class="literal">'YYMMDD'</code>
format, provided that the string makes sense as a date. For
example, <code class="literal">'20070523'</code> and
<code class="literal">'070523'</code> are interpreted as
<code class="literal">'2007-05-23'</code>, but
<code class="literal">'071332'</code> is illegal (it has nonsensical
month and day parts) and becomes
<code class="literal">'0000-00-00'</code>.
</p></li><li><p>
As a number in either <code class="literal">YYYYMMDDHHMMSS</code> or
<code class="literal">YYMMDDHHMMSS</code> format, provided that the
number makes sense as a date. For example,
<code class="literal">19830905132800</code> and
<code class="literal">830905132800</code> are interpreted as
<code class="literal">'1983-09-05 13:28:00'</code>.
</p></li><li><p>
As a number in either <code class="literal">YYYYMMDD</code> or
<code class="literal">YYMMDD</code> format, provided that the number
makes sense as a date. For example,
<code class="literal">19830905</code> and <code class="literal">830905</code>
are interpreted as <code class="literal">'1983-09-05'</code>.
</p></li><li><p>
As the result of a function that returns a value that is
acceptable in a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>, or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> context, such as
<a href="functions.html#function_now"><code class="literal">NOW()</code></a> or
<a href="functions.html#function_current-date"><code class="literal">CURRENT_DATE</code></a>.
</p></li></ul></div><p>
A microseconds part is allowable in temporal values in some
contexts, such as in literal values, and in the arguments to or
return values from some temporal functions. Microseconds are
specified as a trailing <code class="literal">.uuuuuu</code> part in the
value. Example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT MICROSECOND('2010-12-10 14:12:09.019473');</code></strong>
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
| 19473 |
+-------------------------------------------+
</pre><p>
However, microseconds cannot be stored into a column of any
temporal data type. Any microseconds part is discarded.
</p><p>
Conversion of <a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> values to numeric form
(for example, by adding <code class="literal">+0</code>) results in a
double value with a microseconds part of
<code class="literal">.000000</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT CURTIME(), CURTIME()+0;</code></strong>
+-----------+---------------+
| CURTIME() | CURTIME()+0 |
+-----------+---------------+
| 10:41:36 | 104136.000000 |
+-----------+---------------+
mysql> <strong class="userinput"><code>SELECT NOW(), NOW()+0;</code></strong>
+---------------------+-----------------------+
| NOW() | NOW()+0 |
+---------------------+-----------------------+
| 2007-11-30 10:41:47 | 20071130104147.000000 |
+---------------------+-----------------------+
</pre><p>
Illegal <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>, or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> values are converted to
the “<span class="quote">zero</span>” value of the appropriate type
(<code class="literal">'0000-00-00 00:00:00'</code> or
<code class="literal">'0000-00-00'</code>).
</p><p>
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 <code class="literal">10</code>.
<code class="literal">'1979-6-9'</code> is the same as
<code class="literal">'1979-06-09'</code>. 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 <code class="literal">10</code>.
<code class="literal">'1979-10-30 1:2:3'</code> is the same as
<code class="literal">'1979-10-30 01:02:03'</code>.
</p><p>
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
<code class="literal">YYYYMMDD</code> or <code class="literal">YYYYMMDDHHMMSS</code>
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
<code class="literal">YYMMDD</code> or <code class="literal">YYMMDDHHMMSS</code>
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.
</p><a class="indexterm" name="id4335214"></a><a class="indexterm" name="id4335222"></a><p>
Values specified as nondelimited 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 <code class="literal">'9903'</code>, thinking that represents
March, 1999, MySQL inserts a “<span class="quote">zero</span>” date value into
your table. This occurs because the year and month values are
<code class="literal">99</code> and <code class="literal">03</code>, 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
<code class="literal">'990300'</code> to insert the value
<code class="literal">'1999-03-00'</code>.
</p><p>
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If you assign a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> value to
a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> object, the time
part of the resulting value is set to
<code class="literal">'00:00:00'</code> because the
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> value contains no time
information.
</p></li><li><p>
If you assign a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> value to a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> object, the time part of
the resulting value is deleted because the
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> type stores no time
information.
</p></li><li><p>
Remember that although
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>, and
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> values all can be
specified using the same set of formats, the types do not
all have the same range of values. For example,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> values cannot be
earlier than <code class="literal">1970</code> UTC or later than
<code class="literal">'2038-01-19 03:14:07'</code> UTC. This means
that a date such as <code class="literal">'1968-01-01'</code>, while
legal as a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> or
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> value, is not valid as a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> value and is
converted to <code class="literal">0</code>.
</p></li></ul></div><a class="indexterm" name="id4335448"></a><a class="indexterm" name="id4335461"></a><p>
Be aware of certain problems when specifying date values:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The relaxed format allowed for values specified as strings
can be deceiving. For example, a value such as
<code class="literal">'10:11:12'</code> might look like a time value
because of the “<span class="quote"><code class="literal">:</code></span>”
delimiter, but if used in a date context is interpreted as
the year <code class="literal">'2010-11-12'</code>. The value
<code class="literal">'10:45:15'</code> is converted to
<code class="literal">'0000-00-00'</code> because
<code class="literal">'45'</code> is not a legal month.
</p></li><li><p>
The server requires that month and day values be legal, and
not merely in the range 1 to 12 and 1 to 31, respectively.
With strict mode disabled, invalid dates such as
<code class="literal">'2004-04-31'</code> are converted to
<code class="literal">'0000-00-00'</code> and a warning is generated.
With strict mode enabled, invalid dates generate an error.
To allow such dates, enable
<a href="server-administration.html#sqlmode_allow_invalid_dates"><code class="literal">ALLOW_INVALID_DATES</code></a>. See
<a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>, for more information.
</p></li><li><p>
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
<code class="literal">'0000-00-00 00:00:00'</code>.
</p></li><li><p>
Dates containing two-digit year values are ambiguous because
the century is unknown. MySQL interprets two-digit year
values using the following rules:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Year values in the range <code class="literal">00-69</code> are
converted to <code class="literal">2000-2069</code>.
</p></li><li><p>
Year values in the range <code class="literal">70-99</code> are
converted to <code class="literal">1970-1999</code>.
</p></li></ul></div></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="timestamp"></a>10.3.1.1. <code class="literal">TIMESTAMP</code> Properties</h4></div></div></div><p>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> columns are displayed
in the same format as <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>
columns. In other words, the display width is fixed at 19
characters, and the format is <code class="literal">'YYYY-MM-DD
HH:MM:SS'</code>.
</p><p>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> values are converted
from the current time zone to UTC for storage, and converted
back from UTC to the current time zone for retrieval. (This
occurs only for the <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a>
data type, not for other types such as
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>.) By default, the
current time zone for each connection is the server's time.
The time zone can be set on a per-connection basis, as
described in <a href="internationalization-localization.html#time-zone-support" title="9.7. MySQL Server Time Zone Support">Section 9.7, “MySQL Server Time Zone Support”</a>. As long as
the time zone setting remains constant, you get back the same
value you store. If you store a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> value, and then
change the time zone and retrieve the value, the retrieved
value is different from 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 <a href="server-administration.html#sysvar_time_zone"><code class="literal">time_zone</code></a> system
variable.
</p><p>
The <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> data type offers
automatic initialization and updating. You can choose whether
to use these properties and which column should have them:
</p><div class="itemizedlist"><ul type="disc"><li><p>
For one <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column in
a table, you can assign the current timestamp as the
default value and the auto-update value. It is possible to
have the current timestamp be the default value for
initializing the column, for the auto-update value, or
both. It is not possible to have the current timestamp be
the default value for one column and the auto-update value
for another column.
</p></li><li><p>
Any single <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column
in a table can be used as the one that is initialized to
the current date and time, or updated automatically. This
need not be the first
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column.
</p></li><li><p>
If a <code class="literal">DEFAULT</code> value is specified for the
first <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column in a
table, it is not ignored. The default can be
<a href="functions.html#function_current-timestamp"><code class="literal">CURRENT_TIMESTAMP</code></a> or a
constant date and time value.
</p></li><li><p>
In a <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
statement, the first
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column can be
declared in any of the following ways:
</p><div class="itemizedlist"><ul type="circle"><li><p>
With both <code class="literal">DEFAULT CURRENT_TIMESTAMP</code>
and <code class="literal">ON UPDATE CURRENT_TIMESTAMP</code>
clauses, the column has the current timestamp for its
default value, and is automatically updated.
</p></li><li><p>
With neither <code class="literal">DEFAULT</code> nor
<code class="literal">ON UPDATE</code> clauses, it is the same
as <code class="literal">DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP</code>.
</p></li><li><p>
With a <code class="literal">DEFAULT CURRENT_TIMESTAMP</code>
clause and no <code class="literal">ON UPDATE</code> clause, the
column has the current timestamp for its default value
but is not automatically updated.
</p></li><li><p>
With no <code class="literal">DEFAULT</code> clause and with an
<code class="literal">ON UPDATE CURRENT_TIMESTAMP</code> clause,
the column has a default of 0 and is automatically
updated.
</p></li><li><p>
With a constant <code class="literal">DEFAULT</code> value, the
column has the given default and is not automatically
initialized to the current timestamp. If the column
also has an <code class="literal">ON UPDATE
CURRENT_TIMESTAMP</code> clause, it is
automatically updated; otherwise, it has a constant
default and is not automatically updated.
</p></li></ul></div><p>
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 <code class="literal">ON
UPDATE</code> to enable auto-update without also having
the column auto-initialized.) The following column
definitions demonstrate each of the possibilities:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Auto-initialization and auto-update:
</p><pre class="programlisting">ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
</pre></li><li><p>
Auto-initialization only:
</p><pre class="programlisting">ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
</pre></li><li><p>
Auto-update only:
</p><pre class="programlisting">ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
</pre></li><li><p>
Neither:
</p><pre class="programlisting">ts TIMESTAMP DEFAULT 0
</pre></li></ul></div></li><li><p>
To specify automatic default or updating for a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column other than
the first one, you must suppress the automatic
initialization and update behaviors for the first
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column by
explicitly assigning it a constant
<code class="literal">DEFAULT</code> value (for example,
<code class="literal">DEFAULT 0</code> or <code class="literal">DEFAULT
'2003-01-01 00:00:00'</code>). Then, for the other
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column, the rules
are the same as for the first
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column, except
that if you omit both of the <code class="literal">DEFAULT</code>
and <code class="literal">ON UPDATE</code> clauses, no automatic
initialization or updating occurs.
</p><p>
Example:
</p><pre class="programlisting">CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
</pre></li><li><p>
<a href="functions.html#function_current-timestamp"><code class="literal">CURRENT_TIMESTAMP</code></a> or any of
its synonyms
(<a href="functions.html#function_current-timestamp"><code class="literal">CURRENT_TIMESTAMP()</code></a>,
<a href="functions.html#function_now"><code class="literal">NOW()</code></a>,
<a href="functions.html#function_localtime"><code class="literal">LOCALTIME</code></a>,
<a href="functions.html#function_localtime"><code class="literal">LOCALTIME()</code></a>,
<a href="functions.html#function_localtimestamp"><code class="literal">LOCALTIMESTAMP</code></a>, or
<a href="functions.html#function_localtimestamp"><code class="literal">LOCALTIMESTAMP()</code></a>) can be
used in the <code class="literal">DEFAULT</code> and <code class="literal">ON
UPDATE</code> clauses. They all mean “<span class="quote">the current
timestamp.</span>”
(<a href="functions.html#function_utc-timestamp"><code class="literal">UTC_TIMESTAMP</code></a> is not
allowed. Its range of values does not align with those of
the <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column anyway
unless the current time zone is <code class="literal">UTC</code>.)
</p></li><li><p>
The order of the <code class="literal">DEFAULT</code> and
<code class="literal">ON UPDATE</code> attributes does not matter.
If both <code class="literal">DEFAULT</code> and <code class="literal">ON
UPDATE</code> are specified for a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column, either
can precede the other. For example, these statements are
equivalent:
</p><pre class="programlisting">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);
</pre></li></ul></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
The examples that use <code class="literal">DEFAULT 0</code> will not
work if the <a href="server-administration.html#sqlmode_no_zero_date"><code class="literal">NO_ZERO_DATE</code></a>
SQL mode is enabled because that mode causes
“<span class="quote">zero</span>” date values (specified as
<code class="literal">0</code>, <code class="literal">'0000-00-00</code>, or
<code class="literal">'0000-00-00 00:00:00'</code>) to be rejected. Be
aware that the <a href="server-administration.html#sqlmode_traditional"><code class="literal">TRADITIONAL</code></a>
SQL mode includes
<a href="server-administration.html#sqlmode_no_zero_date"><code class="literal">NO_ZERO_DATE</code></a>.
</p></div><p>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> columns are
<code class="literal">NOT NULL</code> by default, cannot contain
<code class="literal">NULL</code> values, and assigning
<code class="literal">NULL</code> assigns the current timestamp.
However, a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column can
be allowed to contain <code class="literal">NULL</code> by declaring it
with the <code class="literal">NULL</code> attribute. In this case, the
default value also becomes <code class="literal">NULL</code> unless
overridden with a <code class="literal">DEFAULT</code> clause that
specifies a different default value. <code class="literal">DEFAULT
NULL</code> can be used to explicitly specify
<code class="literal">NULL</code> as the default value. (For a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column not declared
with the <code class="literal">NULL</code> attribute, <code class="literal">DEFAULT
NULL</code> is illegal.) If a
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column allows
<code class="literal">NULL</code> values, assigning
<code class="literal">NULL</code> sets it to <code class="literal">NULL</code>,
not to the current timestamp.
</p><p>
The following table contains several
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> columns that allow
<code class="literal">NULL</code> values:
</p><pre class="programlisting">CREATE TABLE t
(
ts1 TIMESTAMP NULL DEFAULT NULL,
ts2 TIMESTAMP NULL DEFAULT 0,
ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
</pre><p>
Note that a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column
that allows <code class="literal">NULL</code> values will
<span class="emphasis"><em>not</em></span> take on the current timestamp except
under one of the following conditions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Its default value is defined as
<a href="functions.html#function_current-timestamp"><code class="literal">CURRENT_TIMESTAMP</code></a>
</p></li><li><p>
<a href="functions.html#function_now"><code class="literal">NOW()</code></a> or
<a href="functions.html#function_current-timestamp"><code class="literal">CURRENT_TIMESTAMP</code></a> is
inserted into the column
</p></li></ul></div><p>
In other words, a <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a>
column defined as <code class="literal">NULL</code> will auto-initialize
only if it is created using a definition such as the
following:
</p><pre class="programlisting">CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
</pre><p>
Otherwise — that is, if the
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> column is defined to
allow <code class="literal">NULL</code> values but not using
<code class="literal">DEFAULT CURRENT_TIMESTAMP</code>, as shown
here…
</p><pre class="programlisting">CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT NULL);
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
</pre><p>
…then you must explicitly insert a value corresponding
to the current date and time. For example:
</p><pre class="programlisting">INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
The MySQL server can be run with the
<a href="server-administration.html#sqlmode_maxdb"><code class="literal">MAXDB</code></a> SQL mode enabled.
When the server runs with this mode enabled,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> is identical with
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>. That is, if this
mode is enabled at the time that a table is created,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> columns are created
as <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> columns. As a
result, such columns use
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> display format, have
the same range of values, and there is no automatic
initialization or updating to the current date and time.
</p></div><p>
To enable <a href="server-administration.html#sqlmode_maxdb"><code class="literal">MAXDB</code></a> mode, set
the server SQL mode to <a href="server-administration.html#sqlmode_maxdb"><code class="literal">MAXDB</code></a>
at startup using the
<a href="server-administration.html#option_mysqld_sql-mode"><code class="option">--sql-mode=MAXDB</code></a> server option
or by setting the global
<a href="server-administration.html#sysvar_sql_mode"><code class="literal">sql_mode</code></a> variable at runtime:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET GLOBAL sql_mode=MAXDB;</code></strong>
</pre><p>
A client can cause the server to run in
<a href="server-administration.html#sqlmode_maxdb"><code class="literal">MAXDB</code></a> mode for its own
connection as follows:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET SESSION sql_mode=MAXDB;</code></strong>
</pre></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="time"></a>10.3.2. The <code class="literal">TIME</code> Type</h3></div></div></div><a class="indexterm" name="id4336670"></a><a class="indexterm" name="id4336679"></a><p>
MySQL retrieves and displays <a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a>
values in <code class="literal">'HH:MM:SS'</code> format (or
<code class="literal">'HHH:MM:SS'</code> format for large hours values).
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> values may range from
<code class="literal">'-838:59:59'</code> to
<code class="literal">'838:59:59'</code>. The hours part may be so large
because the <a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> type can 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).
</p><p>
You can specify <a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> values in a
variety of formats:
</p><div class="itemizedlist"><ul type="disc"><li><p>
As a string in <code class="literal">'D HH:MM:SS.fraction'</code>
format. You can also use one of the following
“<span class="quote">relaxed</span>” syntaxes:
<code class="literal">'HH:MM:SS.fraction'</code>,
<code class="literal">'HH:MM:SS'</code>, <code class="literal">'HH:MM'</code>,
<code class="literal">'D HH:MM:SS'</code>, <code class="literal">'D
HH:MM'</code>, <code class="literal">'D HH'</code>, or
<code class="literal">'SS'</code>. Here <code class="literal">D</code>
represents days and can have a value from 0 to 34. Note that
MySQL does not store the fraction part.
</p></li><li><p>
As a string with no delimiters in
<code class="literal">'HHMMSS'</code> format, provided that it makes
sense as a time. For example, <code class="literal">'101112'</code> is
understood as <code class="literal">'10:11:12'</code>, but
<code class="literal">'109712'</code> is illegal (it has a nonsensical
minute part) and becomes <code class="literal">'00:00:00'</code>.
</p></li><li><p>
As a number in <code class="literal">HHMMSS</code> format, provided
that it makes sense as a time. For example,
<code class="literal">101112</code> is understood as
<code class="literal">'10:11:12'</code>. The following alternative
formats are also understood: <code class="literal">SS</code>,
<code class="literal">MMSS</code>, <code class="literal">HHMMSS</code>,
<code class="literal">HHMMSS.fraction</code>. Note that MySQL does not
store the fraction part.
</p></li><li><p>
As the result of a function that returns a value that is
acceptable in a <a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> context,
such as <a href="functions.html#function_current-time"><code class="literal">CURRENT_TIME</code></a>.
</p></li></ul></div><p>
A trailing <code class="literal">.uuuuuu</code> microseconds part of
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> values is allowed under the
same conditions as for other temporal values, as described in
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types">Section 10.3.1, “The <code class="literal">DATETIME</code>,
<code class="literal">DATE</code>, and
<code class="literal">TIMESTAMP</code> Types”</a>. This includes the property that any
microseconds part is discarded from values stored into
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> columns.
</p><p>
For <a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> 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 <code class="literal">10</code>. <code class="literal">'8:3:2'</code>
is the same as <code class="literal">'08:03:02'</code>.
</p><p>
Be careful about assigning abbreviated values to a
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> column. Without colons,
MySQL interprets values using the assumption that the two
rightmost digits represent seconds. (MySQL interprets
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> values as elapsed time
rather than as time of day.) For example, you might think of
<code class="literal">'1112'</code> and <code class="literal">1112</code> as meaning
<code class="literal">'11:12:00'</code> (12 minutes after 11 o'clock), but
MySQL interprets them as <code class="literal">'00:11:12'</code> (11
minutes, 12 seconds). Similarly, <code class="literal">'12'</code> and
<code class="literal">12</code> are interpreted as
<code class="literal">'00:00:12'</code>.
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> values with colons, by
contrast, are always treated as time of the day. That is,
<code class="literal">'11:12'</code> mean <code class="literal">'11:12:00'</code>,
not <code class="literal">'00:11:12'</code>.
</p><p>
By default, values that lie outside the
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> range but are otherwise
legal are clipped to the closest endpoint of the range. For
example, <code class="literal">'-850:00:00'</code> and
<code class="literal">'850:00:00'</code> are converted to
<code class="literal">'-838:59:59'</code> and
<code class="literal">'838:59:59'</code>. Illegal
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> values are converted to
<code class="literal">'00:00:00'</code>. Note that because
<code class="literal">'00:00:00'</code> is itself a legal
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> value, there is no way to
tell, from a value of <code class="literal">'00:00:00'</code> stored in a
table, whether the original value was specified as
<code class="literal">'00:00:00'</code> or whether it was illegal.
</p><p>
For more restrictive treatment of invalid
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a> values, enable strict SQL
mode to cause errors to occur. See
<a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="year"></a>10.3.3. The <code class="literal">YEAR</code> Type</h3></div></div></div><a class="indexterm" name="id4337184"></a><a class="indexterm" name="id4337192"></a><p>
The <a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> type is a one-byte type
used for representing years. It can be declared as
<code class="literal">YEAR(2)</code> or <code class="literal">YEAR(4)</code> to
specify a display width of two or four characters. The default
is four characters if no width is given.
</p><p>
For four-digit format, MySQL displays
<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> values in
<code class="literal">YYYY</code> format, with a range of
<code class="literal">1901</code> to <code class="literal">2155</code>. For
two-digit format, MySQL displays values with a range of
<code class="literal">70</code> (1970) to <code class="literal">69</code> (2069).
</p><p>
You can specify input <a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> values
in a variety of formats:
</p><div class="itemizedlist"><ul type="disc"><li><p>
As a four-digit string in the range
<code class="literal">'1901'</code> to <code class="literal">'2155'</code>.
</p></li><li><p>
As a four-digit number in the range <code class="literal">1901</code>
to <code class="literal">2155</code>.
</p></li><li><p>
As a two-digit string in the range <code class="literal">'00'</code>
to <code class="literal">'99'</code>. Values in the ranges
<code class="literal">'00'</code> to <code class="literal">'69'</code> and
<code class="literal">'70'</code> to <code class="literal">'99'</code> are
converted to <a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> values in
the ranges <code class="literal">2000</code> to
<code class="literal">2069</code> and <code class="literal">1970</code> to
<code class="literal">1999</code>.
</p></li><li><p>
As a two-digit number in the range <code class="literal">1</code> to
<code class="literal">99</code>. Values in the ranges
<code class="literal">1</code> to <code class="literal">69</code> and
<code class="literal">70</code> to <code class="literal">99</code> are converted
to <a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> values in the ranges
<code class="literal">2001</code> to <code class="literal">2069</code> and
<code class="literal">1970</code> to <code class="literal">1999</code>. 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 <code class="literal">2000</code>. You must specify it as a string
<code class="literal">'0'</code> or <code class="literal">'00'</code> or it is
interpreted as <code class="literal">0000</code>.
</p></li><li><p>
As the result of a function that returns a value that is
acceptable in a <a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> context,
such as <a href="functions.html#function_now"><code class="literal">NOW()</code></a>.
</p></li></ul></div><p>
Illegal <a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> values are converted
to <code class="literal">0000</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="y2k-issues"></a>10.3.4. Year 2000 Issues and Date Types</h3></div></div></div><a class="indexterm" name="id4337543"></a><a class="indexterm" name="id4337552"></a><a class="indexterm" name="id4337564"></a><a class="indexterm" name="id4337573"></a><a class="indexterm" name="id4337585"></a><p>
MySQL Server itself has no problems with Year 2000 (Y2K)
compliance:
</p><div class="itemizedlist"><ul type="disc"><li><p>
MySQL Server uses Unix time functions that handle dates into
the year <code class="literal">2038</code> for
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> values. For
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a> and
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> values, dates
through the year <code class="literal">9999</code> are accepted.
</p></li><li><p>
All MySQL date functions are implemented in one source file,
<code class="filename">sql/time.cc</code>, and are coded very
carefully to be year 2000-safe.
</p></li><li><p>
In MySQL, the <a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> data type
can store the years <code class="literal">0</code> and
<code class="literal">1901</code> to <code class="literal">2155</code> in one
byte and display them using two or four digits. All
two-digit years are considered to be in the range
<code class="literal">1970</code> to <code class="literal">2069</code>, which
means that if you store <code class="literal">01</code> in a
<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> column, MySQL Server
treats it as <code class="literal">2001</code>.
</p></li></ul></div><p>
Although MySQL Server itself is Y2K-safe, you may run into
problems if you use it with applications that are not Y2K-safe.
For example, many old applications store or manipulate years
using two-digit values (which are ambiguous) rather than
four-digit values. This problem may be compounded by
applications that use values such as <code class="literal">00</code> or
<code class="literal">99</code> as “<span class="quote">missing</span>” value
indicators. Unfortunately, these problems may be difficult to
fix because different applications may be written by different
programmers, each of whom may use a different set of conventions
and date-handling functions.
</p><p>
Thus, even though MySQL Server has no Y2K problems, <span class="emphasis"><em>it
is the application's responsibility to provide unambiguous
input</em></span>. Any value containing a two-digit year is
ambiguous, because the century is unknown. Such values must be
interpreted into four-digit form because MySQL stores years
internally using four digits.
</p><p>
For <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>,
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a>, and
<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> types, MySQL interprets
dates with ambiguous year values using the following rules:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Year values in the range <code class="literal">00-69</code> are
converted to <code class="literal">2000-2069</code>.
</p></li><li><p>
Year values in the range <code class="literal">70-99</code> are
converted to <code class="literal">1970-1999</code>.
</p></li></ul></div><p>
Remember that these rules are only heuristics that provide
reasonable guesses as to what your data values mean. If the
rules used by MySQL do not produce the correct values, you
should provide unambiguous input containing four-digit year
values.
</p><p>
<code class="literal">ORDER BY</code> properly sorts
<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> values that have two-digit
years.
</p><p>
Some functions like <a href="functions.html#function_min"><code class="literal">MIN()</code></a> and
<a href="functions.html#function_max"><code class="literal">MAX()</code></a> convert a
<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> 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
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> or
<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a> to four-digit year format.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="string-types"></a>10.4. String Types</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="data-types.html#char">10.4.1. The <code class="literal">CHAR</code> and
<code class="literal">VARCHAR</code> Types</a></span></dt><dt><span class="section"><a href="data-types.html#binary-varbinary">10.4.2. The <code class="literal">BINARY</code> and
<code class="literal">VARBINARY</code> Types</a></span></dt><dt><span class="section"><a href="data-types.html#blob">10.4.3. The <code class="literal">BLOB</code> and
<code class="literal">TEXT</code> Types</a></span></dt><dt><span class="section"><a href="data-types.html#enum">10.4.4. The <code class="literal">ENUM</code> Type</a></span></dt><dt><span class="section"><a href="data-types.html#set">10.4.5. The <code class="literal">SET</code> Type</a></span></dt></dl></div><a class="indexterm" name="id4337904"></a><a class="indexterm" name="id4337916"></a><a class="indexterm" name="id4337925"></a><a class="indexterm" name="id4337934"></a><a class="indexterm" name="id4337943"></a><a class="indexterm" name="id4337956"></a><p>
The string types are <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a>,
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>,
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a>,
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a>,
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>, and
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a>. This section describes how
these types work and how to use them in your queries. For string
type storage requirements, see
<a href="data-types.html#storage-requirements" title="10.5. Data Type Storage Requirements">Section 10.5, “Data Type Storage Requirements”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="char"></a>10.4.1. The <code class="literal">CHAR</code> and
<code class="literal">VARCHAR</code> Types</h3></div></div></div><p>
The <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> and
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> 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.
</p><p>
The <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> and
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> types are declared with a
length that indicates the maximum number of characters you want
to store. For example, <code class="literal">CHAR(30)</code> can hold up
to 30 characters.
</p><p>
The length of a <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> 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
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> values are stored, they are
right-padded with spaces to the specified length. When
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> values are retrieved,
trailing spaces are removed unless the
<a href="server-administration.html#sqlmode_pad_char_to_full_length"><code class="literal">PAD_CHAR_TO_FULL_LENGTH</code></a> SQL
mode is enabled.
</p><p>
Values in <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> columns are
variable-length strings. The length can be specified as a value
from 0 to 65,535. The effective maximum length of a
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> is subject to the maximum
row size (65,535 bytes, which is shared among all columns) and
the character set used.
</p><p>
In contrast to <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a>,
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> values are stored as a
one-byte or two-byte length prefix plus data. The length prefix
indicates the number of bytes in the value. A column uses one
length byte if values require no more than 255 bytes, two length
bytes if values may require more than 255 bytes.
</p><p>
If strict SQL mode is not enabled and you assign a value to a
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> or
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> column that exceeds the
column's maximum length, the value is truncated to fit and a
warning is generated. For truncation of nonspace characters, you
can cause an error to occur (rather than a warning) and suppress
insertion of the value by using strict SQL mode. See
<a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p><p>
For <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> columns, trailing
spaces in excess of the column length are truncated prior to
insertion and a warning is generated, regardless of the SQL mode
in use. For <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> columns,
truncation of excess trailing spaces from inserted values is
performed silently regardless of the SQL mode.
</p><p>
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> values are not padded
when they are stored. Trailing spaces are retained when values
are stored and retrieved, in conformance with standard SQL.
</p><p>
The following table illustrates the differences between
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> and
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> by showing the result of
storing various string values into <code class="literal">CHAR(4)</code>
and <code class="literal">VARCHAR(4)</code> columns (assuming that the
column uses a single-byte character set such as
<code class="literal">latin1</code>).
</p><div class="informaltable"><table border="1"><colgroup><col><col><col><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Value</strong></span></td><td><code class="literal">CHAR(4)</code></td><td><span class="bold"><strong>Storage Required</strong></span></td><td><code class="literal">VARCHAR(4)</code></td><td><span class="bold"><strong>Storage Required</strong></span></td></tr><tr><td><code class="literal">''</code></td><td><code class="literal">' '</code></td><td>4 bytes</td><td><code class="literal">''</code></td><td>1 byte</td></tr><tr><td><code class="literal">'ab'</code></td><td><code class="literal">'ab '</code></td><td>4 bytes</td><td><code class="literal">'ab'</code></td><td>3 bytes</td></tr><tr><td><code class="literal">'abcd'</code></td><td><code class="literal">'abcd'</code></td><td>4 bytes</td><td><code class="literal">'abcd'</code></td><td>5 bytes</td></tr><tr><td><code class="literal">'abcdefgh'</code></td><td><code class="literal">'abcd'</code></td><td>4 bytes</td><td><code class="literal">'abcd'</code></td><td>5 bytes</td></tr></tbody></table></div><p>
The values shown as stored in the last row of the table apply
<span class="emphasis"><em>only when not using strict mode</em></span>; if MySQL
is running in strict mode, values that exceed the column length
are <span class="emphasis"><em>not stored</em></span>, and an error results.
</p><p>
If a given value is stored into the <code class="literal">CHAR(4)</code>
and <code class="literal">VARCHAR(4)</code> columns, the values retrieved
from the columns are not always the same because trailing spaces
are removed from <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> columns
upon retrieval. The following example illustrates this
difference:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE vc (v VARCHAR(4), c CHAR(4));</code></strong>
Query OK, 0 rows affected (0.01 sec)
mysql> <strong class="userinput"><code>INSERT INTO vc VALUES ('ab ', 'ab ');</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;</code></strong>
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab ) | (ab) |
+---------------------+---------------------+
1 row in set (0.06 sec)
</pre><p>
Values in <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> and
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> columns are sorted and
compared according to the character set collation assigned to
the column.
</p><p>
All MySQL collations are of type <code class="literal">PADSPACE</code>.
This means that all <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> and
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> values in MySQL are
compared without regard to any trailing spaces. For example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));</code></strong>
Query OK, 0 rows affected (0.09 sec)
mysql> <strong class="userinput"><code>INSERT INTO names VALUES ('Monty ', 'Monty ');</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT myname = 'Monty ', yourname = 'Monty ' FROM names;</code></strong>
+--------------------+----------------------+
| myname = 'Monty ' | yourname = 'Monty ' |
+--------------------+----------------------+
| 1 | 1 |
+--------------------+----------------------+
1 row in set (0.00 sec)
</pre><p>
This is true for all MySQL versions, and is not affected by the
server SQL mode.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
For more information about MySQL character sets and
collations, see <a href="internationalization-localization.html#charset" title="9.1. Character Set Support">Section 9.1, “Character Set Support”</a>.
</p></div><p>
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. For example, if a table contains
<code class="literal">'a'</code>, an attempt to store
<code class="literal">'a '</code> causes a duplicate-key error.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="binary-varbinary"></a>10.4.2. The <code class="literal">BINARY</code> and
<code class="literal">VARBINARY</code> Types</h3></div></div></div><a class="indexterm" name="id4338645"></a><a class="indexterm" name="id4338654"></a><a class="indexterm" name="id4338663"></a><a class="indexterm" name="id4338676"></a><p>
The <a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> and
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> types are similar to
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> and
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>, except that they contain
binary strings rather than nonbinary strings. That is, they
contain byte strings rather than character strings. This means
that they have no character set, and sorting and comparison are
based on the numeric values of the bytes in the values.
</p><p>
The allowable maximum length is the same for
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> and
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> as it is for
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> and
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>, except that the length
for <a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> and
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> is a length in bytes
rather than in characters.
</p><p>
The <a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> and
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> data types are distinct
from the <code class="literal">CHAR BINARY</code> and <code class="literal">VARCHAR
BINARY</code> data types. For the latter types, the
<code class="literal">BINARY</code> 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 nonbinary character strings rather
than binary byte strings. For example, <code class="literal">CHAR(5)
BINARY</code> is treated as <code class="literal">CHAR(5) CHARACTER SET
latin1 COLLATE latin1_bin</code>, assuming that the default
character set is <code class="literal">latin1</code>. This differs from
<code class="literal">BINARY(5)</code>, which stores 5-bytes binary
strings that have no character set or collation. For information
about differences between nonbinary string binary collations and
binary strings, see <a href="internationalization-localization.html#charset-binary-collations" title="9.1.7.4. The _bin and binary Collations">Section 9.1.7.4, “The <code class="literal">_bin</code> and <code class="literal">binary</code> Collations”</a>.
</p><p>
If strict SQL mode is not enabled and you assign a value to a
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> or
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> column that exceeds the
column's maximum length, the value is truncated to fit and a
warning is generated. For cases of truncation, you can cause an
error to occur (rather than a warning) and suppress insertion of
the value by using strict SQL mode. See
<a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p><p>
When <a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> values are stored,
they are right-padded with the pad value to the specified
length. The pad value is <code class="literal">0x00</code> (the zero
byte). Values are right-padded with <code class="literal">0x00</code> on
insert, and no trailing bytes are removed on select. All bytes
are significant in comparisons, including <code class="literal">ORDER
BY</code> and <code class="literal">DISTINCT</code> operations.
<code class="literal">0x00</code> bytes and spaces are different in
comparisons, with <code class="literal">0x00</code> < space.
</p><p>
Example: For a <code class="literal">BINARY(3)</code> column,
<code class="literal">'a '</code> becomes
<code class="literal">'a \0'</code> when inserted.
<code class="literal">'a\0'</code> becomes <code class="literal">'a\0\0'</code> when
inserted. Both inserted values remain unchanged when selected.
</p><p>
For <a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a>, there is no
padding on insert and no bytes are stripped on select. All bytes
are significant in comparisons, including <code class="literal">ORDER
BY</code> and <code class="literal">DISTINCT</code> operations.
<code class="literal">0x00</code> bytes and spaces are different in
comparisons, with <code class="literal">0x00</code> < space.
</p><p>
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. For example, if a table contains <code class="literal">'a'</code>,
an attempt to store <code class="literal">'a\0'</code> causes a
duplicate-key error.
</p><p>
You should consider the preceding padding and stripping
characteristics carefully if you plan to use the
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> data type for storing
binary data and you require that the value retrieved be exactly
the same as the value stored. The following example illustrates
how <code class="literal">0x00</code>-padding of
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> values affects column
value comparisons:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t (c BINARY(3));</code></strong>
Query OK, 0 rows affected (0.01 sec)
mysql> <strong class="userinput"><code>INSERT INTO t SET c = 'a';</code></strong>
Query OK, 1 row affected (0.01 sec)
mysql> <strong class="userinput"><code>SELECT HEX(c), c = 'a', c = 'a\0\0' from t;</code></strong>
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 | 0 | 1 |
+--------+---------+-------------+
1 row in set (0.09 sec)
</pre><p>
If the value retrieved must be the same as the value specified
for storage with no padding, it might be preferable to use
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> or one of the
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> data types instead.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="blob"></a>10.4.3. The <code class="literal">BLOB</code> and
<code class="literal">TEXT</code> Types</h3></div></div></div><a class="indexterm" name="id4339101"></a><a class="indexterm" name="id4339110"></a><a class="indexterm" name="id4339119"></a><a class="indexterm" name="id4339132"></a><a class="indexterm" name="id4339144"></a><a class="indexterm" name="id4339153"></a><p>
A <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> is a binary large object
that can hold a variable amount of data. The four
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> types are
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TINYBLOB</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMBLOB</code></a>, and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGBLOB</code></a>. These differ only in
the maximum length of the values they can hold. The four
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> types are
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TINYTEXT</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMTEXT</code></a>, and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGTEXT</code></a>. These
correspond to the four <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> types
and have the same maximum lengths and storage requirements. See
<a href="data-types.html#storage-requirements" title="10.5. Data Type Storage Requirements">Section 10.5, “Data Type Storage Requirements”</a>.
</p><p>
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> values are treated as binary
strings (byte strings). They have no character set, and sorting
and comparison are based on the numeric values of the bytes in
column values. <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> values are
treated as nonbinary strings (character strings). They have a
character set, and values are sorted and compared based on the
collation of the character set.
</p><p>
If strict SQL mode is not enabled and you assign a value to a
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column that exceeds the
column's maximum length, the value is truncated to fit and a
warning is generated. For truncation of nonspace characters, you
can cause an error to occur (rather than a warning) and suppress
insertion of the value by using strict SQL mode. See
<a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p><p>
Beginning with MySQL 5.1.24, truncation of excess trailing
spaces from values to be inserted into
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> columns always generates a
warning, regardless of the SQL mode.
</p><p>
If a <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column is indexed,
index entry comparisons are space-padded at the end. This means
that, if the index requires unique values, duplicate-key errors
will occur for values that differ only in the number of trailing
spaces. For example, if a table contains <code class="literal">'a'</code>,
an attempt to store <code class="literal">'a '</code> causes a
duplicate-key error. This is not true for
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> columns.
</p><p>
In most respects, you can regard a
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> column as a
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> column that can be as
large as you like. Similarly, you can regard a
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column as a
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> column.
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> differ from
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> and
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> in the following ways:
</p><div class="itemizedlist"><ul type="disc"><li><p>
For indexes on <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> columns, you must
specify an index prefix length. For
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a> and
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>, a prefix length is
optional. See <a href="optimization.html#indexes" title="7.4.2. Column Indexes">Section 7.4.2, “Column Indexes”</a>.
</p></li><li><p>
<a class="indexterm" name="id4339462"></a>
<a class="indexterm" name="id4339474"></a>
<a class="indexterm" name="id4339487"></a>
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> columns cannot have
<code class="literal">DEFAULT</code> values.
</p></li></ul></div><p>
If you use the <code class="literal">BINARY</code> attribute with a
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> data type, the column is
assigned the binary collation of the column character set.
</p><p>
<code class="literal">LONG</code> and <code class="literal">LONG VARCHAR</code> map
to the <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMTEXT</code></a> data type. This
is a compatibility feature.
</p><p>
MySQL Connector/ODBC defines <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a>
values as <code class="literal">LONGVARBINARY</code> and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> values as
<code class="literal">LONGVARCHAR</code>.
</p><p>
Because <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> values can be extremely
long, you might encounter some constraints in using them:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Only the first
<a href="server-administration.html#sysvar_max_sort_length"><code class="literal">max_sort_length</code></a> bytes of
the column are used when sorting. The default value of
<a href="server-administration.html#sysvar_max_sort_length"><code class="literal">max_sort_length</code></a> is 1024.
You can make more bytes significant in sorting or grouping
by increasing the value of
<a href="server-administration.html#sysvar_max_sort_length"><code class="literal">max_sort_length</code></a> at server
startup or runtime. Any client can change the value of its
session <a href="server-administration.html#sysvar_max_sort_length"><code class="literal">max_sort_length</code></a>
variable:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET max_sort_length = 2000;</code></strong>
mysql> <strong class="userinput"><code>SELECT id, comment FROM t</code></strong>
-> <strong class="userinput"><code>ORDER BY comment;</code></strong>
</pre><p>
Another way to use <code class="literal">GROUP BY</code> or
<code class="literal">ORDER BY</code> on a
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column containing long
values when you want more than
<a href="server-administration.html#sysvar_max_sort_length"><code class="literal">max_sort_length</code></a> bytes to be
significant is to convert the column value into a
fixed-length object. The standard way to do this is with the
<a href="functions.html#function_substring"><code class="literal">SUBSTRING()</code></a> function. For
example, the following statement causes 2000 bytes of the
<code class="literal">comment</code> column to be taken into account
for sorting:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT id, SUBSTRING(comment,1,2000) FROM t</code></strong>
-> <strong class="userinput"><code>ORDER BY SUBSTRING(comment,1,2000);</code></strong>
</pre></li><li><p>
Instances of <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> columns in the result of
a query that is processed using a temporary table causes the
server to use a table on disk rather than in memory because
the <code class="literal">MEMORY</code> storage engine does not
support those data types (see
<a href="optimization.html#internal-temporary-tables" title="7.5.10. How MySQL Uses Internal Temporary Tables">Section 7.5.10, “How MySQL Uses Internal Temporary Tables”</a>). Use of disk
incurs a performance penalty, so include
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> columns in the query
result only if they are really needed. For example, avoid
using <a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT
*</code></a>, which selects all columns.
</p></li><li><p>
The maximum size of a <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> 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 <a href="server-administration.html#sysvar_max_allowed_packet"><code class="literal">max_allowed_packet</code></a>
variable, but you must do so for both the server and your
client program. For example, both <a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a>
and <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> allow you to change the
client-side
<a href="server-administration.html#sysvar_max_allowed_packet"><code class="literal">max_allowed_packet</code></a> value.
See <a href="optimization.html#server-parameters" title="7.5.3. Tuning Server Parameters">Section 7.5.3, “Tuning Server Parameters”</a>,
<a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool">Section 4.5.1, “<span><strong class="command">mysql</strong></span> — The MySQL Command-Line Tool”</a>, and <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program">Section 4.5.4, “<span><strong class="command">mysqldump</strong></span> — A Database Backup Program”</a>.
You may also want to compare the packet sizes and the size
of the data objects you are storing with the storage
requirements, see <a href="data-types.html#storage-requirements" title="10.5. Data Type Storage Requirements">Section 10.5, “Data Type Storage Requirements”</a>
</p></li></ul></div><p>
Each <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> value is represented
internally by a separately allocated object. This is in contrast
to all other data types, for which storage is allocated once per
column when the table is opened.
</p><p>
In some cases, it may be desirable to store binary data such as
media files in <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> columns. You may find
MySQL's string handling functions useful for working with such
data. See <a href="functions.html#string-functions" title="11.4. String Functions">Section 11.4, “String Functions”</a>. For security and
other reasons, it is usually preferable to do so using
application code rather than allowing application users the
<a href="server-administration.html#priv_file"><code class="literal">FILE</code></a> privilege. You can discuss
specifics for various languages and platforms in the MySQL
Forums (<a href="http://forums.mysql.com/" target="_top">http://forums.mysql.com/</a>).
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="enum"></a>10.4.4. The <code class="literal">ENUM</code> Type</h3></div></div></div><a class="indexterm" name="id4339945"></a><a class="indexterm" name="id4339954"></a><p>
An <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> 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.
</p><p>
An enumeration value must be a quoted string literal; it may not
be an expression, even one that evaluates to a string value. For
example, you can create a table with an
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> column like this:
</p><pre class="programlisting">CREATE TABLE sizes (
name ENUM('small', 'medium', 'large')
);
</pre><p>
However, this version of the previous
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement does
<span class="emphasis"><em>not</em></span> work:
</p><pre class="programlisting">CREATE TABLE sizes (
c1 ENUM('small', CONCAT('med','ium'), 'large')
);
</pre><p>
You also may not employ a user variable as an enumeration value.
This pair of statements do <span class="emphasis"><em>not</em></span> work:
</p><pre class="programlisting">SET @mysize = 'medium';
CREATE TABLE sizes (
name ENUM('small', @mysize, 'large')
);
</pre><p>
If you wish to use a number as an enumeration value, you must
enclose it in quotes.
</p><p>
Duplicate values in the definition cause a warning, or an error
if strict SQL mode is enabled.
</p><p>
The value may also be the empty string (<code class="literal">''</code>)
or <code class="literal">NULL</code> under certain circumstances:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If you insert an invalid value into an
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> (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 “<span class="quote">normal</span>” empty string
by the fact that this string has the numerical value 0. More
about this later.
</p><p>
If strict SQL mode is enabled, attempts to insert invalid
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> values result in an
error.
</p></li><li><p>
If an <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> column is declared
to allow <code class="literal">NULL</code>, the
<code class="literal">NULL</code> value is a legal value for the
column, and the default value is <code class="literal">NULL</code>. If
an <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> column is declared
<code class="literal">NOT NULL</code>, its default value is the first
element of the list of allowed values.
</p></li></ul></div><p>
Each enumeration value has an index:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Values from the list of allowable elements in the column
specification are numbered beginning with 1.
</p></li><li><p>
The index value of the empty string error value is 0. This
means that you can use the following
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statement to find rows
into which invalid <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>
values were assigned:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM <em class="replaceable"><code>tbl_name</code></em> WHERE <em class="replaceable"><code>enum_col</code></em>=0;</code></strong>
</pre></li><li><p>
The index of the <code class="literal">NULL</code> value is
<code class="literal">NULL</code>.
</p></li><li><p>
The term “<span class="quote">index</span>” here refers only to position
within the list of enumeration values. It has nothing to do
with table indexes.
</p></li></ul></div><p>
For example, a column specified as <code class="literal">ENUM('one', 'two',
'three')</code> can have any of the values shown here. The
index of each value is also shown.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Value</strong></span></td><td><span class="bold"><strong>Index</strong></span></td></tr><tr><td><code class="literal">NULL</code></td><td><code class="literal">NULL</code></td></tr><tr><td><code class="literal">''</code></td><td>0</td></tr><tr><td><code class="literal">'one'</code></td><td>1</td></tr><tr><td><code class="literal">'two'</code></td><td>2</td></tr><tr><td><code class="literal">'three'</code></td><td>3</td></tr></tbody></table></div><p>
An enumeration can have a maximum of 65,535 elements.
</p><p>
Trailing spaces are automatically deleted from
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> member values in the table
definition when a table is created.
</p><p>
When retrieved, values stored into an
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> column are displayed using
the lettercase that was used in the column definition. Note that
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> columns can be assigned a
character set and collation. For binary or case-sensitive
collations, lettercase is taken into account when assigning
values to the column.
</p><p>
If you retrieve an <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> value in
a numeric context, the column value's index is returned. For
example, you can retrieve numeric values from an
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> column like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT <em class="replaceable"><code>enum_col</code></em>+0 FROM <em class="replaceable"><code>tbl_name</code></em>;</code></strong>
</pre><p>
If you store a number into an
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> column, the number is
treated as the index into the possible values, and the value
stored is the enumeration member with that index. (However, this
does <span class="emphasis"><em>not</em></span> work with
<a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax"><code class="literal">LOAD DATA</code></a>, which treats all input
as strings.) If the numeric value is quoted, it is still
interpreted as an index if there is no matching string in the
list of enumeration values. For these reasons, it is not
advisable to define an <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>
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
<code class="literal">'0'</code>, <code class="literal">'1'</code>, and
<code class="literal">'2'</code>, but numeric index values of
<code class="literal">1</code>, <code class="literal">2</code>, and
<code class="literal">3</code>:
</p><pre class="programlisting">numbers ENUM('0','1','2')
</pre><p>
If you store <code class="literal">2</code>, it is interpreted as an index
value, and becomes <code class="literal">'1'</code> (the value with index
2). If you store <code class="literal">'2'</code>, it matches an
enumeration value, so it is stored as <code class="literal">'2'</code>. If
you store <code class="literal">'3'</code>, it does not match any
enumeration value, so it is treated as an index and becomes
<code class="literal">'2'</code> (the value with index 3).
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO t (numbers) VALUES(2),('2'),('3');</code></strong>
mysql> <strong class="userinput"><code>SELECT * FROM t;</code></strong>
+---------+
| numbers |
+---------+
| 1 |
| 2 |
| 2 |
+---------+
</pre><p>
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> values are sorted according
to the order in which the enumeration members were listed in the
column specification. (In other words,
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> values are sorted according
to their index numbers.) For example, <code class="literal">'a'</code>
sorts before <code class="literal">'b'</code> for <code class="literal">ENUM('a',
'b')</code>, but <code class="literal">'b'</code> sorts before
<code class="literal">'a'</code> for <code class="literal">ENUM('b', 'a')</code>.
The empty string sorts before nonempty strings, and
<code class="literal">NULL</code> values sort before all other enumeration
values. To prevent unexpected results, specify the
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> list in alphabetical order.
You can also use <code class="literal">GROUP BY CAST(col AS CHAR)</code>
or <code class="literal">GROUP BY CONCAT(col)</code> to make sure that the
column is sorted lexically rather than by index number.
</p><p>
Functions such as <a href="functions.html#function_sum"><code class="literal">SUM()</code></a> or
<a href="functions.html#function_avg"><code class="literal">AVG()</code></a> that expect a numeric
argument cast the argument to a number if necessary. For
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> values, the cast operation
causes the index number to be used.
</p><p>
If you want to determine all possible values for an
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> column, use <code class="literal">SHOW
COLUMNS FROM <em class="replaceable"><code>tbl_name</code></em> LIKE
<em class="replaceable"><code>enum_col</code></em></code> and parse the
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> definition in the
<code class="literal">Type</code> column of the output.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="set"></a>10.4.5. The <code class="literal">SET</code> Type</h3></div></div></div><a class="indexterm" name="id4340700"></a><a class="indexterm" name="id4340709"></a><p>
A <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> 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.
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> column values that consist of
multiple set members are specified with members separated by
commas (“<span class="quote"><code class="literal">,</code></span>”). A consequence of
this is that <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> member values
should not themselves contain commas.
</p><p>
For example, a column specified as <code class="literal">SET('one', 'two')
NOT NULL</code> can have any of these values:
</p><pre class="programlisting">''
'one'
'two'
'one,two'
</pre><p>
A <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> can have a maximum of 64
different members.
</p><p>
Duplicate values in the definition cause a warning, or an error
if strict SQL mode is enabled.
</p><p>
Trailing spaces are automatically deleted from
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> member values in the table
definition when a table is created.
</p><p>
When retrieved, values stored in a
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> column are displayed using
the lettercase that was used in the column definition. Note that
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> columns can be assigned a
character set and collation. For binary or case-sensitive
collations, lettercase is taken into account when assigning
values to the column.
</p><p>
MySQL stores <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> values
numerically, with the low-order bit of the stored value
corresponding to the first set member. If you retrieve a
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> 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
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> column like this:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT <em class="replaceable"><code>set_col</code></em>+0 FROM <em class="replaceable"><code>tbl_name</code></em>;</code></strong>
</pre><p>
If a number is stored into a <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a>
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 <code class="literal">SET('a','b','c','d')</code>, the
members have the following decimal and binary values.
</p><div class="informaltable"><table border="1"><colgroup><col><col><col></colgroup><tbody><tr><td><a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a>
<span class="bold"><strong>Member</strong></span></td><td><span class="bold"><strong>Decimal Value</strong></span></td><td><span class="bold"><strong>Binary Value</strong></span></td></tr><tr><td><code class="literal">'a'</code></td><td><code class="literal">1</code></td><td><code class="literal">0001</code></td></tr><tr><td><code class="literal">'b'</code></td><td><code class="literal">2</code></td><td><code class="literal">0010</code></td></tr><tr><td><code class="literal">'c'</code></td><td><code class="literal">4</code></td><td><code class="literal">0100</code></td></tr><tr><td><code class="literal">'d'</code></td><td><code class="literal">8</code></td><td><code class="literal">1000</code></td></tr></tbody></table></div><p>
If you assign a value of <code class="literal">9</code> to this column,
that is <code class="literal">1001</code> in binary, so the first and
fourth <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> value members
<code class="literal">'a'</code> and <code class="literal">'d'</code> are selected
and the resulting value is <code class="literal">'a,d'</code>.
</p><p>
For a value containing more than one
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> 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 appears once, with elements listed according to the
order in which they were specified at table creation time. For
example, suppose that a column is specified as
<code class="literal">SET('a','b','c','d')</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));</code></strong>
</pre><p>
If you insert the values <code class="literal">'a,d'</code>,
<code class="literal">'d,a'</code>, <code class="literal">'a,d,d'</code>,
<code class="literal">'a,d,a'</code>, and <code class="literal">'d,a,d'</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO myset (col) VALUES </code></strong>
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
</pre><p>
Then all of these values appear as <code class="literal">'a,d'</code> when
retrieved:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT col FROM myset;</code></strong>
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
5 rows in set (0.04 sec)
</pre><p>
If you set a <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> column to an
unsupported value, the value is ignored and a warning is issued:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO myset (col) VALUES ('a,d,d,s');</code></strong>
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> <strong class="userinput"><code>SHOW WARNINGS;</code></strong>
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)
mysql> <strong class="userinput"><code>SELECT col FROM myset;</code></strong>
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+
6 rows in set (0.01 sec)
</pre><p>
If strict SQL mode is enabled, attempts to insert invalid
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> values result in an error.
</p><p>
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> values are sorted
numerically. <code class="literal">NULL</code> values sort before
non-<code class="literal">NULL</code> <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a>
values.
</p><p>
Functions such as <a href="functions.html#function_sum"><code class="literal">SUM()</code></a> or
<a href="functions.html#function_avg"><code class="literal">AVG()</code></a> that expect a numeric
argument cast the argument to a number if necessary. For
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> values, the cast operation
causes the numeric value to be used.
</p><p>
Normally, you search for <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a>
values using the <a href="functions.html#function_find-in-set"><code class="literal">FIND_IN_SET()</code></a>
function or the <a href="functions.html#operator_like"><code class="literal">LIKE</code></a> operator:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM <em class="replaceable"><code>tbl_name</code></em> WHERE FIND_IN_SET('<em class="replaceable"><code>value</code></em>',<em class="replaceable"><code>set_col</code></em>)>0;</code></strong>
mysql> <strong class="userinput"><code>SELECT * FROM <em class="replaceable"><code>tbl_name</code></em> WHERE <em class="replaceable"><code>set_col</code></em> LIKE '%<em class="replaceable"><code>value</code></em>%';</code></strong>
</pre><p>
The first statement finds rows where
<em class="replaceable"><code>set_col</code></em> contains the
<em class="replaceable"><code>value</code></em> set member. The second is
similar, but not the same: It finds rows where
<em class="replaceable"><code>set_col</code></em> contains
<em class="replaceable"><code>value</code></em> anywhere, even as a substring
of another set member.
</p><p>
The following statements also are legal:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT * FROM <em class="replaceable"><code>tbl_name</code></em> WHERE <em class="replaceable"><code>set_col</code></em> & 1;</code></strong>
mysql> <strong class="userinput"><code>SELECT * FROM <em class="replaceable"><code>tbl_name</code></em> WHERE <em class="replaceable"><code>set_col</code></em> = '<em class="replaceable"><code>val1</code></em>,<em class="replaceable"><code>val2</code></em>';</code></strong>
</pre><p>
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
<code class="literal">'<em class="replaceable"><code>val1</code></em>,<em class="replaceable"><code>val2</code></em>'</code>
returns different results than comparing values to
<code class="literal">'<em class="replaceable"><code>val2</code></em>,<em class="replaceable"><code>val1</code></em>'</code>.
You should specify the values in the same order they are listed
in the column definition.
</p><p>
If you want to determine all possible values for a
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> column, use <code class="literal">SHOW
COLUMNS FROM <em class="replaceable"><code>tbl_name</code></em> LIKE
<em class="replaceable"><code>set_col</code></em></code> and parse the
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> definition in the
<code class="literal">Type</code> column of the output.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="storage-requirements"></a>10.5. Data Type Storage Requirements</h2></div></div></div><a class="indexterm" name="id4341475"></a><a class="indexterm" name="id4341487"></a><p>
The storage requirements for data vary, according to the storage
engine being used for the table in question. Different storage
engines use different methods for recording the raw data and
different data types. In addition, some engines may compress the
information in a given row, either on a column or entire row
basis, making calculation of the storage requirements for a given
table or column structure.
</p><p>
However, all storage engines must communicate and exchange
information on a given row within a table using the same
structure, and this information is consistent, irrespective of the
storage engine used to write the information to disk.
</p><p>
This sections includes some guideliness and information for the
the storage requirements for each of the data types supported by
MySQL, including details for the internal format and the sizes
used by storage engines that used a fixed size representation for
different types. Information is listed by category and/or storage
engine.
</p><a class="indexterm" name="id4341518"></a><p>
The maximum size of a row in a <code class="literal">MyISAM</code> table is
65,535 bytes. This figure excludes
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> columns, which contribute only
9–12 bytes toward this size.
</p><p>
The internal representation of a table also has a maximum row size
of 65,535 bytes, even if the storage engine is capable of
supporting larger rows. For <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a>
and and <code class="literal">TEXT</code> data, the information
is stored internally in a different area of memory than the row
buffer. Different storage engines handle the allocation and
storage of this data in different ways, according to the method
they use for handling the corresponding types. See
<a href="storage-engines.html" title="Chapter 13. Storage Engines">Chapter 13, <i>Storage Engines</i></a>, for more information.
</p><a class="indexterm" name="id4341583"></a><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
For tables using the <code class="literal">NDBCLUSTER</code>
storage engine, there is the factor of <em class="firstterm">4-byte
alignment</em> to be taken into account when calculating
storage requirements. This means that all
<code class="literal">NDB</code> data storage is done in
multiples of 4 bytes. Thus, a column value that would take 15
bytes in a table using a storage engine other than
<code class="literal">NDB</code> requires 16 bytes in an
<code class="literal">NDB</code> table. This requirement applies
in addition to any other considerations that are discussed in
this section. For example, in
<code class="literal">NDBCLUSTER</code> tables, the
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">TINYINT</code></a>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">SMALLINT</code></a>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">MEDIUMINT</code></a>, and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INTEGER</code></a>
(<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT</code></a>) column types each require 4
bytes storage per record due to the alignment factor.
</p><p>
An exception to this rule is the
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIT</code></a> type, which is
<span class="emphasis"><em>not</em></span> 4-byte aligned. In MySQL Cluster
tables, a <code class="literal">BIT(<em class="replaceable"><code>M</code></em>)</code>
column takes <em class="replaceable"><code>M</code></em> bits of storage space.
However, if a table definition contains 1 or more
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIT</code></a> columns (up to 32
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIT</code></a> columns), then
<code class="literal">NDBCLUSTER</code> reserves 4 bytes (32
bits) per row for these. If a table definition contains more
than 32 <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIT</code></a> columns (up to 64
such columns), then <code class="literal">NDBCLUSTER</code>
reserves 8 bytes (that is, 64 bits) per row.
</p><p>
In addition, while a <code class="literal">NULL</code> itself does not
require any storage space,
<code class="literal">NDBCLUSTER</code> reserves 4 bytes per row
if the table definition contains any columns defined as
<code class="literal">NULL</code>, up to 32 <code class="literal">NULL</code>
columns. (If a MySQL Cluster table is defined with more than 32
<code class="literal">NULL</code> columns up to 64 <code class="literal">NULL</code>
columns, then 8 bytes per row is reserved.)
</p></div><p>
When calculating storage requirements for MySQL Cluster tables,
you must also remember that every table using the
<code class="literal">NDBCLUSTER</code> storage engine requires a
primary key; if no primary key is defined by the user, then a
“<span class="quote">hidden</span>” primary key will be created by
<code class="literal">NDB</code>. This hidden primary key consumes
31-35 bytes per table record.
</p><p>
You may find the <code class="filename">ndb_size.pl</code> utility to be
useful for estimating <code class="literal">NDB</code> storage
requirements. This Perl script connects to a current MySQL
(non-Cluster) database and creates a report on how much space that
database would require if it used the
<code class="literal">NDBCLUSTER</code> storage engine. See
<a href="mysql-cluster.html#mysql-cluster-programs-ndb-size-pl" title="17.4.21. ndb_size.pl — NDBCLUSTER Size Requirement Estimator">Section 17.4.21, “<span><strong class="command">ndb_size.pl</strong></span> — NDBCLUSTER Size Requirement Estimator”</a>, for more
information.
</p><a class="indexterm" name="id4341851"></a><a class="indexterm" name="id4341860"></a><p>
<span class="bold"><strong>Storage Requirements for Numeric Types in
<code class="literal">MyISAM</code></strong></span>
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Data Type</strong></span></td><td><span class="bold"><strong>Storage Required</strong></span></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">TINYINT</code></a></td><td>1 byte</td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">SMALLINT</code></a></td><td>2 bytes</td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">MEDIUMINT</code></a></td><td>3 bytes</td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT</code></a>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INTEGER</code></a></td><td>4 bytes</td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a></td><td>8 bytes</td></tr><tr><td><code class="literal">FLOAT(<em class="replaceable"><code>p</code></em>)</code></td><td>4 bytes if 0 <= <em class="replaceable"><code>p</code></em> <= 24, 8 bytes if 25
<= <em class="replaceable"><code>p</code></em> <= 53</td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a></td><td>4 bytes</td></tr><tr><td><code class="literal">DOUBLE [PRECISION]</code>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">REAL</code></a></td><td>8 bytes</td></tr><tr><td><code class="literal">DECIMAL(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code>,
<code class="literal">NUMERIC(<em class="replaceable"><code>M</code></em>,<em class="replaceable"><code>D</code></em>)</code></td><td>Varies; see following discussion</td></tr><tr><td><code class="literal">BIT(<em class="replaceable"><code>M</code></em>)</code></td><td>approximately (<em class="replaceable"><code>M</code></em>+7)/8 bytes</td></tr></tbody></table></div><p>
Values for <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> (and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a>) columns are represented
using a binary format that packs nine decimal (base 10) digits
into four bytes. Storage for the integer and fractional parts of
each value are determined separately. Each multiple of nine digits
requires four bytes, and the “<span class="quote">leftover</span>” digits
require some fraction of four bytes. The storage required for
excess digits is given by the following table.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Leftover Digits</strong></span></td><td><span class="bold"><strong>Number of Bytes</strong></span></td></tr><tr><td>0</td><td>0</td></tr><tr><td>1</td><td>1</td></tr><tr><td>2</td><td>1</td></tr><tr><td>3</td><td>2</td></tr><tr><td>4</td><td>2</td></tr><tr><td>5</td><td>3</td></tr><tr><td>6</td><td>3</td></tr><tr><td>7</td><td>4</td></tr><tr><td>8</td><td>4</td></tr></tbody></table></div><a class="indexterm" name="id4342262"></a><a class="indexterm" name="id4342271"></a><a class="indexterm" name="id4342280"></a><a class="indexterm" name="id4342292"></a><p>
<span class="bold"><strong>Storage Requirements for Date and Time Types
in <code class="literal">MyISAM</code></strong></span>
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Data Type</strong></span></td><td><span class="bold"><strong>Storage Required</strong></span></td></tr><tr><td><a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a></td><td>3 bytes</td></tr><tr><td><a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a></td><td>3 bytes</td></tr><tr><td><a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a></td><td>8 bytes</td></tr><tr><td><a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a></td><td>4 bytes</td></tr><tr><td><a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a></td><td>1 byte</td></tr></tbody></table></div><p>
The storage requirements shown in the table arise from the way
that MySQL represents temporal values:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATE</code></a>: A three-byte integer
packed as <code class="literal">DD</code> +
<code class="literal">MM</code>×32 +
<code class="literal">YYYY</code>×16×32
</p></li><li><p>
<a href="data-types.html#time" title="10.3.2. The TIME Type"><code class="literal">TIME</code></a>: A three-byte integer
packed as <code class="literal">DD</code>×24×3600 +
<code class="literal">HH</code>×3600 +
<code class="literal">MM</code>×60 + <code class="literal">SS</code>
</p></li><li><p>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a>: Eight bytes:
</p><div class="itemizedlist"><ul type="circle"><li><p>
A four-byte integer packed as
<code class="literal">YYYY</code>×10000 +
<code class="literal">MM</code>×100 +
<code class="literal">DD</code>
</p></li><li><p>
A four-byte integer packed as
<code class="literal">HH</code>×10000 +
<code class="literal">MM</code>×100 +
<code class="literal">SS</code>
</p></li></ul></div></li><li><p>
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a>: A four-byte integer
representing seconds UTC since the epoch (<code class="literal">'1970-01-01
00:00:00'</code> UTC)
</p></li><li><p>
<a href="data-types.html#year" title="10.3.3. The YEAR Type"><code class="literal">YEAR</code></a>: A one-byte integer
</p></li></ul></div><p>
<span class="bold"><strong>Storage Requirements for String Types in
<code class="literal">MyISAM</code></strong></span>
</p><p>
In the following table, <em class="replaceable"><code>M</code></em> represents
the declared column length in characters for nonbinary string
types and bytes for binary string types.
<em class="replaceable"><code>L</code></em> represents the actual length in bytes
of a given string value.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Data Type</strong></span></td><td><span class="bold"><strong>Storage Required</strong></span></td></tr><tr><td><code class="literal">CHAR(<em class="replaceable"><code>M</code></em>)</code></td><td><em class="replaceable"><code>M</code></em> × <em class="replaceable"><code>w</code></em> bytes,
0 <code class="literal"><= <em class="replaceable"><code>M</code></em>
<=</code> 255, where <em class="replaceable"><code>w</code></em> is
the number of bytes required for the maximum-length
character in the character set</td></tr><tr><td><code class="literal">BINARY(<em class="replaceable"><code>M</code></em>)</code></td><td><em class="replaceable"><code>M</code></em> bytes, 0 <code class="literal"><=
<em class="replaceable"><code>M</code></em> <=</code> 255</td></tr><tr><td><code class="literal">VARCHAR(<em class="replaceable"><code>M</code></em>)</code>,
<code class="literal">VARBINARY(<em class="replaceable"><code>M</code></em>)</code></td><td><em class="replaceable"><code>L</code></em> + 1 bytes if column values require 0
– 255 bytes, <em class="replaceable"><code>L</code></em> + 2 bytes
if values may require more than 255 bytes</td></tr><tr><td><a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TINYBLOB</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TINYTEXT</code></a></td><td><em class="replaceable"><code>L</code></em> + 1 bytes, where
<em class="replaceable"><code>L</code></em> <
2<sup>8</sup></td></tr><tr><td><a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a>, <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a></td><td><em class="replaceable"><code>L</code></em> + 2 bytes, where
<em class="replaceable"><code>L</code></em> <
2<sup>16</sup></td></tr><tr><td><a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMBLOB</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMTEXT</code></a></td><td><em class="replaceable"><code>L</code></em> + 3 bytes, where
<em class="replaceable"><code>L</code></em> <
2<sup>24</sup></td></tr><tr><td><a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGBLOB</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">LONGTEXT</code></a></td><td><em class="replaceable"><code>L</code></em> + 4 bytes, where
<em class="replaceable"><code>L</code></em> <
2<sup>32</sup></td></tr><tr><td><code class="literal">ENUM('<em class="replaceable"><code>value1</code></em>','<em class="replaceable"><code>value2</code></em>',...)</code></td><td>1 or 2 bytes, depending on the number of enumeration values (65,535
values maximum)</td></tr><tr><td><code class="literal">SET('<em class="replaceable"><code>value1</code></em>','<em class="replaceable"><code>value2</code></em>',...)</code></td><td>1, 2, 3, 4, or 8 bytes, depending on the number of set members (64
members maximum)</td></tr></tbody></table></div><p>
Variable-length string types are stored using a length prefix plus
data. The length prefix requires from one to four bytes depending
on the data type, and the value of the prefix is
<em class="replaceable"><code>L</code></em> (the byte length of the string). For
example, storage for a <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMTEXT</code></a>
value requires <em class="replaceable"><code>L</code></em> bytes to store the
value plus three bytes to store the length of the value.
</p><p>
To calculate the number of bytes used to store a particular
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">CHAR</code></a>,
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>, or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column value, you must take
into account the character set used for that column and whether
the value contains multi-byte characters. In particular, when
using the <code class="literal">utf8</code> Unicode character set, you must
keep in mind that not all <code class="literal">utf8</code> characters use
the same number of bytes and can require up to three bytes per
character. For a breakdown of the storage used for different
categories of <code class="literal">utf8</code> characters, see
<a href="internationalization-localization.html#charset-unicode" title="9.1.10. Unicode Support">Section 9.1.10, “Unicode Support”</a>.
</p><a class="indexterm" name="id4342994"></a><a class="indexterm" name="id4343007"></a><a class="indexterm" name="id4343019"></a><p>
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>,
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a>, and the
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> types are variable-length
types. For each, the storage requirements depend on these factors:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The actual length of the column value
</p></li><li><p>
The column's maximum possible length
</p></li><li><p>
The character set used for the column, because some character
sets contain multi-byte characters
</p></li></ul></div><p>
For example, a <code class="literal">VARCHAR(255)</code> column can hold a
string with a maximum length of 255 characters. Assuming that the
column uses the <code class="literal">latin1</code> character set (one byte
per character), the actual storage required is the length of the
string (<em class="replaceable"><code>L</code></em>), plus one byte to record the
length of the string. For the string <code class="literal">'abcd'</code>,
<em class="replaceable"><code>L</code></em> is 4 and the storage requirement is
five bytes. If the same column is instead declared to use the
<code class="literal">ucs2</code> double-byte character set, the storage
requirement is 10 bytes: The length of <code class="literal">'abcd'</code>
is eight bytes and the column requires two bytes to store lengths
because the maximum length is greater than 255 (up to 510 bytes).
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
The effective maximum number of <span class="emphasis"><em>bytes</em></span> that
can be stored in a <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> or
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">VARBINARY</code></a> column is subject to
the maximum row size of 65,535 bytes, which is shared among all
columns. For a <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> column
that stores multi-byte characters, the effective maximum number
of <span class="emphasis"><em>characters</em></span> is less. For example,
<code class="literal">utf8</code> characters can require up to three bytes
per character, so a <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>
column that uses the <code class="literal">utf8</code> character set can
be declared to be a maximum of 21,844 characters.
</p></div><p>
The <code class="literal">NDBCLUSTER</code> storage engine in
MySQL 5.1 supports variable-width columns. This means that a
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> column in a MySQL Cluster
table requires the same amount of storage as it would using any
other storage engine, with the exception that such values are
4-byte aligned. Thus, the string <code class="literal">'abcd'</code> stored
in a <code class="literal">VARCHAR(50)</code> column using the
<code class="literal">latin1</code> character set requires 8 bytes (rather
than 6 bytes for the same column value in a
<code class="literal">MyISAM</code> table). This represents a change in
behavior from earlier versions of
<code class="literal">NDBCLUSTER</code>, where a
<code class="literal">VARCHAR(50)</code> column would require 52 bytes
storage per record regardless of the length of the string being
stored.
</p><p>
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> columns are implemented
differently in the NDB Cluster storage engine, wherein each row in
a <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> column is made up of two
separate parts. One of these is of fixed size (256 bytes), and is
actually stored in the original table. The other consists of any
data in excess of 256 bytes, which is stored in a hidden table.
The rows in this second table are always 2,000 bytes long. This
means that the size of a <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a>
column is 256 if <em class="replaceable"><code>size</code></em> <= 256 (where
<em class="replaceable"><code>size</code></em> represents the size of the row);
otherwise, the size is 256 + <em class="replaceable"><code>size</code></em> +
(2000 – (<em class="replaceable"><code>size</code></em> – 256) %
2000).
</p><a class="indexterm" name="id4343304"></a><p>
The size of an <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> 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 having between 256 and 65,535 possible
values. See <a href="data-types.html#enum" title="10.4.4. The ENUM Type">Section 10.4.4, “The <code class="literal">ENUM</code> Type”</a>.
</p><a class="indexterm" name="id4343333"></a><p>
The size of a <a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> object is
determined by the number of different set members. If the set size
is <em class="replaceable"><code>N</code></em>, the object occupies
<code class="literal">(<em class="replaceable"><code>N</code></em>+7)/8</code> bytes,
rounded up to 1, 2, 3, 4, or 8 bytes. A
<a href="data-types.html#set" title="10.4.5. The SET Type"><code class="literal">SET</code></a> can have a maximum of 64
members. See <a href="data-types.html#set" title="10.4.5. The SET Type">Section 10.4.5, “The <code class="literal">SET</code> Type”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="choosing-types"></a>10.6. Choosing the Right Type for a Column</h2></div></div></div><a class="indexterm" name="id4343389"></a><a class="indexterm" name="id4343402"></a><p>
For optimum storage, you should try to use the most precise type
in all cases. For example, if an integer column is used for values
in the range from <code class="literal">1</code> to
<code class="literal">99999</code>, <code class="literal">MEDIUMINT UNSIGNED</code> is
the best type. Of the types that represent all the required
values, this type uses the least amount of storage.
</p><p>
All basic calculations (<code class="literal">+</code>,
<code class="literal">-</code>, <code class="literal">*</code>, and
<code class="literal">/</code>) with <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a>
columns are done with precision of 65 decimal (base 10) digits.
See <a href="data-types.html#numeric-type-overview" title="10.1.1. Overview of Numeric Types">Section 10.1.1, “Overview of Numeric Types”</a>.
</p><p>
If accuracy is not too important or if speed is the highest
priority, the <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> type may be
good enough. For high precision, you can always convert to a
fixed-point type stored in a
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a>. This allows you to do all
calculations with 64-bit integers and then convert results back to
floating-point values as necessary.
</p><p>
<code class="literal">PROCEDURE ANALYSE</code> can be used to obtain
suggestions for optimal column data types. For more information,
see <a href="extending-mysql.html#procedure-analyse" title="22.4.1. PROCEDURE ANALYSE">Section 22.4.1, “<code class="literal">PROCEDURE ANALYSE</code>”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="other-vendor-data-types"></a>10.7. Using Data Types from Other Database Engines</h2></div></div></div><a class="indexterm" name="id4343516"></a><a class="indexterm" name="id4343529"></a><a class="indexterm" name="id4343541"></a><a class="indexterm" name="id4343554"></a><a class="indexterm" name="id4343566"></a><p>
To facilitate the use of code written for SQL implementations from
other vendors, MySQL maps data types as shown in the following
table. These mappings make it easier to import table definitions
from other database systems into MySQL.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Other Vendor Type</strong></span></td><td><span class="bold"><strong>MySQL Type</strong></span></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BOOL</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">TINYINT</code></a></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BOOLEAN</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">TINYINT</code></a></td></tr><tr><td><code class="literal">CHARACTER VARYING(<em class="replaceable"><code>M</code></em>)</code></td><td><code class="literal">VARCHAR(<em class="replaceable"><code>M</code></em>)</code></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FIXED</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT4</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT8</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT1</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">TINYINT</code></a></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT2</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">SMALLINT</code></a></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT3</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">MEDIUMINT</code></a></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT4</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INT</code></a></td></tr><tr><td><code class="literal">INT8</code></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIGINT</code></a></td></tr><tr><td><code class="literal">LONG VARBINARY</code></td><td><a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMBLOB</code></a></td></tr><tr><td><code class="literal">LONG VARCHAR</code></td><td><a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMTEXT</code></a></td></tr><tr><td><code class="literal">LONG</code></td><td><a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">MEDIUMTEXT</code></a></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">MIDDLEINT</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">MEDIUMINT</code></a></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">NUMERIC</code></a></td><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a></td></tr></tbody></table></div><p>
Data 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
<code class="literal">DESCRIBE <em class="replaceable"><code>tbl_name</code></em></code>
statement, MySQL reports the table structure using the equivalent
MySQL types. For example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t (a BOOL, b FLOAT8, c LONG VARCHAR, d NUMERIC);</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>DESCRIBE t;</code></strong>
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a | tinyint(1) | YES | | NULL | |
| b | double | YES | | NULL | |
| c | mediumtext | YES | | NULL | |
| d | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
</pre></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="internationalization-localization.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="functions.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 9. Internationalization and Localization </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 11. Functions and Operators</td></tr></table></div></body></html>
