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) 
Also 
Current pager:        less

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 67855984
Server version: 10.6.18-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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 delimiter backslash ('\') because that is the escape character .

edit, nopager, pager, and system work only in *nix.

status outputs information about the connection, server, if in --safe-updates mode status and the values for variables that affect queries.

To log queries and output use tee command. Data displayed on the screen is appended to a file. Useful for debugging. This can be enabled on the command line with --tee , or with tee The tee file can be disabled with notee . Executing tee again re-enables logging defaulting to the previous file. tee flushes results after each statement.

Use --pager to browse or search query results with filters such as less, more or a similar filter. Without a value $PAGER is used. Output paging is enabled with pager and disabled with nopager. The optional argument . With no

Paging works only in *nix. For Windows, tee can be used to save query output, although this is not as convenient as pager for browsing output.

Tips

1. write to only a file

    mysql> pager cat > /tmp/log.txt

   pass options for pager:

    mysql> pager less -n -i -S 

2. the -S useful for browsing wide 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 output 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)