<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="user-variables">
      </a>
      11.4 User-Defined Variables
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045218692656">
  </a>
  <a class="indexterm" name="idm46045218691168">
  </a>
  <a class="indexterm" name="idm46045218690096">
  </a>
  <a class="indexterm" name="idm46045218688608">
  </a>
  <a class="indexterm" name="idm46045218687536">
  </a>
  <a class="indexterm" name="idm46045218686464">
  </a>
  <p>
   You can store a value in a user-defined variable in one statement
      and refer to it later in another statement. This enables you to
      pass values from one statement to another.
  </p>
  <p>
   User variables are written as
   <code class="literal">
    @
    <em class="replaceable">
     <code>
      var_name
     </code>
    </em>
   </code>
   , where the
      variable name
   <em class="replaceable">
    <code>
     var_name
    </code>
   </em>
   consists of
      alphanumeric characters,
   <code class="literal">
    .
   </code>
   ,
   <code class="literal">
    _
   </code>
   , and
   <code class="literal">
    $
   </code>
   . A user variable
      name can contain other characters if you quote it as a string or
      identifier (for example,
   <code class="literal">
    @'my-var'
   </code>
   ,
   <code class="literal">
    @"my-var"
   </code>
   , or
   <code class="literal">
    @`my-var`
   </code>
   ).
  </p>
  <p>
   User-defined variables are session specific. A user variable
      defined by one client cannot be seen or used by other clients.
      (Exception: A user with access to the Performance Schema
   <a class="link" href="performance-schema-user-variable-tables.html" title="29.12.10 Performance Schema User-Defined Variable Tables">
    <code class="literal">
     user_variables_by_thread
    </code>
   </a>
   table can
      see all user variables for all sessions.) All variables for a
      given client session are automatically freed when that client
      exits.
  </p>
  <p>
   User variable names are not case-sensitive. Names have a maximum
      length of 64 characters.
  </p>
  <p>
   One way to set a user-defined variable is by issuing 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 one-line language-sql"><div class="docs-select-all right" id="sa47432904"><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 variable">@<em class="replaceable">var_name</em></span> <span class="token operator">=</span> <em class="replaceable">expr</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token variable">@<em class="replaceable">var_name</em></span> <span class="token operator">=</span> <em class="replaceable">expr</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   For
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   ,
      either
   <a class="link" href="assignment-operators.html#operator_assign-equal">
    <code class="literal">
     =
    </code>
   </a>
   or
   <a class="link" href="assignment-operators.html#operator_assign-value">
    <code class="literal">
     :=
    </code>
   </a>
   can be
      used as the assignment operator.
  </p>
  <p>
   User variables can be assigned a value from a limited set of data
      types: integer, decimal, floating-point, binary or nonbinary
      string, or
   <code class="literal">
    NULL
   </code>
   value. Assignment of decimal
      and real values does not preserve the precision or scale of the
      value. A value of a type other than one of the permissible types
      is converted to a permissible type. For example, a value having a
      temporal or spatial data type is converted to a binary string. A
      value having the
   <a class="link" href="json.html" title="13.5 The JSON Data Type">
    <code class="literal">
     JSON
    </code>
   </a>
   data type is
      converted to a string with a character set of
   <code class="literal">
    utf8mb4
   </code>
   and a collation of
   <code class="literal">
    utf8mb4_bin
   </code>
   .
  </p>
  <p>
   If a user variable is assigned a nonbinary (character) string
      value, it has the same character set and collation as the string.
      The coercibility of user variables is implicit. (This is the same
      coercibility as for table column values.)
  </p>
  <p>
   Hexadecimal or bit values assigned to user variables are treated
      as binary strings. To assign a hexadecimal or bit value as a
      number to a user variable, use it in numeric context. For example,
      add 0 or use
   <a class="link" href="cast-functions.html#function_cast">
    <code class="literal">
     CAST(... AS UNSIGNED)
    </code>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68395829"><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">@v1</span> <span class="token operator">=</span> X<span class="token string">'41'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@v2</span> <span class="token operator">=</span> X<span class="token string">'41'</span><span class="token operator">+</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@v3</span> <span class="token operator">=</span> <span class="token function">CAST</span><span class="token punctuation">(</span>X<span class="token string">'41'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@v1</span><span class="token punctuation">,</span> <span class="token variable">@v2</span><span class="token punctuation">,</span> <span class="token variable">@v3</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @v1  <span class="token punctuation">|</span> @v2  <span class="token punctuation">|</span> @v3  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> A    <span class="token punctuation">|</span>   65 <span class="token punctuation">|</span>   65 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@v1</span> <span class="token operator">=</span> b<span class="token string">'1000001'</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@v2</span> <span class="token operator">=</span> b<span class="token string">'1000001'</span><span class="token operator">+</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@v3</span> <span class="token operator">=</span> <span class="token function">CAST</span><span class="token punctuation">(</span>b<span class="token string">'1000001'</span> <span class="token keyword">AS</span> <span class="token keyword">UNSIGNED</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@v1</span><span class="token punctuation">,</span> <span class="token variable">@v2</span><span class="token punctuation">,</span> <span class="token variable">@v3</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @v1  <span class="token punctuation">|</span> @v2  <span class="token punctuation">|</span> @v3  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> A    <span class="token punctuation">|</span>   65 <span class="token punctuation">|</span>   65 <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></code></pre>
  </div>
  <p>
   If the value of a user variable is selected in a result set, it is
      returned to the client as a string.
  </p>
  <p>
   If you refer to a variable that has not been initialized, it has a
      value of
   <code class="literal">
    NULL
   </code>
   and a type of string.
  </p>
  <p>
   A reference to a user variable in a prepared statement has its
      type determined when the statement is first prepared, and retains
      this type each time the statement is executed thereafter.
      Similarly, the type of a user variable employed in a statement
      within a stored procedure is determined the first time the stored
      procedure is invoked, and retains this type with each subsequent
      invocation.
  </p>
  <p>
   User variables may be used in most contexts where expressions are
      permitted. This does not currently include contexts that
      explicitly require a literal value, such as in the
   <code class="literal">
    LIMIT
   </code>
   clause of a
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement, or the
   <code class="literal">
    IGNORE
    <em class="replaceable">
     <code>
      N
     </code>
    </em>
    LINES
   </code>
   clause of a
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   statement.
  </p>
  <p>
   Previous releases of MySQL made it possible to assign a value to a
      user variable in statements other than
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   . This
      functionality is supported in MySQL 8.4 for backward
      compatibility but is subject to removal in a future release of
      MySQL.
  </p>
  <p>
   When making an assignment in this way, you must use
   <a class="link" href="assignment-operators.html#operator_assign-value">
    <code class="literal">
     :=
    </code>
   </a>
   as the
      assignment operator;
   <code class="literal">
    =
   </code>
   is treated as the
      comparison operator in statements other than
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   .
  </p>
  <p>
   The order of evaluation for expressions involving user variables
      is undefined. For example, there is no guarantee that
   <code class="literal">
    SELECT @a, @a:=@a+1
   </code>
   evaluates
   <code class="literal">
    @a
   </code>
   first and then performs the assignment.
  </p>
  <p>
   In addition, the default result type of a variable is based on its
      type at the beginning of the statement. This may have unintended
      effects if a variable holds a value of one type at the beginning
      of a statement in which it is also assigned a new value of a
      different type.
  </p>
  <p>
   To avoid problems with this behavior, either do not assign a value
      to and read the value of the same variable within a single
      statement, or else set the variable to
   <code class="literal">
    0
   </code>
   ,
   <code class="literal">
    0.0
   </code>
   , or
   <code class="literal">
    ''
   </code>
   to define its
      type before you use it.
  </p>
  <p>
   <code class="literal">
    HAVING
   </code>
   ,
   <code class="literal">
    GROUP BY
   </code>
   , and
   <code class="literal">
    ORDER BY
   </code>
   , when referring to a variable that is
      assigned a value in the select expression list do not work as
      expected because the expression is evaluated on the client and
      thus can use stale column values from a previous row.
  </p>
  <p>
   User variables are intended to provide data values. They cannot be
      used directly in an SQL statement as an identifier or as part of
      an identifier, such as in contexts where a table or database name
      is expected, or as a reserved word such as
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   . This is true even if the
      variable is quoted, as shown in the following example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa52236240"><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> c1 <span class="token keyword">FROM</span> t<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> 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>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@col</span> <span class="token operator">=</span> <span class="token string">"c1"</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 variable">@col</span> <span class="token keyword">FROM</span> t<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>
<span class="token output"><span class="token punctuation">|</span> @col <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>
<span class="token output"><span class="token punctuation">|</span> 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>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token punctuation">`</span><span class="token variable">@col</span><span class="token punctuation">`</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span>
<span class="token output">ERROR 1054 (42S22)<span class="token punctuation">:</span> Unknown column '@col' in 'field list'
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token variable">@col</span> <span class="token operator">=</span> <span class="token string">"`c1`"</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 variable">@col</span> <span class="token keyword">FROM</span> t<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>
<span class="token output"><span class="token punctuation">|</span> @col <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>
<span class="token output"><span class="token punctuation">|</span> `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>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   An exception to this principle that user variables cannot be used
      to provide identifiers, is when you are constructing a string for
      use as a prepared statement to execute later. In this case, user
      variables can be used to provide any part of the statement. The
      following example illustrates how this can be done:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46072929"><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">@c</span> <span class="token operator">=</span> <span class="token string">"c1"</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">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 "</span><span class="token punctuation">,</span> <span class="token variable">@c</span><span class="token punctuation">,</span> <span class="token string">" FROM t"</span><span class="token punctuation">)</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">PREPARE</span> stmt <span class="token keyword">FROM</span> <span class="token variable">@s</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.04 sec)</span>
Statement prepared

<span class="token prompt">mysql&gt;</span> <span class="token keyword">EXECUTE</span> stmt<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> 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>
<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><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DEALLOCATE</span> <span class="token keyword">PREPARE</span> stmt<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span></code></pre>
  </div>
  <p>
   See
   <a class="xref" href="sql-prepared-statements.html" title="15.5 Prepared Statements">
    Section 15.5, “Prepared Statements”
   </a>
   , for more
      information.
  </p>
  <p>
   A similar technique can be used in application programs to
      construct SQL statements using program variables, as shown here
      using PHP 5:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-php"><div class="docs-select-all right" id="sa27766320"><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-php"><span class="token php language-php"><span class="token delimiter important">&lt;?php</span>
  <span class="token variable">$mysqli</span> <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">mysqli</span><span class="token punctuation">(</span><span class="token string">"localhost"</span><span class="token punctuation">,</span> <span class="token string">"user"</span><span class="token punctuation">,</span> <span class="token string">"pass"</span><span class="token punctuation">,</span> <span class="token string">"test"</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token keyword">if</span><span class="token punctuation">(</span> <span class="token function">mysqli_connect_errno</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">)</span>
    <span class="token keyword">die</span><span class="token punctuation">(</span><span class="token string">"Connection failed: %s\n"</span><span class="token punctuation">,</span> <span class="token function">mysqli_connect_error</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token variable">$col</span> <span class="token operator">=</span> <span class="token string">"c1"</span><span class="token punctuation">;</span>

  <span class="token variable">$query</span> <span class="token operator">=</span> <span class="token string">"SELECT $col FROM t"</span><span class="token punctuation">;</span>

  <span class="token variable">$result</span> <span class="token operator">=</span> <span class="token variable">$mysqli</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">query</span><span class="token punctuation">(</span><span class="token variable">$query</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token keyword">while</span><span class="token punctuation">(</span><span class="token variable">$row</span> <span class="token operator">=</span> <span class="token variable">$result</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">fetch_assoc</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
  <span class="token punctuation">{</span>
    <span class="token keyword">echo</span> <span class="token string">"&lt;p&gt;"</span> <span class="token punctuation">.</span> <span class="token variable">$row</span><span class="token punctuation">[</span><span class="token string">"$col"</span><span class="token punctuation">]</span> <span class="token punctuation">.</span> <span class="token string">"&lt;/p&gt;\n"</span><span class="token punctuation">;</span>
  <span class="token punctuation">}</span>

  <span class="token variable">$result</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

  <span class="token variable">$mysqli</span><span class="token operator">-</span><span class="token operator">&gt;</span><span class="token function">close</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token delimiter important">?&gt;</span></span></code></pre>
  </div>
  <p>
   Assembling an SQL statement in this fashion is sometimes known as
   <span class="quote">
    “
    <span class="quote">
     Dynamic SQL
    </span>
    ”
   </span>
   .
  </p>
 </div>
 <br/>
</div>
