<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="set-variable">
      </a>
      15.7.6.1 SET Syntax for Variable Assignment
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045171561584">
  </a>
  <a class="indexterm" name="idm46045171560128">
  </a>
  <a class="indexterm" name="idm46045171558640">
  </a>
  <a class="indexterm" name="idm46045171557152">
  </a>
  <a class="indexterm" name="idm46045171555664">
  </a>
  <a class="indexterm" name="idm46045171554176">
  </a>
  <a class="indexterm" name="idm46045171553088">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa78442177"><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> <em class="replaceable">variable</em> <span class="token operator">=</span> <em class="replaceable">expr</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">variable</em> <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>

<em class="replaceable">variable</em>: {
    <em class="replaceable">user_var_name</em>
  <span class="token operator">|</span> <em class="replaceable">param_name</em>
  <span class="token operator">|</span> <em class="replaceable">local_var_name</em>
  <span class="token operator">|</span> {<span class="token keyword">GLOBAL</span> <span class="token operator">|</span> <span class="token variable">@@GLOBAL.</span>} <em class="replaceable">system_var_name</em>
  <span class="token operator">|</span> {<span class="token keyword">PERSIST</span> <span class="token operator">|</span> <span class="token variable">@@PERSIST.</span>} <em class="replaceable">system_var_name</em>
  <span class="token operator">|</span> {<span class="token keyword">PERSIST_ONLY</span> <span class="token operator">|</span> <span class="token variable">@@PERSIST_ONLY.</span>} <em class="replaceable">system_var_name</em>
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">SESSION</span> <span class="token operator">|</span> <span class="token variable">@@SESSION.</span> <span class="token operator">|</span> @@<span class="token punctuation">]</span> <em class="replaceable">system_var_name</em>
}</code></pre>
  </div>
  <p>
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   syntax for variable assignment enables you to assign values to
        different types of variables that affect the operation of the
        server or clients:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      User-defined variables. See
      <a class="xref" href="user-variables.html" title="11.4 User-Defined Variables">
       Section 11.4, “User-Defined Variables”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Stored procedure and function parameters, and stored program
            local variables. See
      <a class="xref" href="stored-program-variables.html" title="15.6.4 Variables in Stored Programs">
       Section 15.6.4, “Variables in Stored Programs”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      System variables. See
      <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
       Section 7.1.8, “Server System Variables”
      </a>
      . System variables
            also can be set at server startup, as described in
      <a class="xref" href="using-system-variables.html" title="7.1.9 Using System Variables">
       Section 7.1.9, “Using System Variables”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   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 that assigns variable values is not written to the
        binary log, so in replication scenarios it affects only the host
        on which you execute it. To affect all replication hosts,
        execute the statement on each host.
  </p>
  <p>
   The following sections describe
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   syntax for setting variables. They use the
   <a class="link" href="assignment-operators.html#operator_assign-equal">
    <code class="literal">
     =
    </code>
   </a>
   assignment operator, but the
   <a class="link" href="assignment-operators.html#operator_assign-value">
    <code class="literal">
     :=
    </code>
   </a>
   assignment operator is also permitted for this purpose.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="set-variable.html#set-variable-user-variables" title="User-Defined Variable Assignment">
       User-Defined Variable Assignment
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="set-variable.html#set-variable-parameters-local-variables" title="Parameter and Local Variable Assignment">
       Parameter and Local Variable Assignment
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="set-variable.html#set-variable-system-variables" title="System Variable Assignment">
       System Variable Assignment
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="set-variable.html#set-variable-error-handling" title="SET Error Handling">
       SET Error Handling
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="set-variable.html#set-variable-multiple-assignments" title="Multiple Variable Assignment">
       Multiple Variable Assignment
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="set-variable.html#variable-references-in-expressions" title="System Variable References in Expressions">
       System Variable References in Expressions
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="set-variable-user-variables">
       </a>
       User-Defined Variable Assignment
      </h5>
     </div>
    </div>
   </div>
   <p>
    User-defined variables are created locally within a session
          and exist only within the context of that session; see
    <a class="xref" href="user-variables.html" title="11.4 User-Defined Variables">
     Section 11.4, “User-Defined Variables”
    </a>
    .
   </p>
   <p>
    A user-defined variable is written as
    <code class="literal">
     @
     <em class="replaceable">
      <code>
       var_name
      </code>
     </em>
    </code>
    and is
          assigned an expression value as follows:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa52038967"><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></code></pre>
   </div>
   <p>
    Examples:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61134265"><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">@name</span> <span class="token operator">=</span> <span class="token number">43</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@total_tax</span> <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token function">SUM</span><span class="token punctuation">(</span>tax<span class="token punctuation">)</span> <span class="token keyword">FROM</span> taxable_transactions<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    As demonstrated by those statements,
    <em class="replaceable">
     <code>
      expr
     </code>
    </em>
    can range from simple (a
          literal value) to more complex (the value returned by a scalar
          subquery).
   </p>
   <p>
    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
          contains information about user-defined variables. See
    <a class="xref" href="performance-schema-user-variable-tables.html" title="29.12.10 Performance Schema User-Defined Variable Tables">
     Section 29.12.10, “Performance Schema User-Defined Variable Tables”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="set-variable-parameters-local-variables">
       </a>
       Parameter and Local Variable Assignment
      </h5>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    applies to parameters and local variables in the context of
          the stored object within which they are defined. The following
          procedure uses the
    <code class="literal">
     increment
    </code>
    procedure
          parameter and
    <code class="literal">
     counter
    </code>
    local variable:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1950027"><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">PROCEDURE</span> p<span class="token punctuation">(</span>increment <span class="token datatype">INT</span><span class="token punctuation">)</span>
