<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="type-conversion">
      </a>
      14.3 Type Conversion in Expression Evaluation
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045207934368">
  </a>
  <a class="indexterm" name="idm46045207933328">
  </a>
  <p>
   When an operator is used with operands of different types, type
      conversion occurs to make the operands compatible. Some
      conversions occur implicitly. For example, MySQL automatically
      converts strings to numbers as necessary, and vice versa.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75541447"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token operator">+</span><span class="token string">'1'</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">2</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token string">' test'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'2 test'</span></code></pre>
  </div>
  <p>
   It is also possible to convert a number to a string explicitly
      using the
   <a class="link" href="cast-functions.html#function_cast">
    <code class="literal">
     CAST()
    </code>
   </a>
   function.
      Conversion occurs implicitly with the
   <a class="link" href="string-functions.html#function_concat">
    <code class="literal">
     CONCAT()
    </code>
   </a>
   function because it
      expects string arguments.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36599371"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">38.8</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token number">38.8</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">38.8</span><span class="token punctuation">,</span> <span class="token string">'38.8'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">38.8</span><span class="token punctuation">,</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token number">38.8</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">38.8</span><span class="token punctuation">,</span> <span class="token string">'38.8'</span></code></pre>
  </div>
  <p>
   See later in this section for information about the character set
      of implicit number-to-string conversions, and for modified rules
      that apply to
   <code class="literal">
    CREATE TABLE ... SELECT
   </code>
   statements.
  </p>
  <p>
   The following rules describe how conversion occurs for comparison
      operations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If one or both arguments are
      <code class="literal">
       NULL
      </code>
      , the
          result of the comparison is
      <code class="literal">
       NULL
      </code>
      , except
          for the
      <code class="literal">
       NULL
      </code>
      -safe
      <a class="link" href="comparison-operators.html#operator_equal-to">
       <code class="literal">
        &lt;=&gt;
       </code>
      </a>
      equality comparison operator. For
      <code class="literal">
       NULL &lt;=&gt;
          NULL
      </code>
      , the result is true. No conversion is needed.
     </p>
    </li>
    <li class="listitem">
     <p>
      If both arguments in a comparison operation are strings, they
          are compared as strings.
     </p>
    </li>
    <li class="listitem">
     <p>
      If both arguments are integers, they are compared as integers.
     </p>
    </li>
    <li class="listitem">
     <p>
      Hexadecimal values are treated as binary strings if not
          compared to a number.
     </p>
    </li>
    <li class="listitem">
     <p>
      If one of the arguments is a
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      or
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      column and the other
          argument is a constant, the constant is converted to a
          timestamp before the comparison is performed. This is done to
          be more ODBC-friendly. This is not done for the arguments to
      <a class="link" href="comparison-operators.html#operator_in">
       <code class="literal">
        IN()
       </code>
      </a>
      . To be safe, always use
          complete datetime, date, or time strings when doing
          comparisons. For example, to achieve best results when using
      <a class="link" href="comparison-operators.html#operator_between">
       <code class="literal">
        BETWEEN
       </code>
      </a>
      with date or time values,
          use
      <a class="link" href="cast-functions.html#function_cast">
       <code class="literal">
        CAST()
       </code>
      </a>
      to explicitly
          convert the values to the desired data type.
     </p>
     <p>
      A single-row subquery from a table or tables is not considered
          a constant. For example, if a subquery returns an integer to
          be compared to a
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      value, the comparison is done as two integers. The integer is
          not converted to a temporal value. To compare the operands as
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      values, use
      <a class="link" href="cast-functions.html#function_cast">
       <code class="literal">
        CAST()
       </code>
      </a>
      to explicitly convert
          the subquery value to
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045207900864">
     </a>
     <a class="indexterm" name="idm46045207899792">
     </a>
    </li>
    <li class="listitem">
     <p>
      If one of the arguments is a decimal value, comparison depends
          on the other argument. The arguments are compared as decimal
          values if the other argument is a decimal or integer value, or
          as floating-point values if the other argument is a
          floating-point value.
     </p>
    </li>
    <li class="listitem">
     <p>
      In all other cases, the arguments are compared as
          floating-point (double-precision) numbers. For example, a
          comparison of string and numeric operands takes place as a
          comparison of floating-point numbers.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For information about conversion of values from one temporal type
      to another, see
   <a class="xref" href="date-and-time-type-conversion.html" title="13.2.8 Conversion Between Date and Time Types">
    Section 13.2.8, “Conversion Between Date and Time Types”
   </a>
   .
  </p>
  <p>
   Comparison of JSON values takes place at two levels. The first
      level of comparison is based on the JSON types of the compared
      values. If the types differ, the comparison result is determined
      solely by which type has higher precedence. If the two values have
      the same JSON type, a second level of comparison occurs using
      type-specific rules. For comparison of JSON and non-JSON values,
      the non-JSON value is converted to JSON and the values compared as
      JSON values. For details, see
   <a class="xref" href="json.html#json-comparison" title="Comparison and Ordering of JSON Values">
    Comparison and Ordering of JSON Values
   </a>
   .
  </p>
  <p>
   The following examples illustrate conversion of strings to numbers
      for comparison operations:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79926722"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token operator">&gt;</span> <span class="token string">'6x'</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">0</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">7</span> <span class="token operator">&gt;</span> <span class="token string">'6x'</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">1</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">0</span> <span class="token operator">&gt;</span> <span class="token string">'x6'</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">0</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">0</span> <span class="token operator">=</span> <span class="token string">'x6'</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">1</span></code></pre>
  </div>
  <p>
   For comparisons of a string column with a number, MySQL cannot use
      an index on the column to look up the value quickly. If
   <em class="replaceable">
    <code>
     str_col
    </code>
   </em>
   is an indexed string column,
      the index cannot be used when performing the lookup in the
      following statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa57983256"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">str_col</em><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The reason for this is that there are many different strings that
      may convert to the value
   <code class="literal">
    1
   </code>
   , such as
   <code class="literal">
    '1'
   </code>
   ,
   <code class="literal">
    ' 1'
   </code>
   , or
   <code class="literal">
    '1a'
   </code>
   .
  </p>
  <p>
   Another issue can arise when comparing a string column with
      integer
   <code class="literal">
    0
   </code>
   . Consider table
   <code class="literal">
    t1
   </code>
   created and populated as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76517459"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>   c1 <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>   c2 <span class="token datatype">INT</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>   c3 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>   <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>c1<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">52</span><span class="token punctuation">,</span> <span class="token string">'grape'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">139</span><span class="token punctuation">,</span> <span class="token string">'apple'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> 
