<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="cast-functions">
      </a>
      14.10 Cast Functions and Operators
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045201038160">
  </a>
  <a class="indexterm" name="idm46045201037120">
  </a>
  <a class="indexterm" name="idm46045201036048">
  </a>
  <a class="indexterm" name="idm46045201034560">
  </a>
  <a class="indexterm" name="idm46045201033488">
  </a>
  <div class="table">
   <a name="idm46045201031184">
   </a>
   <p class="title">
    <b>
     Table 14.15 Cast Functions and Operators
    </b>
   </p>
   <div class="table-contents">
    <table frame="box" rules="all" summary="A reference that lists cast functions and operators.">
     <colgroup>
      <col style="width: 25%"/>
      <col style="width: 62%"/>
      <col style="width: 12%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Name
       </th>
       <th>
        Description
       </th>
       <th>
        Deprecated
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <th scope="row">
        <a class="link" href="cast-functions.html#operator_binary">
         <code class="literal">
          BINARY
         </code>
        </a>
       </th>
       <td>
        Cast a string to a binary string
       </td>
       <td>
        Yes
       </td>
      </tr>
      <tr>
       <th scope="row">
        <a class="link" href="cast-functions.html#function_cast">
         <code class="literal">
          CAST()
         </code>
        </a>
       </th>
       <td>
        Cast a value as a certain type
       </td>
       <td>
       </td>
      </tr>
      <tr>
       <th scope="row">
        <a class="link" href="cast-functions.html#function_convert">
         <code class="literal">
          CONVERT()
         </code>
        </a>
       </th>
       <td>
        Cast a value as a certain type
       </td>
       <td>
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   Cast functions and operators enable conversion of values from one
      data type to another.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="cast-functions.html#cast-function-descriptions" title="Cast Function and Operator Descriptions">
       Cast Function and Operator Descriptions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="cast-functions.html#cast-character-set-conversions" title="Character Set Conversions">
       Character Set Conversions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="cast-functions.html#cast-string-comparisons" title="Character Set Conversions for String Comparisons">
       Character Set Conversions for String Comparisons
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="cast-functions.html#cast-spatial-types" title="Cast Operations on Spatial Types">
       Cast Operations on Spatial Types
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="cast-functions.html#cast-other-uses" title="Other Uses for Cast Operations">
       Other Uses for Cast Operations
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="cast-function-descriptions">
       </a>
       Cast Function and Operator Descriptions
      </h3>
     </div>
    </div>
   </div>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <a name="operator_binary">
      </a>
      <p>
       <a class="link" href="cast-functions.html#operator_binary">
        <code class="literal">
         BINARY
        </code>
       </a>
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
      </p>
      <a class="indexterm" name="idm46045200999280">
      </a>
      <p>
       The
       <a class="link" href="cast-functions.html#operator_binary">
        <code class="literal">
         BINARY
        </code>
       </a>
       operator converts
            the expression to a binary string (a string that has the
       <code class="literal">
        binary
       </code>
       character set and
       <code class="literal">
        binary
       </code>
       collation). A common use for
       <a class="link" href="cast-functions.html#operator_binary">
        <code class="literal">
         BINARY
        </code>
       </a>
       is to force a character
            string comparison to be done byte by byte using numeric byte
            values rather than character by character. The
       <a class="link" href="cast-functions.html#operator_binary">
        <code class="literal">
         BINARY
        </code>
       </a>
       operator also causes
            trailing spaces in comparisons to be significant. For
            information about the differences between the
       <code class="literal">
        binary
       </code>
       collation of the
       <code class="literal">
        binary
       </code>
       character set and the
       <code class="literal">
        _bin
       </code>
       collations of nonbinary character
            sets, see
       <a class="xref" href="charset-binary-collations.html" title="12.8.5 The binary Collation Compared to _bin Collations">
        Section 12.8.5, “The binary Collation Compared to _bin Collations”
       </a>
       .
      </p>
      <p>
       The
       <code class="literal">
        BINARY
       </code>
       operator is deprecated; you
            should expect its removal in a future version of MySQL. Use
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST(... AS BINARY)
        </code>
       </a>
       instead.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95194992"><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">SET</span> <span class="token keyword">NAMES</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_general_ci<span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> OK
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token string">'a'</span> <span class="token operator">=</span> <span class="token string">'A'</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 datatype">BINARY</span> <span class="token string">'a'</span> <span class="token operator">=</span> <span class="token string">'A'</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 string">'a'</span> <span class="token operator">=</span> <span class="token string">'a '</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 datatype">BINARY</span> <span class="token string">'a'</span> <span class="token operator">=</span> <span class="token string">'a '</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">0</span></code></pre>
      </div>
      <p>
       In a comparison,
       <a class="link" href="cast-functions.html#operator_binary">
        <code class="literal">
         BINARY
        </code>
       </a>
       affects
            the entire operation; it can be given before either operand
            with the same result.
      </p>
      <p>
       To convert a string expression to a binary string, these
            constructs are equivalent:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14107900"><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 function">CONVERT</span><span class="token punctuation">(</span><em class="replaceable">expr</em> <span class="token keyword">USING</span> <span class="token datatype">BINARY</span><span class="token punctuation">)</span>
<span class="token function">CAST</span><span class="token punctuation">(</span><em class="replaceable">expr</em> <span class="token keyword">AS</span> <span class="token datatype">BINARY</span><span class="token punctuation">)</span>
<span class="token datatype">BINARY</span> <em class="replaceable">expr</em></code></pre>
      </div>
      <p>
       If a value is a string literal, it can be designated as a
            binary string without converting it by using the
       <code class="literal">
        _binary
       </code>
       character set introducer:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84804834"><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">'a'</span> <span class="token operator">=</span> <span class="token string">'A'</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> _binary <span class="token string">'a'</span> <span class="token operator">=</span> <span class="token string">'A'</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">0</span></code></pre>
      </div>
      <p>
       For information about introducers, see
       <a class="xref" href="charset-introducer.html" title="12.3.8 Character Set Introducers">
        Section 12.3.8, “Character Set Introducers”
       </a>
       .
      </p>
      <p>
       The
       <a class="link" href="cast-functions.html#operator_binary">
        <code class="literal">
         BINARY
        </code>
       </a>
       operator in
            expressions differs in effect from the
       <code class="literal">
        BINARY
       </code>
       attribute in character column
            definitions. For a character column defined with the
       <code class="literal">
        BINARY
       </code>
       attribute, MySQL assigns the table
            default character set and the binary
            (
       <code class="literal">
        _bin
       </code>
       ) collation of that character set.
            Every nonbinary character set has a
       <code class="literal">
        _bin
       </code>
       collation. For example, if the table default character set
            is
       <code class="literal">
        utf8mb4
       </code>
       , these two column definitions
            are equivalent:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60353153"><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 datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token datatype">BINARY</span>
<span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_bin</code></pre>
      </div>
      <p>
       The use of
       <code class="literal">
        CHARACTER SET binary
       </code>
       in the
            definition of 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">
         TEXT
        </code>
       </a>
       column causes the column
            to be treated as the corresponding binary string data type.
            For example, the following pairs of definitions are
            equivalent:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9983187"><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 datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token datatype">binary</span>
<span class="token datatype">BINARY</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>

<span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token datatype">binary</span>
<span class="token datatype">VARBINARY</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>

