MySQL Options

When you run the mysql command to connect to MySQL, you can use any of the options shown in Table 2.5.

Table 2.5: MySQL Option

Option	Description
-?, --help	Displays the help and exits.
-A, --no-auto-rehash	Allows for quicker startup. Automatic rehashing is the feature that allows you to press Tab; MySQL will try to complete the table or field. MySQL hashes the field and table names on startup, but sometimes, when you have many tables and fields, startup can become too slow. Using this option switches this off. To use hashing when this option has been specified on startup, enter rehash on the command line.
-b, --no-beep	Turns off the beeping each time there's an error.
-B, --batch	Accepts SQL statements in batch mode. Displays results with tab separation. Doesn't use history file.
--character-sets-dir=...	Tells MySQL in which directory character sets are located.
-C, --compress	Uses compression in server/client protocol.
-#, --debug[=...]	Creates a debug log. The default is d:t:o:,/tmp/mysql.trace, which enables debugging, turns on function call entry and exit tracing, and outputs to /tmp/ mysql.trace. You can override this by specifying another file.
-D, --database=...	Indicates which database to use. Ordinarily, you can select a database without specifying this option, but this is useful in the configuration file.
--default-character-set=...	Sets the default character set.
-e, --execute=...	Executes command and quits. The output is the same as with the -B option.
-E, --vertical	Prints the output of a query vertically, with each field on a different line. Without this option you can also force this output by ending your statements with \G.
-f, --force	Forces MySQL to continue processing even if you get a SQL error. Useful in batch mode when processing from files.
-g, --no-named-commands	Disables named commands. Uses \* form only, or uses named commands only in the beginning of a line ending with a semicolon (;). Since version 10.9, the client starts with this option enabled by default! With the -g option, however, long format commands will still work from the first line.
-G, --enable-named-commands	Enables named commands. Long format commands are allowed as well as shortened \* commands.
-h, --host=...	Connects to the specified host machine.
-H, --html	Formats the results of a query in HTML. Most commonly you'll use a programming language to format this, but this option can be useful for aquick and dirty way to generate HTML.
-i, --ignore-space	Ignores spaces after function names.
-L, --skip-line-numbers	Causes MySQL not to write line number for errors. Can be useful when outputting to result files in which you later want to search for errors or compare.
--no-pager	Disables the pager and results in output going to standard output. See the -pager option.
--no-tee	Disables outfile. See interactive help (\h) also.
-n, --unbuffered	Flushes buffer after each query.
-N, --skip-column-names	Causes MySQL not to write column names in results.
-O, --set-variable var=option	Gives a variable a value. --help lists variables.
-o, --one-database	Only updates the default database. This can be useful for skipping updates to other databases in the update log.
--pager[=...]	Long results outputs will usually scroll off the screen. You can output the result to a pager. The default pager is your ENV variable PAGER. Valid pagers are less, more, cat [> filename], and so on. This option does not work in batch mode. Pager works only in Unix.
-p[password], --password[=...]	Password to use when connecting to server. If a password is not given on the command line, you will be prompted for it. If you enter the password on the command line, you can't have a space between the option and the password.
-P, --port=...	By default, you connect to a MySQL server through port 3306. You can change this by specifying the TCP/IP port number to use for connection.
-q, --quick	Causes the results to be displayed row by row. This speeds up output if theresults are very large. This can slow down the server if the output is suspended. Doesn't use the history file.
-r, --raw	Write column values without escape conversion. Used with --batch.
-s, --silent	Does not display as much output.
-S, --socket=...	Socket file to use for connection.
-t, --table	Outputs in table format. This is default in nonbatch mode.
-T, --debug-info	Prints some debug information at exit.
--tee=...	Appends everything into outfile. See interactive help (\h) also. Does not work in batch mode.
-u, --user=#	Specifies a user for login. If a user is not specified, MySQL will assume it's the current user (if there is one).
-U, --safe-updates[=#], --i-am-a-dummy[=#]	Only allows UPDATE and DELETE that use keys. If this option is set by default, you can reset it by using --safe-updates=0.
-v, --verbose	Causes MySQL to give more verbose output (-v -v -v gives the table output format, -t).
-V, --version	Outputs the version information and exit.
-w, --wait	If the connection is down, this option will wait and try to connect later, rather than aborting.

Automatic rehashing is the feature that allows you to press the Tab key and complete the table or field. MySQL creates this when you connect, but sometimes, when you have many tables and fields, startup can become too slow. Using the -A or -- no-auto-rehash option switches this off.

The -E option prints the results vertically. You can get this kind of output, even if you haven't connected to MySQL with this option active, by using \G at the end of a query:

mysql> SELECT * FROM customer\G; *************************** 1. row ***************************         id: 1 first_name: Yvonne    surname: Clegg *************************** 2. row ***************************         id: 2 first_name: Johnny    surname: Chaka-Chaka *************************** 3. row ***************************         id: 3 first_name: Winston    surname: Powers *************************** 4. row ***************************         id: 4 first_name: Patricia    surname: Mankunku *************************** 5. row ***************************         id: 5 first_name: Francois    surname: Papo *************************** 6. row ***************************         id: 7 first_name: Winnie    surname: Dlamini *************************** 7. row ***************************         id: 6 first_name: Neil    surname: Beneke 7 rows in set (0.00 sec) 

The ignore space option (-i) allows you to be more lax in using functions in your queries. For example, the following normally causes an error (note the space after MAX):

mysql> SELECT MAX (value) FROM sales; ERROR 1064: You have an error in your SQL syntax near '(value) from  sales' at line 1

But if you'd used the -i option to connect, there'd have been no problem:

mysql> SELECT MAX(value) FROM sales; +-------------+ | MAX (value) | +-------------+ |        3800 | +-------------+

The -H (or --html) option places query results inside an HTML table. If you connect with this option, you'll see the following sort of output:

mysql> SELECT * FROM customer; <TABLE BORDER=1><TR><TH>id</TH><TH>first_name</TH><TH>surname</TH></TR> <TR><TD>1</TD><TD>Yvonne</TD><TD>Clegg</TD></TR> <TR><TD>2</TD><TD>Johnny</TD><TD>Chaka-Chaka</TD></TR> <TR><TD>3</TD><TD>Winston</TD><TD>Powers</TD></TR> <TR><TD>4</TD><TD>Patricia</TD><TD>Mankunku</TD></TR> <TR><TD>5</TD><TD>Francois</TD><TD>Papo</TD></TR> <TR><TD>7</TD><TD>Winnie</TD><TD>Dlamini</TD></TR> <TR><TD>6</TD><TD>Neil</TD><TD>Beneke</TD></TR></TABLE> 7 rows in set (0.00 sec) 

The -o option only allows updates to the default database. If you connected with this option, you would not have been able to make an update to any tables in the firstdb database:

mysql> UPDATE customer SET first_name='John' WHERE first_name='Johnny'; Ignoring query to other database

The -U option (also called the "I am a dummy" option) helps avoid unpleasant surprises by not permitting an UPDATE or DELETE that does not use a key (you'll look at keys in detail in Chapter 4, "Indexes and Query Optimization"). If you connect with this option, the following command will not work:

mysql> DELETE FROM customer;  ERROR 1175: You are using safe update mode and you tried to update a  table without a WHERE that uses a KEY column