<span class="token prompt">    -&gt;</span>                       <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">37</span><span class="token punctuation">,</span> <span class="token string">'peach'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">221</span><span class="token punctuation">,</span> <span class="token string">'watermelon'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>                       <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">83</span><span class="token punctuation">,</span> <span class="token string">'pear'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 5 rows affected (0.01 sec)</span>
<span class="token output">Records: 5  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   Observe the result when selecting from this table and comparing
   <code class="literal">
    c3
   </code>
   , which is a
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   column, with integer
   <code class="literal">
    0
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50041352"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c3 <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> c1 <span class="token punctuation">|</span> c2   <span class="token punctuation">|</span> c3         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span>   52 <span class="token punctuation">|</span> grape      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  2 <span class="token punctuation">|</span>  139 <span class="token punctuation">|</span> apple      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <span class="token punctuation">|</span>   37 <span class="token punctuation">|</span> peach      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  4 <span class="token punctuation">|</span>  221 <span class="token punctuation">|</span> watermelon <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  5 <span class="token punctuation">|</span>   83 <span class="token punctuation">|</span> pear       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">5 rows in set, 5 warnings (0.00 sec)</span></code></pre>
  </div>
  <p>
   <span class="emphasis">
    <em>
     This occurs even when using strict SQL mode
    </em>
   </span>
   .
      To prevent this from happening, quote the value, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61040365"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c3 <span class="token operator">=</span> <span class="token string">'0'</span><span class="token punctuation">;</span>
<span class="token output">Empty set (0.00 sec)</span></code></pre>
  </div>
  <p>
   This does
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   occur when
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   is part of a data definition
      statement such as
   <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
    <code class="literal">
     CREATE TABLE
      ... SELECT
    </code>
   </a>
   ; in strict mode, the statement fails due to
      the invalid comparison:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16975585"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c3 <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1292 (22007)<span class="token punctuation">:</span> Truncated incorrect DOUBLE value<span class="token punctuation">:</span> 'grape'</span></code></pre>
  </div>
  <p>
   When the
   <code class="literal">
    0
   </code>
   is quoted, the statement succeeds,
      but the table created contains no rows because there were none
      matching
   <code class="literal">
    '0'
   </code>
   , as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16759237"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> c3 <span class="token operator">=</span> <span class="token string">'0'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>
