<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="mysql-tips">
      </a>
      6.5.1.6 mysql Client Tips
     </h4>
    </div>
   </div>
  </div>
  <p>
   This section provides information about techniques for more
        effective use of
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   and about
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   operational behavior.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-tips.html#mysql-input-editing" title="Input-Line Editing">
       Input-Line Editing
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-tips.html#mysql-history" title="Disabling Interactive History">
       Disabling Interactive History
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-tips.html#windows-unicode-support" title="Unicode Support on Windows">
       Unicode Support on Windows
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-tips.html#vertical-query-results" title="Displaying Query Results Vertically">
       Displaying Query Results Vertically
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-tips.html#safe-updates" title="Using Safe-Updates Mode (--safe-updates)">
       Using Safe-Updates Mode (--safe-updates)
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-tips.html#mysql-reconnect" title="Disabling mysql Auto-Reconnect">
       Disabling mysql Auto-Reconnect
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="mysql-tips.html#mysql-parsers" title="mysql Client Parser Versus Server Parser">
       mysql Client Parser Versus Server Parser
      </a>
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="mysql-input-editing">
   </a>
   Input-Line Editing
  </h5>
  <p>
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   supports input-line editing, which
        enables you to modify the current input line in place or recall
        previous input lines. For example, the
   <span class="keycap">
    <strong>
     left-arrow
    </strong>
   </span>
   and
   <span class="keycap">
    <strong>
     right-arrow
    </strong>
   </span>
   keys move horizontally within the current input line, and the
   <span class="keycap">
    <strong>
     up-arrow
    </strong>
   </span>
   and
   <span class="keycap">
    <strong>
     down-arrow
    </strong>
   </span>
   keys
        move up and down through the set of previously entered lines.
   <span class="keycap">
    <strong>
     Backspace
    </strong>
   </span>
   deletes the character before the
        cursor and typing new characters enters them at the cursor
        position. To enter the line, press
   <span class="keycap">
    <strong>
     Enter
    </strong>
   </span>
   .
  </p>
  <p>
   On Windows, the editing key sequences are the same as supported
        for command editing in console windows. On Unix, the key
        sequences depend on the input library used to build
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   (for example, the
   <code class="literal">
    libedit
   </code>
   or
   <code class="literal">
    readline
   </code>
   library).
  </p>
  <p>
   Documentation for the
   <code class="literal">
    libedit
   </code>
   and
   <code class="literal">
    readline
   </code>
   libraries is available online. To
        change the set of key sequences permitted by a given input
        library, define key bindings in the library startup file. This
        is a file in your home directory:
   <code class="filename">
    .editrc
   </code>
   for
   <code class="literal">
    libedit
   </code>
   and
   <code class="filename">
    .inputrc
   </code>
   for
   <code class="literal">
    readline
   </code>
   .
  </p>
  <p>
   For example, in
   <code class="literal">
    libedit
   </code>
   ,
   <span class="keycap">
    <strong>
     Control+W
    </strong>
   </span>
   deletes everything before the current
        cursor position and
   <span class="keycap">
    <strong>
     Control+U
    </strong>
   </span>
   deletes the
        entire line. In
   <code class="literal">
    readline
   </code>
   ,
   <span class="keycap">
    <strong>
     Control+W
    </strong>
   </span>
   deletes the word before the cursor
        and
   <span class="keycap">
    <strong>
     Control+U
    </strong>
   </span>
   deletes everything before the
        current cursor position. If
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   was built
        using
   <code class="literal">
    libedit
   </code>
   , a user who prefers the
   <code class="literal">
    readline
   </code>
   behavior for these two keys can put
        the following lines in the
   <code class="filename">
    .editrc
   </code>
   file
        (creating the file if necessary):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa81865867"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal">bind <span class="token atrule">"^W"</span> ed-delete-prev-word
