mysql

The MySQL command-line tool

a SQL shell (with GNU readline capabilities). Output format can be changed using command options.Default:

1. interactively, results are in ASCII-table format.

   > mysql db_name

   Or:

   > mysql --user=user_name [--password=password] db_name

2. non-interactively (for example, as a filter), results are in tab-separated format.

   > mysql db_name script.sql output.tab

Current pager:      stdout

Current pager:      less

<TABLE BORDER=1>
<TR><TH>name</TH><TH>entry</TH><TH>class</TH></TR>
<TR><TD>Rosemary Murphy</TD><TD>209</TD><TD>OC1</TD></TR>
<TR><TD>Mike Parkinson</TD><TD>200</TD><TD>SK1</TD></TR>
<TR><TD>David Impens</TD><TD>237</TD><TD>RC1</TD></TR>
<TR><TD>Amber Tomas</TD><TD>236</TD><TD>ICFK1</TD></TR>
<TR><TD>Charlie Johnson</TD><TD>235</TD><TD>Tr1</TD></TR>
<TR><TD>Gary Ballina</TD><TD>218</TD><TD>K2</TD></TR>
<TR><TD>Emilia Rastick</TD><TD>225</TD><TD>OC1</TD></TR>
<TR><TD>Meegan Coll</TD><TD>226</TD><TD>OC1</TD></TR>
<TR><TD>Glen Green</TD><TD>219</TD><TD>K1</TD></TR></TABLE>
36 rows in set (0.03 sec)

Current pager:     less

<?xml version="1.0"?> 
<resultset statement="select name,entry, class from PCCraceApp;" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="name">Rosemary Murphy</field>
    <field name="entry">209</field>
    <field name="class">OC1</field>
  </row> 
  <row>
    <field name="name">Mike Parkinson</field>
    <field name="entry">200</field>
    <field name="class">SK1</field>
  </row>
</resultset>
36 rows in set (0.00 sec) 

Current pager:        less

History file On Unix, mysql writes statements to .mysql_history.
To specify a different file, set MYSQL_HISTFILE environment variable.

To disable this first remove .mysql_history and either
Set MYSQL_HISTFILE /dev/null or
Create .mysql_history as a symbolic link to /dev/null:

> ln -s /dev/null $HOME/.mysql_history

MySQL COMMANDS

These are commands that mysql itself processes.

Commands have long and short form.
The long form is not case sensitive; the short form is.
The long form can be followed by an optional semicolon terminator, but the short form should not.

If you provide an argument to the help command, mysql uses it as a search string to access server-side help from the contents of the MySQL Reference Manual. For more information, see the section called "MYSQL SERVER-SIDE HELP".

Avoid the use delimiter of the backslash ('\') character because that is the escape character for MySQL.

The edit, nopager, pager, and system commands work only in Unix.

The status command provides some information about the connection and the server you are using. If you are running in --safe-updates mode, status also prints the values for the mysql variables that affect your queries.

To log queries and their output, use the tee command. All the data displayed on the screen is appended into a given file. This can be very useful for debugging purposes also. You can enable this feature on the command line with the --tee option, or interactively with the tee command. The tee file can be disabled interactively with the notee command. Executing tee again re-enables logging. Without a parameter, the previous file is used. Note that tee flushes query results to the file after each statement, just before mysql prints its next prompt.

By using the --pager option, it is possible to browse or search query results in interactive mode with Unix programs such as less, more, or any other similar program. If you specify no value for the option, mysql checks the value of the PAGER environment variable and sets the pager to that. Output paging can be enabled interactively with the pager command and disabled with nopager. The command takes an optional argument; if given, the paging program is set to that. With no argument, the pager is set to the pager that was set on the command line, or stdout if no pager was specified.

Output paging works only in Unix because it uses the popen() function, which does not exist on Windows. For Windows, the tee option can be used instead to save query output, although this is not as convenient as pager for browsing output in some situations.

Here are a few tips about the pager command:

1. You can use it to write to a file and the results go only to the file:

    mysql> pager cat > /tmp/log.txt

   You can also pass any options for the program that you want to use as your pager:

    mysql> pager less -n -i -S 

2. In the preceding example, note the -S option. You may find it very useful for browsing wide query results. Sometimes a very wide result set is difficult to read on the screen. The -S option to less can make the result set much more readable because you can scroll it horizontally using the left-arrow and right-arrow keys. You can also use -S interactively within less to switch the horizontal-browse mode on and off. For more information, read the less manual page:

    shell> man less

3. You can specify very complex pager commands for handling query output:

    mysql> pager cat | tee /dr1/tmp/res.txt \
                     | tee /dr2/tmp/res2.txt | less -n -i -S

   In this example, the command would send query results to two files in two different directories on two different filesystems mounted on /dr1 and /dr2, yet still display the results onscreen via less.

You can also combine the tee and pager functions. Have a tee file enabled and pager set to less, and you are able to browse the results using the less program and still have everything appended into a file the same time. The difference between the Unix tee used with the pager command and the mysql built-in tee command is that the built-in tee works even if you do not have the Unix tee available. The built-in tee also logs everything that is printed on the screen, whereas the Unix tee used with pager does not log quite that much. Additionally, tee file logging can be turned on and off interactively from within mysql. This is useful when you want to log some queries to a file, but not others.

string for defining the prompt can contain the following special sequences:

If you specify the prompt command with no argument, mysql resets the prompt to the default of mysql>.

Set the prompt using :
1. MYSQL_PS1 environment variable. shell> export MYSQL_PS1="(\u@\h) [\d]> "
2. --prompt=xxx command-line option. shell> mysql --prompt="\u@\h [\d]> " user@host [database]>
3. Use an option file. the prompt option in the [mysql] group of any MySQL option file, such as /etc/my.cnf or the .my.cnf file in your home directory. For example:

 [mysql]
          prompt=(\\u@\\h) [\\d]>\\_

 [mysql]
          prompt="\\r:\\m:\\s> "

          mysql> help search_string

          mysql> help contents

          mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
          *************************** 1. row ***************************
            msg_nro: 3068
               date: 2000-03-01 23:29:50
          time_zone: +0200
          mail_from: Monty
              reply: monty@no.spam.com
            mail_to: "Thimble Smith" 
                sbj: UTF-8
                txt: >>>>> "Thimble" == Thimble Smith writes:
          Thimble> Hi.  I think this is a good idea.  Is anyone familiar
          Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
          Thimble> TODO list and see what happens.
          Yes, please do that.
          Regards,
          Monty
               file: inbox-jani-1
               hash: 190402944
          1 row in set (0.09 sec)