<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="sql-prepared-statements">
      </a>
      15.5 Prepared Statements
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="prepare.html">
       15.5.1 PREPARE Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="execute.html">
       15.5.2 EXECUTE Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="deallocate-prepare.html">
       15.5.3 DEALLOCATE PREPARE Statement
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045176785728">
  </a>
  <a class="indexterm" name="idm46045176784656">
  </a>
  <a class="indexterm" name="idm46045176783584">
  </a>
  <a class="indexterm" name="idm46045176782512">
  </a>
  <p>
   MySQL 8.4 provides support for server-side prepared
    statements. This support takes advantage of the efficient
    client/server binary protocol. Using prepared statements with
    placeholders for parameter values has the following benefits:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Less overhead for parsing the statement each time it is
        executed. Typically, database applications process large volumes
        of almost-identical statements, with only changes to literal or
        variable values in clauses such as
      <code class="literal">
       WHERE
      </code>
      for
        queries and deletes,
      <code class="literal">
       SET
      </code>
      for updates, and
      <code class="literal">
       VALUES
      </code>
      for inserts.
     </p>
    </li>
    <li class="listitem">
     <p>
      Protection against SQL injection attacks. The parameter values
        can contain unescaped SQL quote and delimiter characters.
     </p>
     <a class="indexterm" name="idm46045176776528">
     </a>
     <a class="indexterm" name="idm46045176775456">
     </a>
    </li>
   </ul>
  </div>
  <p>
   The following sections provide an overview of the characteristics of
    prepared statements:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="sql-prepared-statements.html#prepared-statements-in-applications" title="Prepared Statements in Application Programs">
       Prepared Statements in Application Programs
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="sql-prepared-statements.html#prepared-statements-in-scripts" title="Prepared Statements in SQL Scripts">
       Prepared Statements in SQL Scripts
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="sql-prepared-statements.html#prepared-statement-types" title="PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements">
       PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="sql-prepared-statements.html#prepared-statements-permitted" title="SQL Syntax Permitted in Prepared Statements">
       SQL Syntax Permitted in Prepared Statements
      </a>
     </p>
    </li>
   </ul>
  </div>
  <h3>
   <a name="prepared-statements-in-applications">
   </a>
   Prepared Statements in Application Programs
  </h3>
  <p>
   You can use server-side prepared statements through client
    programming interfaces, including the
   <a class="ulink" href="/doc/c-api/8.4/en/" target="_top">
    MySQL C
    API client library
   </a>
   for C programs,
   <a class="ulink" href="/doc/connector-j/en/" target="_top">
    MySQL Connector/J
   </a>
   for Java
    programs, and
   <a class="ulink" href="/doc/connector-net/en/" target="_top">
    MySQL Connector/NET
   </a>
   for
    programs using .NET technologies. For example, the C API provides a
    set of function calls that make up its prepared statement API. See
   <a class="ulink" href="/doc/c-api/8.4/en/c-api-prepared-statement-interface.html" target="_top">
    C API Prepared Statement Interface
   </a>
   . Other language
    interfaces can provide support for prepared statements that use the
    binary protocol by linking in the C client library, one example
    being the
   <a class="ulink" href="http://php.net/mysqli" target="_blank">
    <code class="literal">
     mysqli
    </code>
    extension
   </a>
   , available in PHP 5.0 and later.
  </p>
  <h3>
   <a name="prepared-statements-in-scripts">
   </a>
   Prepared Statements in SQL Scripts
  </h3>
  <p>
   An alternative SQL interface to prepared statements is available.
    This interface is not as efficient as using the binary protocol
    through a prepared statement API, but requires no programming
    because it is available directly at the SQL level:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You can use it when no programming interface is available to
        you.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can use it from any program that can send SQL statements to
        the server to be executed, such as the
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      client program.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can use it even if the client is using an old version of the
        client library.
     </p>
    </li>
   </ul>
  </div>
  <p>
   SQL syntax for prepared statements is intended to be used for
    situations such as these:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To test how prepared statements work in your application before
        coding it.
     </p>
    </li>
    <li class="listitem">
     <p>
      To use prepared statements when you do not have access to a
        programming API that supports them.
     </p>
    </li>
    <li class="listitem">
     <p>
      To interactively troubleshoot application issues with prepared
        statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      To create a test case that reproduces a problem with prepared
        statements, so that you can file a bug report.
     </p>
    </li>
   </ul>
  </div>
  <h3>
   <a name="prepared-statement-types">
   </a>
   PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements
  </h3>
  <p>
   SQL syntax for prepared statements is based on three SQL statements:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="prepare.html" title="15.5.1 PREPARE Statement">
       <code class="literal">
        PREPARE
       </code>
      </a>
      prepares a statement for
        execution (see
      <a class="xref" href="prepare.html" title="15.5.1 PREPARE Statement">
       Section 15.5.1, “PREPARE Statement”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="execute.html" title="15.5.2 EXECUTE Statement">
       <code class="literal">
        EXECUTE
       </code>
      </a>
      executes a prepared
        statement (see
      <a class="xref" href="execute.html" title="15.5.2 EXECUTE Statement">
       Section 15.5.2, “EXECUTE Statement”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="deallocate-prepare.html" title="15.5.3 DEALLOCATE PREPARE Statement">
       <code class="literal">
        DEALLOCATE PREPARE
       </code>
      </a>
      releases a
        prepared statement (see
      <a class="xref" href="deallocate-prepare.html" title="15.5.3 DEALLOCATE PREPARE Statement">
       Section 15.5.3, “DEALLOCATE PREPARE Statement”
      </a>
      ).
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following examples show two equivalent ways of preparing a
    statement that computes the hypotenuse of a triangle given the
    lengths of the two sides.
  </p>
  <p>
   The first example shows how to create a prepared statement by using
    a string literal to supply the text of the statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63366899"><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">PREPARE</span> stmt1 <span class="token keyword">FROM</span> <span class="token string">'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@a</span> <span class="token operator">=</span> <span class="token number">3</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@b</span> <span class="token operator">=</span> <span class="token number">4</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">EXECUTE</span> stmt1 <span class="token keyword">USING</span> <span class="token variable">@a</span><span class="token punctuation">,</span> <span class="token variable">@b</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>
<span class="token output"><span class="token punctuation">|</span> hypotenuse <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>
<span class="token output"><span class="token punctuation">|</span>          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>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">DEALLOCATE</span> <span class="token keyword">PREPARE</span> stmt1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The second example is similar, but supplies the text of the
    statement as a user variable:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11220015"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@s</span> <span class="token operator">=</span> <span class="token string">'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">PREPARE</span> stmt2 <span class="token keyword">FROM</span> <span class="token variable">@s</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@a</span> <span class="token operator">=</span> <span class="token number">6</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@b</span> <span class="token operator">=</span> <span class="token number">8</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">EXECUTE</span> stmt2 <span class="token keyword">USING</span> <span class="token variable">@a</span><span class="token punctuation">,</span> <span class="token variable">@b</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>
<span class="token output"><span class="token punctuation">|</span> hypotenuse <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>
<span class="token output"><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>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">DEALLOCATE</span> <span class="token keyword">PREPARE</span> stmt2<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Here is an additional example that demonstrates how to choose the
    table on which to perform a query at runtime, by storing the name of
    the table as a user variable:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80249"><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">USE</span> test<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 punctuation">(</span>a <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> t1 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">8</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">80</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@table</span> <span class="token operator">=</span> <span class="token string">'t1'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@s</span> <span class="token operator">=</span> <span class="token function">CONCAT</span><span class="token punctuation">(</span><span class="token string">'SELECT * FROM '</span><span class="token punctuation">,</span> <span class="token variable">@table</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">PREPARE</span> stmt3 <span class="token keyword">FROM</span> <span class="token variable">@s</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">EXECUTE</span> stmt3<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> a  <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>  4 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  8 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 11 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 32 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 80 <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 prompt">mysql&gt;</span> <span class="token keyword">DEALLOCATE</span> <span class="token keyword">PREPARE</span> stmt3<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   A prepared statement is specific to the session in which it was
    created. If you terminate a session without deallocating a
    previously prepared statement, the server deallocates it
    automatically.
  </p>
  <p>
   A prepared statement is also global to the session. If you create a
    prepared statement within a stored routine, it is not deallocated
    when the stored routine ends.
  </p>
  <p>
   To guard against too many prepared statements being created
    simultaneously, set the
   <a class="link" href="server-system-variables.html#sysvar_max_prepared_stmt_count">
    <code class="literal">
     max_prepared_stmt_count
    </code>
   </a>
   system
    variable. To prevent the use of prepared statements, set the value
    to 0.
  </p>
  <h3>
   <a name="prepared-statements-permitted">
   </a>
   SQL Syntax Permitted in Prepared Statements
  </h3>
  <p>
   The following SQL statements can be used as prepared statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88896382"><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">ALTER</span> {<span class="token keyword">INSTANCE</span> <span class="token operator">|</span> <span class="token keyword">TABLE</span> <span class="token operator">|</span> <span class="token keyword">USER</span>}
<span class="token keyword">ANALYZE</span>
<span class="token keyword">CALL</span>
<span class="token keyword">CHANGE</span> {<span class="token keyword">REPLICATION</span> <span class="token keyword">SOURCE</span> <span class="token keyword">TO</span> <span class="token operator">|</span> <span class="token keyword">REPLICATION</span> <span class="token keyword">FILTER</span>}
<span class="token keyword">CHECKSUM</span>
<span class="token keyword">COMMIT</span>
{<span class="token keyword">CREATE</span> <span class="token operator">|</span> <span class="token keyword">DROP</span>} <span class="token keyword">INDEX</span>
{<span class="token keyword">CREATE</span> <span class="token operator">|</span> <span class="token keyword">DROP</span> <span class="token operator">|</span> <span class="token keyword">RENAME</span>} <span class="token keyword">DATABASE</span>
{<span class="token keyword">CREATE</span> <span class="token operator">|</span> <span class="token keyword">DROP</span> <span class="token operator">|</span> <span class="token keyword">RENAME</span>} <span class="token keyword">TABLE</span>
{<span class="token keyword">CREATE</span> <span class="token operator">|</span> <span class="token keyword">DROP</span> <span class="token operator">|</span> <span class="token keyword">RENAME</span>} <span class="token keyword">USER</span>
<span class="token keyword">DEALLOCATE</span> <span class="token keyword">PREPARE</span>
<span class="token keyword">DROP</span> <span class="token keyword">VIEW</span>
<span class="token keyword">DELETE</span>
<span class="token keyword">DO</span>
<span class="token keyword">EXECUTE</span>
<span class="token keyword">FLUSH</span>
<span class="token keyword">GRANT</span> {<span class="token keyword">ROLE</span>}
<span class="token keyword">INSERT</span>
<span class="token keyword">INSTALL</span> <span class="token keyword">PLUGIN</span>
<span class="token keyword">KILL</span>
<span class="token keyword">OPTIMIZE</span>
<span class="token keyword">PREPARE</span>
<span class="token keyword">REPAIR</span> <span class="token keyword">TABLE</span>
<span class="token keyword">REPLACE</span>
<span class="token keyword">REPLICA</span> {<span class="token keyword">START</span> <span class="token operator">|</span> <span class="token keyword">STOP</span>}
<span class="token keyword">RESET</span>
<span class="token keyword">REVOKE</span> {<span class="token keyword">ALL</span> <span class="token operator">|</span> <span class="token keyword">ROLE</span>}
<span class="token keyword">SELECT</span>
<span class="token keyword">SET</span> <span class="token keyword">ROLE</span>
<span class="token keyword">SHOW</span> {<span class="token keyword">BINLOG</span> <span class="token keyword">EVENTS</span> <span class="token operator">|</span> <span class="token datatype">BINARY</span> <span class="token keyword">LOGS</span> <span class="token operator">|</span> <span class="token datatype">BINARY</span> LOG <span class="token keyword">STATUS</span> <span class="token operator">|</span> <span class="token keyword">CHARACTER</span> SETS <span class="token operator">|</span> COLLATIONS <span class="token operator">|</span> <span class="token keyword">DATABASES</span> <span class="token operator">|</span> <span class="token keyword">ENGINES</span> <span class="token operator">|</span>
      <span class="token keyword">ERRORS</span> <span class="token operator">|</span> <span class="token keyword">EVENTS</span> <span class="token operator">|</span> <span class="token keyword">FIELDS</span> <span class="token operator">|</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">CODE</span> <span class="token operator">|</span> <span class="token keyword">FUNCTION</span> <span class="token keyword">STATUS</span> <span class="token operator">|</span> <span class="token keyword">GRANTS</span> <span class="token operator">|</span> <span class="token keyword">KEYS</span> <span class="token operator">|</span> <span class="token keyword">OPEN</span> <span class="token keyword">TABLES</span> <span class="token operator">|</span>
      <span class="token keyword">PLUGINS</span> <span class="token operator">|</span> <span class="token keyword">PRIVILEGES</span> <span class="token operator">|</span> <span class="token keyword">PROCEDURE</span> <span class="token keyword">CODE</span> <span class="token operator">|</span> <span class="token keyword">PROCEDURE</span> <span class="token keyword">STATUS</span> <span class="token operator">|</span> <span class="token keyword">PROCESSLIST</span> <span class="token operator">|</span> <span class="token keyword">PROFILE</span> <span class="token operator">|</span> <span class="token keyword">PROFILES</span> <span class="token operator">|</span> 
      <span class="token keyword">RELAYLOG</span> <span class="token keyword">EVENTS</span> <span class="token operator">|</span> <span class="token keyword">REPLICAS</span> <span class="token operator">|</span> <span class="token keyword">REPLICA</span> <span class="token keyword">STATUS</span> <span class="token operator">|</span> <span class="token keyword">STATUS</span> <span class="token operator">|</span> <span class="token keyword">PROCEDURE</span> <span class="token keyword">STATUS</span> <span class="token operator">|</span> <span class="token keyword">TABLE</span> <span class="token keyword">STATUS</span> <span class="token operator">|</span> <span class="token keyword">TABLES</span> <span class="token operator">|</span> 
      <span class="token keyword">TRIGGERS</span> <span class="token operator">|</span> <span class="token keyword">VARIABLES</span> <span class="token operator">|</span> <span class="token keyword">WARNINGS</span>}
<span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> { <span class="token keyword">DATABASE</span> <span class="token operator">|</span> <span class="token keyword">EVENT</span> <span class="token operator">|</span> <span class="token keyword">FUNCTION</span> <span class="token operator">|</span> <span class="token keyword">PROCEDURE</span> <span class="token operator">|</span> <span class="token keyword">TABLE</span> <span class="token operator">|</span> <span class="token keyword">TRIGGER</span> <span class="token operator">|</span> <span class="token keyword">USER</span> <span class="token operator">|</span> <span class="token keyword">VIEW</span>}
<span class="token keyword">TRUNCATE</span>
<span class="token keyword">UNINSTALL</span> <span class="token keyword">PLUGIN</span>
<span class="token keyword">UPDATE</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    <code class="literal">
     CREATE TABLE ... START TRANSACTION
    </code>
    is not
      supported in prepared statements.
   </p>
  </div>
  <p>
   Other statements are not supported.
  </p>
  <p>
   For compliance with the SQL standard, which states that diagnostics
    statements are not preparable, MySQL does not support the following
    as prepared statements:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       SHOW COUNT(*) WARNINGS
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SHOW COUNT(*) ERRORS
      </code>
     </p>
    </li>
    <li class="listitem">
     <p>
      Statements containing any reference to the
      <a class="link" href="server-system-variables.html#sysvar_warning_count">
       <code class="literal">
        warning_count
       </code>
      </a>
      or
      <a class="link" href="server-system-variables.html#sysvar_error_count">
       <code class="literal">
        error_count
       </code>
      </a>
      system variable.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Generally, statements not permitted in SQL prepared statements are
    also not permitted in stored programs. Exceptions are noted in
   <a class="xref" href="stored-program-restrictions.html" title="27.8 Restrictions on Stored Programs">
    Section 27.8, “Restrictions on Stored Programs”
   </a>
   .
  </p>
  <p>
   Metadata changes to tables or views referred to by prepared
    statements are detected and cause automatic repreparation of the
    statement when it is next executed. For more information, see
   <a class="xref" href="statement-caching.html" title="10.10.3 Caching of Prepared Statements and Stored Programs">
    Section 10.10.3, “Caching of Prepared Statements and Stored Programs”
   </a>
   .
  </p>
  <p>
   Placeholders can be used for the arguments of the
   <code class="literal">
    LIMIT
   </code>
   clause when using prepared statements. See
   <a class="xref" href="select.html" title="15.2.13 SELECT Statement">
    Section 15.2.13, “SELECT Statement”
   </a>
   .
  </p>
  <p>
   In prepared
   <a class="link" href="call.html" title="15.2.1 CALL Statement">
    <code class="literal">
     CALL
    </code>
   </a>
   statements used with
   <a class="link" href="prepare.html" title="15.5.1 PREPARE Statement">
    <code class="literal">
     PREPARE
    </code>
   </a>
   and
   <a class="link" href="execute.html" title="15.5.2 EXECUTE Statement">
    <code class="literal">
     EXECUTE
    </code>
   </a>
   , placeholder support for
   <code class="literal">
    OUT
   </code>
   and
   <code class="literal">
    INOUT
   </code>
   parameters is
    available beginning with MySQL 8.4. See
   <a class="xref" href="call.html" title="15.2.1 CALL Statement">
    Section 15.2.1, “CALL Statement”
   </a>
   , for an example and a workaround for earlier
    versions. Placeholders can be used for
   <code class="literal">
    IN
   </code>
   parameters regardless of version.
  </p>
  <p>
   SQL syntax for prepared statements cannot be used in nested fashion.
    That is, a statement passed to
   <a class="link" href="prepare.html" title="15.5.1 PREPARE Statement">
    <code class="literal">
     PREPARE
    </code>
   </a>
   cannot itself be a
   <a class="link" href="prepare.html" title="15.5.1 PREPARE Statement">
    <code class="literal">
     PREPARE
    </code>
   </a>
   ,
   <a class="link" href="execute.html" title="15.5.2 EXECUTE Statement">
    <code class="literal">
     EXECUTE
    </code>
   </a>
   , or
   <a class="link" href="deallocate-prepare.html" title="15.5.3 DEALLOCATE PREPARE Statement">
    <code class="literal">
     DEALLOCATE PREPARE
    </code>
   </a>
   statement.
  </p>
  <p>
   SQL syntax for prepared statements is distinct from using prepared
    statement API calls. For example, you cannot use the
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-stmt-prepare.html" target="_top">
    <code class="literal">
     mysql_stmt_prepare()
    </code>
   </a>
   C API function
    to prepare a
   <a class="link" href="prepare.html" title="15.5.1 PREPARE Statement">
    <code class="literal">
     PREPARE
    </code>
   </a>
   ,
   <a class="link" href="execute.html" title="15.5.2 EXECUTE Statement">
    <code class="literal">
     EXECUTE
    </code>
   </a>
   , or
   <a class="link" href="deallocate-prepare.html" title="15.5.3 DEALLOCATE PREPARE Statement">
    <code class="literal">
     DEALLOCATE PREPARE
    </code>
   </a>
   statement.
  </p>
  <p>
   SQL syntax for prepared statements can be used within stored
    procedures, but not in stored functions or triggers. However, a
    cursor cannot be used for a dynamic statement that is prepared and
    executed with
   <a class="link" href="prepare.html" title="15.5.1 PREPARE Statement">
    <code class="literal">
     PREPARE
    </code>
   </a>
   and
   <a class="link" href="execute.html" title="15.5.2 EXECUTE Statement">
    <code class="literal">
     EXECUTE
    </code>
   </a>
   . The statement for a cursor
    is checked at cursor creation time, so the statement cannot be
    dynamic.
  </p>
  <p>
   SQL syntax for prepared statements does not support multi-statements
    (that is, multiple statements within a single string separated by
   <code class="literal">
    ;
   </code>
   characters).
  </p>
  <p>
   To write C programs that use the
   <a class="link" href="call.html" title="15.2.1 CALL Statement">
    <code class="literal">
     CALL
    </code>
   </a>
   SQL statement to execute stored procedures that contain prepared
    statements, the
   <code class="literal">
    CLIENT_MULTI_RESULTS
   </code>
   flag must be
    enabled. This is because each
   <a class="link" href="call.html" title="15.2.1 CALL Statement">
    <code class="literal">
     CALL
    </code>
   </a>
   returns a result to indicate the call status, in addition to any
    result sets that might be returned by statements executed within the
    procedure.
  </p>
  <p>
   <code class="literal">
    CLIENT_MULTI_RESULTS
   </code>
   can be enabled when you call
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-connect.html" target="_top">
    <code class="literal">
     mysql_real_connect()
    </code>
   </a>
   , either
    explicitly by passing the
   <code class="literal">
    CLIENT_MULTI_RESULTS
   </code>
   flag itself, or implicitly by passing
   <code class="literal">
    CLIENT_MULTI_STATEMENTS
   </code>
   (which also enables
   <code class="literal">
    CLIENT_MULTI_RESULTS
   </code>
   ). For additional
    information, see
   <a class="xref" href="call.html" title="15.2.1 CALL Statement">
    Section 15.2.1, “CALL Statement”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