bind <span class="token atrule">"^U"</span> vi-kill-line-prev</code></pre>
  </div>
  <p>
   To see the current set of key bindings, temporarily put a line
        that says only
   <code class="literal">
    bind
   </code>
   at the end of
   <code class="filename">
    .editrc
   </code>
   .
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   shows the
        bindings when it starts.
  </p>
  <h5>
   <a name="mysql-history">
   </a>
   Disabling Interactive History
  </h5>
  <a class="indexterm" name="idm46045317268560">
  </a>
  <p>
   The
   <span class="keycap">
    <strong>
     up-arrow
    </strong>
   </span>
   key enables you to recall input
        lines from current and previous sessions. In cases where a
        console is shared, this behavior may be unsuitable.
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   supports disabling the interactive
        history partially or fully, depending on the host platform.
  </p>
  <p>
   On Windows, the history is stored in memory.
   <span class="keycap">
    <strong>
     Alt+F7
    </strong>
   </span>
   deletes all input lines stored in memory
        for the current history buffer. It also deletes the list of
        sequential numbers in front of the input lines displayed with
   <span class="keycap">
    <strong>
     F7
    </strong>
   </span>
   and recalled (by number) with
   <span class="keycap">
    <strong>
     F9
    </strong>
   </span>
   . New input lines entered after you press
   <span class="keycap">
    <strong>
     Alt+F7
    </strong>
   </span>
   repopulate the current history buffer.
        Clearing the buffer does not prevent logging to the Windows
        Event Viewer, if the
   <a class="link" href="mysql-command-options.html#option_mysql_syslog">
    <code class="option">
     --syslog
    </code>
   </a>
   option was used to start
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   . Closing the
        console window also clears the current history buffer.
  </p>
  <p>
   To disable interactive history on Unix, first delete the
   <code class="filename">
    .mysql_history
   </code>
   file, if it exists (previous
        entries are recalled otherwise). Then start
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   with the
   <code class="literal">
    --histignore="*"
   </code>
   option to ignore all new
        input lines. To re-enable the recall (and logging) behavior,
        restart
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   without the option.
  </p>
  <p>
   If you prevent the
   <code class="filename">
    .mysql_history
   </code>
   file from
        being created (see
   <a class="xref" href="mysql-logging.html#mysql-logging-history-file" title="Controlling the History File">
    Controlling the History File
   </a>
   )
        and use
   <code class="literal">
    --histignore="*"
   </code>
   to start 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, the interactive history recall
        facility is disabled fully. Alternatively, if you omit the
   <a class="link" href="mysql-command-options.html#option_mysql_histignore">
    <code class="option">
     --histignore
    </code>
   </a>
   option, you can
        recall the input lines entered during the current session.
  </p>
  <h5>
   <a name="windows-unicode-support">
   </a>
   Unicode Support on Windows
  </h5>
  <p>
   Windows provides APIs based on UTF-16LE for reading from and
        writing to the console; 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 for
        Windows is able to use these APIs. The Windows installer creates
        an item in the MySQL menu named
   <code class="literal">
    MySQL command line
        client - Unicode
   </code>
   . This item invokes 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 with properties set to
        communicate through the console to the MySQL server using
        Unicode.
  </p>
  <p>
   To take advantage of this support manually, run
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   within a console that uses a compatible
        Unicode font and set the default character set to a Unicode
        character set that is supported for communication with the
        server:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Open a console window.
     </p>
    </li>
    <li class="listitem">
     <p>
      Go to the console window properties, select the font tab,
            and choose Lucida Console or some other compatible Unicode
            font. This is necessary because console windows start by
            default using a DOS raster font that is inadequate for
            Unicode.
     </p>
    </li>
    <li class="listitem">
     <p>
      Execute
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql.exe
        </strong>
       </span>
      </a>
      with the
      <a class="link" href="mysql-command-options.html#option_mysql_default-character-set">
       <code class="option">
        --default-character-set=utf8mb4
       </code>
      </a>
      (or
      <code class="literal">
       utf8mb3
      </code>
      ) option. This option is
            necessary because
      <code class="literal">
       utf16le
      </code>
      is one of the
            character sets that cannot be used as the client character
            set. See
      <a class="xref" href="charset-connection.html#charset-connection-impermissible-client-charset" title="Impermissible Client Character Sets">
       Impermissible Client Character Sets
      </a>
      .
     </p>
    </li>
   </ol>
  </div>
  <p>
   With those changes,
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   uses the Windows
        APIs to communicate with the console using UTF-16LE, and
        communicate with the server using UTF-8. (The menu item
        mentioned previously sets the font and character set as just
        described.)
  </p>
  <p>
   To avoid those steps each time you run
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   ,
        you can create a shortcut that invokes
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql.exe
     </strong>
    </span>
   </a>
   . The shortcut should set the
        console font to Lucida Console or some other compatible Unicode
        font, and pass the
   <a class="link" href="mysql-command-options.html#option_mysql_default-character-set">
    <code class="option">
     --default-character-set=utf8mb4
    </code>
   </a>
   (or
   <code class="literal">
    utf8mb3
   </code>
   ) option to
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql.exe
     </strong>
    </span>
   </a>
   .
  </p>
  <p>
   Alternatively, create a shortcut that only sets the console
        font, and set the character set in the
   <code class="literal">
    [mysql]
   </code>
   group of your
   <code class="filename">
    my.ini
   </code>
   file:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa10822671"><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-ini"><span class="token selector">[mysql]</span>