<span class="token datatype">TEXT</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token datatype">binary</span>
<span class="token datatype">BLOB</span></code></pre>
      </div>
      <p>
       If
       <a class="link" href="cast-functions.html#operator_binary">
        <code class="literal">
         BINARY
        </code>
       </a>
       is invoked from
            within the
       <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
        <span class="command">
         <strong>
          mysql
         </strong>
        </span>
       </a>
       client, binary strings
            display using hexadecimal notation, depending on the value
            of the
       <a class="link" href="mysql-command-options.html#option_mysql_binary-as-hex">
        <code class="option">
         --binary-as-hex
        </code>
       </a>
       . For
            more information about that option, see
       <a class="xref" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
        Section 6.5.1, “mysql — The MySQL Command-Line Client”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <a name="function_cast">
      </a>
      <p>
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST(
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         AS
         <em class="replaceable">
          <code>
           type
          </code>
         </em>
         [ARRAY])
        </code>
       </a>
      </p>
      <p>
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST(
         <em class="replaceable">
          <code>
           timestamp_value
          </code>
         </em>
         AT TIME ZONE
         <em class="replaceable">
          <code>
           timezone_specifier
          </code>
         </em>
         AS
            DATETIME[(
         <em class="replaceable">
          <code>
           precision
          </code>
         </em>
         )])
        </code>
       </a>
      </p>
      <p>
       <em class="replaceable">
        <code>
         timezone_specifier
        </code>
       </em>
       : [INTERVAL]
            '+00:00' | 'UTC'
      </p>
      <a class="indexterm" name="idm46045200944144">
      </a>
      <p>
       With
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST(
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         AS
         <em class="replaceable">
          <code>
           type
          </code>
         </em>
        </code>
       </a>
       syntax, the
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST()
        </code>
       </a>
       function takes an
            expression of any type and produces a result value of the
            specified type. This operation may also be expressed as
       <a class="link" href="cast-functions.html#function_convert">
        <code class="literal">
         CONVERT(
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         ,
         <em class="replaceable">
          <code>
           type
          </code>
         </em>
         )
        </code>
       </a>
       , which is
            equivalent. If
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       is
       <code class="literal">
        NULL
       </code>
       ,
       <code class="literal">
        CAST()
       </code>
       returns
       <code class="literal">
        NULL
       </code>
       .
      </p>
      <p>
       These
       <em class="replaceable">
        <code>
         type
        </code>
       </em>
       values are permitted:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           BINARY[(
           <em class="replaceable">
            <code>
             N
            </code>
           </em>
           )]
          </code>
         </p>
         <p>
          Produces a string with the
          <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
           <code class="literal">
            VARBINARY
           </code>
          </a>
          data type,
                except that when the expression
          <em class="replaceable">
           <code>
            expr
           </code>
          </em>
          is empty (zero length),
                the result type is
          <code class="literal">
           BINARY(0)
          </code>
          . If the
                optional length
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          is given,
          <code class="literal">
           BINARY(
           <em class="replaceable">
            <code>
             N
            </code>
           </em>
           )
          </code>
          causes the cast to use no more than
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          bytes of the argument.
                Values shorter than
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          bytes
                are padded with
          <code class="literal">
           0x00
          </code>
          bytes to a
                length of
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          . If the optional
                length
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          is not given, MySQL
                calculates the maximum length from the expression. If
                the supplied or calculated length is greater than an
                internal threshold, the result type is
          <code class="literal">
           BLOB
          </code>
          . If the length is still too
                long, the result type is
          <code class="literal">
           LONGBLOB
          </code>
          .
         </p>
         <p>
          For a description of how casting to
          <code class="literal">
           BINARY
          </code>
          affects comparisons, see
          <a class="xref" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
           Section 13.3.3, “The BINARY and VARBINARY Types”
          </a>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           CHAR[(
           <em class="replaceable">
            <code>
             N
            </code>
           </em>
           )]
                [
           <em class="replaceable">
            <code>
             charset_info
            </code>
           </em>
           ]
          </code>
         </p>
         <p>
          Produces a string with the
          <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
           <code class="literal">
            VARCHAR
           </code>
          </a>
          data type, unless
                the expression
          <em class="replaceable">
           <code>
            expr
           </code>
          </em>
          is empty
                (zero length), in which case the result type is
          <code class="literal">
           CHAR(0)
          </code>
          . If the optional length
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          is given,
          <code class="literal">
           CHAR(
           <em class="replaceable">
            <code>
             N
            </code>
           </em>
           )
          </code>
          causes the cast to use no more than
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          characters of the argument.
                No padding occurs for values shorter than
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          characters. If the optional
                length
          <em class="replaceable">
           <code>
            N
           </code>
          </em>
          is not given, MySQL
                calculates the maximum length from the expression. If
                the supplied or calculated length is greater than an
                internal threshold, the result type is
          <code class="literal">
           TEXT
          </code>
          . If the length is still too
                long, the result type is
          <code class="literal">
           LONGTEXT
          </code>
          .
         </p>
         <p>
          With no
          <em class="replaceable">
           <code>
            charset_info
           </code>
          </em>
          clause,
          <code class="literal">
           CHAR
          </code>
          produces a string with the
                default character set. To specify the character set
                explicitly, these
          <em class="replaceable">
           <code>
            charset_info
           </code>
          </em>
          values are
                permitted:
         </p>
         <div class="itemizedlist">
          <ul class="itemizedlist" style="list-style-type: square; ">
           <li class="listitem">
            <p>
             <code class="literal">
              CHARACTER SET
              <em class="replaceable">
               <code>
                charset_name
               </code>
              </em>
             </code>
             :
                    Produces a string with the given character set.
            </p>
           </li>
           <li class="listitem">
            <p>
             <code class="literal">
              ASCII
             </code>
             : Shorthand for
             <code class="literal">
              CHARACTER SET latin1
             </code>
             .
            </p>
           </li>
           <li class="listitem">
            <p>
             <code class="literal">
              UNICODE
             </code>
             : Shorthand for
             <code class="literal">
              CHARACTER SET ucs2
             </code>
             .
            </p>
           </li>
          </ul>
         </div>
         <p>
          In all cases, the string has the character set default
                collation.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           DATE
          </code>
         </p>
         <p>
          Produces a
          <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
           <code class="literal">
            DATE
           </code>
          </a>
          value.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           DATETIME[(
           <em class="replaceable">
            <code>
             M
            </code>
           </em>
           )]
          </code>
         </p>
         <p>
          Produces a
          <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
           <code class="literal">
            DATETIME
           </code>
          </a>
          value. If the optional
          <em class="replaceable">
           <code>
            M
           </code>
          </em>
          value is given, it specifies the fractional seconds
                precision.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           DECIMAL[(
           <em class="replaceable">
            <code>
             M
            </code>
           </em>
           [,
           <em class="replaceable">
            <code>
             D
            </code>
           </em>
           ])]
          </code>
         </p>
         <p>
          Produces 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>
          value.
                If the optional
          <em class="replaceable">
           <code>
            M
           </code>
          </em>
          and
          <em class="replaceable">
           <code>
            D
           </code>
          </em>
          values are given, they
                specify the maximum number of digits (the precision) and
                the number of digits following the decimal point (the
                scale). If
          <em class="replaceable">
           <code>
            D
           </code>
          </em>
          is omitted, 0 is
                assumed. If
          <em class="replaceable">
           <code>
            M
           </code>
          </em>
          is omitted, 10
                is assumed.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           DOUBLE
          </code>
         </p>
         <p>
          Produces 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>
          result.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           FLOAT[(
           <em class="replaceable">
            <code>
             p
            </code>
           </em>
           )]
          </code>
         </p>
         <p>
          If the precision
          <em class="replaceable">
           <code>
            p
           </code>
          </em>
          is not
                specified, produces a result of type
          <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>
          . If
          <em class="replaceable">
           <code>
            p
           </code>
          </em>
          is provided and 0 &lt;=
                &lt;
          <em class="replaceable">
           <code>
            p
           </code>
          </em>
          &lt;= 24, the result
                is of type
          <code class="literal">
           FLOAT
          </code>
          . If 25 &lt;=
          <em class="replaceable">
           <code>
            p
           </code>
          </em>
          &lt;= 53, the result is of
                type
          <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>
          . If
          <em class="replaceable">
           <code>
            p
           </code>
          </em>
          &lt; 0 or
          <em class="replaceable">
           <code>
            p
           </code>
          </em>
          &gt; 53, an error is
                returned.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           JSON
          </code>
         </p>
         <p>
          Produces a
          <a class="link" href="json.html" title="13.5 The JSON Data Type">
           <code class="literal">
            JSON
           </code>
          </a>
          value.
                For details on the rules for conversion of values
                between
          <a class="link" href="json.html" title="13.5 The JSON Data Type">
           <code class="literal">
            JSON
           </code>
          </a>
          and other
                types, see
          <a class="xref" href="json.html#json-comparison" title="Comparison and Ordering of JSON Values">
           Comparison and Ordering of JSON Values
          </a>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           NCHAR[(
           <em class="replaceable">
            <code>
             N
            </code>
           </em>
           )]
          </code>
         </p>
         <p>
          Like
          <code class="literal">
           CHAR
          </code>
          , but produces a string with
                the national character set. See
          <a class="xref" href="charset-national.html" title="12.3.7 The National Character Set">
           Section 12.3.7, “The National Character Set”
          </a>
          .
         </p>
         <p>
          Unlike
          <code class="literal">
           CHAR
          </code>
          ,
          <code class="literal">
           NCHAR
          </code>
          does not permit trailing character set information to be
                specified.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           REAL
          </code>
         </p>
         <p>
          Produces a result of type
          <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
           <code class="literal">
            REAL
           </code>
          </a>
          . This is actually
          <code class="literal">
           FLOAT
          </code>
          if the
          <a class="link" href="sql-mode.html#sqlmode_real_as_float">
           <code class="literal">
            REAL_AS_FLOAT
           </code>
          </a>
          SQL mode
                is enabled; otherwise the result is of type
          <code class="literal">
           DOUBLE
          </code>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           SIGNED [INTEGER]
          </code>
         </p>
         <p>
          Produces a signed
          <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>
          value.
         </p>
        </li>
        <li class="listitem">
         <p>
          <em class="replaceable">
           <code>
            spatial_type
           </code>
          </em>
         </p>
         <p>
          <a class="link" href="cast-functions.html#function_cast">
           <code class="literal">
            CAST()
           </code>
          </a>
          and
          <a class="link" href="cast-functions.html#function_convert">
           <code class="literal">
            CONVERT()
           </code>
          </a>
          support casting
                geometry values from one spatial type to another, for
                certain combinations of spatial types. For details, see
          <a class="xref" href="cast-functions.html#cast-spatial-types" title="Cast Operations on Spatial Types">
           Cast Operations on Spatial Types
          </a>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           TIME[(
           <em class="replaceable">
            <code>
             M
            </code>
           </em>
           )]
          </code>
         </p>
         <p>
          Produces a
          <a class="link" href="time.html" title="13.2.3 The TIME Type">
           <code class="literal">
            TIME
           </code>
          </a>
          value. If
                the optional
          <em class="replaceable">
           <code>
            M
           </code>
          </em>
          value is
                given, it specifies the fractional seconds precision.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           UNSIGNED [INTEGER]
          </code>
         </p>
         <p>
          Produces an unsigned
          <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>
          value.
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           YEAR
          </code>
         </p>
         <p>
          Produces a
          <a class="link" href="year.html" title="13.2.4 The YEAR Type">
           <code class="literal">
            YEAR
           </code>
          </a>
          value.
                These rules govern conversion to
          <code class="literal">
           YEAR
          </code>
          as follows:
         </p>
         <div class="itemizedlist">
          <ul class="itemizedlist" style="list-style-type: square; ">
           <li class="listitem">
            <p>
             For a four-digit number in the range 1901-2155
                    inclusive, or for a string which can be interpreted
                    as a four-digit number in this range, return the
                    corresponding
             <code class="literal">
              YEAR
             </code>
             value.
            </p>
           </li>
           <li class="listitem">
            <p>
             For a number consisting of one or two digits, or for
                    a string which can be interpreted as such a number,
                    return a
             <code class="literal">
              YEAR
             </code>
             value as follows:
            </p>
            <div class="itemizedlist">
             <ul class="itemizedlist" style="list-style-type: disc; ">
              <li class="listitem">
               <p>
                If the number is in the range 1-69 inclusive,
                        add 2000 and return the sum.
               </p>
              </li>
              <li class="listitem">
               <p>
                If the number is in the range 70-99 inclusive,
                        add 1900 and return the sum.
               </p>
              </li>
             </ul>
            </div>
           </li>
           <li class="listitem">
            <p>
             For a string which evaluates to 0, return 2000.
            </p>
           </li>
           <li class="listitem">
            <p>
             For the number 0, return 0.
            </p>
           </li>
           <li class="listitem">
            <p>
             For a
             <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
              <code class="literal">
               DATE
              </code>
             </a>
             ,
             <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
              <code class="literal">
               DATETIME
              </code>
             </a>
             , or
             <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
              <code class="literal">
               TIMESTAMP
              </code>
             </a>
             value,
                    return the
             <code class="literal">
              YEAR
             </code>
             portion of the
                    value. For a
             <a class="link" href="time.html" title="13.2.3 The TIME Type">
              <code class="literal">
               TIME
              </code>
             </a>
             value, return the current year.
            </p>
            <p>
             If you do not specify the type of a
             <code class="literal">
              TIME
             </code>
             argument, you may get a
                    different result from what you expect, as shown
                    here:
            </p>
            <div class="copytoclipboard-wrapper">
             <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96234471"><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">"11:35:00"</span> <span class="token keyword">AS</span> <span class="token datatype">YEAR</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token datatype">TIME</span> <span class="token string">"11:35:00"</span> <span class="token keyword">AS</span> <span class="token datatype">YEAR</span><span class="token punctuation">)</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 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 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> CAST("11:35:00" AS YEAR) <span class="token punctuation">|</span> CAST(TIME "11:35:00" AS YEAR) <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 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 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>                     2011 <span class="token punctuation">|</span>                          2021 <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 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 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></code></pre>
            </div>
           </li>
           <li class="listitem">
            <p>
             If the argument is of type
             <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>
             ,
             <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>
             ,
             <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
             <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
              <code class="literal">
               REAL
              </code>
             </a>
             , round the value
                    to the nearest integer, then attempt to cast the
                    value to
             <code class="literal">
              YEAR
             </code>
             using the rules for
                    integer values, as shown here:
            </p>
            <div class="copytoclipboard-wrapper">
             <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa64145784"><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 number">1944.35</span> <span class="token keyword">AS</span> <span class="token datatype">YEAR</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token number">1944.50</span> <span class="token keyword">AS</span> <span class="token datatype">YEAR</span><span class="token punctuation">)</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 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> CAST(1944.35 AS YEAR) <span class="token punctuation">|</span> CAST(1944.50 AS YEAR) <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 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>                  1944 <span class="token punctuation">|</span>                  1945 <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 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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token number">66.35</span> <span class="token keyword">AS</span> <span class="token datatype">YEAR</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token number">66.50</span> <span class="token keyword">AS</span> <span class="token datatype">YEAR</span><span class="token punctuation">)</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 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> CAST(66.35 AS YEAR) <span class="token punctuation">|</span> CAST(66.50 AS YEAR) <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 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>                2066 <span class="token punctuation">|</span>                2067 <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 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></code></pre>
            </div>
           </li>
           <li class="listitem">
            <p>
             An argument of type
             <a class="link" href="spatial-type-overview.html" title="13.4.1 Spatial Data Types">
              <code class="literal">
               GEOMETRY
              </code>
             </a>
             cannot be
                    converted to
             <a class="link" href="year.html" title="13.2.4 The YEAR Type">
              <code class="literal">
               YEAR
              </code>
             </a>
             .
            </p>
           </li>
           <li class="listitem">
            <p>
             For a value that cannot be successfully converted to
             <code class="literal">
              YEAR
             </code>
             , return
             <code class="literal">
              NULL
             </code>
             .
            </p>
           </li>
          </ul>
         </div>
         <p>
          A string value containing non-numeric characters which
                must be truncated prior to conversion raises a warning,
                as shown here:
         </p>
         <div class="copytoclipboard-wrapper">
          <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53644799"><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">"1979aaa"</span> <span class="token keyword">AS</span> <span class="token datatype">YEAR</span><span class="token punctuation">)</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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> CAST("1979aaa" AS YEAR) <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 class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                    1979 <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 class="token punctuation">+</span></span>
