<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="batch-mode">
      </a>
      5.5 Using mysql in Batch Mode
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045324800112">
  </a>
  <a class="indexterm" name="idm46045324798656">
  </a>
  <a class="indexterm" name="idm46045324797584">
  </a>
  <a class="indexterm" name="idm46045324796096">
  </a>
  <a class="indexterm" name="idm46045324795024">
  </a>
  <a class="indexterm" name="idm46045324793536">
  </a>
  <p>
   In the previous sections, you used
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   interactively to enter statements and view the results. You can
      also 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>
   in batch mode. To do this, put
      the statements you want to run in a file, then tell
   <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 read its input from the file:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa11174434"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> &lt; <em class="replaceable">batch-file</em></code></pre>
  </div>
  <p>
   If you are running
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   under Windows and have
      some special characters in the file that cause problems, you can
      do this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa55670718"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">C:\&gt;</span><span class="token command"> mysql</span> <span class="token property">-e</span> <span class="token atrule">"source <em class="replaceable">batch-file</em>"</span></code></pre>
  </div>
  <p>
   If you need to specify connection parameters on the command line,
      the command might look like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa41083408"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> <span class="token property">-h</span> <em class="replaceable">host</em> <span class="token property">-u</span> <em class="replaceable">user</em> <span class="token property">-p</span> &lt; <em class="replaceable">batch-file</em>
Enter password<span class="token punctuation">:</span> ********</code></pre>
  </div>
  <p>
   When you use
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   this way, you are creating a
      script file, then executing the script.
  </p>
  <p>
   If you want the script to continue even if some of the statements
      in it produce errors, you should use the
   <a class="link" href="mysql-command-options.html#option_mysql_force">
    <code class="option">
     --force
    </code>
   </a>
   command-line option.
  </p>
  <p>
   Why use a script? Here are a few reasons:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If you run a query repeatedly (say, every day or every week),
          making it a script enables you to avoid retyping it each time
          you execute it.
     </p>
    </li>
    <li class="listitem">
     <p>
      You can generate new queries from existing ones that are
          similar by copying and editing script files.
     </p>
    </li>
    <li class="listitem">
     <p>
      Batch mode can also be useful while you're developing a query,
          particularly for multiple-line statements or
          multiple-statement sequences. If you make a mistake, you don't
          have to retype everything. Just edit your script to correct
          the error, then tell
      <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 it
          again.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you have a query that produces a lot of output, you can run
          the output through a pager rather than watching it scroll off
          the top of your screen:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa36754319"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> &lt; <em class="replaceable">batch-file</em> | more</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      You can catch the output in a file for further processing:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa12386551"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> &lt; <em class="replaceable">batch-file</em> &gt; mysql<span class="token punctuation">.</span>out</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      You can distribute your script to other people so that they
          can also run the statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      Some situations do not allow for interactive use, for example,
          when you run a query from a
      <span class="command">
       <strong>
        cron
       </strong>
      </span>
      job. In
          this case, you must use batch mode.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The default output format is different (more concise) when 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>
   in batch mode than when you use it
      interactively. For example, the output of
   <code class="literal">
    SELECT DISTINCT
      species FROM pet
   </code>
   looks like this when
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   is run interactively:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa667301"><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-none">+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+</code></pre>
  </div>
  <p>
   In batch mode, the output looks like this instead:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa93617487"><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-none">species
bird
cat
dog
hamster
snake</code></pre>
  </div>
  <p>
   If you want to get the interactive output format in batch mode,
      use
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql -t
     </strong>
    </span>
   </a>
   . To echo to the output the
      statements that are executed, use
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql -v
     </strong>
    </span>
   </a>
   .
  </p>
  <a class="indexterm" name="idm46045324751984">
  </a>
  <a class="indexterm" name="idm46045324750896">
  </a>
  <a class="indexterm" name="idm46045324749808">
  </a>
  <a class="indexterm" name="idm46045324748704">
  </a>
  <p>
   You can also use scripts from 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>
   prompt
      by using the
   <code class="literal">
    source
   </code>
   command or
   <code class="literal">
    \.
   </code>
   command:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79207160"><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">source</span> <em class="replaceable">filename</em><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> \<span class="token punctuation">.</span> <em class="replaceable">filename</em></code></pre>
  </div>
  <p>
   See
   <a class="xref" href="mysql-batch-commands.html" title="6.5.1.5 Executing SQL Statements from a Text File">
    Section 6.5.1.5, “Executing SQL Statements from a Text File”
   </a>
   , for more information.
  </p>
 </div>
 <br/>
</div>