<span class="token constant">default-character-set</span><span class="token attr-value"><span class="token punctuation">=</span>utf8mb4   </span><span class="token comment" spellcheck="true"># or utf8mb3</span></code></pre>
  </div>
  <h5>
   <a name="vertical-query-results">
   </a>
   Displaying Query Results Vertically
  </h5>
  <p>
   Some query results are much more readable when displayed
        vertically, instead of in the usual horizontal table format.
        Queries can be displayed vertically by terminating the query
        with \G instead of a semicolon. For example, longer text values
        that include newlines often are much easier to read with
        vertical output:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97586432"><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> <span class="token operator">*</span> <span class="token keyword">FROM</span> mails <span class="token keyword">WHERE</span> <span class="token function">LENGTH</span><span class="token punctuation">(</span>txt<span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token number">300</span> <span class="token keyword">LIMIT</span> <span class="token number">300</span><span class="token punctuation">,</span><span class="token number">1</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
  msg_nro<span class="token punctuation">:</span> 3068
     date<span class="token punctuation">:</span> 2000-03-01 23<span class="token punctuation">:</span>29<span class="token punctuation">:</span>50
time_zone<span class="token punctuation">:</span> +0200
mail_from<span class="token punctuation">:</span> Jones
    reply<span class="token punctuation">:</span> jones@example.com
  mail_to<span class="token punctuation">:</span> "John Smith" &lt;smith@example.com&gt;
      sbj<span class="token punctuation">:</span> UTF-8
      txt<span class="token punctuation">:</span> &gt;&gt;&gt;&gt;&gt; "John" == John Smith writes<span class="token punctuation">:</span>
</span>
John<span class="token operator">&gt;</span> Hi<span class="token punctuation">.</span>  I think this <span class="token operator">is</span> a good idea<span class="token punctuation">.</span>  <span class="token operator">Is</span> anyone familiar
John<span class="token operator">&gt;</span> <span class="token keyword">with</span> UTF<span class="token number">-8</span> <span class="token operator">or</span> <span class="token keyword">Unicode</span>? Otherwise<span class="token punctuation">,</span> I'll put this <span class="token keyword">on</span> my
John<span class="token operator">&gt;</span> TODO <span class="token keyword">list</span> <span class="token operator">and</span> see what happens<span class="token punctuation">.</span>

Yes<span class="token punctuation">,</span> please <span class="token keyword">do</span> that<span class="token punctuation">.</span>