<span class="token keyword">BEGIN</span>
  <span class="token keyword">DECLARE</span> counter <span class="token datatype">INT</span> <span class="token keyword">DEFAULT</span> <span class="token number">0</span><span class="token punctuation">;</span>
  <span class="token keyword">WHILE</span> counter <span class="token operator">&lt;</span> <span class="token number">10</span> <span class="token keyword">DO</span>
    <span class="token comment" spellcheck="true">-- ... do work ...</span>
    <span class="token keyword">SET</span> counter <span class="token operator">=</span> counter <span class="token operator">+</span> increment<span class="token punctuation">;</span>
  <span class="token keyword">END</span> <span class="token keyword">WHILE</span><span class="token punctuation">;</span>
<span class="token keyword">END</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="set-variable-system-variables">
       </a>
       System Variable Assignment
      </h5>
     </div>
    </div>
   </div>
   <p>
    The MySQL server maintains system variables that configure its
          operation. A system variable can have a global value that
          affects server operation as a whole, a session value that
          affects the current session, or both. Many system variables
          are dynamic and can be changed at runtime using the
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    statement to affect operation of the current server instance.
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    can also be used to persist certain system variables to the
    <code class="filename">
     mysqld-auto.cnf
    </code>
    file in the data
          directory, to affect server operation for subsequent startups.
   </p>
   <p>
    If a
    <code class="literal">
     SET
    </code>
    statement is issued for a
          sensitive system variable, the query is rewritten to replace
          the value with
    <span class="quote">
     “
     <span class="quote">
      <code class="literal">
       &lt;redacted&gt;
      </code>
     </span>
     ”
    </span>
    before it is logged to the general log and audit log. This
          takes place even if secure storage through a keyring component
          is not available on the server instance.
   </p>
   <p>
    If you change a session system variable, the value remains in
          effect within your session until you change the variable to a
          different value or the session ends. The change has no effect
          on other sessions.
   </p>
   <p>
    If you change a global system variable, the value is
          remembered and used to initialize the session value for new
          sessions until you change the variable to a different value or
          the server exits. The change is visible to any client that
          accesses the global value. However, the change affects the
          corresponding session value only for clients that connect
          after the change. The global variable change does not affect
          the session value for any current client sessions (not even
          the session within which the global value change occurs).
   </p>
   <p>
    To make a global system variable setting permanent so that it
          applies across server restarts, you can persist it to the
    <code class="filename">
     mysqld-auto.cnf
    </code>
    file in the data
          directory. It is also possible to make persistent
          configuration changes by manually modifying a
    <code class="filename">
     my.cnf
    </code>
    option file, but that is more
          cumbersome, and an error in a manually entered setting might
          not be discovered until much later.
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    statements that persist system variables are more convenient
          and avoid the possibility of malformed settings because
          settings with syntax errors do not succeed and do not change
          server configuration. For more information about persisting
          system variables and the
    <code class="filename">
     mysqld-auto.cnf
    </code>
    file, see
    <a class="xref" href="persisted-system-variables.html" title="7.1.9.3 Persisted System Variables">
     Section 7.1.9.3, “Persisted System Variables”
    </a>
    .
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     Setting or persisting a global system variable value always
            requires special privileges. Setting a session system
            variable value normally requires no special privileges and
            can be done by any user, although there are exceptions. For
            more information, see
     <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
      Section 7.1.9.1, “System Variable Privileges”
     </a>
     .
    </p>
   </div>
   <p>
    The following discussion describes the syntax options for
          setting and persisting system variables:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       To assign a value to a global system variable, precede the
              variable name by the
       <code class="literal">
        GLOBAL
       </code>
       keyword or
              the
       <code class="literal">
        @@GLOBAL.
       </code>
       qualifier:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa54863156"><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> max_connections <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@GLOBAL.max_connections</span> <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       To assign a value to a session system variable, precede
              the variable name by the
       <code class="literal">
        SESSION
       </code>
       or
       <code class="literal">
        LOCAL
       </code>
       keyword, by the
       <code class="literal">
        @@SESSION.
       </code>
       ,
       <code class="literal">
        @@LOCAL.
       </code>
       , or
       <code class="literal">
        @@
       </code>
       qualifier, or by no keyword or no modifier at all:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7530995"><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">SESSION</span> sql_mode <span class="token operator">=</span> <span class="token string">'TRADITIONAL'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">LOCAL</span> sql_mode <span class="token operator">=</span> <span class="token string">'TRADITIONAL'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@SESSION.sql_mode</span> <span class="token operator">=</span> <span class="token string">'TRADITIONAL'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@LOCAL.sql_mode</span> <span class="token operator">=</span> <span class="token string">'TRADITIONAL'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@sql_mode</span> <span class="token operator">=</span> <span class="token string">'TRADITIONAL'</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> sql_mode <span class="token operator">=</span> <span class="token string">'TRADITIONAL'</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       A client can change its own session variables, but not
              those of any other client.
      </p>
     </li>
     <li class="listitem">
      <p>
       To persist a global system variable to the
       <code class="filename">
        mysqld-auto.cnf
       </code>
       option file in the
              data directory, precede the variable name by the
       <code class="literal">
        PERSIST
       </code>
       keyword or the
       <code class="literal">
        @@PERSIST.
       </code>
       qualifier:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67691389"><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">PERSIST</span> max_connections <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@PERSIST.max_connections</span> <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       This
       <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
        <code class="literal">
         SET
        </code>
       </a>
       syntax enables you to make configuration changes at
              runtime that also persist across server restarts. Like
       <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
        <code class="literal">
         SET
              GLOBAL
        </code>
       </a>
       ,
       <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
        <code class="literal">
         SET
              PERSIST
        </code>
       </a>
       sets the global variable runtime value,
              but also writes the variable setting to the
       <code class="filename">
        mysqld-auto.cnf
       </code>
       file (replacing any
              existing variable setting if there is one).
      </p>
     </li>
     <li class="listitem">
      <p>
       To persist a global system variable to the
       <code class="filename">
        mysqld-auto.cnf
       </code>
       file without setting
              the global variable runtime value, precede the variable
              name by the
       <code class="literal">
        PERSIST_ONLY
       </code>
       keyword or the
       <code class="literal">
        @@PERSIST_ONLY.
       </code>
       qualifier:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17432351"><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">PERSIST_ONLY</span> back_log <span class="token operator">=</span> <span class="token number">100</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@PERSIST_ONLY.back_log</span> <span class="token operator">=</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Like
       <code class="literal">
        PERSIST
       </code>
       ,
       <code class="literal">
        PERSIST_ONLY
       </code>
       writes the variable
              setting to
       <code class="filename">
        mysqld-auto.cnf
       </code>
       . However,
              unlike
       <code class="literal">
        PERSIST
       </code>
       ,
       <code class="literal">
        PERSIST_ONLY
       </code>
       does not modify the global
              variable runtime value. This makes
       <code class="literal">
        PERSIST_ONLY
       </code>
       suitable for configuring
              read-only system variables that can be set only at server
              startup.
      </p>
     </li>
    </ul>
   </div>
   <p>
    To set a global system variable value to the compiled-in MySQL
          default value or a session system variable to the current
          corresponding global value, set the variable to the value
    <code class="literal">
     DEFAULT
    </code>
    . For example, the following two
          statements are identical in setting the session value of
    <a class="link" href="server-system-variables.html#sysvar_max_join_size">
     <code class="literal">
      max_join_size
     </code>
    </a>
    to the current
          global value:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa907642"><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">@@SESSION.max_join_size</span> <span class="token operator">=</span> <span class="token keyword">DEFAULT</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@SESSION.max_join_size</span> <span class="token operator">=</span> <span class="token variable">@@GLOBAL.max_join_size</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Using
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    to
          persist a global system variable to a value of
    <code class="literal">
     DEFAULT
    </code>
    or to its literal default value
          assigns the variable its default value and adds a setting for
          the variable to
    <code class="filename">
     mysqld-auto.cnf
    </code>
    . To
          remove the variable from the file, use
    <a class="link" href="reset-persist.html" title="15.7.8.7 RESET PERSIST Statement">
     <code class="literal">
      RESET PERSIST
     </code>
    </a>
    .
   </p>
   <p>
    Some system variables cannot be persisted or are
          persist-restricted. See
    <a class="xref" href="nonpersistible-system-variables.html" title="7.1.9.4 Nonpersistible and Persist-Restricted System Variables">
     Section 7.1.9.4, “Nonpersistible and Persist-Restricted System Variables”
    </a>
    .
   </p>
   <p>
    A system variable implemented by a plugin can be persisted if
          the plugin is installed when the
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    statement is executed. Assignment of the persisted plugin
          variable takes effect for subsequent server restarts if the
          plugin is still installed. If the plugin is no longer
          installed, the plugin variable no longer exists when the
          server reads the
    <code class="filename">
     mysqld-auto.cnf
    </code>
    file. In
          this case, the server writes a warning to the error log and
          continues:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa36842257"><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">currently unknown variable '<em class="replaceable">var_name</em>'