<span class="token output">Records: 0  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span>
<span class="token output">Empty set (0.00 sec)</span></code></pre>
  </div>
  <p>
   This is a known issue, which is due to the fact that strict mode
      is not applied when processing
   <code class="literal">
    SELECT
   </code>
   . See also
   <a class="xref" href="sql-mode.html#sql-mode-strict" title="Strict SQL Mode">
    Strict SQL Mode
   </a>
   .
  </p>
  <p>
   Comparisons between floating-point numbers and large integer
      values are approximate because the integer is converted to
      double-precision floating point before comparison, which is not
      capable of representing all 64-bit integers exactly. For example,
      the integer value 2
   <sup>
    53
   </sup>
   + 1 is not
      representable as a float, and is rounded to
      2
   <sup>
    53
   </sup>
   or 2
   <sup>
    53
   </sup>
   +
      2 before a float comparison, depending on the platform.
  </p>
  <p>
   To illustrate, only the first of the following comparisons
      compares equal values, but both comparisons return true (1):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82291208"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token string">'9223372036854775807'</span> <span class="token operator">=</span> <span class="token number">9223372036854775807</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">1</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token string">'9223372036854775807'</span> <span class="token operator">=</span> <span class="token number">9223372036854775806</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">1</span></code></pre>
  </div>
  <p>
   When conversions from string to floating-point and from integer to
      floating-point occur, they do not necessarily occur the same way.
      The integer may be converted to floating-point by the CPU, whereas
      the string is converted digit by digit in an operation that
      involves floating-point multiplications. Also, results can be
      affected by factors such as computer architecture or the compiler
      version or optimization level. One way to avoid such problems is
      to use
   <a class="link" href="cast-functions.html#function_cast">
    <code class="literal">
     CAST()
    </code>
   </a>
   so that a value is
      not converted implicitly to a float-point number:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21928713"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'9223372036854775807'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span><span class="token punctuation">)</span> <span class="token operator">=</span> <span class="token number">9223372036854775806</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">0</span></code></pre>
  </div>
  <p>
   For more information about floating-point comparisons, see
   <a class="xref" href="problems-with-float.html" title="B.3.4.8 Problems with Floating-Point Values">
    Section B.3.4.8, “Problems with Floating-Point Values”
   </a>
   .
  </p>
  <p>
   The server includes
   <code class="literal">
    dtoa
   </code>
   , a conversion library
      that provides the basis for improved conversion between string or
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   values and
      approximate-value
      (
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     FLOAT
    </code>
   </a>
   /
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     DOUBLE
    </code>
   </a>
   )
      numbers:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Consistent conversion results across platforms, which
          eliminates, for example, Unix versus Windows conversion
          differences.
     </p>
    </li>
    <li class="listitem">
     <p>
      Accurate representation of values in cases where results
          previously did not provide sufficient precision, such as for
          values close to IEEE limits.
     </p>
    </li>
    <li class="listitem">
     <p>
      Conversion of numbers to string format with the best possible
          precision. The precision of
      <code class="literal">
       dtoa
      </code>
      is always
          the same or better than that of the standard C library
          functions.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Because the conversions produced by this library differ in some
      cases from non-
   <code class="literal">
    dtoa
   </code>
   results, the potential
      exists for incompatibilities in applications that rely on previous
      results. For example, applications that depend on a specific exact
      result from previous conversions might need adjustment to
      accommodate additional precision.
  </p>
  <p>
   The
   <code class="literal">
    dtoa
   </code>
   library provides conversions with the
      following properties.
   <em class="replaceable">
    <code>
     D
    </code>
   </em>
   represents a
      value with a
   <a class="link" href="fixed-point-types.html" title="13.1.3 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC">
    <code class="literal">
     DECIMAL
    </code>
   </a>
   or string
      representation, and
   <em class="replaceable">
    <code>
     F
    </code>
   </em>
   represents a
      floating-point number in native binary (IEEE) format.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        F
       </code>
      </em>
      -&gt;
      <em class="replaceable">
       <code>
        D
       </code>
      </em>
      conversion is done with the best
          possible precision, returning
      <em class="replaceable">
       <code>
        D
       </code>
      </em>
      as
          the shortest string that yields
      <em class="replaceable">
       <code>
        F
       </code>
      </em>
      when read back in and rounded to the nearest value in native
          binary format as specified by IEEE.
     </p>
    </li>
    <li class="listitem">
     <p>
      <em class="replaceable">
       <code>
        D
       </code>
      </em>
      -&gt;
      <em class="replaceable">
       <code>
        F
       </code>
      </em>
      conversion is done such that
      <em class="replaceable">
       <code>
        F
       </code>
      </em>
      is the nearest native binary
          number to the input decimal string
      <em class="replaceable">
       <code>
        D
       </code>
      </em>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   These properties imply that
   <em class="replaceable">
    <code>
     F
    </code>
   </em>
   -&gt;
   <em class="replaceable">
    <code>
     D
    </code>
   </em>
   -&gt;
   <em class="replaceable">
    <code>
     F
    </code>
   </em>
   conversions are lossless unless
   <em class="replaceable">
    <code>
     F
    </code>
   </em>
   is
   <code class="literal">
    -inf
   </code>
   ,
   <code class="literal">
    +inf
   </code>
   , or
   <code class="literal">
    NaN
   </code>
   . The latter values are not supported
      because the SQL standard defines them as invalid values for
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     FLOAT
    </code>
   </a>
   or
   <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
    <code class="literal">
     DOUBLE
    </code>
   </a>
   .
  </p>
  <p>
   For
   <em class="replaceable">
    <code>
     D
    </code>
   </em>
   -&gt;
   <em class="replaceable">
    <code>
     F
    </code>
   </em>
   -&gt;
   <em class="replaceable">
    <code>
     D
    </code>
   </em>
   conversions, a sufficient condition for losslessness is that
   <em class="replaceable">
    <code>
     D
    </code>
   </em>
   uses 15 or fewer digits of precision,
      is not a denormal value,
   <code class="literal">
    -inf
   </code>
   ,
   <code class="literal">
    +inf
   </code>
   , or
   <code class="literal">
    NaN
   </code>
   . In some cases,
      the conversion is lossless even if
   <em class="replaceable">
    <code>
     D
    </code>
   </em>
   has more than 15 digits of precision, but this is not always the
      case.
  </p>
  <p>
   Implicit conversion of a numeric or temporal value to string
      produces a value that has a character set and collation determined
      by the
   <a class="link" href="server-system-variables.html#sysvar_character_set_connection">
    <code class="literal">
     character_set_connection
    </code>
   </a>
   and
   <a class="link" href="server-system-variables.html#sysvar_collation_connection">
    <code class="literal">
     collation_connection
    </code>
   </a>
   system
      variables. (These variables commonly are set with
   <a class="link" href="set-names.html" title="15.7.6.3 SET NAMES Statement">
    <code class="literal">
     SET NAMES
    </code>
   </a>
   . For information about
      connection character sets, see
   <a class="xref" href="charset-connection.html" title="12.4 Connection Character Sets and Collations">
    Section 12.4, “Connection Character Sets and Collations”
   </a>
   .)
  </p>
  <p>
   This means that such a conversion results in a character
      (nonbinary) string (a
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     CHAR
    </code>
   </a>
   ,
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   , or
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     LONGTEXT
    </code>
   </a>
   value), except in the case
      that the connection character set is set to
   <code class="literal">
    binary
   </code>
   . In that case, the conversion result is
      a binary string (a
   <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
    <code class="literal">
     BINARY
    </code>
   </a>
   ,
   <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
    <code class="literal">
     VARBINARY
    </code>
   </a>
   , or
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     LONGBLOB
    </code>
   </a>
   value).
  </p>
  <p>
   For integer expressions, the preceding remarks about expression
   <span class="emphasis">
    <em>
     evaluation
    </em>
   </span>
   apply somewhat differently for
      expression
   <span class="emphasis">
    <em>
     assignment
    </em>
   </span>
   ; for example, in a
      statement such as this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa49289651"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token keyword">SELECT</span> <em class="replaceable">integer_expr</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In this case, the table in the column resulting from the
      expression has type
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INT
    </code>
   </a>
   or
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     BIGINT
    </code>
   </a>
   depending on the length of
      the integer expression. If the maximum length of the expression
      does not fit in an
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INT
    </code>
   </a>
   ,
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     BIGINT
    </code>
   </a>
   is used instead. The length
      is taken from the
   <code class="literal">
    max_length
   </code>
   value of the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   result set metadata (see
   <a class="ulink" href="/doc/c-api/8.4/en/c-api-data-structures.html" target="_top">
    C API Basic Data Structures
   </a>
   ). This means that you can
      force a
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     BIGINT
    </code>
   </a>
   rather than
   <a class="link" href="integer-types.html" title="13.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT">
    <code class="literal">
     INT
    </code>
   </a>
   by use of a sufficiently long
      expression:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa36209382"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token keyword">SELECT</span> <span class="token number">000000000000000000000</span><span class="token punctuation">;</span></code></pre>
  </div>
 </div>
 <br/>
</div>