Regards<span class="token punctuation">,</span>
Jones
     <span class="token keyword">file</span>: inbox<span class="token operator">-</span>jani<span class="token number">-1</span>
     <span class="token keyword">hash</span>: <span class="token number">190402944</span>
<span class="token output">1 row in set (0.09 sec)</span></code></pre>
  </div>
  <h5>
   <a name="safe-updates">
   </a>
   Using Safe-Updates Mode (--safe-updates)
  </h5>
  <a class="indexterm" name="idm46045317217136">
  </a>
  <a class="indexterm" name="idm46045317216096">
  </a>
  <a class="indexterm" name="idm46045317214608">
  </a>
  <a class="indexterm" name="idm46045317213520">
  </a>
  <a class="indexterm" name="idm46045317212032">
  </a>
  <a class="indexterm" name="idm46045317210944">
  </a>
  <a class="indexterm" name="idm46045317209456">
  </a>
  <a class="indexterm" name="idm46045317208368">
  </a>
  <p>
   For beginners, a useful startup option is
   <a class="link" href="mysql-command-options.html#option_mysql_safe-updates">
    <code class="option">
     --safe-updates
    </code>
   </a>
   (or
   <a class="link" href="mysql-command-options.html#option_mysql_safe-updates">
    <code class="option">
     --i-am-a-dummy
    </code>
   </a>
   ,
        which has the same effect). Safe-updates mode is helpful for
        cases when you might have issued an
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   or
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   statement but forgotten
        the
   <code class="literal">
    WHERE
   </code>
   clause indicating which rows to
        modify. Normally, such statements update or delete all rows in
        the table. With
   <a class="link" href="mysql-command-options.html#option_mysql_safe-updates">
    <code class="option">
     --safe-updates
    </code>
   </a>
   ,
        you can modify rows only by specifying the key values that
        identify them, or a
   <code class="literal">
    LIMIT
   </code>
   clause, or both.
        This helps prevent accidents. Safe-updates mode also restricts
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statements that produce
        (or are estimated to produce) very large result sets.
  </p>
  <p>
   The
   <a class="link" href="mysql-command-options.html#option_mysql_safe-updates">
    <code class="option">
     --safe-updates
    </code>
   </a>
   option causes
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   to execute the following statement when
        it connects to the MySQL server, to set the session values of
        the
   <a class="link" href="server-system-variables.html#sysvar_sql_safe_updates">
    <code class="literal">
     sql_safe_updates
    </code>
   </a>
   ,
   <a class="link" href="server-system-variables.html#sysvar_sql_select_limit">
    <code class="literal">
     sql_select_limit
    </code>
   </a>
   , and
   <a class="link" href="server-system-variables.html#sysvar_max_join_size">
    <code class="literal">
     max_join_size
    </code>
   </a>
   system variables:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa73712258"><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> sql_safe_updates<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">,</span> sql_select_limit<span class="token operator">=</span><span class="token number">1000</span><span class="token punctuation">,</span> max_join_size<span class="token operator">=</span><span class="token number">1000000</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   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 affects statement processing as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Enabling
      <a class="link" href="server-system-variables.html#sysvar_sql_safe_updates">
       <code class="literal">
        sql_safe_updates
       </code>
      </a>
      causes
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statements to produce
            an error if they do not specify a key constraint in the
      <code class="literal">
       WHERE
      </code>
      clause, or provide a
      <code class="literal">
       LIMIT
      </code>
      clause, or both. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33978309"><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">UPDATE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">SET</span> <em class="replaceable">not_key_column</em><span class="token operator">=</span><em class="replaceable">val</em> <span class="token keyword">WHERE</span> <em class="replaceable">key_column</em><span class="token operator">=</span><em class="replaceable">val</em><span class="token punctuation">;</span>

