<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.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 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="spatial-extensions.html" title="Chapter 17. Spatial Extensions">Chapter 17, <i>Spatial Extensions</i></a>, provides information about these data types. </p><a class="indexterm" name="id2757032"></a><a class="indexterm" name="id2757041"></a><a class="indexterm" name="id2757053"></a><a class="indexterm" name="id2757066"></a><p> Data type descriptions use these conventions: </p><div class="itemizedlist"><ul type="disc"><li><p> <a class="indexterm" name="id2757089"></a> <a class="indexterm" name="id2757098"></a> <a class="indexterm" name="id2757110"></a> <a class="indexterm" name="id2757119"></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="id2757156"></a> <a class="indexterm" name="id2757165"></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="id2757191"></a> <a class="indexterm" name="id2757204"></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="id2757239"></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><p> A summary of the numeric data types follows. For additional information, 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 <code class="literal">NO_UNSIGNED_SUBTRACTION</code> 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="id2757413"></a> <a class="indexterm" name="id2757422"></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><p> This data type was added in MySQL 5.0.3 for <code class="literal">MyISAM</code>, and extended in 5.0.5 to <code class="literal">MEMORY</code>, <code class="literal">InnoDB</code>, and <code class="literal">BDB</code>. Before 5.0.3, <code class="literal">BIT</code> is a synonym for <code class="literal">TINYINT(1)</code>. </p></li><li><p> <a class="indexterm" name="id2757526"></a> <a class="indexterm" name="id2757535"></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="id2757614"></a> <a class="indexterm" name="id2757624"></a> <a class="indexterm" name="id2757633"></a> <a class="indexterm" name="id2757645"></a> <code class="literal">BOOL</code>, <code class="literal">BOOLEAN</code> </p><p> These types are synonyms for <code class="literal">TINYINT(1)</code>. A value of zero is considered false. Non-zero 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="id2757856"></a> <a class="indexterm" name="id2757865"></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="id2757944"></a> <a class="indexterm" name="id2757954"></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="id2758034"></a> <a class="indexterm" name="id2758043"></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="id2758116"></a> <a class="indexterm" name="id2758125"></a> <code class="literal">INTEGER[(<em class="replaceable"><code>M</code></em>)] [UNSIGNED] [ZEROFILL]</code> </p><p> This type is a synonym for <code class="literal">INT</code>. </p></li><li><p> <a class="indexterm" name="id2758186"></a> <a class="indexterm" name="id2758195"></a> <a class="indexterm" name="id2758207"></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 <code class="literal">BIGINT</code> columns: </p><div class="itemizedlist"><ul type="circle"><li><p> <a class="indexterm" name="id2758295"></a> All arithmetic is done using signed <code class="literal">BIGINT</code> or <code class="literal">DOUBLE</code> 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 <code class="literal">BIGINT</code> value to a <code class="literal">DOUBLE</code>. </p><p> MySQL can handle <code class="literal">BIGINT</code> in the following cases: </p><div class="itemizedlist"><ul type="square"><li><p> When using integers to store large unsigned values in a <code class="literal">BIGINT</code> 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 <code class="literal">BIGINT</code> column. </p></li><li><p> When using operators (<code class="literal">+</code>, <code class="literal">-</code>, <code class="literal">*</code>, 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 <code class="literal">BIGINT</code> 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 <code class="literal">-</code>, <code class="literal">+</code>, and <code class="literal">*</code> operators use <code class="literal">BIGINT</code> 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="id2758521"></a> <a class="indexterm" name="id2758530"></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 <code class="literal">FLOAT</code> 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="id2758672"></a> <a class="indexterm" name="id2758681"></a> <a class="indexterm" name="id2758690"></a> <a class="indexterm" name="id2758703"></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="id2758823"></a> <a class="indexterm" name="id2758832"></a> <a class="indexterm" name="id2758841"></a> <a class="indexterm" name="id2758853"></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 <code class="literal">DOUBLE</code>. Exception: If the <code class="literal">REAL_AS_FLOAT</code> SQL mode is enabled, <code class="literal">REAL</code> is a synonym for <code class="literal">FLOAT</code> rather than <code class="literal">DOUBLE</code>. </p></li><li><p> <a class="indexterm" name="id2758939"></a> <a class="indexterm" name="id2758948"></a> <a class="indexterm" name="id2758958"></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 <code class="literal">FLOAT</code> or <code class="literal">DOUBLE</code> for the resulting data type. If <em class="replaceable"><code>p</code></em> is from 0 to 24, the data type becomes <code class="literal">FLOAT</code> 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 <code class="literal">DOUBLE</code> 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 <code class="literal">FLOAT</code> or double-precision <code class="literal">DOUBLE</code> data types described earlier in this section. </p><p> <a class="indexterm" name="id2759055"></a> <a class="indexterm" name="id2759064"></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="id2759111"></a> <a class="indexterm" name="id2759120"></a> <code class="literal">DECIMAL[(<em class="replaceable"><code>M</code></em>[,<em class="replaceable"><code>D</code></em>])] [UNSIGNED] [ZEROFILL]</code> </p><p> For MySQL 5.0.3 and above: </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 <code class="literal">DECIMAL</code> is 65 (64 from 5.0.3 to 5.0.5). 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 <code class="literal">DECIMAL</code> columns are done with a precision of 65 digits. </p><p> Before MySQL 5.0.3: </p><p> An unpacked fixed-point number. Behaves like a <code class="literal">CHAR</code> column; “<span class="quote">unpacked</span>” means the number is stored as a string, using one character for each digit of the value. <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 after the decimal point. 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>, although space for them is reserved. If <em class="replaceable"><code>D</code></em> is 0, values have no decimal point or fractional part. The maximum range of <code class="literal">DECIMAL</code> values is the same as for <code class="literal">DOUBLE</code>, but the actual range for a given <code class="literal">DECIMAL</code> column may be constrained by the choice of <em class="replaceable"><code>M</code></em> and <em class="replaceable"><code>D</code></em>. 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> The behavior used by the server for <code class="literal">DECIMAL</code> columns in a table depends on the version of MySQL used to create the table. If your server is from MySQL 5.0.3 or higher, but you have <code class="literal">DECIMAL</code> columns in tables that were created before 5.0.3, the old behavior still applies to those columns. To convert the tables to the newer <code class="literal">DECIMAL</code> format, dump them with <span><strong class="command">mysqldump</strong></span> and reload them. </p></li><li><p> <a class="indexterm" name="id2759390"></a> <a class="indexterm" name="id2759399"></a> <a class="indexterm" name="id2759408"></a> <a class="indexterm" name="id2759417"></a> <a class="indexterm" name="id2759429"></a> <a class="indexterm" name="id2759442"></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 <code class="literal">DECIMAL</code>. The <code class="literal">FIXED</code> 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, 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 <code class="literal">DATETIME</code> and <code class="literal">DATE</code> 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="id2759595"></a> <a class="indexterm" name="id2759604"></a> <code class="literal">DATE</code> </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 <code class="literal">DATE</code> values in <code class="literal">'YYYY-MM-DD'</code> format, but allows assignment of values to <code class="literal">DATE</code> columns using either strings or numbers. </p></li><li><p> <a class="indexterm" name="id2759682"></a> <a class="indexterm" name="id2759691"></a> <code class="literal">DATETIME</code> </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 <code class="literal">DATETIME</code> values in <code class="literal">'YYYY-MM-DD HH:MM:SS'</code> format, but allows assignment of values to <code class="literal">DATETIME</code> columns using either strings or numbers. </p></li><li><p> <a class="indexterm" name="id2759776"></a> <a class="indexterm" name="id2759785"></a> <code class="literal">TIMESTAMP</code> </p><p> A timestamp. The range is <code class="literal">'1970-01-01 00:00:01'</code> UTC to partway through the year <code class="literal">2038</code>. <code class="literal">TIMESTAMP</code> values are stored as the number of seconds since the epoch (<code class="literal">'1970-01-01 00:00:00'</code> UTC). A <code class="literal">TIMESTAMP</code> 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>” <code class="literal">TIMESTAMP</code> value. </p><p> A <code class="literal">TIMESTAMP</code> column is useful for recording the date and time of an <code class="literal">INSERT</code> or <code class="literal">UPDATE</code> operation. By default, the first <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> format that was used prior to MySQL 4.1 is not supported in MySQL 5.0; 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="id2759990"></a> <a class="indexterm" name="id2759999"></a> <code class="literal">TIME</code> </p><p> A time. The range is <code class="literal">'-838:59:59'</code> to <code class="literal">'838:59:59'</code>. MySQL displays <code class="literal">TIME</code> values in <code class="literal">'HH:MM:SS'</code> format, but allows assignment of values to <code class="literal">TIME</code> columns using either strings or numbers. </p></li><li><p> <a class="indexterm" name="id2760076"></a> <a class="indexterm" name="id2760085"></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 <code class="literal">YEAR</code> values in <code class="literal">YYYY</code> format, but allows you to assign values to <code class="literal">YEAR</code> 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 non-numeric 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, 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 <code class="literal">CREATE TABLE</code> or <code class="literal">ALTER TABLE</code> statement. See <a href="sql-syntax.html#silent-column-changes" title="12.1.5.1. Silent Column Specification Changes">Section 12.1.5.1, “Silent Column Specification Changes”</a>. </p><p> In MySQL 4.1 and up, string data types include some features that you may not have encountered in working with versions of MySQL prior to 4.1: </p><div class="itemizedlist"><ul type="disc"><li><p> MySQL interprets length specifications in character column definitions in character units. (Before MySQL 4.1, column lengths were interpreted in bytes.) This applies to <code class="literal">CHAR</code>, <code class="literal">VARCHAR</code>, and the <code class="literal">TEXT</code> types. </p></li><li><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 <code class="literal">CHAR</code>, <code class="literal">VARCHAR</code>, the <code class="literal">TEXT</code> types, <code class="literal">ENUM</code>, and <code class="literal">SET</code> data types: </p><div class="itemizedlist"><ul type="circle"><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> <code class="literal">CHARSET</code> is a synonym for <code class="literal">CHARACTER SET</code>. </p></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. (Before MySQL 4.1, <code class="literal">BINARY</code> caused a column to store binary strings and sorting and comparison were based on numeric byte values. This is the same as using character values for single-byte character sets, but not for multi-byte character sets.) </p></li></ul></div></li><li><p> Character column sorting and comparison are based on the character set assigned to the column. (Before MySQL 4.1, sorting and comparison were based on the collation of the server character set.) For the <code class="literal">CHAR</code>, <code class="literal">VARCHAR</code>, <code class="literal">TEXT</code>, <code class="literal">ENUM</code>, and <code class="literal">SET</code> 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></li></ul></div><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="id2760572"></a> <a class="indexterm" name="id2760581"></a> <a class="indexterm" name="id2760590"></a> <a class="indexterm" name="id2760599"></a> <a class="indexterm" name="id2760608"></a> <a class="indexterm" name="id2760620"></a> <a class="indexterm" name="id2760633"></a> <a class="indexterm" name="id2760645"></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 <code class="literal">CHAR</code> values are retrieved. </p></div><p> Before MySQL 5.0.3, a <code class="literal">CHAR</code> column with a length specification greater than 255 is converted to the smallest <code class="literal">TEXT</code> type that can hold values of the given length. For example, <code class="literal">CHAR(500)</code> is converted to <code class="literal">TEXT</code>, and <code class="literal">CHAR(200000)</code> is converted to <code class="literal">MEDIUMTEXT</code>. However, this conversion causes the column to become a variable-length column, and also affects trailing-space removal. </p><p> In MySQL 5.0.3 and later, a <code class="literal">CHAR</code> length greater than 255 is illegal and fails with an error: </p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE c1 (col1 INT, col2 CHAR(500));</code></strong> ERROR 1074 (42000): Column length too big for column 'col' (max = 255); use BLOB or TEXT instead </pre><p> <code class="literal">CHAR</code> is shorthand for <code class="literal">CHARACTER</code>. <code class="literal">NATIONAL CHAR</code> (or its equivalent short form, <code class="literal">NCHAR</code>) is the standard SQL way to define that a <code class="literal">CHAR</code> 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 <code class="literal">CHAR BYTE</code> data type is an alias for the <code class="literal">BINARY</code> 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="id2760937"></a> <a class="indexterm" name="id2760946"></a> <a class="indexterm" name="id2760955"></a> <a class="indexterm" name="id2760965"></a> <a class="indexterm" name="id2760974"></a> <a class="indexterm" name="id2760983"></a> <a class="indexterm" name="id2760992"></a> <a class="indexterm" name="id2761004"></a> <a class="indexterm" name="id2761017"></a> <a class="indexterm" name="id2761030"></a> <a class="indexterm" name="id2761042"></a> <a class="indexterm" name="id2761055"></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. In MySQL 5.0, the range of <em class="replaceable"><code>M</code></em> is 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in MySQL 5.0.3 and later. The effective maximum length of a <code class="literal">VARCHAR</code> in MySQL 5.0.3 and later 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 <code class="literal">VARCHAR</code> 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 <code class="literal">VARCHAR</code> values as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A <code class="literal">VARCHAR</code> 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> Before 5.0.3, trailing spaces were removed when <code class="literal">VARCHAR</code> values were stored, which differs from the standard SQL specification. </p></div><p> Prior to MySQL 5.0.3, a <code class="literal">VARCHAR</code> column with a length specification greater than 255 is converted to the smallest <code class="literal">TEXT</code> type that can hold values of the given length. For example, <code class="literal">VARCHAR(500)</code> is converted to <code class="literal">TEXT</code>, and <code class="literal">VARCHAR(200000)</code> is converted to <code class="literal">MEDIUMTEXT</code>. However, this conversion affects trailing-space removal. </p><p> <code class="literal">VARCHAR</code> is shorthand for <code class="literal">CHARACTER VARYING</code>. <code class="literal">NATIONAL VARCHAR</code> is the standard SQL way to define that a <code class="literal">VARCHAR</code> 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>. <code class="literal">NVARCHAR</code> is shorthand for <code class="literal">NATIONAL VARCHAR</code>. </p></li><li><p> <a class="indexterm" name="id2761288"></a> <a class="indexterm" name="id2761297"></a> <code class="literal">BINARY(<em class="replaceable"><code>M</code></em>)</code> </p><p> The <code class="literal">BINARY</code> type is similar to the <code class="literal">CHAR</code> type, but stores binary byte strings rather than non-binary character strings. <em class="replaceable"><code>M</code></em> represents the column length in bytes. </p></li><li><p> <a class="indexterm" name="id2761363"></a> <a class="indexterm" name="id2761372"></a> <code class="literal">VARBINARY(<em class="replaceable"><code>M</code></em>)</code> </p><p> The <code class="literal">VARBINARY</code> type is similar to the <code class="literal">VARCHAR</code> type, but stores binary byte strings rather than non-binary character strings. <em class="replaceable"><code>M</code></em> represents the maximum column length in bytes. </p></li><li><p> <a class="indexterm" name="id2761438"></a> <a class="indexterm" name="id2761447"></a> <code class="literal">TINYBLOB</code> </p><p> A <code class="literal">BLOB</code> column with a maximum length of 255 (2<sup>8</sup> – 1) bytes. Each <code class="literal">TINYBLOB</code> 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="id2761510"></a> <a class="indexterm" name="id2761519"></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 <code class="literal">TEXT</code> 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 <code class="literal">TINYTEXT</code> 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="id2761594"></a> <a class="indexterm" name="id2761603"></a> <code class="literal">BLOB[(<em class="replaceable"><code>M</code></em>)]</code> </p><p> A <code class="literal">BLOB</code> column with a maximum length of 65,535 (2<sup>16</sup> – 1) bytes. Each <code class="literal">BLOB</code> 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 <code class="literal">BLOB</code> type large enough to hold values <em class="replaceable"><code>M</code></em> bytes long. </p></li><li><p> <a class="indexterm" name="id2761689"></a> <a class="indexterm" name="id2761698"></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 <code class="literal">TEXT</code> 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 <code class="literal">TEXT</code> 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 <code class="literal">TEXT</code> type large enough to hold values <em class="replaceable"><code>M</code></em> characters long. </p></li><li><p> <a class="indexterm" name="id2761796"></a> <a class="indexterm" name="id2761806"></a> <code class="literal">MEDIUMBLOB</code> </p><p> A <code class="literal">BLOB</code> column with a maximum length of 16,777,215 (2<sup>24</sup> – 1) bytes. Each <code class="literal">MEDIUMBLOB</code> 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="id2761869"></a> <a class="indexterm" name="id2761878"></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 <code class="literal">TEXT</code> 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 <code class="literal">MEDIUMTEXT</code> 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="id2761960"></a> <a class="indexterm" name="id2761969"></a> <code class="literal">LONGBLOB</code> </p><p> A <code class="literal">BLOB</code> column with a maximum length of 4,294,967,295 or 4GB (2<sup>32</sup> – 1) bytes. The effective maximum length of <code class="literal">LONGBLOB</code> columns depends on the configured maximum packet size in the client/server protocol and available memory. Each <code class="literal">LONGBLOB</code> 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="id2762042"></a> <a class="indexterm" name="id2762052"></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 <code class="literal">TEXT</code> 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 <code class="literal">LONGTEXT</code> columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each <code class="literal">LONGTEXT</code> 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="id2762137"></a> <a class="indexterm" name="id2762146"></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 <code class="literal">ENUM</code> column can have a maximum of 65,535 distinct values. <code class="literal">ENUM</code> values are represented internally as integers. </p></li><li><p> <a class="indexterm" name="id2762258"></a> <a class="indexterm" name="id2762267"></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 <code class="literal">SET</code> column can have a maximum of 64 members. <code class="literal">SET</code> 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="id2762361"></a><a class="indexterm" name="id2762370"></a><a class="indexterm" name="id2762382"></a><a class="indexterm" name="id2762392"></a><a class="indexterm" name="id2762404"></a><a class="indexterm" name="id2762413"></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 <code class="literal">CURRENT_DATE</code>. The exception is that you can specify <code class="literal">CURRENT_TIMESTAMP</code> as the default for a <code class="literal">TIMESTAMP</code> 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> Prior to MySQL 5.0.2, 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 an explicit <code class="literal">DEFAULT</code> clause, using the implicit default value for the column data type. 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 <code class="literal">TIMESTAMP</code>, the default is the appropriate “<span class="quote">zero</span>” value for the type. For the first <code class="literal">TIMESTAMP</code> 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 <code class="literal">ENUM</code>, the default value is the empty string. For <code class="literal">ENUM</code>, the default is the first enumeration value. </p></li></ul></div><p> <code class="literal">BLOB</code> and <code class="literal">TEXT</code> columns cannot be assigned a default value. </p><p> As of MySQL 5.0.2, 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. This is the same as before 5.0.2. </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 <code class="literal">INSERT</code> or <code class="literal">REPLACE</code> statement includes no value for the column, 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 non-transactional 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 <code class="literal">DEFAULT(i)</code> 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.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>. </p><p> For a given table, you can use the <code class="literal">SHOW CREATE TABLE</code> statement to see which columns have an explicit <code class="literal">DEFAULT</code> clause. </p><a class="indexterm" name="id2762769"></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><p> MySQL supports all of the standard SQL numeric data types. These types include the exact numeric data types (<code class="literal">INTEGER</code>, <code class="literal">SMALLINT</code>, <code class="literal">DECIMAL</code>, and <code class="literal">NUMERIC</code>), as well as the approximate numeric data types (<code class="literal">FLOAT</code>, <code class="literal">REAL</code>, and <code class="literal">DOUBLE PRECISION</code>). The keyword <code class="literal">INT</code> is a synonym for <code class="literal">INTEGER</code>, and the keyword <code class="literal">DEC</code> is a synonym for <code class="literal">DECIMAL</code>. 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> As of MySQL 5.0.3, a <code class="literal">BIT</code> data type is available for storing bit-field values. (Before 5.0.3, MySQL interprets <code class="literal">BIT</code> as <code class="literal">TINYINT(1)</code>.) In MySQL 5.0.3, <code class="literal">BIT</code> is supported only for <code class="literal">MyISAM</code>. MySQL 5.0.5 extends <code class="literal">BIT</code> support to <code class="literal">MEMORY</code>, <code class="literal">InnoDB</code>, and <code class="literal">BDB</code>. </p><p> As an extension to the SQL standard, MySQL also supports the integer types <code class="literal">TINYINT</code>, <code class="literal">MEDIUMINT</code>, and <code class="literal">BIGINT</code>. 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><code class="literal">TINYINT</code></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><code class="literal">SMALLINT</code></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><code class="literal">MEDIUMINT</code></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><code class="literal">INT</code></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><code class="literal">BIGINT</code></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 is used to display integer values having a width less than the width specified for the column by left-padding them with spaces. </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 <code class="literal">SMALLINT</code> 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 stripped when a column is involved in expressions or <code class="literal">UNION</code> queries. </p></div><p> All integer types can have an optional (non-standard) attribute <code class="literal">UNSIGNED</code>. Unsigned values can be used when you want to allow only non-negative numbers in a column and you need a larger upper numeric range for the column. For example, if an <code class="literal">INT</code> 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 <code class="literal">FLOAT</code> and <code class="literal">DOUBLE</code> data types are used to represent approximate numeric data values. For <code class="literal">FLOAT</code> the SQL standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword <code class="literal">FLOAT</code> 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 <code class="literal">FLOAT</code> column. A precision from 24 to 53 results in an eight-byte double-precision <code class="literal">DOUBLE</code> column. </p><p> MySQL allows a non-standard 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 <code class="literal">DOUBLE</code> as a synonym for <code class="literal">DOUBLE PRECISION</code> (a non-standard extension). MySQL also treats <code class="literal">REAL</code> as a synonym for <code class="literal">DOUBLE PRECISION</code> (a non-standard variation), unless the <code class="literal">REAL_AS_FLOAT</code> SQL mode is enabled. </p><p> For maximum portability, code requiring storage of approximate numeric data values should use <code class="literal">FLOAT</code> or <code class="literal">DOUBLE PRECISION</code> with no specification of precision or number of digits. </p><p> The <code class="literal">DECIMAL</code> and <code class="literal">NUMERIC</code> data types are used to store exact numeric data values. In MySQL, <code class="literal">NUMERIC</code> is implemented as <code class="literal">DECIMAL</code>. These types are used to store values for which it is important to preserve exact precision, for example with monetary data. </p><p> As of MySQL 5.0.3, <code class="literal">DECIMAL</code> and <code class="literal">NUMERIC</code> values are stored in binary format. Previously, they were stored as strings, with one character used for each digit of the value, the decimal point (if the scale is greater than 0), and the “<span class="quote"><code class="literal">-</code></span>” sign (for negative numbers). See <a href="precision-math.html" title="Chapter 22. Precision Math">Chapter 22, <i>Precision Math</i></a>. </p><p> When declaring a <code class="literal">DECIMAL</code> or <code class="literal">NUMERIC</code> 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, <code class="literal">DECIMAL</code> and <code class="literal">NUMERIC</code> 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>. MySQL enforces this limit as of MySQL 5.0.3. Before 5.0.3, on the positive end of the range, the column could actually store numbers up to <code class="literal">9999.99</code>. (For positive numbers, MySQL 5.0.2 and earlier used the byte reserved for the sign to extend the upper end of the range.) </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 <code class="literal">DECIMAL</code> 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 <code class="literal">DECIMAL</code> and <code class="literal">NUMERIC</code> syntax. The default value of <em class="replaceable"><code>M</code></em> is 10. </p><p> The maximum number of digits for <code class="literal">DECIMAL</code> or <code class="literal">NUMERIC</code> is 65 (64 from MySQL 5.0.3 to 5.0.5). Before MySQL 5.0.3, the maximum range of <code class="literal">DECIMAL</code> and <code class="literal">NUMERIC</code> values is the same as for <code class="literal">DOUBLE</code>, but the actual range for a given <code class="literal">DECIMAL</code> or <code class="literal">NUMERIC</code> 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> As of MySQL 5.0.3, the <code class="literal">BIT</code> 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 the mode is set to <code class="literal">TRADITIONAL</code>, 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 non-strict 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 <code class="literal">TINYINT</code> 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> Conversions that occur due to clipping when MySQL is not operating in strict mode are reported as warnings for <code class="literal">ALTER TABLE</code>, <code class="literal">LOAD DATA INFILE</code>, <code class="literal">UPDATE</code>, and multiple-row <code class="literal">INSERT</code> 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.2.6. SQL Modes">Section 5.2.6, “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="id2764207"></a><a class="indexterm" name="id2764219"></a><p> The date and time types for representing temporal values are <code class="literal">DATETIME</code>, <code class="literal">DATE</code>, <code class="literal">TIMESTAMP</code>, <code class="literal">TIME</code>, and <code class="literal">YEAR</code>. 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 <code class="literal">TIMESTAMP</code> 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> Starting from MySQL 5.0.2, 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.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>.) You can get MySQL to accept certain dates, such as <code class="literal">'1999-11-31'</code>, by using the <code class="literal">ALLOW_INVALID_DATES</code> SQL mode. (Before 5.0.2, this mode was the default behavior for MySQL.) 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 <code class="literal">DATE</code> or <code class="literal">DATETIME</code> 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">'1999-00-00'</code> or <code class="literal">'1999-01-00'</code>. If you store dates such as these, you should not expect to get correct results for functions such as <code class="literal">DATE_SUB()</code> or <code class="literal">DATE_ADD</code> that require complete dates. (If you do <span class="emphasis"><em>not</em></span> want to allow zero in dates, you can use the <code class="literal">NO_ZERO_IN_DATE</code> SQL mode). </p><p> Prior to MySQL 5.0.42, when <code class="literal">DATE</code> values are compared with <code class="literal">DATETIME</code> values the time portion of the <code class="literal">DATETIME</code> value is ignored. Starting from MySQL 5.0.42, a <code class="literal">DATE</code> value is coerced to the <code class="literal">DATETIME</code> 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 perform the comparison in the following way: </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 <code class="literal">NO_ZERO_DATE</code> 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 <code class="literal">TIME</code> values are clipped to the appropriate endpoint of the <code class="literal">TIME</code> 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 <code class="literal">NO_ZERO_DATE</code> 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><code class="literal">DATETIME</code></td><td><code class="literal">'0000-00-00 00:00:00'</code></td></tr><tr><td><code class="literal">DATE</code></td><td><code class="literal">'0000-00-00'</code></td></tr><tr><td><code class="literal">TIMESTAMP</code></td><td><code class="literal">'0000-00-00 00:00:00'</code></td></tr><tr><td><code class="literal">TIME</code></td><td><code class="literal">'00:00:00'</code></td></tr><tr><td><code class="literal">YEAR</code></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="id2764813"></a><a class="indexterm" name="id2764822"></a><a class="indexterm" name="id2764832"></a><a class="indexterm" name="id2764841"></a><a class="indexterm" name="id2764853"></a><a class="indexterm" name="id2764865"></a><p> The <code class="literal">DATETIME</code>, <code class="literal">DATE</code>, and <code class="literal">TIMESTAMP</code> types are related. This section describes their characteristics, how they are similar, and how they differ. </p><p> The <code class="literal">DATETIME</code> type is used when you need values that contain both date and time information. MySQL retrieves and displays <code class="literal">DATETIME</code> 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 <code class="literal">DATE</code> type is used when you need only a date value, without a time part. MySQL retrieves and displays <code class="literal">DATE</code> 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 <code class="literal">DATETIME</code> and <code class="literal">DATE</code> range descriptions, “<span class="quote">supported</span>” means that although earlier values might work, there is no guarantee. </p><p> The <code class="literal">TIMESTAMP</code> data type has varying properties, depending on the MySQL version and the SQL mode the server is running in. These properties are described later in this section. </p><p> You can specify <code class="literal">DATETIME</code>, <code class="literal">DATE</code>, and <code class="literal">TIMESTAMP</code> 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">'19970523091528'</code> and <code class="literal">'970523091528'</code> are interpreted as <code class="literal">'1997-05-23 09:15:28'</code>, but <code class="literal">'971122129015'</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">'19970523'</code> and <code class="literal">'970523'</code> are interpreted as <code class="literal">'1997-05-23'</code>, but <code class="literal">'971332'</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 <code class="literal">DATETIME</code>, <code class="literal">DATE</code>, or <code class="literal">TIMESTAMP</code> context, such as <a href="functions.html#function_now"><code class="literal">NOW()</code></a> or <code class="literal">CURRENT_DATE</code>. </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> As of MySQL 5.0.8, conversion of <code class="literal">TIME</code> or <code class="literal">DATETIME</code> 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> Before MySQL 5.0.8, the conversion results in an integer value with no microseconds part. </p><p> Illegal <code class="literal">DATETIME</code>, <code class="literal">DATE</code>, or <code class="literal">TIMESTAMP</code> 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="id2765581"></a><a class="indexterm" name="id2765590"></a><p> Values specified as non-delimited strings are interpreted using their length as given. If the string is 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise, the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute, and second values, for as many parts as are present in the string. This means you should not use strings that have fewer than 6 characters. For example, if you specify <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 <code class="literal">DATE</code> value to a <code class="literal">DATETIME</code> or <code class="literal">TIMESTAMP</code> object, the time part of the resulting value is set to <code class="literal">'00:00:00'</code> because the <code class="literal">DATE</code> value contains no time information. </p></li><li><p> If you assign a <code class="literal">DATETIME</code> or <code class="literal">TIMESTAMP</code> value to a <code class="literal">DATE</code> object, the time part of the resulting value is deleted because the <code class="literal">DATE</code> type stores no time information. </p></li><li><p> Remember that although <code class="literal">DATETIME</code>, <code class="literal">DATE</code>, and <code class="literal">TIMESTAMP</code> values all can be specified using the same set of formats, the types do not all have the same range of values. For example, <code class="literal">TIMESTAMP</code> values cannot be earlier than <code class="literal">1970</code> or later than <code class="literal">2038</code>. This means that a date such as <code class="literal">'1968-01-01'</code>, while legal as a <code class="literal">DATETIME</code> or <code class="literal">DATE</code> value, is not valid as a <code class="literal">TIMESTAMP</code> value and is converted to <code class="literal">0</code>. </p></li></ul></div><a class="indexterm" name="id2765817"></a><a class="indexterm" name="id2765829"></a><p> Be aware of certain pitfalls 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> As of 5.0.2, 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 <code class="literal">ALLOW_INVALID_DATES</code>. See <a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>, for more information. </p><p> Before MySQL 5.0.2, the MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date containing parts not within these ranges is subject to conversion to <code class="literal">'0000-00-00'</code>. Please note that this still allows you to store invalid dates such as <code class="literal">'2002-04-31'</code>. To ensure that a date is valid, you should perform a check in your application. </p></li><li><p> As of MySQL 5.0.2, 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><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p> In older versions of MySQL (prior to 4.1), the properties of the <code class="literal">TIMESTAMP</code> data type differ significantly in several ways from what is described in this section. See the <em class="citetitle">MySQL 3.23, 4.0, 4.1 Reference Manual</em> for details. </p></div><p> <code class="literal">TIMESTAMP</code> columns are displayed in the same format as <code class="literal">DATETIME</code> 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> <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> data type, not for other types such as <code class="literal">DATETIME</code>.) 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.9. MySQL Server Time Zone Support">Section 9.9, “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 <code class="literal">TIMESTAMP</code> 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 <code class="literal">time_zone</code> system variable. </p><p> The <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> column. </p></li><li><p> If a <code class="literal">DEFAULT</code> value is specified for the first <code class="literal">TIMESTAMP</code> column in a table, it is not ignored. The default can be <code class="literal">CURRENT_TIMESTAMP</code> or a constant date and time value. </p></li><li><p> In a <code class="literal">CREATE TABLE</code> statement, the first <code class="literal">TIMESTAMP</code> 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 possiblities: </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 <code class="literal">TIMESTAMP</code> column other than the first one, you must suppress the automatic initialization and update behaviors for the first <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> column, the rules are the same as for the first <code class="literal">TIMESTAMP</code> 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> <code class="literal">CURRENT_TIMESTAMP</code> or any of its synonyms (<code class="literal">CURRENT_TIMESTAMP()</code>, <a href="functions.html#function_now"><code class="literal">NOW()</code></a>, <code class="literal">LOCALTIME</code>, <a href="functions.html#function_localtime"><code class="literal">LOCALTIME()</code></a>, <code class="literal">LOCALTIMESTAMP</code>, 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>” (<code class="literal">UTC_TIMESTAMP</code> is not allowed. Its range of values does not align with those of the <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> 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><p> <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> column not declared with the <code class="literal">NULL</code> attribute, <code class="literal">DEFAULT NULL</code> is illegal.) If a <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> 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 <code class="literal">CURRENT_TIMESTAMP</code> </p></li><li><p> <a href="functions.html#function_now"><code class="literal">NOW()</code></a> or <code class="literal">CURRENT_TIMESTAMP</code> is inserted into the column </p></li></ul></div><p> In other words, a <code class="literal">TIMESTAMP</code> 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 <code class="literal">TIMESTAMP</code> 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 <code class="literal">MAXDB</code> SQL mode enabled. When the server runs with this mode enabled, <code class="literal">TIMESTAMP</code> is identical with <code class="literal">DATETIME</code>. That is, if this mode is enabled at the time that a table is created, <code class="literal">TIMESTAMP</code> columns are created as <code class="literal">DATETIME</code> columns. As a result, such columns use <code class="literal">DATETIME</code> 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 <code class="literal">MAXDB</code> mode, set the server SQL mode to <code class="literal">MAXDB</code> at startup using the <code class="option">--sql-mode=MAXDB</code> server option or by setting the global <code class="literal">sql_mode</code> 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 <code class="literal">MAXDB</code> 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="id2767073"></a><a class="indexterm" name="id2767082"></a><p> MySQL retrieves and displays <code class="literal">TIME</code> values in <code class="literal">'HH:MM:SS'</code> format (or <code class="literal">'HHH:MM:SS'</code> format for large hours values). <code class="literal">TIME</code> 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 <code class="literal">TIME</code> 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 <code class="literal">TIME</code> 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 <code class="literal">TIME</code> context, such as <code class="literal">CURRENT_TIME</code>. </p></li></ul></div><p> A trailing <code class="literal">.uuuuuu</code> microseconds part of <code class="literal">TIME</code> 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 <code class="literal">TIME</code> columns. </p><p> For <code class="literal">TIME</code> 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 <code class="literal">TIME</code> column. Without colons, MySQL interprets values using the assumption that the two rightmost digits represent seconds. (MySQL interprets <code class="literal">TIME</code> 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>. <code class="literal">TIME</code> 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 <code class="literal">TIME</code> 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 <code class="literal">TIME</code> 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 <code class="literal">TIME</code> 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 <code class="literal">TIME</code> values, enable strict SQL mode to cause errors to occur. See <a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “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="id2767601"></a><a class="indexterm" name="id2767610"></a><p> The <code class="literal">YEAR</code> 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 <code class="literal">YEAR</code> 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 <code class="literal">YEAR</code> 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 <code class="literal">YEAR</code> 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 <code class="literal">YEAR</code> 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 <code class="literal">YEAR</code> context, such as <a href="functions.html#function_now"><code class="literal">NOW()</code></a>. </p></li></ul></div><p> Illegal <code class="literal">YEAR</code> 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="id2767967"></a><a class="indexterm" name="id2767976"></a><a class="indexterm" name="id2767988"></a><a class="indexterm" name="id2767997"></a><a class="indexterm" name="id2768010"></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 <code class="literal">TIMESTAMP</code> values. For <code class="literal">DATE</code> and <code class="literal">DATETIME</code> 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 <code class="literal">YEAR</code> 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 <code class="literal">YEAR</code> 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 <code class="literal">DATETIME</code>, <code class="literal">DATE</code>, <code class="literal">TIMESTAMP</code>, and <code class="literal">YEAR</code> 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 <code class="literal">YEAR</code> 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 <code class="literal">YEAR</code> 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 <code class="literal">TIMESTAMP</code> or <code class="literal">YEAR</code> 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="id2768338"></a><a class="indexterm" name="id2768351"></a><a class="indexterm" name="id2768360"></a><a class="indexterm" name="id2768369"></a><a class="indexterm" name="id2768378"></a><a class="indexterm" name="id2768390"></a><p> The string types are <code class="literal">CHAR</code>, <code class="literal">VARCHAR</code>, <code class="literal">BINARY</code>, <code class="literal">VARBINARY</code>, <code class="literal">BLOB</code>, <code class="literal">TEXT</code>, <code class="literal">ENUM</code>, and <code class="literal">SET</code>. 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 <code class="literal">CHAR</code> and <code class="literal">VARCHAR</code> types are similar, but differ in the way they are stored and retrieved. As of MySQL 5.0.3, they also differ in maximum length and in whether trailing spaces are retained. </p><p> The <code class="literal">CHAR</code> and <code class="literal">VARCHAR</code> 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 <code class="literal">CHAR</code> 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 <code class="literal">CHAR</code> values are stored, they are right-padded with spaces to the specified length. When <code class="literal">CHAR</code> values are retrieved, trailing spaces are removed. </p><p> Values in <code class="literal">VARCHAR</code> columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a <code class="literal">VARCHAR</code> in MySQL 5.0.3 and later 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 <code class="literal">CHAR</code>, <code class="literal">VARCHAR</code> 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 <code class="literal">CHAR</code> or <code class="literal">VARCHAR</code> column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of non-space characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See <a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>. </p><p> <code class="literal">VARCHAR</code> values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a <code class="literal">VARCHAR</code> column; this means that the spaces also are absent from retrieved values. </p><p> Before MySQL 5.0.3, if you need a data type for which trailing spaces are not removed, consider using a <code class="literal">BLOB</code> or <code class="literal">TEXT</code> type. Also, if you want to store binary values such as results from an encryption or compression function that might contain arbitrary byte values, use a <code class="literal">BLOB</code> column rather than a <code class="literal">CHAR</code> or <code class="literal">VARCHAR</code> column, to avoid potential problems with trailing space removal that would change data values. </p><p> The following table illustrates the differences between <code class="literal">CHAR</code> and <code class="literal">VARCHAR</code> 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 <code class="literal">CHAR</code> 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 <code class="literal">CHAR</code> and <code class="literal">VARCHAR</code> 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 <code class="literal">CHAR</code> and <code class="literal">VARCHAR</code> 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 it makes no difference whether your version trims trailing spaces from <code class="literal">VARCHAR</code> values before storing them. Nor does the server SQL mode make any difference in this regard. </p><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="id2769128"></a><a class="indexterm" name="id2769137"></a><a class="indexterm" name="id2769146"></a><a class="indexterm" name="id2769159"></a><p> The <code class="literal">BINARY</code> and <code class="literal">VARBINARY</code> types are similar to <code class="literal">CHAR</code> and <code class="literal">VARCHAR</code>, except that they contain binary strings rather than non-binary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values. </p><p> The allowable maximum length is the same for <code class="literal">BINARY</code> and <code class="literal">VARBINARY</code> as it is for <code class="literal">CHAR</code> and <code class="literal">VARCHAR</code>, except that the length for <code class="literal">BINARY</code> and <code class="literal">VARBINARY</code> is a length in bytes rather than in characters. </p><p> The <code class="literal">BINARY</code> and <code class="literal">VARBINARY</code> 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 non-binary 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. </p><p> If strict SQL mode is not enabled and you assign a value to a <code class="literal">BINARY</code> or <code class="literal">VARBINARY</code> 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.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>. </p><p> When <code class="literal">BINARY</code> values are stored, they are right-padded with the pad value to the specified length. The pad value and how it is handled is version specific: </p><div class="itemizedlist"><ul type="disc"><li><p> As of MySQL 5.0.15, 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></li><li><p> Before MySQL 5.0.15, the pad value is space. Values are right-padded with space on insert, and trailing spaces are removed on select. Trailing spaces are ignored 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 '</code> when inserted and <code class="literal">'a'</code> when selected. <code class="literal">'a\0'</code> becomes <code class="literal">'a\0 '</code> when inserted and <code class="literal">'a\0'</code> when selected. </p></li></ul></div><p> For <code class="literal">VARBINARY</code>, 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. (Exceptions: Before MySQL 5.0.3, trailing spaces are removed when values are stored. Before MySQL 5.0.15, trailing 0x00 bytes are removed for <code class="literal">ORDER BY</code> operations.) </p><p> Note: The <code class="literal">InnoDB</code> storage engine continues to preserve trailing spaces in <code class="literal">BINARY</code> and <code class="literal">VARBINARY</code> column values through MySQL 5.0.18. Beginning with MySQL 5.0.19, <code class="literal">InnoDB</code> uses trailing space characters in making comparisons as do other MySQL storage engines. </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 <code class="literal">BINARY</code> 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 <code class="literal">BINARY</code> 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 <code class="literal">VARBINARY</code> or one of the <code class="literal">BLOB</code> 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="id2769737"></a><a class="indexterm" name="id2769746"></a><a class="indexterm" name="id2769755"></a><a class="indexterm" name="id2769767"></a><a class="indexterm" name="id2769780"></a><a class="indexterm" name="id2769789"></a><p> A <code class="literal">BLOB</code> is a binary large object that can hold a variable amount of data. The four <code class="literal">BLOB</code> types are <code class="literal">TINYBLOB</code>, <code class="literal">BLOB</code>, <code class="literal">MEDIUMBLOB</code>, and <code class="literal">LONGBLOB</code>. These differ only in the maximum length of the values they can hold. The four <code class="literal">TEXT</code> types are <code class="literal">TINYTEXT</code>, <code class="literal">TEXT</code>, <code class="literal">MEDIUMTEXT</code>, and <code class="literal">LONGTEXT</code>. These correspond to the four <code class="literal">BLOB</code> 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> <code class="literal">BLOB</code> columns are treated as binary strings (byte strings). <code class="literal">TEXT</code> columns are treated as non-binary strings (character strings). <code class="literal">BLOB</code> columns have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. <code class="literal">TEXT</code> columns 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 <code class="literal">BLOB</code> or <code class="literal">TEXT</code> column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of non-space characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See <a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>. </p><p> If a <code class="literal">TEXT</code> 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 <code class="literal">BLOB</code> columns. </p><p> In most respects, you can regard a <code class="literal">BLOB</code> column as a <code class="literal">VARBINARY</code> column that can be as large as you like. Similarly, you can regard a <code class="literal">TEXT</code> column as a <code class="literal">VARCHAR</code> column. <code class="literal">BLOB</code> and <code class="literal">TEXT</code> differ from <code class="literal">VARBINARY</code> and <code class="literal">VARCHAR</code> in the following ways: </p><div class="itemizedlist"><ul type="disc"><li><p> There is no trailing-space removal for <code class="literal">BLOB</code> and <code class="literal">TEXT</code> columns when values are stored or retrieved. Before MySQL 5.0.3, this differs from <code class="literal">VARBINARY</code> and <code class="literal">VARCHAR</code>, for which trailing spaces are removed when values are stored. </p><p> On comparisons, <code class="literal">TEXT</code> is space extended to fit the compared object, exactly like <code class="literal">CHAR</code> and <code class="literal">VARCHAR</code>. </p></li><li><p> For indexes on <code class="literal">BLOB</code> and <code class="literal">TEXT</code> columns, you must specify an index prefix length. For <code class="literal">CHAR</code> and <code class="literal">VARCHAR</code>, a prefix length is optional. See <a href="optimization.html#indexes" title="6.4.3. Column Indexes">Section 6.4.3, “Column Indexes”</a>. </p></li><li><p> <a class="indexterm" name="id2770142"></a> <a class="indexterm" name="id2770154"></a> <a class="indexterm" name="id2770167"></a> <code class="literal">BLOB</code> and <code class="literal">TEXT</code> columns cannot have <code class="literal">DEFAULT</code> values. </p></li></ul></div><p> <code class="literal">LONG</code> and <code class="literal">LONG VARCHAR</code> map to the <code class="literal">MEDIUMTEXT</code> data type. This is a compatibility feature. If you use the <code class="literal">BINARY</code> attribute with a <code class="literal">TEXT</code> data type, the column is assigned the binary collation of the column character set. </p><p> MySQL Connector/ODBC defines <code class="literal">BLOB</code> values as <code class="literal">LONGVARBINARY</code> and <code class="literal">TEXT</code> values as <code class="literal">LONGVARCHAR</code>. </p><p> Because <code class="literal">BLOB</code> and <code class="literal">TEXT</code> 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 <code class="literal">max_sort_length</code> bytes of the column are used when sorting. The default value of <code class="literal">max_sort_length</code> is 1024. This value can be changed using the <code class="option">--max_sort_length=<em class="replaceable"><code>N</code></em></code> option when starting the <span><strong class="command">mysqld</strong></span> server. See <a href="server-administration.html#server-system-variables" title="5.2.3. System Variables">Section 5.2.3, “System Variables”</a>. </p><p> You can make more bytes significant in sorting or grouping by increasing the value of <code class="literal">max_sort_length</code> at runtime. Any client can change the value of its session <code class="literal">max_sort_length</code> 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 <code class="literal">BLOB</code> or <code class="literal">TEXT</code> column containing long values when you want more than <code class="literal">max_sort_length</code> 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> The maximum size of a <code class="literal">BLOB</code> or <code class="literal">TEXT</code> 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 <code class="literal">max_allowed_packet</code> variable, but you must do so for both the server and your client program. For example, both <span><strong class="command">mysql</strong></span> and <span><strong class="command">mysqldump</strong></span> allow you to change the client-side <code class="literal">max_allowed_packet</code> value. See <a href="optimization.html#server-parameters" title="6.5.2. Tuning Server Parameters">Section 6.5.2, “Tuning Server Parameters”</a>, <a href="client-utility-programs.html#mysql" title="7.7. mysql — The MySQL Command-Line Tool">Section 7.7, “<span><strong class="command">mysql</strong></span> — The MySQL Command-Line Tool”</a>, and <a href="client-utility-programs.html#mysqldump" title="7.12. mysqldump — A Database Backup Program">Section 7.12, “<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 <code class="literal">BLOB</code> or <code class="literal">TEXT</code> 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 <code class="literal">BLOB</code> or <code class="literal">TEXT</code> 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 <code class="literal">FILE</code> 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="id2770589"></a><a class="indexterm" name="id2770598"></a><p> An <code class="literal">ENUM</code> 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. This means that you also may not employ a user variable as an enumeration value. </p><p> For example, you can create a table with an <code class="literal">ENUM</code> column like this: </p><pre class="programlisting">CREATE TABLE sizes ( name ENUM('small', 'medium', 'large') ); </pre><p> However, this version of the previous <code class="literal">CREATE TABLE</code> 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> 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 <code class="literal">ENUM</code> (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 <code class="literal">ENUM</code> values result in an error. </p></li><li><p> If an <code class="literal">ENUM</code> 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 <code class="literal">ENUM</code> 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 <code class="literal">SELECT</code> statement to find rows into which invalid <code class="literal">ENUM</code> 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 <code class="literal">ENUM</code> member values in the table definition when a table is created. </p><p> When retrieved, values stored into an <code class="literal">ENUM</code> column are displayed using the lettercase that was used in the column definition. Note that <code class="literal">ENUM</code> 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 <code class="literal">ENUM</code> value in a numeric context, the column value's index is returned. For example, you can retrieve numeric values from an <code class="literal">ENUM</code> 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 <code class="literal">ENUM</code> 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 <code class="literal">LOAD DATA</code>, 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 <code class="literal">ENUM</code> 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> <code class="literal">ENUM</code> values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, <code class="literal">ENUM</code> 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 non-empty strings, and <code class="literal">NULL</code> values sort before all other enumeration values. To prevent unexpected results, specify the <code class="literal">ENUM</code> 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 <code class="literal">ENUM</code> values, the cast operation causes the index number to be used. </p><p> If you want to determine all possible values for an <code class="literal">ENUM</code> 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 <code class="literal">ENUM</code> 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="id2771373"></a><a class="indexterm" name="id2771382"></a><p> A <code class="literal">SET</code> 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. <code class="literal">SET</code> 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 <code class="literal">SET</code> 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 <code class="literal">SET</code> can have a maximum of 64 different members. </p><p> Trailing spaces are automatically deleted from <code class="literal">SET</code> member values in the table definition when a table is created. </p><p> When retrieved, values stored in a <code class="literal">SET</code> column are displayed using the lettercase that was used in the column definition. Note that <code class="literal">SET</code> 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 <code class="literal">SET</code> values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a <code class="literal">SET</code> 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 <code class="literal">SET</code> 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 <code class="literal">SET</code> 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><code class="literal">SET</code> <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 <code class="literal">SET</code> 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 <code class="literal">SET</code> 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 <code class="literal">SET</code> 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 <code class="literal">SET</code> values result in an error. </p><p> <code class="literal">SET</code> values are sorted numerically. <code class="literal">NULL</code> values sort before non-<code class="literal">NULL</code> <code class="literal">SET</code> 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 <code class="literal">SET</code> values, the cast operation causes the numeric value to be used. </p><p> Normally, you search for <code class="literal">SET</code> values using the <code class="literal">FIND_IN_SET()</code> function or the <code class="literal">LIKE</code> 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 <code class="literal">SET</code> 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 <code class="literal">SET</code> 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="id2772176"></a><a class="indexterm" name="id2772188"></a><p> The storage requirements for each of the data types supported by MySQL are listed here by category. </p><a class="indexterm" name="id2772206"></a><p> The maximum size of a row in a <code class="literal">MyISAM</code> table is 65,535 bytes. Each <code class="literal">BLOB</code> and <code class="literal">TEXT</code> column accounts for only nine to twelve bytes toward this size. This limitation may be shared by other storage engines as well. </p><a class="indexterm" name="id2772244"></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 <code class="literal">TINYINT</code>, <code class="literal">SMALLINT</code>, <code class="literal">MEDIUMINT</code>, and <code class="literal">INTEGER</code> (<code class="literal">INT</code>) column types each require 4 bytes storage per record due to the alignment factor. </p></div><p> In addition, when calculating storage requirements for Cluster tables, you must 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-utilities-ndb-size" title="16.8.14. ndb_size.pl — NDBCluster Size Requirement Estimator">Section 16.8.14, “<span><strong class="command">ndb_size.pl</strong></span> — NDBCluster Size Requirement Estimator”</a>, for more information. </p><a class="indexterm" name="id2772390"></a><a class="indexterm" name="id2772399"></a><p> <span class="bold"><strong>Storage Requirements for Numeric Types</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><code class="literal">TINYINT</code></td><td>1 byte</td></tr><tr><td><code class="literal">SMALLINT</code></td><td>2 bytes</td></tr><tr><td><code class="literal">MEDIUMINT</code></td><td>3 bytes</td></tr><tr><td><code class="literal">INT</code>, <code class="literal">INTEGER</code></td><td>4 bytes</td></tr><tr><td><code class="literal">BIGINT</code></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><code class="literal">FLOAT</code></td><td>4 bytes</td></tr><tr><td><code class="literal">DOUBLE [PRECISION]</code>, <code class="literal">REAL</code></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> The storage requirements for <code class="literal">DECIMAL</code> (and <code class="literal">NUMERIC</code>) are version-specific: </p><p> As of MySQL 5.0.3, values for <code class="literal">DECIMAL</code> 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><p> Before MySQL 5.0.3, <code class="literal">DECIMAL</code> columns are represented as strings and storage requirements are: <em class="replaceable"><code>M</code></em>+2 bytes if <em class="replaceable"><code>D</code></em> > 0, <code class="literal"><em class="replaceable"><code>M</code></em>+1</code> bytes if <em class="replaceable"><code>D</code></em> = 0, <em class="replaceable"><code>D</code></em>+2 if <code class="literal"><em class="replaceable"><code>M</code></em> < <em class="replaceable"><code>D</code></em></code> </p><a class="indexterm" name="id2772846"></a><a class="indexterm" name="id2772855"></a><a class="indexterm" name="id2772864"></a><a class="indexterm" name="id2772876"></a><p> <span class="bold"><strong>Storage Requirements for Date and Time Types</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><code class="literal">DATE</code></td><td>3 bytes</td></tr><tr><td><code class="literal">TIME</code></td><td>3 bytes</td></tr><tr><td><code class="literal">DATETIME</code></td><td>8 bytes</td></tr><tr><td><code class="literal">TIMESTAMP</code></td><td>4 bytes</td></tr><tr><td><code class="literal">YEAR</code></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> <code class="literal">DATE</code>: 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> <code class="literal">TIME</code>: 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> <code class="literal">DATETIME</code>: 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> <code class="literal">TIMESTAMP</code>: 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> <code class="literal">YEAR</code>: A one-byte integer </p></li></ul></div><p> <span class="bold"><strong>Storage Requirements for String Types</strong></span> </p><p> In the following table, <em class="replaceable"><code>M</code></em> represents the declared column length in characters for non-binary 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><code class="literal">TINYBLOB</code>, <code class="literal">TINYTEXT</code></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><code class="literal">BLOB</code>, <code class="literal">TEXT</code></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><code class="literal">MEDIUMBLOB</code>, <code class="literal">MEDIUMTEXT</code></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><code class="literal">LONGBLOB</code>, <code class="literal">LONGTEXT</code></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 <code class="literal">MEDIUMTEXT</code> 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 <code class="literal">CHAR</code>, <code class="literal">VARCHAR</code>, or <code class="literal">TEXT</code> 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.8. Unicode Support">Section 9.1.8, “Unicode Support”</a>. </p><a class="indexterm" name="id2773568"></a><a class="indexterm" name="id2773580"></a><a class="indexterm" name="id2773593"></a><p> <code class="literal">VARCHAR</code>, <code class="literal">VARBINARY</code>, and the <code class="literal">BLOB</code> and <code class="literal">TEXT</code> 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 <code class="literal">VARCHAR</code> or <code class="literal">VARBINARY</code> column is subject to the maximum row size of 65,535 bytes, which is shared among all columns. For a <code class="literal">VARCHAR</code> 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 <code class="literal">VARCHAR</code> 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> As of MySQL 5.0.3, the <code class="literal">NDBCLUSTER</code> engine supports only fixed-width columns. This means that a <code class="literal">VARCHAR</code> column from a table in a MySQL Cluster will behave as follows: </p><div class="itemizedlist"><ul type="disc"><li><p> If the size of the column is fewer than 256 characters, the column requires one byte extra storage per row. </p></li><li><p> If the size of the column is 256 characters or more, the column requires two bytes extra storage per row. </p></li></ul></div><p> The number of bytes required per character varies according to the character set used. For example, if a <code class="literal">VARCHAR(100)</code> column in a Cluster table uses the <code class="literal">utf8</code> character set, each character requires 3 bytes storage. This means that each record in such a column takes up 100 × 3 + 1 = 301 bytes for storage, regardless of the length of the string actually stored in any given record. For a <code class="literal">VARCHAR(1000)</code> column in a table using the <code class="literal">NDBCLUSTER</code> storage engine with the <code class="literal">utf8</code> character set, each record will use 1000 × 3 + 2 = 3002 bytes storage; that is, the column is 1,000 characters wide, each character requires 3 bytes storage, and each record has a 2-byte overhead because 1,000 >= 256. </p><p> <code class="literal">TEXT</code> and <code class="literal">BLOB</code> columns are implemented differently in the NDB Cluster storage engine, wherein each row in a <code class="literal">TEXT</code> 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 <code class="literal">TEXT</code> 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="id2773914"></a><p> The size of an <code class="literal">ENUM</code> 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="id2773944"></a><p> The size of a <code class="literal">SET</code> 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 <code class="literal">SET</code> 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="id2774002"></a><a class="indexterm" name="id2774014"></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> Tables created in MySQL 5.0.3 and above uses a new storage format for <code class="literal">DECIMAL</code> columns. All basic calculation (<code class="literal">+,-,*,/</code>) with <code class="literal">DECIMAL</code> 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> Prior to MySQL 5.0.3, calculations on <code class="literal">DECIMAL</code> values are performed using double-precision operations. If accuracy is not too important or if speed is the highest priority, the <code class="literal">DOUBLE</code> type may be good enough. For high precision, you can always convert to a fixed-point type stored in a <code class="literal">BIGINT</code>. This allows you to do all calculations with 64-bit integers and then convert results back to floating-point values as necessary. </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="id2774117"></a><a class="indexterm" name="id2774129"></a><a class="indexterm" name="id2774142"></a><a class="indexterm" name="id2774154"></a><a class="indexterm" name="id2774166"></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><code class="literal">BOOL</code></td><td><code class="literal">TINYINT</code></td></tr><tr><td><code class="literal">BOOLEAN</code></td><td><code class="literal">TINYINT</code></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><code class="literal">FIXED</code></td><td><code class="literal">DECIMAL</code></td></tr><tr><td><code class="literal">FLOAT4</code></td><td><code class="literal">FLOAT</code></td></tr><tr><td><code class="literal">FLOAT8</code></td><td><code class="literal">DOUBLE</code></td></tr><tr><td><code class="literal">INT1</code></td><td><code class="literal">TINYINT</code></td></tr><tr><td><code class="literal">INT2</code></td><td><code class="literal">SMALLINT</code></td></tr><tr><td><code class="literal">INT3</code></td><td><code class="literal">MEDIUMINT</code></td></tr><tr><td><code class="literal">INT4</code></td><td><code class="literal">INT</code></td></tr><tr><td><code class="literal">INT8</code></td><td><code class="literal">BIGINT</code></td></tr><tr><td><code class="literal">LONG VARBINARY</code></td><td><code class="literal">MEDIUMBLOB</code></td></tr><tr><td><code class="literal">LONG VARCHAR</code></td><td><code class="literal">MEDIUMTEXT</code></td></tr><tr><td><code class="literal">LONG</code></td><td><code class="literal">MEDIUMTEXT</code></td></tr><tr><td><code class="literal">MIDDLEINT</code></td><td><code class="literal">MEDIUMINT</code></td></tr><tr><td><code class="literal">NUMERIC</code></td><td><code class="literal">DECIMAL</code></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>