was read from the persisted config file</code></pre>
   </div>
   <p>
    To display system variable names and values:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Use the
       <a class="link" href="show-variables.html" title="15.7.7.41 SHOW VARIABLES Statement">
        <code class="literal">
         SHOW VARIABLES
        </code>
       </a>
       statement; see
       <a class="xref" href="show-variables.html" title="15.7.7.41 SHOW VARIABLES Statement">
        Section 15.7.7.41, “SHOW VARIABLES Statement”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Several Performance Schema tables provide system variable
              information. See
       <a class="xref" href="performance-schema-system-variable-tables.html" title="29.12.14 Performance Schema System Variable Tables">
        Section 29.12.14, “Performance Schema System Variable Tables”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The Performance Schema
       <a class="link" href="performance-schema-variables-info-table.html" title="29.12.14.2 Performance Schema variables_info Table">
        <code class="literal">
         variables_info
        </code>
       </a>
       table contains
              information showing when and by which user each system
              variable was most recently set. See
       <a class="xref" href="performance-schema-variables-info-table.html" title="29.12.14.2 Performance Schema variables_info Table">
        Section 29.12.14.2, “Performance Schema variables_info Table”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The Performance Schema
       <a class="link" href="performance-schema-persisted-variables-table.html" title="29.12.14.1 Performance Schema persisted_variables Table">
        <code class="literal">
         persisted_variables
        </code>
       </a>
       table
              provides an SQL interface to the
       <code class="filename">
        mysqld-auto.cnf
       </code>
       file, enabling its
              contents to be inspected at runtime using
       <a class="link" href="select.html" title="15.2.13 SELECT Statement">
        <code class="literal">
         SELECT
        </code>
       </a>
       statements. See
       <a class="xref" href="performance-schema-persisted-variables-table.html" title="29.12.14.1 Performance Schema persisted_variables Table">
        Section 29.12.14.1, “Performance Schema persisted_variables Table”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="set-variable-error-handling">
       </a>
       SET Error Handling
      </h5>
     </div>
    </div>
   </div>
   <p>
    If any variable assignment in 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 fails, the entire statement fails and no variables
          are changed, nor is the
    <code class="filename">
     mysqld-auto.cnf
    </code>
    file changed.
   </p>
   <p>
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    produces an error under the circumstances described here. Most
          of the examples show
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    statements that use keyword syntax (for example,
    <code class="literal">
     GLOBAL
    </code>
    or
    <code class="literal">
     SESSION
    </code>
    ), but
          the principles are also true for statements that use the
          corresponding modifiers (for example,
    <code class="literal">
     @@GLOBAL.
    </code>
    or
    <code class="literal">
     @@SESSION.
    </code>
    ).
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Use of
       <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
        <code class="literal">
         SET
        </code>
       </a>
       (any variant) to set a read-only variable:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24986582"><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> version <span class="token operator">=</span> <span class="token string">'abc'</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1238 (HY000)<span class="token punctuation">:</span> Variable 'version' is a read only variable</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Use of
       <code class="literal">
        GLOBAL
       </code>
       ,
       <code class="literal">
        PERSIST
       </code>
       , or
       <code class="literal">
        PERSIST_ONLY
       </code>
       to set a variable that has
              only a session value:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26297130"><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_log_bin <span class="token operator">=</span> <span class="token keyword">ON</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1228 (HY000)<span class="token punctuation">:</span> Variable 'sql_log_bin' is a SESSION