<span class="token keyword">UPDATE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">SET</span> <em class="replaceable">not_key_column</em><span class="token operator">=</span><em class="replaceable">val</em> <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Setting
      <a class="link" href="server-system-variables.html#sysvar_sql_select_limit">
       <code class="literal">
        sql_select_limit
       </code>
      </a>
      to
            1,000 causes the server to limit all
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      result sets to 1,000
            rows unless the statement includes a
      <code class="literal">
       LIMIT
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      Setting
      <a class="link" href="server-system-variables.html#sysvar_max_join_size">
       <code class="literal">
        max_join_size
       </code>
      </a>
      to
            1,000,000 causes multiple-table
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements to produce
            an error if the server estimates it must examine more than
            1,000,000 row combinations.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To specify result set limits different from 1,000 and 1,000,000,
        you can override the defaults by using the
   <a class="link" href="mysql-command-options.html#option_mysql_select-limit">
    <code class="option">
     --select-limit
    </code>
   </a>
   and
   <a class="link" href="mysql-command-options.html#option_mysql_max-join-size">
    <code class="option">
     --max-join-size
    </code>
   </a>
   options when you
        invoke
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa72814004"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal">mysql <span class="token property">--safe-updates</span> <span class="token constant">--select-limit</span><span class="token attr-value"><span class="token punctuation">=</span>500</span> <span class="token constant">--max-join-size</span><span class="token attr-value"><span class="token punctuation">=</span>10000</span></code></pre>
  </div>
  <p>
   It is possible for
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   and
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   statements to produce an
        error in safe-updates mode even with a key specified in the
   <code class="literal">
    WHERE
   </code>
   clause, if the optimizer decides not to
        use the index on the key column:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Range access on the index cannot be used if memory usage
            exceeds that permitted by the
      <a class="link" href="server-system-variables.html#sysvar_range_optimizer_max_mem_size">
       <code class="literal">
        range_optimizer_max_mem_size
       </code>
      </a>
      system variable. The optimizer then falls back to a table
            scan. See
      <a class="xref" href="range-optimization.html#range-optimization-memory-use" title="Limiting Memory Use for Range Optimization">
       Limiting Memory Use for Range Optimization
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If key comparisons require type conversion, the index may
            not be used (see
      <a class="xref" href="mysql-indexes.html" title="10.3.1 How MySQL Uses Indexes">
       Section 10.3.1, “How MySQL Uses Indexes”
      </a>
      ). Suppose
            that an indexed string column
      <code class="literal">
       c1
      </code>
      is
            compared to a numeric value using
      <code class="literal">
       WHERE c1 =
            2222
      </code>
      . For such comparisons, the string value is
            converted to a number and the operands are compared
            numerically (see
      <a class="xref" href="type-conversion.html" title="14.3 Type Conversion in Expression Evaluation">
       Section 14.3, “Type Conversion in Expression Evaluation”
      </a>
      ),
            preventing use of the index. If safe-updates mode is
            enabled, an error occurs.
     </p>
    </li>
   </ul>
  </div>
  <p>
   These behaviors are included in safe-updates mode:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      with
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      and
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      statements does not
            produce safe-updates errors. This enables use of
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN
       </code>
      </a>
      plus
      <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
       <code class="literal">
        SHOW WARNINGS
       </code>
      </a>
      to see why an
            index is not used, which can be helpful in cases such as
            when a
      <a class="link" href="server-system-variables.html#sysvar_range_optimizer_max_mem_size">
       <code class="literal">
        range_optimizer_max_mem_size
       </code>
      </a>
      violation or type conversion occurs and the optimizer does
            not use an index even though a key column was specified in
            the
      <code class="literal">
       WHERE
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      When a safe-updates error occurs, the error message includes
            the first diagnostic that was produced, to provide
            information about the reason for failure. For example, the
            message may indicate that the
      <a class="link" href="server-system-variables.html#sysvar_range_optimizer_max_mem_size">
       <code class="literal">
        range_optimizer_max_mem_size
       </code>
      </a>
      value was exceeded or type conversion occurred, either of
            which can preclude use of an index.
     </p>
    </li>
    <li class="listitem">
     <p>
      For multiple-table deletes and updates, an error is produced
            with safe updates enabled only if any target table uses a
            table scan.
     </p>
    </li>
   </ul>
  </div>
  <h5>
   <a name="mysql-reconnect">
   </a>
   Disabling mysql Auto-Reconnect
  </h5>
  <p>
   If 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 loses its connection to
        the server while sending a statement, it immediately and
        automatically tries to reconnect once to the server and send the
        statement again. However, even if
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   succeeds in reconnecting, your first connection has ended and
        all your previous session objects and settings are lost:
        temporary tables, the autocommit mode, and user-defined and
        session variables. Also, any current transaction rolls back.
        This behavior may be dangerous for you, as in the following
        example where the server was shut down and restarted between the
        first and second statements without you knowing it:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55854111"><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">@a</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.05 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token variable">@a</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 2006<span class="token punctuation">:</span> MySQL server has gone away
