<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="sql-mode">
      </a>
      7.1.11 Server SQL Modes
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045261679616">
  </a>
  <p>
   The MySQL server can operate in different SQL modes, and can apply
      these modes differently for different clients, depending on the
      value of the
   <a class="link" href="server-system-variables.html#sysvar_sql_mode">
    <code class="literal">
     sql_mode
    </code>
   </a>
   system
      variable. DBAs can set the global SQL mode to match site server
      operating requirements, and each application can set its session
      SQL mode to its own requirements.
  </p>
  <p>
   Modes affect the SQL syntax MySQL supports and the data validation
      checks it performs. This makes it easier to use MySQL in different
      environments and to use MySQL together with other database
      servers.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="sql-mode.html#sql-mode-setting" title="Setting the SQL Mode">
       Setting the SQL Mode
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="sql-mode.html#sql-mode-important" title="The Most Important SQL Modes">
       The Most Important SQL Modes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="sql-mode.html#sql-mode-full" title="Full List of SQL Modes">
       Full List of SQL Modes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="sql-mode.html#sql-mode-combo" title="Combination SQL Modes">
       Combination SQL Modes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="sql-mode.html#sql-mode-strict" title="Strict SQL Mode">
       Strict SQL Mode
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="sql-mode.html#ignore-strict-comparison" title="Comparison of the IGNORE Keyword and Strict SQL Mode">
       Comparison of the IGNORE Keyword and Strict SQL Mode
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   For answers to questions often asked about server SQL modes in
      MySQL, see
   <a class="xref" href="faqs-sql-modes.html" title="A.3 MySQL 8.4 FAQ: Server SQL Mode">
    Section A.3, “MySQL 8.4 FAQ: Server SQL Mode”
   </a>
   .
  </p>
  <p>
   When working with
   <code class="literal">
    InnoDB
   </code>
   tables, consider also
      the
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   system
      variable. It enables additional error checks for
   <code class="literal">
    InnoDB
   </code>
   tables.
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="sql-mode-setting">
       </a>
       Setting the SQL Mode
      </h4>
     </div>
    </div>
   </div>
   <p>
    The default SQL mode in MySQL 8.4 includes these
        modes:
    <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
     <code class="literal">
      ONLY_FULL_GROUP_BY
     </code>
    </a>
    ,
    <a class="link" href="sql-mode.html#sqlmode_strict_trans_tables">
     <code class="literal">
      STRICT_TRANS_TABLES
     </code>
    </a>
    ,
    <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
     <code class="literal">
      NO_ZERO_IN_DATE
     </code>
    </a>
    ,
    <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
     <code class="literal">
      NO_ZERO_DATE
     </code>
    </a>
    ,
    <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
     <code class="literal">
      ERROR_FOR_DIVISION_BY_ZERO
     </code>
    </a>
    ,
        and
    <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
     <code class="literal">
      NO_ENGINE_SUBSTITUTION
     </code>
    </a>
    .
   </p>
   <p>
    To set the SQL mode at server startup, use the
    <a class="link" href="server-options.html#option_mysqld_sql-mode">
     <code class="option">
      --sql-mode="
      <em class="replaceable">
       <code>
        modes
       </code>
      </em>
      "
     </code>
    </a>
    option on the command line, or
    <a class="link" href="server-options.html#option_mysqld_sql-mode">
     <code class="option">
      sql-mode="
      <em class="replaceable">
       <code>
        modes
       </code>
      </em>
      "
     </code>
    </a>
    in an option file such as
    <code class="filename">
     my.cnf
    </code>
    (Unix
        operating systems) or
    <code class="filename">
     my.ini
    </code>
    (Windows).
    <em class="replaceable">
     <code>
      modes
     </code>
    </em>
    is a list of different modes
        separated by commas. To clear the SQL mode explicitly, set it to
        an empty string using
    <a class="link" href="server-options.html#option_mysqld_sql-mode">
     <code class="option">
      --sql-mode=""
     </code>
    </a>
    on the command
        line, or
    <a class="link" href="server-options.html#option_mysqld_sql-mode">
     <code class="option">
      sql-mode=""
     </code>
    </a>
    in an option
        file.
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     MySQL installation programs may configure the SQL mode during
          the installation process.
    </p>
    <p>
     If the SQL mode differs from the default or from what you
          expect, check for a setting in an option file that the server
          reads at startup.
    </p>
   </div>
   <p>
    To change the SQL mode at runtime, set the global or session
    <a class="link" href="server-system-variables.html#sysvar_sql_mode">
     <code class="literal">
      sql_mode
     </code>
    </a>
    system variable using
        a
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20616293"><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">SET</span> <span class="token keyword">GLOBAL</span> sql_mode <span class="token operator">=</span> <span class="token string">'<em class="replaceable">modes</em>'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">SESSION</span> sql_mode <span class="token operator">=</span> <span class="token string">'<em class="replaceable">modes</em>'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Setting the
    <code class="literal">
     GLOBAL
    </code>
    variable requires the
    <a class="link" href="privileges-provided.html#priv_system-variables-admin">
     <code class="literal">
      SYSTEM_VARIABLES_ADMIN
     </code>
    </a>
    privilege
        (or the deprecated
    <a class="link" href="privileges-provided.html#priv_super">
     <code class="literal">
      SUPER
     </code>
    </a>
    privilege) and affects the operation of all clients that connect
        from that time on. Setting the
    <code class="literal">
     SESSION
    </code>
    variable affects only the current client. Each client can change
        its session
    <a class="link" href="server-system-variables.html#sysvar_sql_mode">
     <code class="literal">
      sql_mode
     </code>
    </a>
    value at
        any time.
   </p>
   <p>
    To determine the current global or session
    <a class="link" href="server-system-variables.html#sysvar_sql_mode">
     <code class="literal">
      sql_mode
     </code>
    </a>
    setting, select its
        value:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48404178"><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 variable">@@GLOBAL.sql_mode</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token variable">@@SESSION.sql_mode</span><span class="token punctuation">;</span></code></pre>
   </div>
   <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Important
    </div>
    <p>
     <b>
      SQL mode and user-defined partitioning.
     </b>
     Changing the server SQL mode after creating and inserting
            data into partitioned tables can cause major changes in the
            behavior of such tables, and could lead to loss or
            corruption of data. It is strongly recommended that you
            never change the SQL mode once you have created tables
            employing user-defined partitioning.
    </p>
    <p>
     When replicating partitioned tables, differing SQL modes on
          the source and replica can also lead to problems. For best
          results, you should always use the same server SQL mode on the
          source and replica.
    </p>
    <p>
     For more information, see
     <a class="xref" href="partitioning-limitations.html" title="26.6 Restrictions and Limitations on Partitioning">
      Section 26.6, “Restrictions and Limitations on Partitioning”
     </a>
     .
    </p>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="sql-mode-important">
       </a>
       The Most Important SQL Modes
      </h4>
     </div>
    </div>
   </div>
   <p>
    The most important
    <a class="link" href="server-system-variables.html#sysvar_sql_mode">
     <code class="literal">
      sql_mode
     </code>
    </a>
    values are probably these:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="indexterm" name="idm46045261625408">
       </a>
       <a class="indexterm" name="idm46045261624336">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_ansi">
        <code class="literal">
         ANSI
        </code>
       </a>
      </p>
      <p>
       This mode changes syntax and behavior to conform more
            closely to standard SQL. It is one of the special
       <a class="link" href="sql-mode.html#sql-mode-combo" title="Combination SQL Modes">
        combination modes
       </a>
       listed at the end of this section.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="indexterm" name="idm46045261619280">
       </a>
       <a class="indexterm" name="idm46045261618192">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_strict_trans_tables">
        <code class="literal">
         STRICT_TRANS_TABLES
        </code>
       </a>
      </p>
      <p>
       If a value could not be inserted as given into a
            transactional table, abort the statement. For a
            nontransactional table, abort the statement if the value
            occurs in a single-row statement or the first row of a
            multiple-row statement. More details are given later in this
            section.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="indexterm" name="idm46045261613888">
       </a>
       <a class="indexterm" name="idm46045261612816">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_traditional">
        <code class="literal">
         TRADITIONAL
        </code>
       </a>
      </p>
      <p>
       Make MySQL behave like a
       <span class="quote">
        “
        <span class="quote">
         traditional
        </span>
        ”
       </span>
       SQL
            database system. A simple description of this mode is
       <span class="quote">
        “
        <span class="quote">
         give an error instead of a warning
        </span>
        ”
       </span>
       when
            inserting an incorrect value into a column. It is one of the
            special
       <a class="link" href="sql-mode.html#sql-mode-combo" title="Combination SQL Modes">
        combination
            modes
       </a>
       listed at the end of this section.
      </p>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        With
        <a class="link" href="sql-mode.html#sqlmode_traditional">
         <code class="literal">
          TRADITIONAL
         </code>
        </a>
        mode
              enabled, an
        <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
         <code class="literal">
          INSERT
         </code>
        </a>
        or
        <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
         <code class="literal">
          UPDATE
         </code>
        </a>
        aborts as soon as an
              error occurs. If you are using a nontransactional storage
              engine, this may not be what you want because data changes
              made prior to the error may not be rolled back, resulting
              in a
        <span class="quote">
         “
         <span class="quote">
          partially done
         </span>
         ”
        </span>
        update.
       </p>
      </div>
     </li>
    </ul>
   </div>
   <a class="indexterm" name="idm46045261602224">
   </a>
   <a class="indexterm" name="idm46045261601152">
   </a>
   <p>
    When this manual refers to
    <span class="quote">
     “
     <span class="quote">
      strict mode,
     </span>
     ”
    </span>
    it means
        a mode with either or both
    <a class="link" href="sql-mode.html#sqlmode_strict_trans_tables">
     <code class="literal">
      STRICT_TRANS_TABLES
     </code>
    </a>
    or
    <a class="link" href="sql-mode.html#sqlmode_strict_all_tables">
     <code class="literal">
      STRICT_ALL_TABLES
     </code>
    </a>
    enabled.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="sql-mode-full">
       </a>
       Full List of SQL Modes
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following list describes all supported SQL modes:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a name="sqlmode_allow_invalid_dates">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_allow_invalid_dates">
        <code class="literal">
         ALLOW_INVALID_DATES
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261591648">
      </a>
      <a class="indexterm" name="idm46045261590608">
      </a>
      <p>
       Do not perform full checking of dates. Check only that the
            month is in the range from 1 to 12 and the day is in the
            range from 1 to 31. This may be useful for Web applications
            that obtain year, month, and day in three different fields
            and store exactly what the user inserted, without date
            validation. This mode applies to
       <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
        <code class="literal">
         DATE
        </code>
       </a>
       and
       <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
        <code class="literal">
         DATETIME
        </code>
       </a>
       columns. It does not
            apply to
       <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
        <code class="literal">
         TIMESTAMP
        </code>
       </a>
       columns,
            which always require a valid date.
      </p>
      <p>
       With
       <a class="link" href="sql-mode.html#sqlmode_allow_invalid_dates">
        <code class="literal">
         ALLOW_INVALID_DATES
        </code>
       </a>
       disabled, the server requires that month and day values be
            legal, and not merely in the range 1 to 12 and 1 to 31,
            respectively. With strict mode disabled, invalid dates such
            as
       <code class="literal">
        '2004-04-31'
       </code>
       are converted to
       <code class="literal">
        '0000-00-00'
       </code>
       and a warning is generated.
            With strict mode enabled, invalid dates generate an error.
            To permit such dates, enable
       <a class="link" href="sql-mode.html#sqlmode_allow_invalid_dates">
        <code class="literal">
         ALLOW_INVALID_DATES
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_ansi_quotes">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_ansi_quotes">
        <code class="literal">
         ANSI_QUOTES
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261577376">
      </a>
      <a class="indexterm" name="idm46045261576304">
      </a>
      <p>
       Treat
       <code class="literal">
        "
       </code>
       as an identifier quote character
            (like the
       <code class="literal">
        `
       </code>
       quote character) and not as a
            string quote character. You can still use
       <code class="literal">
        `
       </code>
       to quote identifiers with this mode
            enabled. With
       <a class="link" href="sql-mode.html#sqlmode_ansi_quotes">
        <code class="literal">
         ANSI_QUOTES
        </code>
       </a>
       enabled, you cannot use double quotation marks to quote
            literal strings because they are interpreted as identifiers.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_error_for_division_by_zero">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261568368">
      </a>
      <a class="indexterm" name="idm46045261567280">
      </a>
      <p>
       The
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
       mode affects handling of division by zero, which includes
       <a class="link" href="mathematical-functions.html#function_mod">
        <code class="literal">
         MOD(
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
         ,0)
        </code>
       </a>
       .
            For data-change operations
            (
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       ,
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       ), its effect also
            depends on whether strict SQL mode is enabled.
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If this mode is not enabled, division by zero inserts
          <code class="literal">
           NULL
          </code>
          and produces no warning.
         </p>
        </li>
        <li class="listitem">
         <p>
          If this mode is enabled, division by zero inserts
          <code class="literal">
           NULL
          </code>
          and produces a warning.
         </p>
        </li>
        <li class="listitem">
         <p>
          If this mode and strict mode are enabled, division by
                zero produces an error, unless
          <code class="literal">
           IGNORE
          </code>
          is given as well. For
          <code class="literal">
           INSERT IGNORE
          </code>
          and
          <code class="literal">
           UPDATE IGNORE
          </code>
          , division by zero
                inserts
          <code class="literal">
           NULL
          </code>
          and produces a warning.
         </p>
        </li>
       </ul>
      </div>
      <p>
       For
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       , division by zero
            returns
       <code class="literal">
        NULL
       </code>
       . Enabling
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
       causes a warning to be produced as well, regardless of
            whether strict mode is enabled.
      </p>
      <p>
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
       is deprecated.
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
       is not part of strict mode, but should be used in
            conjunction with strict mode and is enabled by default. A
            warning occurs if
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
       is enabled without also enabling strict mode or vice versa.
      </p>
      <p>
       Because
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
       is deprecated, you should expect it to be removed in a
            future MySQL release as a separate mode name and its effect
            included in the effects of strict SQL mode.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_high_not_precedence">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_high_not_precedence">
        <code class="literal">
         HIGH_NOT_PRECEDENCE
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261539536">
      </a>
      <a class="indexterm" name="idm46045261538496">
      </a>
      <p>
       The precedence of the
       <a class="link" href="logical-operators.html#operator_not">
        <code class="literal">
         NOT
        </code>
       </a>
       operator is such that expressions such as
       <code class="literal">
        NOT a
            BETWEEN b AND c
       </code>
       are parsed as
       <code class="literal">
        NOT (a
            BETWEEN b AND c)
       </code>
       . In some older versions of MySQL,
            the expression was parsed as
       <code class="literal">
        (NOT a) BETWEEN b AND
            c
       </code>
       . The old higher-precedence behavior can be
            obtained by enabling the
       <a class="link" href="sql-mode.html#sqlmode_high_not_precedence">
        <code class="literal">
         HIGH_NOT_PRECEDENCE
        </code>
       </a>
       SQL
            mode.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63282799"><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> sql_mode <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">NOT</span> <span class="token number">1</span> <span class="token operator">BETWEEN</span> <span class="token operator">-</span><span class="token number">5</span> <span class="token operator">AND</span> <span class="token number">5</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">SET</span> sql_mode <span class="token operator">=</span> <span class="token string">'HIGH_NOT_PRECEDENCE'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">NOT</span> <span class="token number">1</span> <span class="token operator">BETWEEN</span> <span class="token operator">-</span><span class="token number">5</span> <span class="token operator">AND</span> <span class="token number">5</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token number">1</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_ignore_space">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_ignore_space">
        <code class="literal">
         IGNORE_SPACE
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261525296">
      </a>
      <a class="indexterm" name="idm46045261524224">
      </a>
      <p>
       Permit spaces between a function name and the
       <code class="literal">
        (
       </code>
       character. This causes built-in
            function names to be treated as reserved words. As a result,
            identifiers that are the same as function names must be
            quoted as described in
       <a class="xref" href="identifiers.html" title="11.2 Schema Object Names">
        Section 11.2, “Schema Object Names”
       </a>
       . For
            example, because there is a
       <a class="link" href="aggregate-functions.html#function_count">
        <code class="literal">
         COUNT()
        </code>
       </a>
       function, the use of
       <code class="literal">
        count
       </code>
       as a table name in the following
            statement causes an error:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14017833"><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> <span class="token function">count</span> <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1064 (42000)<span class="token punctuation">:</span> You have an error in your SQL syntax</span></code></pre>
      </div>
      <p>
       The table name should be quoted:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa23783479"><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> <span class="token punctuation">`</span>count<span class="token punctuation">`</span> <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span></code></pre>
      </div>
      <p>
       The
       <a class="link" href="sql-mode.html#sqlmode_ignore_space">
        <code class="literal">
         IGNORE_SPACE
        </code>
       </a>
       SQL mode
            applies to built-in functions, not to loadable functions or
            stored functions. It is always permissible to have spaces
            after a loadable function or stored function name,
            regardless of whether
       <a class="link" href="sql-mode.html#sqlmode_ignore_space">
        <code class="literal">
         IGNORE_SPACE
        </code>
       </a>
       is enabled.
      </p>
      <p>
       For further discussion of
       <a class="link" href="sql-mode.html#sqlmode_ignore_space">
        <code class="literal">
         IGNORE_SPACE
        </code>
       </a>
       , see
       <a class="xref" href="function-resolution.html" title="11.2.5 Function Name Parsing and Resolution">
        Section 11.2.5, “Function Name Parsing and Resolution”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_no_auto_value_on_zero">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_no_auto_value_on_zero">
        <code class="literal">
         NO_AUTO_VALUE_ON_ZERO
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261506800">
      </a>
      <a class="indexterm" name="idm46045261505760">
      </a>
      <p>
       <a class="link" href="sql-mode.html#sqlmode_no_auto_value_on_zero">
        <code class="literal">
         NO_AUTO_VALUE_ON_ZERO
        </code>
       </a>
       affects handling of
       <code class="literal">
        AUTO_INCREMENT
       </code>
       columns. Normally, you generate the next sequence number for
            the column by inserting either
       <code class="literal">
        NULL
       </code>
       or
       <code class="literal">
        0
       </code>
       into it.
       <a class="link" href="sql-mode.html#sqlmode_no_auto_value_on_zero">
        <code class="literal">
         NO_AUTO_VALUE_ON_ZERO
        </code>
       </a>
       suppresses this behavior for
       <code class="literal">
        0
       </code>
       so that
            only
       <code class="literal">
        NULL
       </code>
       generates the next sequence
            number.
      </p>
      <p>
       This mode can be useful if
       <code class="literal">
        0
       </code>
       has been
            stored in a table's
       <code class="literal">
        AUTO_INCREMENT
       </code>
       column. (Storing
       <code class="literal">
        0
       </code>
       is not a recommended
            practice, by the way.) For example, if you dump the table
            with
       <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
        <span class="command">
         <strong>
          mysqldump
         </strong>
        </span>
       </a>
       and then reload it, MySQL
            normally generates new sequence numbers when it encounters
            the
       <code class="literal">
        0
       </code>
       values, resulting in a table with
            contents different from the one that was dumped. Enabling
       <a class="link" href="sql-mode.html#sqlmode_no_auto_value_on_zero">
        <code class="literal">
         NO_AUTO_VALUE_ON_ZERO
        </code>
       </a>
       before reloading the dump file solves this problem. For this
            reason,
       <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
        <span class="command">
         <strong>
          mysqldump
         </strong>
        </span>
       </a>
       automatically includes
            in its output a statement that enables
       <a class="link" href="sql-mode.html#sqlmode_no_auto_value_on_zero">
        <code class="literal">
         NO_AUTO_VALUE_ON_ZERO
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_no_backslash_escapes">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_no_backslash_escapes">
        <code class="literal">
         NO_BACKSLASH_ESCAPES
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261486224">
      </a>
      <a class="indexterm" name="idm46045261485184">
      </a>
      <p>
       Enabling this mode disables the use of the backslash
            character (
       <code class="literal">
        \
       </code>
       ) as an escape character
            within strings and identifiers. With this mode enabled,
            backslash becomes an ordinary character like any other, and
            the default escape sequence for
       <a class="link" href="string-comparison-functions.html#operator_like">
        <code class="literal">
         LIKE
        </code>
       </a>
       expressions is changed so
            that no escape character is used.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_no_dir_in_create">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_no_dir_in_create">
        <code class="literal">
         NO_DIR_IN_CREATE
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261478320">
      </a>
      <a class="indexterm" name="idm46045261477280">
      </a>
      <p>
       When creating a table, ignore all
       <code class="literal">
        INDEX
            DIRECTORY
       </code>
       and
       <code class="literal">
        DATA DIRECTORY
       </code>
       directives. This option is useful on replica servers.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_no_engine_substitution">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
        <code class="literal">
         NO_ENGINE_SUBSTITUTION
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261471312">
      </a>
      <a class="indexterm" name="idm46045261470272">
      </a>
      <p>
       Control automatic substitution of the default storage engine
            when a statement such as
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE
            TABLE
        </code>
       </a>
       or
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER
            TABLE
        </code>
       </a>
       specifies a storage engine that is disabled
            or not compiled in.
      </p>
      <p>
       By default,
       <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
        <code class="literal">
         NO_ENGINE_SUBSTITUTION
        </code>
       </a>
       is
            enabled.
      </p>
      <p>
       Because storage engines can be pluggable at runtime,
            unavailable engines are treated the same way:
      </p>
      <p>
       With
       <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
        <code class="literal">
         NO_ENGINE_SUBSTITUTION
        </code>
       </a>
       disabled, for
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
        </code>
       </a>
       the default engine is used and a warning occurs if the
            desired engine is unavailable. For
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       , a warning occurs
            and the table is not altered.
      </p>
      <p>
       With
       <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
        <code class="literal">
         NO_ENGINE_SUBSTITUTION
        </code>
       </a>
       enabled, an error occurs and the table is not created or
            altered if the desired engine is unavailable.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_no_unsigned_subtraction">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_no_unsigned_subtraction">
        <code class="literal">
         NO_UNSIGNED_SUBTRACTION
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261454640">
      </a>
      <a class="indexterm" name="idm46045261453600">
      </a>
      <p>
       Subtraction between integer values, where one is of type
       <code class="literal">
        UNSIGNED
       </code>
       , produces an unsigned result by
            default. If the result would otherwise have been negative,
            an error results:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49612646"><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> sql_mode <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (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><span class="token number">0</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">1</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1690 (22003)<span class="token punctuation">:</span> BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'</span></code></pre>
      </div>
      <p>
       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 result is negative:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62624752"><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> sql_mode <span class="token operator">=</span> <span class="token string">'NO_UNSIGNED_SUBTRACTION'</span><span class="token punctuation">;</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">0</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">1</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(0 AS UNSIGNED) <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>
<span class="token output"><span class="token punctuation">|</span>                      <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></code></pre>
      </div>
      <p>
       If the result of such an operation is used to update an
       <code class="literal">
        UNSIGNED
       </code>
       integer column, the result is
            clipped to the maximum value for the column type, or clipped
            to 0 if
       <a class="link" href="sql-mode.html#sqlmode_no_unsigned_subtraction">
        <code class="literal">
         NO_UNSIGNED_SUBTRACTION
        </code>
       </a>
       is
            enabled. With strict SQL mode enabled, an error occurs and
            the column remains unchanged.
      </p>
      <p>
       When
       <a class="link" href="sql-mode.html#sqlmode_no_unsigned_subtraction">
        <code class="literal">
         NO_UNSIGNED_SUBTRACTION
        </code>
       </a>
       is
            enabled, the subtraction result is signed,
       <span class="emphasis">
        <em>
         even if
            any operand is unsigned
        </em>
       </span>
       . For example, compare the
            type of column
       <code class="literal">
        c2
       </code>
       in table
       <code class="literal">
        t1
       </code>
       with that of column
       <code class="literal">
        c2
       </code>
       in table
       <code class="literal">
        t2
       </code>
       :
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa49764665"><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> sql_mode<span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> test <span class="token punctuation">(</span>c1 <span class="token datatype">BIGINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">SELECT</span> c1 <span class="token operator">-</span> <span class="token number">1</span> <span class="token keyword">AS</span> c2 <span class="token keyword">FROM</span> test<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">DESCRIBE</span> t1<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> Field <span class="token punctuation">|</span> Type                <span class="token punctuation">|</span> Null <span class="token punctuation">|</span> Key <span class="token punctuation">|</span> Default <span class="token punctuation">|</span> Extra <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> c2    <span class="token punctuation">|</span> bigint(21) unsigned <span class="token punctuation">|</span> NO   <span class="token punctuation">|</span>     <span class="token punctuation">|</span> 0       <span class="token punctuation">|</span>       <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 prompt">mysql&gt;</span> <span class="token keyword">SET</span> sql_mode<span class="token operator">=</span><span class="token string">'NO_UNSIGNED_SUBTRACTION'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">SELECT</span> c1 <span class="token operator">-</span> <span class="token number">1</span> <span class="token keyword">AS</span> c2 <span class="token keyword">FROM</span> test<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">DESCRIBE</span> t2<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>
<span class="token output"><span class="token punctuation">|</span> Field <span class="token punctuation">|</span> Type       <span class="token punctuation">|</span> Null <span class="token punctuation">|</span> Key <span class="token punctuation">|</span> Default <span class="token punctuation">|</span> Extra <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>
<span class="token output"><span class="token punctuation">|</span> c2    <span class="token punctuation">|</span> bigint(21) <span class="token punctuation">|</span> NO   <span class="token punctuation">|</span>     <span class="token punctuation">|</span> 0       <span class="token punctuation">|</span>       <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></code></pre>
      </div>
      <p>
       This means that
       <code class="literal">
        BIGINT UNSIGNED
       </code>
       is not
            100% usable in all contexts. See
       <a class="xref" href="cast-functions.html" title="14.10 Cast Functions and Operators">
        Section 14.10, “Cast Functions and Operators”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_no_zero_date">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
        <code class="literal">
         NO_ZERO_DATE
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261424576">
      </a>
      <a class="indexterm" name="idm46045261423504">
      </a>
      <p>
       The
       <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
        <code class="literal">
         NO_ZERO_DATE
        </code>
       </a>
       mode
            affects whether the server permits
       <code class="literal">
        '0000-00-00'
       </code>
       as a valid date. Its effect
            also depends on whether strict SQL mode is enabled.
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If this mode is not enabled,
          <code class="literal">
           '0000-00-00'
          </code>
          is permitted and inserts
                produce no warning.
         </p>
        </li>
        <li class="listitem">
         <p>
          If this mode is enabled,
          <code class="literal">
           '0000-00-00'
          </code>
          is permitted and inserts produce a warning.
         </p>
        </li>
        <li class="listitem">
         <p>
          If this mode and strict mode are enabled,
          <code class="literal">
           '0000-00-00'
          </code>
          is not permitted and
                inserts produce an error, unless
          <code class="literal">
           IGNORE
          </code>
          is given as well. For
          <code class="literal">
           INSERT IGNORE
          </code>
          and
          <code class="literal">
           UPDATE
                IGNORE
          </code>
          ,
          <code class="literal">
           '0000-00-00'
          </code>
          is
                permitted and inserts produce a warning.
         </p>
        </li>
       </ul>
      </div>
      <p>
       <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
        <code class="literal">
         NO_ZERO_DATE
        </code>
       </a>
       is
            deprecated.
       <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
        <code class="literal">
         NO_ZERO_DATE
        </code>
       </a>
       is not part of strict mode, but should be used in
            conjunction with strict mode and is enabled by default. A
            warning occurs if
       <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
        <code class="literal">
         NO_ZERO_DATE
        </code>
       </a>
       is enabled
            without also enabling strict mode or vice versa.
      </p>
      <p>
       Because
       <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
        <code class="literal">
         NO_ZERO_DATE
        </code>
       </a>
       is
            deprecated, you should expect it to be removed in a future
            MySQL release as a separate mode name and its effect
            included in the effects of strict SQL mode.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_no_zero_in_date">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
        <code class="literal">
         NO_ZERO_IN_DATE
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261402736">
      </a>
      <a class="indexterm" name="idm46045261401648">
      </a>
      <p>
       The
       <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
        <code class="literal">
         NO_ZERO_IN_DATE
        </code>
       </a>
       mode
            affects whether the server permits dates in which the year
            part is nonzero but the month or day part is 0. (This mode
            affects dates such as
       <code class="literal">
        '2010-00-01'
       </code>
       or
       <code class="literal">
        '2010-01-00'
       </code>
       , but not
       <code class="literal">
        '0000-00-00'
       </code>
       . To control whether the
            server permits
       <code class="literal">
        '0000-00-00'
       </code>
       , use the
       <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
        <code class="literal">
         NO_ZERO_DATE
        </code>
       </a>
       mode.) The
            effect of
       <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
        <code class="literal">
         NO_ZERO_IN_DATE
        </code>
       </a>
       also depends on whether strict SQL mode is enabled.
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If this mode is not enabled, dates with zero parts are
                permitted and inserts produce no warning.
         </p>
        </li>
        <li class="listitem">
         <p>
          If this mode is enabled, dates with zero parts are
                inserted as
          <code class="literal">
           '0000-00-00'
          </code>
          and produce
                a warning.
         </p>
        </li>
        <li class="listitem">
         <p>
          If this mode and strict mode are enabled, dates with
                zero parts are not permitted and inserts produce an
                error, unless
          <code class="literal">
           IGNORE
          </code>
          is given as
                well. For
          <code class="literal">
           INSERT IGNORE
          </code>
          and
          <code class="literal">
           UPDATE IGNORE
          </code>
          , dates with zero parts
                are inserted as
          <code class="literal">
           '0000-00-00'
          </code>
          and
                produce a warning.
         </p>
        </li>
       </ul>
      </div>
      <p>
       <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
        <code class="literal">
         NO_ZERO_IN_DATE
        </code>
       </a>
       is
            deprecated.
       <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
        <code class="literal">
         NO_ZERO_IN_DATE
        </code>
       </a>
       is not
            part of strict mode, but should be used in conjunction with
            strict mode and is enabled by default. A warning occurs if
       <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
        <code class="literal">
         NO_ZERO_IN_DATE
        </code>
       </a>
       is enabled
            without also enabling strict mode or vice versa.
      </p>
      <p>
       Because
       <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
        <code class="literal">
         NO_ZERO_IN_DATE
        </code>
       </a>
       is
            deprecated, you should expect it to be removed in a future
            MySQL release as a separate mode name and its effect
            included in the effects of strict SQL mode.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_only_full_group_by">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
        <code class="literal">
         ONLY_FULL_GROUP_BY
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261377344">
      </a>
      <a class="indexterm" name="idm46045261376304">
      </a>
      <a class="indexterm" name="idm46045261374816">
      </a>
      <p>
       Reject queries for which the select list,
       <code class="literal">
        HAVING
       </code>
       condition, or
       <code class="literal">
        ORDER
            BY
       </code>
       list refer to nonaggregated columns that are
            neither named in the
       <code class="literal">
        GROUP BY
       </code>
       clause nor
            are functionally dependent on (uniquely determined by)
       <code class="literal">
        GROUP BY
       </code>
       columns.
      </p>
      <p>
       A MySQL extension to standard SQL permits references in the
       <code class="literal">
        HAVING
       </code>
       clause to aliased expressions in
            the select list. The
       <code class="literal">
        HAVING
       </code>
       clause can
            refer to aliases regardless of whether
       <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
        <code class="literal">
         ONLY_FULL_GROUP_BY
        </code>
       </a>
       is
            enabled.
      </p>
      <p>
       For additional discussion and examples, see
       <a class="xref" href="group-by-handling.html" title="14.19.3 MySQL Handling of GROUP BY">
        Section 14.19.3, “MySQL Handling of GROUP BY”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_pad_char_to_full_length">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_pad_char_to_full_length">
        <code class="literal">
         PAD_CHAR_TO_FULL_LENGTH
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261363184">
      </a>
      <a class="indexterm" name="idm46045261362144">
      </a>
      <p>
       By default, trailing spaces are trimmed from
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR
        </code>
       </a>
       column values on
            retrieval. If
       <a class="link" href="sql-mode.html#sqlmode_pad_char_to_full_length">
        <code class="literal">
         PAD_CHAR_TO_FULL_LENGTH
        </code>
       </a>
       is
            enabled, trimming does not occur and retrieved
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         CHAR
        </code>
       </a>
       values are padded to
            their full length. This mode does not apply to
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       columns, for which
            trailing spaces are retained on retrieval.
      </p>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        <a class="link" href="sql-mode.html#sqlmode_pad_char_to_full_length">
         <code class="literal">
          PAD_CHAR_TO_FULL_LENGTH
         </code>
        </a>
        is deprecated. Expect it to be removed in a future version
              of MySQL.
       </p>
      </div>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48440011"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <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 punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.37 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>c1<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'xy'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> sql_mode <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> c1<span class="token punctuation">,</span> <span class="token function">CHAR_LENGTH</span><span class="token punctuation">(</span>c1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> c1   <span class="token punctuation">|</span> CHAR_LENGTH(c1) <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> xy   <span class="token punctuation">|</span>               2 <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">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> sql_mode <span class="token operator">=</span> <span class="token string">'PAD_CHAR_TO_FULL_LENGTH'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> c1<span class="token punctuation">,</span> <span class="token function">CHAR_LENGTH</span><span class="token punctuation">(</span>c1<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<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>
<span class="token output"><span class="token punctuation">|</span> c1         <span class="token punctuation">|</span> CHAR_LENGTH(c1) <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>
<span class="token output"><span class="token punctuation">|</span> xy         <span class="token punctuation">|</span>              10 <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>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_pipes_as_concat">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_pipes_as_concat">
        <code class="literal">
         PIPES_AS_CONCAT
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261344176">
      </a>
      <a class="indexterm" name="idm46045261343088">
      </a>
      <p>
       Treat
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         ||
        </code>
       </a>
       as a
            string concatenation operator (same as
       <a class="link" href="string-functions.html#function_concat">
        <code class="literal">
         CONCAT()
        </code>
       </a>
       ) rather than as a
            synonym for
       <a class="link" href="logical-operators.html#operator_or">
        <code class="literal">
         OR
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_real_as_float">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_real_as_float">
        <code class="literal">
         REAL_AS_FLOAT
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261334960">
      </a>
      <a class="indexterm" name="idm46045261333888">
      </a>
      <p>
       Treat
       <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>
       as a synonym for
       <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
        <code class="literal">
         FLOAT
        </code>
       </a>
       . By default, MySQL
            treats
       <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>
       as a synonym for
       <a class="link" href="floating-point-types.html" title="13.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE">
        <code class="literal">
         DOUBLE
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_strict_all_tables">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_strict_all_tables">
        <code class="literal">
         STRICT_ALL_TABLES
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261324496">
      </a>
      <a class="indexterm" name="idm46045261323456">
      </a>
      <p>
       Enable strict SQL mode for all storage engines. Invalid data
            values are rejected. For details, see
       <a class="xref" href="sql-mode.html#sql-mode-strict" title="Strict SQL Mode">
        Strict SQL Mode
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_strict_trans_tables">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_strict_trans_tables">
        <code class="literal">
         STRICT_TRANS_TABLES
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261318240">
      </a>
      <a class="indexterm" name="idm46045261317200">
      </a>
      <p>
       Enable strict SQL mode for transactional storage engines,
            and when possible for nontransactional storage engines. For
            details, see
       <a class="xref" href="sql-mode.html#sql-mode-strict" title="Strict SQL Mode">
        Strict SQL Mode
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_time_truncate_fractional">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_time_truncate_fractional">
        <code class="literal">
         TIME_TRUNCATE_FRACTIONAL
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261311920">
      </a>
      <a class="indexterm" name="idm46045261310832">
      </a>
      <p>
       Control whether rounding or truncation occurs when inserting
            a
       <a class="link" href="time.html" title="13.2.3 The TIME Type">
        <code class="literal">
         TIME
        </code>
       </a>
       ,
       <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
        <code class="literal">
         DATE
        </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 with a
            fractional seconds part into a column having the same type
            but fewer fractional digits. The default behavior is to use
            rounding. If this mode is enabled, truncation occurs
            instead. The following sequence of statements illustrates
            the difference:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14562979"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">,</span> tval <span class="token function">TIME</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> sql_mode<span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token punctuation">(</span>id<span class="token punctuation">,</span> tval<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1.55</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> sql_mode<span class="token operator">=</span><span class="token string">'TIME_TRUNCATE_FRACTIONAL'</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token punctuation">(</span>id<span class="token punctuation">,</span> tval<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1.55</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The resulting table contents look like this, where the first
            value has been subject to rounding and the second to
            truncation:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70526421"><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> id<span class="token punctuation">,</span> tval <span class="token keyword">FROM</span> t <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> id<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>
<span class="token output"><span class="token punctuation">|</span> id   <span class="token punctuation">|</span> tval       <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>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> 00:00:01.6 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> 00:00:01.5 <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></code></pre>
      </div>
      <p>
       See also
       <a class="xref" href="fractional-seconds.html" title="13.2.6 Fractional Seconds in Time Values">
        Section 13.2.6, “Fractional Seconds in Time Values”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="sql-mode-combo">
       </a>
       Combination SQL Modes
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following special modes are provided as shorthand for
        combinations of mode values from the preceding list.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a name="sqlmode_ansi">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_ansi">
        <code class="literal">
         ANSI
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261295520">
      </a>
      <a class="indexterm" name="idm46045261294448">
      </a>
      <p>
       Equivalent to
       <a class="link" href="sql-mode.html#sqlmode_real_as_float">
        <code class="literal">
         REAL_AS_FLOAT
        </code>
       </a>
       ,
       <a class="link" href="sql-mode.html#sqlmode_pipes_as_concat">
        <code class="literal">
         PIPES_AS_CONCAT
        </code>
       </a>
       ,
       <a class="link" href="sql-mode.html#sqlmode_ansi_quotes">
        <code class="literal">
         ANSI_QUOTES
        </code>
       </a>
       ,
       <a class="link" href="sql-mode.html#sqlmode_ignore_space">
        <code class="literal">
         IGNORE_SPACE
        </code>
       </a>
       , and
       <a class="link" href="sql-mode.html#sqlmode_only_full_group_by">
        <code class="literal">
         ONLY_FULL_GROUP_BY
        </code>
       </a>
       .
      </p>
      <p>
       <a class="link" href="sql-mode.html#sqlmode_ansi">
        <code class="literal">
         ANSI
        </code>
       </a>
       mode also causes the
            server to return an error for queries where a set function
       <em class="replaceable">
        <code>
         S
        </code>
       </em>
       with an outer reference
       <code class="literal">
        <em class="replaceable">
         <code>
          S
         </code>
        </em>
        (
        <em class="replaceable">
         <code>
          outer_ref
         </code>
        </em>
        )
       </code>
       cannot be aggregated in the outer query against which the
            outer reference has been resolved. This is such a query:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa4626556"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span>a <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">MAX</span><span class="token punctuation">(</span>t1<span class="token punctuation">.</span>b<span class="token punctuation">)</span> <span class="token keyword">FROM</span> t2 <span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Here,
       <a class="link" href="aggregate-functions.html#function_max">
        <code class="literal">
         MAX(t1.b)
        </code>
       </a>
       cannot
            aggregated in the outer query because it appears in the
       <code class="literal">
        WHERE
       </code>
       clause of that query. Standard SQL
            requires an error in this situation. If
       <a class="link" href="sql-mode.html#sqlmode_ansi">
        <code class="literal">
         ANSI
        </code>
       </a>
       mode is not enabled,
            the server treats
       <code class="literal">
        <em class="replaceable">
         <code>
          S
         </code>
        </em>
        (
        <em class="replaceable">
         <code>
          outer_ref
         </code>
        </em>
        )
       </code>
       in such queries the same way that it would interpret
       <code class="literal">
        <em class="replaceable">
         <code>
          S
         </code>
        </em>
        (
        <em class="replaceable">
         <code>
          const
         </code>
        </em>
        )
       </code>
       .
      </p>
      <p>
       See
       <a class="xref" href="compatibility.html" title="1.7 MySQL Standards Compliance">
        Section 1.7, “MySQL Standards Compliance”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       <a name="sqlmode_traditional">
       </a>
       <a class="link" href="sql-mode.html#sqlmode_traditional">
        <code class="literal">
         TRADITIONAL
        </code>
       </a>
      </p>
      <a class="indexterm" name="idm46045261271664">
      </a>
      <a class="indexterm" name="idm46045261270592">
      </a>
      <p>
       <a class="link" href="sql-mode.html#sqlmode_traditional">
        <code class="literal">
         TRADITIONAL
        </code>
       </a>
       is equivalent
            to
       <a class="link" href="sql-mode.html#sqlmode_strict_trans_tables">
        <code class="literal">
         STRICT_TRANS_TABLES
        </code>
       </a>
       ,
       <a class="link" href="sql-mode.html#sqlmode_strict_all_tables">
        <code class="literal">
         STRICT_ALL_TABLES
        </code>
       </a>
       ,
       <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
        <code class="literal">
         NO_ZERO_IN_DATE
        </code>
       </a>
       ,
       <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
        <code class="literal">
         NO_ZERO_DATE
        </code>
       </a>
       ,
       <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
        <code class="literal">
         ERROR_FOR_DIVISION_BY_ZERO
        </code>
       </a>
       ,
            and
       <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
        <code class="literal">
         NO_ENGINE_SUBSTITUTION
        </code>
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="sql-mode-strict">
       </a>
       Strict SQL Mode
      </h4>
     </div>
    </div>
   </div>
   <p>
    Strict mode controls how MySQL handles invalid or missing values
        in data-change statements such as
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    or
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    . A value can be invalid
        for several reasons. For example, it might have the wrong data
        type for the column, or it might be out of range. A value is
        missing when a new row to be inserted does not contain a value
        for a non-
    <code class="literal">
     NULL
    </code>
    column that has no explicit
    <code class="literal">
     DEFAULT
    </code>
    clause in its definition. (For a
    <code class="literal">
     NULL
    </code>
    column,
    <code class="literal">
     NULL
    </code>
    is
        inserted if the value is missing.) Strict mode also affects DDL
        statements such as
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    .
   </p>
   <p>
    If strict mode is not in effect, MySQL inserts adjusted values
        for invalid or missing values and produces warnings (see
    <a class="xref" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
     Section 15.7.7.42, “SHOW WARNINGS Statement”
    </a>
    ). In strict mode, you can
        produce this behavior by using
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT IGNORE
     </code>
    </a>
    or
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
        IGNORE
     </code>
    </a>
    .
   </p>
   <p>
    For statements such as
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    that do not change data, invalid values generate a warning in
        strict mode, not an error.
   </p>
   <p>
    Strict mode produces an error for attempts to create a key that
        exceeds the maximum key length. When strict mode is not enabled,
        this results in a warning and truncation of the key to the
        maximum key length.
   </p>
   <p>
    Strict mode does not affect whether foreign key constraints are
        checked.
    <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
     <code class="literal">
      foreign_key_checks
     </code>
    </a>
    can
        be used for that. (See
    <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
     Section 7.1.8, “Server System Variables”
    </a>
    .)
   </p>
   <p>
    Strict SQL mode is in effect if either
    <a class="link" href="sql-mode.html#sqlmode_strict_all_tables">
     <code class="literal">
      STRICT_ALL_TABLES
     </code>
    </a>
    or
    <a class="link" href="sql-mode.html#sqlmode_strict_trans_tables">
     <code class="literal">
      STRICT_TRANS_TABLES
     </code>
    </a>
    is
        enabled, although the effects of these modes differ somewhat:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       For transactional tables, an error occurs for invalid or
            missing values in a data-change statement when either
       <a class="link" href="sql-mode.html#sqlmode_strict_all_tables">
        <code class="literal">
         STRICT_ALL_TABLES
        </code>
       </a>
       or
       <a class="link" href="sql-mode.html#sqlmode_strict_trans_tables">
        <code class="literal">
         STRICT_TRANS_TABLES
        </code>
       </a>
       is
            enabled. The statement is aborted and rolled back.
      </p>
     </li>
     <li class="listitem">
      <p>
       For nontransactional tables, the behavior is the same for
            either mode if the bad value occurs in the first row to be
            inserted or updated: The statement is aborted and the table
            remains unchanged. If the statement inserts or modifies
            multiple rows and the bad value occurs in the second or
            later row, the result depends on which strict mode is
            enabled:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          For
          <a class="link" href="sql-mode.html#sqlmode_strict_all_tables">
           <code class="literal">
            STRICT_ALL_TABLES
           </code>
          </a>
          ,
                MySQL returns an error and ignores the rest of the rows.
                However, because the earlier rows have been inserted or
                updated, the result is a partial update. To avoid this,
                use single-row statements, which can be aborted without
                changing the table.
         </p>
        </li>
        <li class="listitem">
         <p>
          For
          <a class="link" href="sql-mode.html#sqlmode_strict_trans_tables">
           <code class="literal">
            STRICT_TRANS_TABLES
           </code>
          </a>
          ,
                MySQL converts an invalid value to the closest valid
                value for the column and inserts the adjusted value. If
                a value is missing, MySQL inserts the implicit default
                value for the column data type. In either case, MySQL
                generates a warning rather than an error and continues
                processing the statement. Implicit defaults are
                described in
          <a class="xref" href="data-type-defaults.html" title="13.6 Data Type Default Values">
           Section 13.6, “Data Type Default Values”
          </a>
          .
         </p>
        </li>
       </ul>
      </div>
     </li>
    </ul>
   </div>
   <p>
    Strict mode affects handling of division by zero, zero dates,
        and zeros in dates as follows:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Strict mode affects handling of division by zero, which
            includes
       <a class="link" href="mathematical-functions.html#function_mod">
        <code class="literal">
         MOD(
         <em class="replaceable">
          <code>
           N
          </code>
         </em>
         ,0)
        </code>
       </a>
       :
      </p>
      <p>
       For data-change operations
            (
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       ,
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       ):
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If strict mode is not enabled, division by zero inserts
          <code class="literal">
           NULL
          </code>
          and produces no warning.
         </p>
        </li>
        <li class="listitem">
         <p>
          If strict mode is enabled, division by zero produces an
                error, unless
          <code class="literal">
           IGNORE
          </code>
          is given as
                well. For
          <code class="literal">
           INSERT IGNORE
          </code>
          and
          <code class="literal">
           UPDATE IGNORE
          </code>
          , division by zero
                inserts
          <code class="literal">
           NULL
          </code>
          and produces a warning.
         </p>
        </li>
       </ul>
      </div>
      <p>
       For
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       , division by zero
            returns
       <code class="literal">
        NULL
       </code>
       . Enabling strict mode causes
            a warning to be produced as well.
      </p>
     </li>
     <li class="listitem">
      <p>
       Strict mode affects whether the server permits
       <code class="literal">
        '0000-00-00'
       </code>
       as a valid date:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If strict mode is not enabled,
          <code class="literal">
           '0000-00-00'
          </code>
          is permitted and inserts
                produce no warning.
         </p>
        </li>
        <li class="listitem">
         <p>
          If strict mode is enabled,
          <code class="literal">
           '0000-00-00'
          </code>
          is not permitted and
                inserts produce an error, unless
          <code class="literal">
           IGNORE
          </code>
          is given as well. For
          <code class="literal">
           INSERT IGNORE
          </code>
          and
          <code class="literal">
           UPDATE
                IGNORE
          </code>
          ,
          <code class="literal">
           '0000-00-00'
          </code>
          is
                permitted and inserts produce a warning.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       Strict mode affects whether the server permits dates in
            which the year part is nonzero but the month or day part is
            0 (dates such as
       <code class="literal">
        '2010-00-01'
       </code>
       or
       <code class="literal">
        '2010-01-00'
       </code>
       ):
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          If strict mode is not enabled, dates with zero parts are
                permitted and inserts produce no warning.
         </p>
        </li>
        <li class="listitem">
         <p>
          If strict mode is enabled, dates with zero parts are not
                permitted and inserts produce an error, unless
          <code class="literal">
           IGNORE
          </code>
          is given as well. For
          <code class="literal">
           INSERT IGNORE
          </code>
          and
          <code class="literal">
           UPDATE
                IGNORE
          </code>
          , dates with zero parts are inserted as
          <code class="literal">
           '0000-00-00'
          </code>
          (which is considered
                valid with
          <code class="literal">
           IGNORE
          </code>
          ) and produce a
                warning.
         </p>
        </li>
       </ul>
      </div>
     </li>
    </ul>
   </div>
   <p>
    For more information about strict mode with respect to
    <code class="literal">
     IGNORE
    </code>
    , see
    <a class="xref" href="sql-mode.html#ignore-strict-comparison" title="Comparison of the IGNORE Keyword and Strict SQL Mode">
     Comparison of the IGNORE Keyword and Strict SQL Mode
    </a>
    .
   </p>
   <p>
    Strict mode affects handling of division by zero, zero dates,
        and zeros in dates in conjunction with the
    <a class="link" href="sql-mode.html#sqlmode_error_for_division_by_zero">
     <code class="literal">
      ERROR_FOR_DIVISION_BY_ZERO
     </code>
    </a>
    ,
    <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
     <code class="literal">
      NO_ZERO_DATE
     </code>
    </a>
    , and
    <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
     <code class="literal">
      NO_ZERO_IN_DATE
     </code>
    </a>
    modes.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="ignore-strict-comparison">
       </a>
       Comparison of the IGNORE Keyword and Strict SQL Mode
      </h4>
     </div>
    </div>
   </div>
   <p>
    This section compares the effect on statement execution of the
    <code class="literal">
     IGNORE
    </code>
    keyword (which downgrades errors to
        warnings) and strict SQL mode (which upgrades warnings to
        errors). It describes which statements they affect, and which
        errors they apply to.
   </p>
   <p>
    The following table presents a summary comparison of statement
        behavior when the default is to produce an error versus a
        warning. An example of when the default is to produce an error
        is inserting a
    <code class="literal">
     NULL
    </code>
    into a
    <code class="literal">
     NOT
        NULL
    </code>
    column. An example of when the default is to
        produce a warning is inserting a value of the wrong data type
        into a column (such as inserting the string
    <code class="literal">
     'abc'
    </code>
    into an integer column).
   </p>
   <div class="informaltable">
    <table summary="Comparison of statement behavior when the default is to produce an error versus a warning.">
     <colgroup>
      <col style="width: 33%"/>
      <col style="width: 33%"/>
      <col style="width: 33%"/>
     </colgroup>
     <thead>
      <tr>
       <th scope="col">
        Operational Mode
       </th>
       <th scope="col">
        When Statement Default is Error
       </th>
       <th scope="col">
        When Statement Default is Warning
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <th scope="row">
        Without
        <code class="literal">
         IGNORE
        </code>
        or strict SQL mode
       </th>
       <td>
        Error
       </td>
       <td>
        Warning
       </td>
      </tr>
      <tr>
       <th scope="row">
        With
        <code class="literal">
         IGNORE
        </code>
       </th>
       <td>
        Warning
       </td>
       <td>
        Warning (same as without
        <code class="literal">
         IGNORE
        </code>
        or strict SQL mode)
       </td>
      </tr>
      <tr>
       <th scope="row">
        With strict SQL mode
       </th>
       <td>
        Error (same as without
        <code class="literal">
         IGNORE
        </code>
        or strict SQL mode)
       </td>
       <td>
        Error
       </td>
      </tr>
      <tr>
       <th scope="row">
        With
        <code class="literal">
         IGNORE
        </code>
        and strict SQL mode
       </th>
       <td>
        Warning
       </td>
       <td>
        Warning
       </td>
      </tr>
     </tbody>
    </table>
   </div>
   <p>
    One conclusion to draw from the table is that when the
    <code class="literal">
     IGNORE
    </code>
    keyword and strict SQL mode are both
        in effect,
    <code class="literal">
     IGNORE
    </code>
    takes precedence. This
        means that, although
    <code class="literal">
     IGNORE
    </code>
    and strict SQL
        mode can be considered to have opposite effects on error
        handling, they do not cancel when used together.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="xref" href="sql-mode.html#ignore-effect-on-execution" title="The Effect of IGNORE on Statement Execution">
        The Effect of IGNORE on Statement Execution
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="sql-mode.html#strict-sql-mode-effect-on-execution" title="The Effect of Strict SQL Mode on Statement Execution">
        The Effect of Strict SQL Mode on Statement Execution
       </a>
      </p>
     </li>
    </ul>
   </div>
   <h5>
    <a name="ignore-effect-on-execution">
    </a>
    The Effect of IGNORE on Statement Execution
   </h5>
   <p>
    Several statements in MySQL support an optional
    <code class="literal">
     IGNORE
    </code>
    keyword. This keyword causes the
        server to downgrade certain types of errors and generate
        warnings instead. For a multiple-row statement, downgrading an
        error to a warning may enable a row to be processed. Otherwise,
    <code class="literal">
     IGNORE
    </code>
    causes the statement to skip to the
        next row instead of aborting. (For nonignorable errors, an error
        occurs regardless of the
    <code class="literal">
     IGNORE
    </code>
    keyword.)
   </p>
   <p>
    Example: If the table
    <code class="literal">
     t
    </code>
    has a primary key
        column
    <code class="literal">
     i
    </code>
    containing unique values, attempting
        to insert the same value of
    <code class="literal">
     i
    </code>
    into multiple
        rows normally produces a duplicate-key error:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3674695"><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> t <span class="token punctuation">(</span>i <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token punctuation">(</span>i<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1062 (23000)<span class="token punctuation">:</span> Duplicate entry '1' for key 't.PRIMARY'</span></code></pre>
   </div>
   <p>
    With
    <code class="literal">
     IGNORE
    </code>
    , the row containing the duplicate
        key still is not inserted, but a warning occurs instead of an
        error:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8913614"><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">INSERT</span> <span class="token keyword">IGNORE</span> <span class="token keyword">INTO</span> t <span class="token punctuation">(</span>i<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected, 1 warning (0.01 sec)</span>
<span class="token output">Records: 2  Duplicates: 1  Warnings: 1</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>
<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>
<span class="token output"><span class="token punctuation">|</span> Warning <span class="token punctuation">|</span> 1062 <span class="token punctuation">|</span> Duplicate entry '1' for key 't.PRIMARY' <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">1 row in set (0.00 sec)</span></code></pre>
   </div>
   <p>
    Example: If the table
    <code class="literal">
     t2
    </code>
    has a
    <code class="literal">
     NOT
        NULL
    </code>
    column
    <code class="literal">
     id
    </code>
    , attempting to
        insert
    <code class="literal">
     NULL
    </code>
    produces an error in strict SQL
        mode:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42102891"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1048 (23000)<span class="token punctuation">:</span> Column 'id' cannot be null</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span>
<span class="token output">Empty set (0.00 sec)</span></code></pre>
   </div>
   <p>
    If the SQL mode is not strict,
    <code class="literal">
     IGNORE
    </code>
    causes
        the
    <code class="literal">
     NULL
    </code>
    to be inserted as the column
        implicit default (0 in this case), which enables the row to be
        handled without skipping it:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa98446054"><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">INSERT</span> <span class="token keyword">INTO</span> t2 <span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="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> id <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>
<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>  0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  3 <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></code></pre>
   </div>
   <p>
    These statements support the
    <code class="literal">
     IGNORE
    </code>
    keyword:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
            ... SELECT
        </code>
       </a>
       :
       <code class="literal">
        IGNORE
       </code>
       does not
            apply to the
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
        </code>
       </a>
       or
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       parts of the statement
            but to inserts into the table of rows produced by the
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       . Rows that duplicate
            an existing row on a unique key value are discarded.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       :
       <code class="literal">
        IGNORE
       </code>
       causes MySQL to ignore errors
            during the process of deleting rows.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       : With
       <code class="literal">
        IGNORE
       </code>
       , rows that duplicate an existing
            row on a unique key value are discarded. Rows set to values
            that would cause data conversion errors are set to the
            closest valid values instead.
      </p>
      <p>
       <a class="indexterm" name="idm46045261117168">
       </a>
       For partitioned tables where no partition matching a given
            value is found,
       <code class="literal">
        IGNORE
       </code>
       causes the insert
            operation to fail silently for rows containing the unmatched
            value.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
        <code class="literal">
         LOAD DATA
        </code>
       </a>
       ,
       <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
        <code class="literal">
         LOAD XML
        </code>
       </a>
       : With
       <code class="literal">
        IGNORE
       </code>
       , rows that duplicate an existing
            row on a unique key value are discarded.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       : With
       <code class="literal">
        IGNORE
       </code>
       , rows for which duplicate-key
            conflicts occur on a unique key value are not updated. Rows
            updated to values that would cause data conversion errors
            are updated to the closest valid values instead.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The
    <code class="literal">
     IGNORE
    </code>
    keyword applies to the following
        ignorable errors:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_bad_null_error" target="_top">
        <code class="literal">
         ER_BAD_NULL_ERROR
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_dup_entry" target="_top">
        <code class="literal">
         ER_DUP_ENTRY
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_dup_entry_with_key_name" target="_top">
        <code class="literal">
         ER_DUP_ENTRY_WITH_KEY_NAME
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_dup_key" target="_top">
        <code class="literal">
         ER_DUP_KEY
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_no_partition_for_given_value" target="_top">
        <code class="literal">
         ER_NO_PARTITION_FOR_GIVEN_VALUE
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_no_partition_for_given_value_silent" target="_top">
        <code class="literal">
         ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_no_referenced_row_2" target="_top">
        <code class="literal">
         ER_NO_REFERENCED_ROW_2
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_row_does_not_match_given_partition_set" target="_top">
        <code class="literal">
         ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_row_is_referenced_2" target="_top">
        <code class="literal">
         ER_ROW_IS_REFERENCED_2
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_subquery_no_1_row" target="_top">
        <code class="literal">
         ER_SUBQUERY_NO_1_ROW
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_view_check_failed" target="_top">
        <code class="literal">
         ER_VIEW_CHECK_FAILED
        </code>
       </a>
      </p>
     </li>
    </ul>
   </div>
   <h5>
    <a name="strict-sql-mode-effect-on-execution">
    </a>
    The Effect of Strict SQL Mode on Statement Execution
   </h5>
   <p>
    The MySQL server can operate in different SQL modes, and can
        apply these modes differently for different clients, depending
        on the value of the
    <a class="link" href="server-system-variables.html#sysvar_sql_mode">
     <code class="literal">
      sql_mode
     </code>
    </a>
    system variable. In
    <span class="quote">
     “
     <span class="quote">
      strict
     </span>
     ”
    </span>
    SQL mode, the server
        upgrades certain warnings to errors.
   </p>
   <p>
    For example, in non-strict SQL mode, inserting the string
    <code class="literal">
     'abc'
    </code>
    into an integer column results in
        conversion of the value to 0 and a warning:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75191061"><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> sql_mode <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token punctuation">(</span>i<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'abc'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected, 1 warning (0.01 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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> 1366 <span class="token punctuation">|</span> Incorrect integer value: 'abc' for column 'i' at row 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 class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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></code></pre>
   </div>
   <p>
    In strict SQL mode, the invalid value is rejected with an error:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25710482"><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> sql_mode <span class="token operator">=</span> <span class="token string">'STRICT_ALL_TABLES'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token punctuation">(</span>i<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'abc'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1366 (HY000)<span class="token punctuation">:</span> Incorrect integer value<span class="token punctuation">:</span> 'abc' for column 'i' at row 1</span></code></pre>
   </div>
   <p>
    For more information about possible settings of the
    <a class="link" href="server-system-variables.html#sysvar_sql_mode">
     <code class="literal">
      sql_mode
     </code>
    </a>
    system variable, see
    <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
     Section 7.1.11, “Server SQL Modes”
    </a>
    .
   </p>
   <p>
    Strict SQL mode applies to the following statements under
        conditions for which some value might be out of range or an
        invalid row is inserted into or deleted from a table:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
            ... SELECT
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
        <code class="literal">
         DELETE
        </code>
       </a>
       (both single table and
            multiple table)
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
        <code class="literal">
         LOAD DATA
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
        <code class="literal">
         LOAD XML
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
            SLEEP()
        </code>
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       (both single table and
            multiple table)
      </p>
     </li>
    </ul>
   </div>
   <p>
    Within stored programs, individual statements of the types just
        listed execute in strict SQL mode if the program was defined
        while strict mode was in effect.
   </p>
   <p>
    Strict SQL mode applies to the following errors, which represent
        a class of errors in which an input value is either invalid or
        missing. A value is invalid if it has the wrong data type for
        the column or might be out of range. A value is missing if a new
        row to be inserted does not contain a value for a
    <code class="literal">
     NOT
        NULL
    </code>
    column that has no explicit
    <code class="literal">
     DEFAULT
    </code>
    clause in its definition.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa50965071"><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-simple">ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED</code></pre>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     Because continued MySQL development defines new errors, there
          may be errors not in the preceding list to which strict SQL
          mode applies.
    </p>
   </div>
  </div>
 </div>
 <br/>
</div>
