<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="compatibility">
      </a>
      1.7 MySQL Standards Compliance
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="extensions-to-ansi.html">
       1.7.1 MySQL Extensions to Standard SQL
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="differences-from-ansi.html">
       1.7.2 MySQL Differences from Standard SQL
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="constraints.html">
       1.7.3 How MySQL Deals with Constraints
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045333578368">
  </a>
  <a class="indexterm" name="idm46045333576912">
  </a>
  <a class="indexterm" name="idm46045333575840">
  </a>
  <a class="indexterm" name="idm46045333574352">
  </a>
  <a class="indexterm" name="idm46045333572864">
  </a>
  <p>
   This section describes how MySQL relates to the ANSI/ISO SQL
      standards. MySQL Server has many extensions to the SQL standard,
      and here you can find out what they are and how to use them. You
      can also find information about functionality missing from MySQL
      Server, and how to work around some of the differences.
  </p>
  <p>
   The SQL standard has been evolving since 1986 and several versions
      exist. In this manual,
   <span class="quote">
    “
    <span class="quote">
     SQL-92
    </span>
    ”
   </span>
   refers to the
      standard released in 1992.
   <span class="quote">
    “
    <span class="quote">
     SQL:1999
    </span>
    ”
   </span>
   ,
   <span class="quote">
    “
    <span class="quote">
     SQL:2003
    </span>
    ”
   </span>
   ,
   <span class="quote">
    “
    <span class="quote">
     SQL:2008
    </span>
    ”
   </span>
   , and
   <span class="quote">
    “
    <span class="quote">
     SQL:2011
    </span>
    ”
   </span>
   refer to the versions of the standard
      released in the corresponding years, with the last being the most
      recent version. We use the phrase
   <span class="quote">
    “
    <span class="quote">
     the SQL standard
    </span>
    ”
   </span>
   or
   <span class="quote">
    “
    <span class="quote">
     standard SQL
    </span>
    ”
   </span>
   to mean the current version of the
      SQL Standard at any time.
  </p>
  <p>
   One of our main goals with the product is to continue to work
      toward compliance with the SQL standard, but without sacrificing
      speed or reliability. We are not afraid to add extensions to SQL
      or support for non-SQL features if this greatly increases the
      usability of MySQL Server for a large segment of our user base.
      The
   <a class="link" href="handler.html" title="15.2.5 HANDLER Statement">
    <code class="literal">
     HANDLER
    </code>
   </a>
   interface is an example
      of this strategy. See
   <a class="xref" href="handler.html" title="15.2.5 HANDLER Statement">
    Section 15.2.5, “HANDLER Statement”
   </a>
   .
  </p>
  <p>
   We continue to support transactional and nontransactional
      databases to satisfy both mission-critical 24/7 usage and heavy
      Web or logging usage.
  </p>
  <p>
   MySQL Server was originally designed to work with medium-sized
      databases (10-100 million rows, or about 100MB per table) on small
      computer systems. Today MySQL Server handles terabyte-sized
      databases.
  </p>
  <p>
   We are not targeting real-time support, although MySQL replication
      capabilities offer significant functionality.
  </p>
  <p>
   MySQL supports ODBC levels 0 to 3.51.
  </p>
  <p>
   MySQL supports high-availability database clustering using the
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDBCLUSTER
    </code>
   </a>
   storage engine. See
   <a class="xref" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    Chapter 25,
    <i>
     MySQL NDB Cluster 8.4
    </i>
   </a>
   .
  </p>
  <p>
   We implement XML functionality which supports most of the W3C
      XPath standard. See
   <a class="xref" href="xml-functions.html" title="14.11 XML Functions">
    Section 14.11, “XML Functions”
   </a>
   .
  </p>
  <p>
   MySQL supports a native JSON data type as defined by RFC 7159, and
      based on the ECMAScript standard (ECMA-262). See
   <a class="xref" href="json.html" title="13.5 The JSON Data Type">
    Section 13.5, “The JSON Data Type”
   </a>
   . MySQL also implements a subset of the
      SQL/JSON functions specified by a pre-publication draft of the
      SQL:2016 standard; see
   <a class="xref" href="json-functions.html" title="14.17 JSON Functions">
    Section 14.17, “JSON Functions”
   </a>
   , for more
      information.
  </p>
  <h3>
   <a name="idm46045333555408">
   </a>
   Selecting SQL Modes
  </h3>
  <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>
  <p>
   For more information on setting the SQL mode, see
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .
  </p>
  <h3>
   <a name="idm46045333550848">
   </a>
   Running MySQL in ANSI Mode
  </h3>
  <a class="indexterm" name="idm46045333550128">
  </a>
  <a class="indexterm" name="idm46045333548640">
  </a>
  <p>
   To run MySQL Server in ANSI mode, start
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   with the
   <a class="link" href="server-options.html#option_mysqld_ansi">
    <code class="option">
     --ansi
    </code>
   </a>
   option. Running the
      server in ANSI mode is the same as starting it with the following
      options:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa36083667"><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-terminal"><span class="token constant">--transaction-isolation</span><span class="token attr-value"><span class="token punctuation">=</span>SERIALIZABLE</span> <span class="token constant">--sql-mode</span><span class="token attr-value"><span class="token punctuation">=</span>ANSI</span></code></pre>
  </div>
  <p>
   To achieve the same effect at runtime, execute these two
      statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66808167"><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> <span class="token keyword">TRANSACTION</span> <span class="token keyword">ISOLATION</span> <span class="token keyword">LEVEL</span> <span class="token keyword">SERIALIZABLE</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> sql_mode <span class="token operator">=</span> <span class="token string">'ANSI'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can see that setting the
   <a class="link" href="server-system-variables.html#sysvar_sql_mode">
    <code class="literal">
     sql_mode
    </code>
   </a>
   system variable to
   <code class="literal">
    'ANSI'
   </code>
   enables all SQL mode options that are
      relevant for ANSI mode as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55954343"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> sql_mode<span class="token operator">=</span><span class="token string">'ANSI'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@GLOBAL.sql_mode</span><span class="token punctuation">;</span>
<span class="token prompt">        -&gt;</span> <span class="token string">'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI'</span></code></pre>
  </div>
  <p>
   Running the server in ANSI mode with
   <a class="link" href="server-options.html#option_mysqld_ansi">
    <code class="option">
     --ansi
    </code>
   </a>
   is not quite the same as
      setting the SQL mode to
   <code class="literal">
    'ANSI'
   </code>
   because the
   <a class="link" href="server-options.html#option_mysqld_ansi">
    <code class="option">
     --ansi
    </code>
   </a>
   option also sets the
      transaction isolation level.
  </p>
  <p>
   See
   <a class="xref" href="server-options.html" title="7.1.7 Server Command Options">
    Section 7.1.7, “Server Command Options”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