variable and can't be used with SET GLOBAL</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Use of
       <code class="literal">
        SESSION
       </code>
       to set a variable that
              has only a global value:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71771167"><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">SESSION</span> max_connections <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1229 (HY000)<span class="token punctuation">:</span> Variable 'max_connections' is a
GLOBAL variable and should be set with SET GLOBAL</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Omission of
       <code class="literal">
        GLOBAL
       </code>
       ,
       <code class="literal">
        PERSIST
       </code>
       , or
       <code class="literal">
        PERSIST_ONLY
       </code>
       to set a variable that has
              only a global value:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63488095"><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> max_connections <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1229 (HY000)<span class="token punctuation">:</span> Variable 'max_connections' is a
GLOBAL variable and should be set with SET GLOBAL</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Use of
       <code class="literal">
        PERSIST
       </code>
       or
       <code class="literal">
        PERSIST_ONLY
       </code>
       to set a variable that
              cannot be persisted:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55690928"><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">PERSIST</span> <span class="token keyword">port</span> <span class="token operator">=</span> <span class="token number">3307</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1238 (HY000)<span class="token punctuation">:</span> Variable 'port' is a read only variable</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">PERSIST_ONLY</span> <span class="token keyword">port</span> <span class="token operator">=</span> <span class="token number">3307</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1238 (HY000)<span class="token punctuation">:</span> Variable 'port' is a non persistent read only variable</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        @@GLOBAL.
       </code>
       ,
       <code class="literal">
        @@PERSIST.
       </code>
       ,
       <code class="literal">
        @@PERSIST_ONLY.
       </code>
       ,
       <code class="literal">
        @@SESSION.
       </code>
       , and
       <code class="literal">
        @@
       </code>
       modifiers apply only to system variables. An error occurs
              for attempts to apply them to user-defined variables,
              stored procedure or function parameters, or stored program
              local variables.
      </p>
     </li>
     <li class="listitem">
      <p>
       Not all system variables can be set to
       <code class="literal">
        DEFAULT
       </code>
       . In such cases, assigning
       <code class="literal">
        DEFAULT
       </code>
       results in an error.
      </p>
     </li>
     <li class="listitem">
      <p>
       An error occurs for attempts to assign
       <code class="literal">
        DEFAULT
       </code>
       to user-defined variables,
              stored procedure or function parameters, or stored program
              local variables.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="set-variable-multiple-assignments">
       </a>
       Multiple Variable Assignment
      </h5>
     </div>
    </div>
   </div>
   <p>
    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 can contain multiple variable assignments, separated
          by commas. This statement assigns values to a user-defined
          variable and a system variable:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa92284593"><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">@x</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token keyword">SESSION</span> sql_mode <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If you set multiple system variables in a single statement,
          the most recent
    <code class="literal">
     GLOBAL
    </code>
    ,
    <code class="literal">
     PERSIST
    </code>
    ,
    <code class="literal">
     PERSIST_ONLY
    </code>
    ,
          or
    <code class="literal">
     SESSION
    </code>
    keyword in the statement is used
          for following assignments that have no keyword specified.
   </p>
   <p>
    Examples of multiple-variable assignment:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70046662"><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> sort_buffer_size <span class="token operator">=</span> <span class="token number">1000000</span><span class="token punctuation">,</span> <span class="token keyword">SESSION</span> sort_buffer_size <span class="token operator">=</span> <span class="token number">1000000</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@@GLOBAL.sort_buffer_size</span> <span class="token operator">=</span> <span class="token number">1000000</span><span class="token punctuation">,</span> <span class="token variable">@@LOCAL.sort_buffer_size</span> <span class="token operator">=</span> <span class="token number">1000000</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> max_connections <span class="token operator">=</span> <span class="token number">1000</span><span class="token punctuation">,</span> sort_buffer_size <span class="token operator">=</span> <span class="token number">1000000</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The
    <code class="literal">
     @@GLOBAL.
    </code>
    ,
    <code class="literal">
     @@PERSIST.
    </code>
    ,
    <code class="literal">
     @@PERSIST_ONLY.
    </code>
    ,
    <code class="literal">
     @@SESSION.
    </code>
    , and
    <code class="literal">
     @@
    </code>
    modifiers apply only to the immediately following system
          variable, not any remaining system variables. This statement
          sets the
    <a class="link" href="server-system-variables.html#sysvar_sort_buffer_size">
     <code class="literal">
      sort_buffer_size
     </code>
    </a>
    global value to 50000 and the session value to 1000000:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa84069354"><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">@@GLOBAL.sort_buffer_size</span> <span class="token operator">=</span> <span class="token number">50000</span><span class="token punctuation">,</span> sort_buffer_size <span class="token operator">=</span> <span class="token number">1000000</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="variable-references-in-expressions">
       </a>
       System Variable References in Expressions
      </h5>
     </div>
    </div>
   </div>
   <p>
    To refer to the value of a system variable in expressions, use
          one of the
    <code class="literal">
     @@
    </code>
    -modifiers (except
    <code class="literal">
     @@PERSIST.
    </code>
    and
    <code class="literal">
     @@PERSIST_ONLY.
    </code>
    , which are not permitted in
          expressions). For example, you can retrieve system variable
          values in a
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statement
          like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa95236464"><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 variable">@@SESSION.sql_mode</span><span class="token punctuation">,</span> <span class="token variable">@@sql_mode</span><span class="token punctuation">;</span></code></pre>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     A reference to a system variable in an expression as
     <code class="literal">
      @@
      <em class="replaceable">
       <code>
        var_name
       </code>
      </em>
     </code>
     (with
     <code class="literal">
      @@
     </code>
     rather than
     <code class="literal">
      @@GLOBAL.
     </code>
     or
     <code class="literal">
      @@SESSION.
     </code>
     ) returns the session value if
            it exists and the global value otherwise. This differs from
     <code class="literal">
      SET @@
      <em class="replaceable">
       <code>
        var_name
       </code>
      </em>
      =
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
     </code>
     , which always
            refers to the session value.
    </p>
   </div>
  </div>
 </div>
 <br/>
</div>