<span class="token output">1 row in set, 1 warning (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</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 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 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> Level   <span class="token punctuation">|</span> Code <span class="token punctuation">|</span> Message                                   <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 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 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> Warning <span class="token punctuation">|</span> 1292 <span class="token punctuation">|</span> Truncated incorrect YEAR value: '1979aaa' <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 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 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></code></pre>
         </div>
        </li>
       </ul>
      </div>
      <p>
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       allows the use of an
            additional
       <code class="literal">
        ARRAY
       </code>
       keyword for creating a
            multi-valued index on a
       <a class="link" href="json.html" title="13.5 The JSON Data Type">
        <code class="literal">
         JSON
        </code>
       </a>
       array as part of
       <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
        <code class="literal">
         CREATE
            INDEX
        </code>
       </a>
       ,
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE
            TABLE
        </code>
       </a>
       , and
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER
            TABLE
        </code>
       </a>
       statements.
       <code class="literal">
        ARRAY
       </code>
       is not
            supported except when used to create a multi-valued index in
            one of these statements, in which case it is required. The
            column being indexed must be a column of type
       <code class="literal">
        JSON
       </code>
       . With
       <code class="literal">
        ARRAY
       </code>
       , the
       <em class="replaceable">
        <code>
         type
        </code>
       </em>
       following the
       <code class="literal">
        AS
       </code>
       keyword may specify any of the types
            supported by
       <code class="literal">
        CAST()
       </code>
       , with the exceptions
            of
       <code class="literal">
        BINARY
       </code>
       ,
       <code class="literal">
        JSON
       </code>
       , and
       <code class="literal">
        YEAR
       </code>
       . For syntax information and
            examples, as well as other relevant information, see
       <a class="xref" href="create-index.html#create-index-multi-valued" title="Multi-Valued Indexes">
        Multi-Valued Indexes
       </a>
       .
      </p>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        <a class="link" href="cast-functions.html#function_convert">
         <code class="literal">
          CONVERT()
         </code>
        </a>
        , unlike
        <a class="link" href="cast-functions.html#function_cast">
         <code class="literal">
          CAST()
         </code>
        </a>
        , does
        <span class="emphasis">
         <em>
          not
         </em>
        </span>
        support multi-valued index
              creation or the
        <code class="literal">
         ARRAY
        </code>
        keyword.
       </p>
      </div>
      <p>
       <code class="literal">
        CAST()
       </code>
       supports retrieval of a
       <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
        <code class="literal">
         TIMESTAMP
        </code>
       </a>
       value as being in
            UTC, using the
       <code class="literal">
        AT TIMEZONE
       </code>
       operator. The
            only supported time zone is UTC; this can be specified as
            either of
       <code class="literal">
        '+00:00'
       </code>
       or
       <code class="literal">
        'UTC'
       </code>
       . The only return type supported by
            this syntax is
       <code class="literal">
        DATETIME
       </code>
       , with an optional
            precision specifier in the range of 0 to 6, inclusive.
      </p>
      <p>
       <code class="literal">
        TIMESTAMP
       </code>
       values that use timezone
            offsets are also supported.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20477201"><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 variable">@@system_time_zone</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>
<span class="token output"><span class="token punctuation">|</span> @@system_time_zone <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>
<span class="token output"><span class="token punctuation">|</span> EDT                <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>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tz <span class="token punctuation">(</span>c <span class="token datatype">TIMESTAMP</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.41 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tz <span class="token keyword">VALUES</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token keyword">CURRENT_TIMESTAMP</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 string">'2020-07-28 14:50:15+1:00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.08 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> tz<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>
<span class="token output"><span class="token punctuation">|</span> c                   <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>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>07<span class="token punctuation">-</span>28 09:22:41 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>07<span class="token punctuation">-</span>28 09:50:15 <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>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span>c <span class="token keyword">AT</span> <span class="token datatype">TIME</span> ZONE <span class="token string">'+00:00'</span> <span class="token keyword">AS</span> <span class="token datatype">DATETIME</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> u <span class="token keyword">FROM</span> tz<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>
<span class="token output"><span class="token punctuation">|</span> u                   <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>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>07<span class="token punctuation">-</span>28 13:22:41 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>07<span class="token punctuation">-</span>28 13:50:15 <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>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span>c <span class="token keyword">AT</span> <span class="token datatype">TIME</span> ZONE <span class="token string">'UTC'</span> <span class="token keyword">AS</span> <span class="token datatype">DATETIME</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> u <span class="token keyword">FROM</span> tz<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> u                      <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> 2020<span class="token punctuation">-</span>07<span class="token punctuation">-</span>28 13:22:41.00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2020<span class="token punctuation">-</span>07<span class="token punctuation">-</span>28 13:50:15.00 <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">2 rows in set (0.00 sec)</span></code></pre>
      </div>
      <p>
       If you use
       <code class="literal">
        'UTC'
       </code>
       as the time zone
            specifier with this form of
       <code class="literal">
        CAST()
       </code>
       , and
            the server raises an error such as
       <span class="errortext">
        Unknown or
            incorrect time zone: 'UTC'
       </span>
       , you may need to
            install the MySQL time zone tables (see
       <a class="xref" href="time-zone-support.html#time-zone-installation" title="Populating the Time Zone Tables">
        Populating the Time Zone Tables
       </a>
       ).
      </p>
      <p>
       <code class="literal">
        AT TIME ZONE
       </code>
       does not support the
       <code class="literal">
        ARRAY
       </code>
       keyword, and is not supported by
            the
       <a class="link" href="cast-functions.html#function_convert">
        <code class="literal">
         CONVERT()
        </code>
       </a>
       function.
      </p>
     </li>
     <li class="listitem">
      <a name="function_convert">
      </a>
      <p>
       <a class="link" href="cast-functions.html#function_convert">
        <code class="literal">
         CONVERT(
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         USING
         <em class="replaceable">
          <code>
           transcoding_name
          </code>
         </em>
         )
        </code>
       </a>
      </p>
      <p>
       <a class="link" href="cast-functions.html#function_convert">
        <code class="literal">
         CONVERT(
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         ,
         <em class="replaceable">
          <code>
           type
          </code>
         </em>
         )
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045200754928">
      </a>
      <p>
       <a class="link" href="cast-functions.html#function_convert">
        <code class="literal">
         CONVERT(
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         USING
         <em class="replaceable">
          <code>
           transcoding_name
          </code>
         </em>
         )
        </code>
       </a>
       is standard SQL syntax. The non-
       <code class="literal">
        USING
       </code>
       form of
       <a class="link" href="cast-functions.html#function_convert">
        <code class="literal">
         CONVERT()
        </code>
       </a>
       is ODBC
            syntax. Regardless of the syntax used, the function returns
       <code class="literal">
        NULL
       </code>
       if
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       is
       <code class="literal">
        NULL
       </code>
       .
      </p>
      <p>
       <a class="link" href="cast-functions.html#function_convert">
        <code class="literal">
         CONVERT(
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         USING
         <em class="replaceable">
          <code>
           transcoding_name
          </code>
         </em>
         )
        </code>
       </a>
       converts data between different character sets. In MySQL,
            transcoding names are the same as the corresponding
            character set names. For example, this statement converts
            the string
       <code class="literal">
        'abc'
       </code>
       in the default character
            set to the corresponding string in the
       <code class="literal">
        utf8mb4
       </code>
       character set:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa25212896"><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 function">CONVERT</span><span class="token punctuation">(</span><span class="token string">'abc'</span> <span class="token keyword">USING</span> utf8mb4<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       <a class="link" href="cast-functions.html#function_convert">
        <code class="literal">
         CONVERT(
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         ,
         <em class="replaceable">
          <code>
           type
          </code>
         </em>
         )
        </code>
       </a>
       syntax (without
       <code class="literal">
        USING
       </code>
       ) takes an expression and a
       <em class="replaceable">
        <code>
         type
        </code>
       </em>
       value specifying a result
            type, and produces a result value of the specified type.
            This operation may also be expressed as
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST(
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         AS
         <em class="replaceable">
          <code>
           type
          </code>
         </em>
         )
        </code>
       </a>
       , which is
            equivalent. For more information, see the description of
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST()
        </code>
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="cast-character-set-conversions">
       </a>
       Character Set Conversions
      </h3>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="cast-functions.html#function_convert">
     <code class="literal">
      CONVERT()
     </code>
    </a>
    with a
    <code class="literal">
     USING
    </code>
    clause converts data between character
        sets:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa40096737"><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 function">CONVERT</span><span class="token punctuation">(</span><em class="replaceable">expr</em> <span class="token keyword">USING</span> <em class="replaceable">transcoding_name</em><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    In MySQL, transcoding names are the same as the corresponding
        character set names.
   </p>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa6303933"><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 function">CONVERT</span><span class="token punctuation">(</span><span class="token string">'test'</span> <span class="token keyword">USING</span> utf8mb4<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">CONVERT</span><span class="token punctuation">(</span>_latin1<span class="token string">'Müller'</span> <span class="token keyword">USING</span> utf8mb4<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> utf8mb4_table <span class="token punctuation">(</span>utf8mb4_column<span class="token punctuation">)</span>
    <span class="token keyword">SELECT</span> <span class="token function">CONVERT</span><span class="token punctuation">(</span>latin1_column <span class="token keyword">USING</span> utf8mb4<span class="token punctuation">)</span> <span class="token keyword">FROM</span> latin1_table<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To convert strings between character sets, you can also use
    <a class="link" href="cast-functions.html#function_convert">
     <code class="literal">
      CONVERT(
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      ,
      <em class="replaceable">
       <code>
        type
       </code>
      </em>
      )
     </code>
    </a>
    syntax (without
    <code class="literal">
     USING
    </code>
    ), or
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST(
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      AS
      <em class="replaceable">
       <code>
        type
       </code>
      </em>
      )
     </code>
    </a>
    , which is equivalent:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8663981"><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 function">CONVERT</span><span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">string</em></span><span class="token punctuation">,</span> <span class="token datatype">CHAR</span><span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">N</em><span class="token punctuation">)</span><span class="token punctuation">]</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">)</span>
<span class="token function">CAST</span><span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">string</em></span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">N</em><span class="token punctuation">)</span><span class="token punctuation">]</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">)</span></code></pre>
   </div>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78562723"><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 function">CONVERT</span><span class="token punctuation">(</span><span class="token string">'test'</span><span class="token punctuation">,</span> <span class="token datatype">CHAR</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'test'</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If you specify
    <code class="literal">
     CHARACTER SET
     <em class="replaceable">
      <code>
       charset_name
      </code>
     </em>
    </code>
    as just shown,
        the character set and collation of the result are
    <em class="replaceable">
     <code>
      charset_name
     </code>
    </em>
    and the default
        collation of
    <em class="replaceable">
     <code>
      charset_name
     </code>
    </em>
    . If you
        omit
    <code class="literal">
     CHARACTER SET
     <em class="replaceable">
      <code>
       charset_name
      </code>
     </em>
    </code>
    , the character
        set and collation of the result are defined 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 that determine the default connection character set
        and collation (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>
    A
    <code class="literal">
     COLLATE
    </code>
    clause is not permitted within a
    <a class="link" href="cast-functions.html#function_convert">
     <code class="literal">
      CONVERT()
     </code>
    </a>
    or
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    call, but you can apply it
        to the function result. For example, these are legal:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa18164038"><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 function">CONVERT</span><span class="token punctuation">(</span><span class="token string">'test'</span> <span class="token keyword">USING</span> utf8mb4<span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">CONVERT</span><span class="token punctuation">(</span><span class="token string">'test'</span><span class="token punctuation">,</span> <span class="token datatype">CHAR</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'test'</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    But these are illegal:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84668854"><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 function">CONVERT</span><span class="token punctuation">(</span><span class="token string">'test'</span> <span class="token keyword">USING</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">CONVERT</span><span class="token punctuation">(</span><span class="token string">'test'</span><span class="token punctuation">,</span> <span class="token datatype">CHAR</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'test'</span> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For string literals, another way to specify the character set is
        to use a character set introducer.
    <code class="literal">
     _latin1
    </code>
    and
    <code class="literal">
     _latin2
    </code>
    in the preceding example are
        instances of introducers. Unlike conversion functions such as
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    , or
    <a class="link" href="cast-functions.html#function_convert">
     <code class="literal">
      CONVERT()
     </code>
    </a>
    , which convert a string
        from one character set to another, an introducer designates a
        string literal as having a particular character set, with no
        conversion involved. For more information, see
    <a class="xref" href="charset-introducer.html" title="12.3.8 Character Set Introducers">
     Section 12.3.8, “Character Set Introducers”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="cast-string-comparisons">
       </a>
       Character Set Conversions for String Comparisons
      </h3>
     </div>
    </div>
   </div>
   <p>
    Normally, you cannot compare a
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      BLOB
     </code>
    </a>
    value or other binary string
        in case-insensitive fashion because binary strings use the
    <code class="literal">
     binary
    </code>
    character set, which has no collation
        with the concept of lettercase. To perform a case-insensitive
        comparison, first use the
    <a class="link" href="cast-functions.html#function_convert">
     <code class="literal">
      CONVERT()
     </code>
    </a>
    or
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    function to convert the
        value to a nonbinary string. Comparisons of the resulting string
        use its collation. For example, if the conversion result
        collation is not case-sensitive, a
    <a class="link" href="string-comparison-functions.html#operator_like">
     <code class="literal">
      LIKE
     </code>
    </a>
    operation is not
        case-sensitive. That is true for the following operation because
        the default
    <code class="literal">
     utf8mb4
    </code>
    collation
        (
    <code class="literal">
     utf8mb4_0900_ai_ci
    </code>
    ) is not case-sensitive:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa6271302"><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 string">'A'</span> <span class="token operator">LIKE</span> <span class="token function">CONVERT</span><span class="token punctuation">(</span><em class="replaceable">blob_col</em> <span class="token keyword">USING</span> utf8mb4<span class="token punctuation">)</span>
  <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To specify a particular collation for the converted string, use
        a
    <code class="literal">
     COLLATE
    </code>
    clause following the
    <a class="link" href="cast-functions.html#function_convert">
     <code class="literal">
      CONVERT()
     </code>
    </a>
    call:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15754603"><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 string">'A'</span> <span class="token operator">LIKE</span> <span class="token function">CONVERT</span><span class="token punctuation">(</span><em class="replaceable">blob_col</em> <span class="token keyword">USING</span> utf8mb4<span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> utf8mb4_unicode_ci
  <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To use a different character set, substitute its name for
    <code class="literal">
     utf8mb4
    </code>
    in the preceding statements (and
        similarly to use a different collation).
   </p>
   <p>
    <a class="link" href="cast-functions.html#function_convert">
     <code class="literal">
      CONVERT()
     </code>
    </a>
    and
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    can be used more generally
        for comparing strings represented in different character sets.
        For example, a comparison of these strings results in an error
        because they have different character sets:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11484303"><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">SET</span> <span class="token variable">@s1</span> <span class="token operator">=</span> _latin1 <span class="token string">'abc'</span><span class="token punctuation">,</span> <span class="token variable">@s2</span> <span class="token operator">=</span> _latin2 <span class="token string">'abc'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@s1</span> <span class="token operator">=</span> <span class="token variable">@s2</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1267 (HY000)<span class="token punctuation">:</span> Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (latin2_general_ci,IMPLICIT) for operation '='</span></code></pre>
   </div>
   <p>
    Converting one of the strings to a character set compatible with
        the other enables the comparison to occur without error:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa54613635"><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 variable">@s1</span> <span class="token operator">=</span> <span class="token function">CONVERT</span><span class="token punctuation">(</span><span class="token variable">@s2</span> <span class="token keyword">USING</span> latin1<span class="token punctuation">)</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 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> @s1 = CONVERT(@s2 USING latin1) <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 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></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 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></code></pre>
   </div>
   <p>
    Character set conversion is also useful preceding lettercase
        conversion of binary strings.
    <a class="link" href="string-functions.html#function_lower">
     <code class="literal">
      LOWER()
     </code>
    </a>
    and
    <a class="link" href="string-functions.html#function_upper">
     <code class="literal">
      UPPER()
     </code>
    </a>
    are ineffective when
        applied directly to binary strings because the concept of
        lettercase does not apply. To perform lettercase conversion of a
        binary string, first convert it to a nonbinary string using a
        character set appropriate for the data stored in the string:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa94992452"><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">SET</span> <span class="token variable">@str</span> <span class="token operator">=</span> <span class="token datatype">BINARY</span> <span class="token string">'New York'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">LOWER</span><span class="token punctuation">(</span><span class="token variable">@str</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">LOWER</span><span class="token punctuation">(</span><span class="token function">CONVERT</span><span class="token punctuation">(</span><span class="token variable">@str</span> <span class="token keyword">USING</span> utf8mb4<span class="token punctuation">)</span><span class="token punctuation">)</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 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> LOWER(@str) <span class="token punctuation">|</span> LOWER(CONVERT(@str USING utf8mb4)) <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 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> New York    <span class="token punctuation">|</span> new york                           <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 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></code></pre>
   </div>
   <p>
    Be aware that if you apply
    <a class="link" href="cast-functions.html#operator_binary">
     <code class="literal">
      BINARY
     </code>
    </a>
    ,
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    , or
    <a class="link" href="cast-functions.html#function_convert">
     <code class="literal">
      CONVERT()
     </code>
    </a>
    to an indexed column,
        MySQL may not be able to use the index efficiently.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="cast-spatial-types">
       </a>
       Cast Operations on Spatial Types
      </h3>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    and
    <a class="link" href="cast-functions.html#function_convert">
     <code class="literal">
      CONVERT()
     </code>
    </a>
    support casting
        geometry values from one spatial type to another, for certain
        combinations of spatial types. The following list shows the
        permitted type combinations, where
    <span class="quote">
     “
     <span class="quote">
      MySQL
        extension
     </span>
     ”
    </span>
    designates casts implemented in MySQL beyond
        those defined in the
    <a class="link" href="spatial-types.html" title="13.4 Spatial Data Types">
     SQL/MM
        standard
    </a>
    :
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       From
       <code class="literal">
        Point
       </code>
       to:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           MultiPoint
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           GeometryCollection
          </code>
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       From
       <code class="literal">
        LineString
       </code>
       to:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           Polygon
          </code>
          (MySQL extension)
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           MultiPoint
          </code>
          (MySQL extension)
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           MultiLineString
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           GeometryCollection
          </code>
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       From
       <code class="literal">
        Polygon
       </code>
       to:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           LineString
          </code>
          (MySQL extension)
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           MultiLineString
          </code>
          (MySQL extension)
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           MultiPolygon
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           GeometryCollection
          </code>
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       From
       <code class="literal">
        MultiPoint
       </code>
       to:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           Point
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           LineString
          </code>
          (MySQL extension)
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           GeometryCollection
          </code>
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       From
       <code class="literal">
        MultiLineString
       </code>
       to:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           LineString
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           Polygon
          </code>
          (MySQL extension)
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           MultiPolygon
          </code>
          (MySQL extension)
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           GeometryCollection
          </code>
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       From
       <code class="literal">
        MultiPolygon
       </code>
       to:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           Polygon
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           MultiLineString
          </code>
          (MySQL extension)
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           GeometryCollection
          </code>
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       From
       <code class="literal">
        GeometryCollection
       </code>
       to:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           Point
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           LineString
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           Polygon
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           MultiPoint
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           MultiLineString
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           MultiPolygon
          </code>
         </p>
        </li>
       </ul>
      </div>
     </li>
    </ul>
   </div>
   <p>
    In spatial casts,
    <code class="literal">
     GeometryCollection
    </code>
    and
    <code class="literal">
     GeomCollection
    </code>
    are synonyms for the same
        result type.
   </p>
   <p>
    Some conditions apply to all spatial type casts, and some
        conditions apply only when the cast result is to have a
        particular spatial type. For information about terms such as
    <span class="quote">
     “
     <span class="quote">
      well-formed geometry,
     </span>
     ”
    </span>
    see
    <a class="xref" href="geometry-well-formedness-validity.html" title="13.4.4 Geometry Well-Formedness and Validity">
     Section 13.4.4, “Geometry Well-Formedness and Validity”
    </a>
    .
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="xref" href="cast-functions.html#spatial-cast-general-conditions" title="General Conditions for Spatial Casts">
        General Conditions for Spatial Casts
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="cast-functions.html#spatial-cast-point-conditions" title="Conditions for Casts to Point">
        Conditions for Casts to Point
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="cast-functions.html#spatial-cast-linestring-conditions" title="Conditions for Casts to LineString">
        Conditions for Casts to LineString
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="cast-functions.html#spatial-cast-polygon-conditions" title="Conditions for Casts to Polygon">
        Conditions for Casts to Polygon
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="cast-functions.html#spatial-cast-multipoint-conditions" title="Conditions for Casts to MultiPoint">
        Conditions for Casts to MultiPoint
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="cast-functions.html#spatial-cast-multilinestring-conditions" title="Conditions for Casts to MultiLineString">
        Conditions for Casts to MultiLineString
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="cast-functions.html#spatial-cast-multipolygon-conditions" title="Conditions for Casts to MultiPolygon">
        Conditions for Casts to MultiPolygon
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="cast-functions.html#spatial-cast-geometrycollection-conditions" title="Conditions for Casts to GeometryCollection">
        Conditions for Casts to GeometryCollection
       </a>
      </p>
     </li>
    </ul>
   </div>
   <h4>
    <a name="spatial-cast-general-conditions">
    </a>
    General Conditions for Spatial Casts
   </h4>
   <p>
    These conditions apply to all spatial casts regardless of the
        result type:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The result of a cast is in the same SRS as that of the
            expression to cast.
      </p>
     </li>
     <li class="listitem">
      <p>
       Casting between spatial types does not change coordinate
            values or order.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is
       <code class="literal">
        NULL
       </code>
       , the
            function result is
       <code class="literal">
        NULL
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Casting to spatial types using the
       <a class="link" href="json-search-functions.html#function_json-value">
        <code class="literal">
         JSON_VALUE()
        </code>
       </a>
       function with a
       <code class="literal">
        RETURNING
       </code>
       clause specifying a spatial
            type is not permitted.
      </p>
     </li>
     <li class="listitem">
      <p>
       Casting to an
       <code class="literal">
        ARRAY
       </code>
       of spatial types is
            not permitted.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the spatial type combination is permitted but the
            expression to cast is not a syntactically well-formed
            geometry, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_gis_invalid_data" target="_top">
        <code class="literal">
         ER_GIS_INVALID_DATA
        </code>
       </a>
       error
            occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the spatial type combination is permitted but the
            expression to cast is a syntactically well-formed geometry
            in an undefined spatial reference system (SRS), an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_srs_not_found" target="_top">
        <code class="literal">
         ER_SRS_NOT_FOUND
        </code>
       </a>
       error
            occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast has a geographic SRS but has a
            longitude or latitude that is out of range, an error occurs:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If a longitude value is not in the range (−180,
                180], an
          <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_geometry_param_longitude_out_of_range" target="_top">
           <code class="literal">
            ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
           </code>
          </a>
          error occurs.
         </p>
        </li>
        <li class="listitem">
         <p>
          If a latitude value is not in the range [−90, 90],
                an
          <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_geometry_param_latitude_out_of_range" target="_top">
           <code class="literal">
            ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
           </code>
          </a>
          error occurs.
         </p>
        </li>
       </ul>
      </div>
      <p>
       Ranges shown are in degrees. If an SRS uses another unit,
            the range uses the corresponding values in its unit. The
            exact range limits deviate slightly due to floating-point
            arithmetic.
      </p>
     </li>
    </ul>
   </div>
   <h4>
    <a name="spatial-cast-point-conditions">
    </a>
    Conditions for Casts to Point
   </h4>
   <p>
    When the cast result type is
    <code class="literal">
     Point
    </code>
    , these
        conditions apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        Point
       </code>
       , the function result is that
       <code class="literal">
        Point
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiPoint
       </code>
       containing a single
       <code class="literal">
        Point
       </code>
       , the function result is that
       <code class="literal">
        Point
       </code>
       . If the expression contains more
            than one
       <code class="literal">
        Point
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        GeometryCollection
       </code>
       containing only a
            single
       <code class="literal">
        Point
       </code>
       , the function result is that
       <code class="literal">
        Point
       </code>
       . If the expression is empty,
            contains more than one
       <code class="literal">
        Point
       </code>
       , or contains
            other geometry types, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
            other than
       <code class="literal">
        Point
       </code>
       ,
       <code class="literal">
        MultiPoint
       </code>
       ,
       <code class="literal">
        GeometryCollection
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
    </ul>
   </div>
   <h4>
    <a name="spatial-cast-linestring-conditions">
    </a>
    Conditions for Casts to LineString
   </h4>
   <p>
    When the cast result type is
    <code class="literal">
     LineString
    </code>
    ,
        these conditions apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        LineString
       </code>
       , the function result is that
       <code class="literal">
        LineString
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        Polygon
       </code>
       that has no inner rings, the
            function result is a
       <code class="literal">
        LineString
       </code>
       containing the points of the outer ring in the same order.
            If the expression has inner rings, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiPoint
       </code>
       containing at least two
            points, the function result is a
       <code class="literal">
        LineString
       </code>
       containing the points of the
       <code class="literal">
        MultiPoint
       </code>
       in the order they appear in
            the expression. If the expression contains only one
       <code class="literal">
        Point
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiLineString
       </code>
       containing a single
       <code class="literal">
        LineString
       </code>
       , the function result is that
       <code class="literal">
        LineString
       </code>
       . If the expression contains
            more than one
       <code class="literal">
        LineString
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        GeometryCollection
       </code>
       , containing only a
            single
       <code class="literal">
        LineString
       </code>
       , the function result is
            that
       <code class="literal">
        LineString
       </code>
       . If the expression is
            empty, contains more than one
       <code class="literal">
        LineString
       </code>
       ,
            or contains other geometry types, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
            other than
       <code class="literal">
        LineString
       </code>
       ,
       <code class="literal">
        Polygon
       </code>
       ,
       <code class="literal">
        MultiPoint
       </code>
       ,
       <code class="literal">
        MultiLineString
       </code>
       , or
       <code class="literal">
        GeometryCollection
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
    </ul>
   </div>
   <h4>
    <a name="spatial-cast-polygon-conditions">
    </a>
    Conditions for Casts to Polygon
   </h4>
   <p>
    When the cast result type is
    <code class="literal">
     Polygon
    </code>
    , these
        conditions apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        LineString
       </code>
       that is a ring (that is, the
            start and end points are the same), the function result is a
       <code class="literal">
        Polygon
       </code>
       with an outer ring consisting of
            the points of the
       <code class="literal">
        LineString
       </code>
       in the same
            order. If the expression is not a ring, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs. If the ring is not in the correct order (the
            exterior ring must be counter-clockwise), an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_polygon_ring_direction" target="_top">
        <code class="literal">
         ER_INVALID_CAST_POLYGON_RING_DIRECTION
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        Polygon
       </code>
       , the function result is that
       <code class="literal">
        Polygon
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiLineString
       </code>
       where all elements are
            rings, the function result is a
       <code class="literal">
        Polygon
       </code>
       with the first
       <code class="literal">
        LineString
       </code>
       as outer ring
            and any additional
       <code class="literal">
        LineString
       </code>
       values as
            inner rings. If any element of the expression is not a ring,
            an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs. If any ring is not in the correct order (the
            exterior ring must be counter-clockwise, interior rings must
            be clockwise), an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_polygon_ring_direction" target="_top">
        <code class="literal">
         ER_INVALID_CAST_POLYGON_RING_DIRECTION
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiPolygon
       </code>
       containing a single
       <code class="literal">
        Polygon
       </code>
       , the function result is that
       <code class="literal">
        Polygon
       </code>
       . If the expression contains more
            than one
       <code class="literal">
        Polygon
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        GeometryCollection
       </code>
       containing only a
            single
       <code class="literal">
        Polygon
       </code>
       , the function result is
            that
       <code class="literal">
        Polygon
       </code>
       . If the expression is empty,
            contains more than one
       <code class="literal">
        Polygon
       </code>
       , or
            contains other geometry types, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
            other than
       <code class="literal">
        LineString
       </code>
       ,
       <code class="literal">
        Polygon
       </code>
       ,
       <code class="literal">
        MultiLineString
       </code>
       ,
       <code class="literal">
        MultiPolygon
       </code>
       , or
       <code class="literal">
        GeometryCollection
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
    </ul>
   </div>
   <h4>
    <a name="spatial-cast-multipoint-conditions">
    </a>
    Conditions for Casts to MultiPoint
   </h4>
   <p>
    When the cast result type is
    <code class="literal">
     MultiPoint
    </code>
    ,
        these conditions apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        Point
       </code>
       , the function result is a
       <code class="literal">
        MultiPoint
       </code>
       containing that
       <code class="literal">
        Point
       </code>
       as its sole element.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        LineString
       </code>
       , the function result is a
       <code class="literal">
        MultiPoint
       </code>
       containing the points of the
       <code class="literal">
        LineString
       </code>
       in the same order.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiPoint
       </code>
       , the function result is that
       <code class="literal">
        MultiPoint
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        GeometryCollection
       </code>
       containing only
            points, the function result is a
       <code class="literal">
        MultiPoint
       </code>
       containing those points. If
            the
       <code class="literal">
        GeometryCollection
       </code>
       is empty or
            contains other geometry types, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
            other than
       <code class="literal">
        Point
       </code>
       ,
       <code class="literal">
        LineString
       </code>
       ,
       <code class="literal">
        MultiPoint
       </code>
       , or
       <code class="literal">
        GeometryCollection
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
    </ul>
   </div>
   <h4>
    <a name="spatial-cast-multilinestring-conditions">
    </a>
    Conditions for Casts to MultiLineString
   </h4>
   <p>
    When the cast result type is
    <code class="literal">
     MultiLineString
    </code>
    ,
        these conditions apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        LineString
       </code>
       , the function result is a
       <code class="literal">
        MultiLineString
       </code>
       containing that
       <code class="literal">
        LineString
       </code>
       as its sole element.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        Polygon
       </code>
       , the function result is a
       <code class="literal">
        MultiLineString
       </code>
       containing the outer ring
            of the
       <code class="literal">
        Polygon
       </code>
       as its first element and
            any inner rings as additional elements in the order they
            appear in the expression.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiLineString
       </code>
       , the function result is
            that
       <code class="literal">
        MultiLineString
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiPolygon
       </code>
       containing only polygons
            without inner rings, the function result is a
       <code class="literal">
        MultiLineString
       </code>
       containing the polygon
            rings in the order they appear in the expression. If the
            expression contains any polygons with inner rings, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_wrong_parameters_to_stored_fct" target="_top">
        <code class="literal">
         ER_WRONG_PARAMETERS_TO_STORED_FCT
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        GeometryCollection
       </code>
       containing only
            linestrings, the function result is a
       <code class="literal">
        MultiLineString
       </code>
       containing those
            linestrings. If the expression is empty or contains other
            geometry types, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
            other than
       <code class="literal">
        LineString
       </code>
       ,
       <code class="literal">
        Polygon
       </code>
       ,
       <code class="literal">
        MultiLineString
       </code>
       ,
       <code class="literal">
        MultiPolygon
       </code>
       , or
       <code class="literal">
        GeometryCollection
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
    </ul>
   </div>
   <h4>
    <a name="spatial-cast-multipolygon-conditions">
    </a>
    Conditions for Casts to MultiPolygon
   </h4>
   <p>
    When the cast result type is
    <code class="literal">
     MultiPolygon
    </code>
    ,
        these conditions apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        Polygon
       </code>
       , the function result is a
       <code class="literal">
        MultiPolygon
       </code>
       containing the
       <code class="literal">
        Polygon
       </code>
       as its sole element.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiLineString
       </code>
       where all elements are
            rings, the function result is a
       <code class="literal">
        MultiPolygon
       </code>
       containing a
       <code class="literal">
        Polygon
       </code>
       with only an outer ring for each
            element of the expression. If any element is not a ring, an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs. If any ring is not in the correct order
            (exterior ring must be counter-clockwise), an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_polygon_ring_direction" target="_top">
        <code class="literal">
         ER_INVALID_CAST_POLYGON_RING_DIRECTION
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiPolygon
       </code>
       , the function result is that
       <code class="literal">
        MultiPolygon
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        GeometryCollection
       </code>
       containing only
            polygons, the function result is a
       <code class="literal">
        MultiPolygon
       </code>
       containing those polygons.
            If the expression is empty or contains other geometry types,
            an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
            other than
       <code class="literal">
        Polygon
       </code>
       ,
       <code class="literal">
        MultiLineString
       </code>
       ,
       <code class="literal">
        MultiPolygon
       </code>
       , or
       <code class="literal">
        GeometryCollection
       </code>
       , an
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_invalid_cast_to_geometry" target="_top">
        <code class="literal">
         ER_INVALID_CAST_TO_GEOMETRY
        </code>
       </a>
       error occurs.
      </p>
     </li>
    </ul>
   </div>
   <h4>
    <a name="spatial-cast-geometrycollection-conditions">
    </a>
    Conditions for Casts to GeometryCollection
   </h4>
   <p>
    When the cast result type is
    <code class="literal">
     GeometryCollection
    </code>
    , these conditions apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        GeometryCollection
       </code>
       and
       <code class="literal">
        GeomCollection
       </code>
       are synonyms for the same
            result type.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        Point
       </code>
       , the function result is a
       <code class="literal">
        GeometryCollection
       </code>
       containing that
       <code class="literal">
        Point
       </code>
       as its sole element.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        LineString
       </code>
       , the function result is a
       <code class="literal">
        GeometryCollection
       </code>
       containing that
       <code class="literal">
        LineString
       </code>
       as its sole element.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        Polygon
       </code>
       , the function result is a
       <code class="literal">
        GeometryCollection
       </code>
       containing that
       <code class="literal">
        Polygon
       </code>
       as its sole element.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiPoint
       </code>
       , the function result is a
       <code class="literal">
        GeometryCollection
       </code>
       containing the points
            in the order they appear in the expression.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiLineString
       </code>
       , the function result is a
       <code class="literal">
        GeometryCollection
       </code>
       containing the
            linestrings in the order they appear in the expression.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        MultiPolygon
       </code>
       , the function result is a
       <code class="literal">
        GeometryCollection
       </code>
       containing the
            elements of the
       <code class="literal">
        MultiPolygon
       </code>
       in the order
            they appear in the expression.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the expression to cast is a well-formed geometry of type
       <code class="literal">
        GeometryCollection
       </code>
       , the function result
            is that
       <code class="literal">
        GeometryCollection
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h3 class="title">
       <a name="cast-other-uses">
       </a>
       Other Uses for Cast Operations
      </h3>
     </div>
    </div>
   </div>
   <p>
    The cast functions are useful for creating a column with a
        specific type in a
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE ...
        SELECT
     </code>
    </a>
    statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20697684"><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> new_table <span class="token keyword">SELECT</span> <span class="token function">CAST</span><span class="token punctuation">(</span><span class="token string">'2000-01-01'</span> <span class="token keyword">AS</span> <span class="token datatype">DATE</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> c1<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> new_table\G
<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 class="token punctuation">*</span> 1. row <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 class="token punctuation">*</span>
       Table<span class="token punctuation">:</span> new_table
Create Table<span class="token punctuation">:</span> CREATE TABLE `new_table` (
  `c1` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</span></code></pre>
   </div>
   <p>
    The cast functions are useful for sorting
    <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
     <code class="literal">
      ENUM
     </code>
    </a>
    columns in lexical order.
        Normally, sorting of
    <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
     <code class="literal">
      ENUM
     </code>
    </a>
    columns
        occurs using the internal numeric values. Casting the values to
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      CHAR
     </code>
    </a>
    results in a lexical sort:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25170567"><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> <em class="replaceable">enum_col</em> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> 
  <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <span class="token function">CAST</span><span class="token punctuation">(</span><em class="replaceable">enum_col</em> <span class="token keyword">AS</span> <span class="token datatype">CHAR</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    also changes the result if
        you use it as part of a more complex expression such as
    <a class="link" href="string-functions.html#function_concat">
     <code class="literal">
      CONCAT('Date: ',CAST(NOW() AS
        DATE))
     </code>
    </a>
    .
   </p>
   <p>
    For temporal values, there is little need to use
    <a class="link" href="cast-functions.html#function_cast">
     <code class="literal">
      CAST()
     </code>
    </a>
    to extract data in
        different formats. Instead, use a function such as
    <a class="link" href="date-and-time-functions.html#function_extract">
     <code class="literal">
      EXTRACT()
     </code>
    </a>
    ,
    <a class="link" href="date-and-time-functions.html#function_date-format">
     <code class="literal">
      DATE_FORMAT()
     </code>
    </a>
    , or
    <a class="link" href="date-and-time-functions.html#function_time-format">
     <code class="literal">
      TIME_FORMAT()
     </code>
    </a>
    . See
    <a class="xref" href="date-and-time-functions.html" title="14.7 Date and Time Functions">
     Section 14.7, “Date and Time Functions”
    </a>
    .
   </p>
   <p>
    To cast a string to a number, it normally suffices to use the
        string value in numeric context:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32638874"><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></code></pre>
   </div>
   <p>
    That is also true for hexadecimal and bit literals, which are
        binary strings by default:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa2868749"><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> X<span class="token string">'41'</span><span class="token punctuation">,</span> X<span class="token string">'41'</span><span class="token operator">+</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'A'</span><span class="token punctuation">,</span> <span class="token number">65</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> b<span class="token string">'1100001'</span><span class="token punctuation">,</span> b<span class="token string">'1100001'</span><span class="token operator">+</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token number">97</span></code></pre>
   </div>
   <p>
    A string used in an arithmetic operation is converted to a
        floating-point number during expression evaluation.
   </p>
   <p>
    A number used in string context is converted to a string:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71913921"><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">CONCAT</span><span class="token punctuation">(</span><span class="token string">'hello you '</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'hello you 2'</span></code></pre>
   </div>
   <p>
    For information about implicit conversion of numbers to strings,
        see
    <a class="xref" href="type-conversion.html" title="14.3 Type Conversion in Expression Evaluation">
     Section 14.3, “Type Conversion in Expression Evaluation”
    </a>
    .
   </p>
   <p>
    MySQL supports arithmetic with both signed and unsigned 64-bit
        values. For numeric operators (such as
    <a class="link" href="arithmetic-functions.html#operator_plus">
     <code class="literal">
      +
     </code>
    </a>
    or
    <a class="link" href="arithmetic-functions.html#operator_minus">
     <code class="literal">
      -
     </code>
    </a>
    ) where one of
        the operands is an unsigned integer, the result is unsigned by
        default (see
    <a class="xref" href="arithmetic-functions.html" title="14.6.1 Arithmetic Operators">
     Section 14.6.1, “Arithmetic Operators”
    </a>
    ). To
        override this, use the
    <code class="literal">
     SIGNED
    </code>
    or
    <code class="literal">
     UNSIGNED
    </code>
    cast operator to cast a value to a
        signed or unsigned 64-bit integer, respectively.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34586787"><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 number">2</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token operator">-</span><span class="token number">1</span>
<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 number">1</span> <span class="token operator">-</span> <span class="token number">2</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">18446744073709551615</span>
<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 function">CAST</span><span class="token punctuation">(</span><span class="token number">1</span> <span class="token operator">-</span> <span class="token number">2</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">SIGNED</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token operator">-</span><span class="token number">1</span></code></pre>
   </div>
   <p>
    If either operand is a floating-point value, the result is a
        floating-point value and is not affected by the preceding rule.
        (In this context,
    <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>
    column
        values are regarded as floating-point values.)
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48706015"><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 number">1</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">2.0</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token operator">-</span><span class="token number">1.0</span></code></pre>
   </div>
   <p>
    The SQL mode affects the result of conversion operations (see
    <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
     Section 7.1.11, “Server SQL Modes”
    </a>
    ). Examples:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For conversion of a
       <span class="quote">
        “
        <span class="quote">
         zero
        </span>
        ”
       </span>
       date string to a
            date,
       <a class="link" href="cast-functions.html#function_convert">
        <code class="literal">
         CONVERT()
        </code>
       </a>
       and
       <a class="link" href="cast-functions.html#function_cast">
        <code class="literal">
         CAST()
        </code>
       </a>
       return
       <code class="literal">
        NULL
       </code>
       and produce a warning when the
       <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
        <code class="literal">
         NO_ZERO_DATE
        </code>
       </a>
       SQL mode is
            enabled.
      </p>
     </li>
     <li class="listitem">
      <p>
       For integer subtraction, if the
       <a class="link" href="sql-mode.html#sqlmode_no_unsigned_subtraction">
        <code class="literal">
         NO_UNSIGNED_SUBTRACTION
        </code>
       </a>
       SQL mode is enabled, the subtraction result is signed even
            if any operand is unsigned.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