No connection. Trying to reconnect...
Connection id<span class="token punctuation">:</span>    1
Current database<span class="token punctuation">:</span> test

</span><span class="token output">Query OK, 1 row affected (1.30 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> 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> 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 class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> NULL <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.05 sec)</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    @a
   </code>
   user variable has been lost with the
        connection, and after the reconnection it is undefined. If it is
        important to have
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   terminate with an
        error if the connection has been lost, you can start 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 with the
   <a class="link" href="mysql-command-options.html#option_mysql_reconnect">
    <code class="option">
     --skip-reconnect
    </code>
   </a>
   option.
  </p>
  <p>
   For more information about auto-reconnect and its effect on
        state information when a reconnection occurs, see
   <a class="ulink" href="/doc/c-api/8.4/en/c-api-auto-reconnect.html" target="_top">
    Automatic Reconnection Control
   </a>
   .
  </p>
  <h5>
   <a name="mysql-parsers">
   </a>
   mysql Client Parser Versus Server Parser
  </h5>
  <a class="indexterm" name="idm46045317124640">
  </a>
  <a class="indexterm" name="idm46045317123184">
  </a>
  <p>
   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 uses a parser on the client
        side that is not a duplicate of the complete parser used by the
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   server on the server side. This can
        lead to differences in treatment of certain constructs.
        Examples:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The server parser treats strings delimited by
      <code class="literal">
       "
      </code>
      characters as identifiers rather than
            as plain strings if the
      <a class="link" href="sql-mode.html#sqlmode_ansi_quotes">
       <code class="literal">
        ANSI_QUOTES
       </code>
      </a>
      SQL mode is
            enabled.
     </p>
     <p>
      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 parser does not take the
      <code class="literal">
       ANSI_QUOTES
      </code>
      SQL mode into account. It
            treats strings delimited by
      <code class="literal">
       "
      </code>
      ,
      <code class="literal">
       '
      </code>
      , and
      <code class="literal">
       `
      </code>
      characters
            the same, regardless of whether
      <a class="link" href="sql-mode.html#sqlmode_ansi_quotes">
       <code class="literal">
        ANSI_QUOTES
       </code>
      </a>
      is enabled.
     </p>
    </li>
    <li class="listitem">
     <p>
      Within
      <code class="literal">
       /*! ... */
      </code>
      and
      <code class="literal">
       /*+ ...
            */
      </code>
      comments, 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
            parser interprets short-form
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      commands. The server parser does not interpret them because
            these commands have no meaning on the server side.
     </p>
     <p>
      If it is desirable for
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      not to
            interpret short-form commands within comments, a partial
            workaround is to use the
      <a class="link" href="mysql-command-options.html#option_mysql_binary-mode">
       <code class="option">
        --binary-mode
       </code>
      </a>
      option, which
            causes all
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      commands to be disabled
            except
      <code class="literal">
       \C
      </code>
      and
      <code class="literal">
       \d
      </code>
      in
            noninteractive mode (for input piped to
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      or loaded using the
      <code class="literal">
       source
      </code>
      command).
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
