2
6 mysql - the MySQL command-line tool
7
9 mysql [options] db_name
10
12 mysql is a simple SQL shell (with GNU readline capabilities). It
13 supports interactive and non-interactive use. When used interactively,
14 query results are presented in an ASCII-table format. When used
15 non-interactively (for example, as a filter), the result is presented
16 in tab-separated format. The output format can be changed using command
17 options.
18 If you have problems due to insufficient memory for large result sets,
20 use the --quick option. This forces mysql to retrieve results from the
21 server a row at a time rather than retrieving the entire result set and
22 buffering it in memory before displaying it. This is done by returning
23 the result set using the mysql_use_result() C API function in the
24 client/server library rather than mysql_store_result().
25 Using mysql is very easy. Invoke it from the prompt of your command
27 interpreter as follows:
28 shell> mysql db_name
30 Or:
32 shell> mysql --user=user_name --password=your_password db_name
34 Then type an SQL statement, end it with ‘;’, \g, or \G and press Enter.
36 As of MySQL 5.0.25, typing Control-C causes mysql to attempt to kill
38 the current statement. If this cannot be done, or Control-C is typed
39 again before the statement is killed, mysql exits. Previously,
40 Control-C caused mysql to exit in all cases.
41 You can execute SQL statements in a script file (batch file) like this:
43 shell> mysql db_name < script.sql > output.tab
45
47 mysql supports the following options:
48 · --help, -?
50 Display a help message and exit.
52 · --auto-rehash
54 Enable automatic rehashing. This option is on by default, which
56 enables table and column name completion. Use --skip-auto-rehash to
57 disable rehashing. That causes mysql to start faster, but you must
58 issue the rehash command if you want to use table and column name
59 completion.
60 · --batch, -B
62 Print results using tab as the column separator, with each row on a
64 new line. With this option, mysql does not use the history file.
65 · --character-sets-dir=path
67 The directory where character sets are installed. See Section 8.1,
69 “The Character Set Used for Data and Sorting”.
70 · --column-names
72 Write column names in results.
74 · --compress, -C
76 Compress all information sent between the client and the server if
78 both support compression.
79 · --database=db_name, -D db_name
81 The database to use. This is useful primarily in an option file.
83 · --debug[=debug_options], -# [debug_options]
85 Write a debugging log. The debug_options string often is
87 ´d:t:o,file_name'. The default is ´d:t:o,/tmp/mysql.trace'.
88 · --debug-info, -T
90 Print some debugging information when the program exits.
92 · --default-character-set=charset_name
94 Use charset_name as the default character set. See Section 8.1, “The
96 Character Set Used for Data and Sorting”.
97 · --delimiter=str
99 Set the statement delimiter. The default is the semicolon character
101 (‘;’).
102 · --execute=statement, -e statement
104 Execute the statement and quit. The default output format is like
106 that produced with --batch. See Section 3.1, “Using Options on the
107 Command Line”, for some examples.
108 · --force, -f
110 Continue even if an SQL error occurs.
112 · --host=host_name, -h host_name
114 Connect to the MySQL server on the given host.
116 · --html, -H
118 Produce HTML output.
120 · --ignore-spaces, -i
122 Ignore spaces after function names. The effect of this is described
124 in the discussion for the IGNORE_SPACE SQL mode (see the section
125 called “SQL MODES”).
126 · --line-numbers
128 Write line numbers for errors. Disable this with
130 --skip-line-numbers.
131 · --local-infile[={0|1}]
133 Enable or disable LOCAL capability for LOAD DATA INFILE. With no
135 value, the option enables LOCAL. The option may be given as
136 --local-infile=0 or --local-infile=1 to explicitly disable or enable
137 LOCAL. Enabling LOCAL has no effect if the server does not also
138 support it.
139 MySQL Enterprise. For expert advice on the security implications of
141 enabling LOCAL, subscribe to the MySQL Network Monitoring and Advisory
142 Service. For more information see
143 http://www.mysql.com/products/enterprise/advisors.html.
144 · --named-commands, -G
146 Enable named mysql commands. Long-format commands are allowed, not
148 just short-format commands. For example, quit and \q both are
149 recognized. Use --skip-named-commands to disable named commands. See
150 the section called “MYSQL COMMANDS”.
151 · --no-auto-rehash, -A
153 Deprecated form of -skip-auto-rehash. See the description for
155 --auto-rehash.
156 · --no-beep, -b
158 Do not beep when errors occur.
160 · --no-named-commands, -g
162 Disable named commands. Use the \* form only, or use named commands
164 only at the beginning of a line ending with a semicolon (‘;’).
165 mysql starts with this option enabled by default. However, even with
166 this option, long-format commands still work from the first line.
167 See the section called “MYSQL COMMANDS”.
168 · --no-pager
170 Deprecated form of --skip-pager. See the --pager option.
172 · --no-tee
174 Do not copy output to a file. the section called “MYSQL COMMANDS”,
176 discusses tee files further.
177 · --one-database, -o
179 Ignore statements except those for the default database named on the
181 command line. This is useful for skipping updates to other databases
182 in the binary log.
183 · --pager[=command]
185 Use the given command for paging query output. If the command is
187 omitted, the default pager is the value of your PAGER environment
188 variable. Valid pagers are less, more, cat [> filename], and so
189 forth. This option works only on Unix. It does not work in batch
190 mode. To disable paging, use --skip-pager. the section called
191 “MYSQL COMMANDS”, discusses output paging further.
192 · --password[=password], -p[password]
194 The password to use when connecting to the server. If you use the
196 short option form (-p), you cannot have a space between the option
197 and the password. If you omit the password value following the
198 --password or -p option on the command line, you are prompted for
199 one.
200 Specifying a password on the command line should be considered
202 insecure. See Section 6.6, “Keeping Your Password Secure”.
203 · --port=port_num, -P port_num
205 The TCP/IP port number to use for the connection.
207 · --prompt=format_str
209 Set the prompt to the specified format. The default is mysql>. The
211 special sequences that the prompt can contain are described in the
212 section called “MYSQL COMMANDS”.
213 · --protocol={TCP|SOCKET|PIPE|MEMORY}
215 The connection protocol to use.
217 · --quick, -q
219 Do not cache each query result, print each row as it is received.
221 This may slow down the server if the output is suspended. With this
222 option, mysql does not use the history file.
223 · --raw, -r
225 Write column values without escape conversion. Often used with the
227 --batch option.
228 · --reconnect
230 If the connection to the server is lost, automatically try to
232 reconnect. A single reconnect attempt is made each time the
233 connection is lost. To suppress reconnection behavior, use
234 --skip-reconnect.
235 · --safe-updates, --i-am-a-dummy, -U
237 Allow only those UPDATE and DELETE statements that specify which
239 rows to modify by using key values. If you have set this option in
240 an option file, you can override it by using --safe-updates on the
241 command line. See the section called “MYSQL TIPS”, for more
242 information about this option.
243 · --secure-auth
245 Do not send passwords to the server in old (pre-4.1.1) format. This
247 prevents connections except for servers that use the newer password
248 format.
249 MySQL Enterprise. For expert advice on database security, subscribe to
251 the MySQL Network Monitoring and Advisory Service. For more information
252 see http://www.mysql.com/products/enterprise/advisors.html.
253 · --show-warnings
255 Cause warnings to be shown after each statement if there are any.
257 This option applies to interactive and batch mode. This option was
258 added in MySQL 5.0.6.
259 · --sigint-ignore
261 Ignore SIGINT signals (typically the result of typing Control-C).
263 · --silent, -s
265 Silent mode. Produce less output. This option can be given multiple
267 times to produce less and less output.
268 · --skip-column-names, -N
270 Do not write column names in results.
272 · --skip-line-numbers, -L
274 Do not write line numbers for errors. Useful when you want to
276 compare result files that include error messages.
277 · --socket=path, -S path
279 For connections to localhost, the Unix socket file to use, or, on
281 Windows, the name of the named pipe to use.
282 · --ssl*
284 Options that begin with --ssl specify whether to connect to the
286 server via SSL and indicate where to find SSL keys and certificates.
287 See Section 6.7.3, “SSL Command Options”.
288 · --table, -t
290 Display output in table format. This is the default for interactive
292 use, but can be used to produce table output in batch mode.
293 · --tee=file_name
295 Append a copy of output to the given file. This option does not work
297 in batch mode. in the section called “MYSQL COMMANDS”, discusses tee
298 files further.
299 · --unbuffered, -n
301 Flush the buffer after each query.
303 · --user=user_name, -u user_name
305 The MySQL username to use when connecting to the server.
307 · --verbose, -v
309 Verbose mode. Produce more output about what the program does. This
311 option can be given multiple times to produce more and more output.
312 (For example, -v -v -v produces table output format even in batch
313 mode.)
314 · --version, -V
316 Display version information and exit.
318 · --vertical, -E
320 Print query output rows vertically (one line per column value).
322 Without this option, you can specify vertical output for individual
323 statements by terminating them with \G.
324 · --wait, -w
326 If the connection cannot be established, wait and retry instead of
328 aborting.
329 · --xml, -X
331 Produce XML output.
333 Note: Prior to MySQL 5.0.26, there was no differentiation in the
335 output when using this option between columns containing the NULL
336 value and columns containing the string literal ´NULL'; both were
337 represented as
338 <field name="column_name">NULL</field>
340 Beginning with MySQL 5.0.26, the output when --xml is used with mysql
341 matches that of mysqldump --xml. See the section of the Manual which
342 discusses the --xml option for mysqldump for details.
343 Beginning with MySQL 5.0.40, the XML output also uses an XML namespace,
345 as shown here:
346 shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"
348 <?xml version="1.0"?>
349 <resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
350 <row>
351 <field name="Variable_name">version</field>
352 <field name="Value">5.0.40-debug</field>
353 </row>
354 <row>
355 <field name="Variable_name">version_comment</field>
356 <field name="Value">Source distribution</field>
357 </row>
358 <row>
359 <field name="Variable_name">version_compile_machine</field>
360 <field name="Value">i686</field>
361 </row>
362 <row>
363 <field name="Variable_name">version_compile_os</field>
364 <field name="Value">suse-linux-gnu</field>
365 </row>
366 </resultset>
367 (See [1]Bug#25946.)
369 You can also set the following variables by using --var_name=value
372 syntax:
373 · connect_timeout
375 The number of seconds before connection timeout. (Default value is
377 0.)
378 · max_allowed_packet
380 The maximum packet length to send to or receive from the server.
382 (Default value is 16MB.)
383 · max_join_size
385 The automatic limit for rows in a join when using --safe-updates.
387 (Default value is 1,000,000.)
388 · net_buffer_length
390 The buffer size for TCP/IP and socket communication. (Default value
392 is 16KB.)
393 · select_limit
395 The automatic limit for SELECT statements when using --safe-updates.
397 (Default value is 1,000.)
398 It is also possible to set variables by using
401 --set-variable=var_name=value or -O var_name=value syntax. This syntax
402 is deprecated.
403 On Unix, the mysql client writes a record of executed statements to a
405 history file. By default, the history file is named .mysql_history and
406 is created in your home directory. To specify a different file, set the
407 value of the MYSQL_HISTFILE environment variable.
408 If you do not want to maintain a history file, first remove
410 .mysql_history if it exists, and then use either of the following
411 techniques:
412 · Set the MYSQL_HISTFILE variable to /dev/null. To cause this setting
414 to take effect each time you log in, put the setting in one of your
415 shell's startup files.
416 · Create .mysql_history as a symbolic link to /dev/null:
418 shell> ln -s /dev/null $HOME/.mysql_history
420 You need do this only once.
421
423 mysql sends each SQL statement that you issue to the server to be
424 executed. There is also a set of commands that mysql itself interprets.
425 For a list of these commands, type help or \h at the mysql> prompt:
426 mysql> help
428 List of all MySQL commands:
429 Note that all text commands must be first on line and end with ';'
430 ? (\?) Synonym for `help'.
431 charset (\C) Switch to another charset. Might be needed for processing
432 binlog with multi-byte charsets.
433 clear (\c) Clear command.
434 connect (\r) Reconnect to the server. Optional arguments are db and host.
435 delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as
436 new delimiter.
437 edit (\e) Edit command with $EDITOR.
438 ego (\G) Send command to mysql server, display result vertically.
439 exit (\q) Exit mysql. Same as quit.
440 go (\g) Send command to mysql server.
441 help (\h) Display this help.
442 nopager (\n) Disable pager, print to stdout.
443 notee (\t) Don't write into outfile.
444 pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
445 print (\p) Print current command.
446 prompt (\R) Change your mysql prompt.
447 quit (\q) Quit mysql.
448 rehash (\#) Rebuild completion hash.
449 source (\.) Execute an SQL script file. Takes a file name as an argument.
450 status (\s) Get status information from the server.
451 system (\!) Execute a system shell command.
452 tee (\T) Set outfile [to_outfile]. Append everything into given
453 outfile.
454 use (\u) Use another database. Takes database name as argument.
455 warnings (\W) Show warnings after every statement.
456 nowarning (\w) Don't show warnings after every statement.
457 For server side help, type 'help contents'
458 Each command has both a long and short form. The long form is not case
460 sensitive; the short form is. The long form can be followed by an
461 optional semicolon terminator, but the short form should not.
462 If you provide an argument to the help command, mysql uses it as a
464 search string to access server-side help from the contents of the MySQL
465 Reference Manual. For more information, see the section called “MYSQL
466 SERVER-SIDE HELP”.
467 The charset command changes the default character set and issues a SET
469 NAMES statement. This enables the character set to remain synchronized
470 on the client and server if mysql is run with auto-reconnect enabled
471 (which is not recommended), because the changed character set is used
472 for reconnects. This command was added in MySQL 5.0.25.
473 In the delimiter command, you should avoid the use of the backslash
475 (‘\’) character because that is the escape character for MySQL.
476 The edit, nopager, pager, and system commands work only in Unix.
478 The status command provides some information about the connection and
480 the server you are using. If you are running in --safe-updates mode,
481 status also prints the values for the mysql variables that affect your
482 queries.
483 To log queries and their output, use the tee command. All the data
485 displayed on the screen is appended into a given file. This can be very
486 useful for debugging purposes also. You can enable this feature on the
487 command line with the --tee option, or interactively with the tee
488 command. The tee file can be disabled interactively with the notee
489 command. Executing tee again re-enables logging. Without a parameter,
490 the previous file is used. Note that tee flushes query results to the
491 file after each statement, just before mysql prints its next prompt.
492 By using the --pager option, it is possible to browse or search query
494 results in interactive mode with Unix programs such as less, more, or
495 any other similar program. If you specify no value for the option,
496 mysql checks the value of the PAGER environment variable and sets the
497 pager to that. Output paging can be enabled interactively with the
498 pager command and disabled with nopager. The command takes an optional
499 argument; if given, the paging program is set to that. With no
500 argument, the pager is set to the pager that was set on the command
501 line, or stdout if no pager was specified.
502 Output paging works only in Unix because it uses the popen() function,
504 which does not exist on Windows. For Windows, the tee option can be
505 used instead to save query output, although this is not as convenient
506 as pager for browsing output in some situations.
507 Here are a few tips about the pager command:
509 · You can use it to write to a file and the results go only to the
511 file:
512 mysql> pager cat > /tmp/log.txt
514 You can also pass any options for the program that you want to use as
515 your pager:
516 mysql> pager less -n -i -S
518 · In the preceding example, note the -S option. You may find it very
520 useful for browsing wide query results. Sometimes a very wide result
521 set is difficult to read on the screen. The -S option to less can
522 make the result set much more readable because you can scroll it
523 horizontally using the left-arrow and right-arrow keys. You can also
524 use -S interactively within less to switch the horizontal-browse
525 mode on and off. For more information, read the less manual page:
526 shell> man less
528 · You can specify very complex pager commands for handling query
530 output:
531 mysql> pager cat | tee /dr1/tmp/res.txt \
533 | tee /dr2/tmp/res2.txt | less -n -i -S
534 In this example, the command would send query results to two files in
535 two different directories on two different filesystems mounted on /dr1
536 and /dr2, yet still display the results onscreen via less.
537 You can also combine the tee and pager functions. Have a tee file
540 enabled and pager set to less, and you are able to browse the results
541 using the less program and still have everything appended into a file
542 the same time. The difference between the Unix tee used with the pager
543 command and the mysql built-in tee command is that the built-in tee
544 works even if you do not have the Unix tee available. The built-in tee
545 also logs everything that is printed on the screen, whereas the Unix
546 tee used with pager does not log quite that much. Additionally, tee
547 file logging can be turned on and off interactively from within mysql.
548 This is useful when you want to log some queries to a file, but not
549 others.
550 The default mysql> prompt can be reconfigured. The string for defining
552 the prompt can contain the following special sequences:
553 ┌─────────────────────┬───────────────────────────────────────┐
555 │Option │ Description │
556 ├─────────────────────┼───────────────────────────────────────┤
557 │\t │ A tab character │
558 ├─────────────────────┼───────────────────────────────────────┤
559 │\ │ A space (a space follows │
560 │ │ the backslash) │
561 ├─────────────────────┼───────────────────────────────────────┤
562 │\_ │ A space │
563 ├─────────────────────┼───────────────────────────────────────┤
564 │\R │ The current time, in │
565 │ │ 24-hour military time │
566 │ │ (0-23) │
567 ├─────────────────────┼───────────────────────────────────────┤
568 │\r │ The current time, standard │
569 │ │ 12-hour time (1-12) │
570 ├─────────────────────┼───────────────────────────────────────┤
571 │\m │ Minutes of the current │
572 │ │ time │
573 ├─────────────────────┼───────────────────────────────────────┤
574 │\y │ The current year, two │
575 │ │ digits │
576 ├─────────────────────┼───────────────────────────────────────┤
577 │\Y │ The current year, four │
578 │ │ digits │
579 ├─────────────────────┼───────────────────────────────────────┤
580 │\D │ The full current date │
581 ├─────────────────────┼───────────────────────────────────────┤
582 │\s │ Seconds of the current │
583 │ │ time │
584 ├─────────────────────┼───────────────────────────────────────┤
585 │\v │ The server version │
586 ├─────────────────────┼───────────────────────────────────────┤
587 │\w │ The current day of the │
588 │ │ week in three-letter │
589 │ │ format (Mon, Tue, ...) │
590 ├─────────────────────┼───────────────────────────────────────┤
591 │\P │ am/pm │
592 ├─────────────────────┼───────────────────────────────────────┤
593 │\o │ The current month in │
594 │ │ numeric format │
595 ├─────────────────────┼───────────────────────────────────────┤
596 │\O │ The current month in │
597 │ │ three-letter format (Jan, │
598 │ │ Feb, ...) │
599 ├─────────────────────┼───────────────────────────────────────┤
600 │\c │ A counter that increments │
601 │ │ for each statement you │
602 │ │ issue │
603 ├─────────────────────┼───────────────────────────────────────┤
604 │\l │ The current delimiter. │
605 │ │ (New in 5.0.25) │
606 ├─────────────────────┼───────────────────────────────────────┤
607 │\S │ Semicolon │
608 ├─────────────────────┼───────────────────────────────────────┤
609 │\' │ Single quote │
610 ├─────────────────────┼───────────────────────────────────────┤
611 │\" │ Double quote │
612 ├─────────────────────┼───────────────────────────────────────┤
613 │\d │ The default database │
614 ├─────────────────────┼───────────────────────────────────────┤
615 │\h │ The server host │
616 ├─────────────────────┼───────────────────────────────────────┤
617 │\p │ The current TCP/IP port or │
618 │ │ socket file │
619 ├─────────────────────┼───────────────────────────────────────┤
620 │\u │ Your username │
621 ├─────────────────────┼───────────────────────────────────────┤
622 │\U │ Your full │
623 │ │ user_name@host_name │
624 │ │ account │
625 │ │ name │
626 ├─────────────────────┼───────────────────────────────────────┤
627 │\T}:T{ A literal ‘\’ │ │
628 │backslash character │ │
629 ├─────────────────────┼───────────────────────────────────────┤
630 │\n │ A newline character │
631 └─────────────────────┴───────────────────────────────────────┘
632 ‘\’ followed by any other letter just becomes that letter.
634 If you specify the prompt command with no argument, mysql resets the
636 prompt to the default of mysql>.
637 You can set the prompt in several ways:
639 · Use an environment variable. You can set the MYSQL_PS1 environment
641 variable to a prompt string. For example:
642 shell> export MYSQL_PS1="(\u@\h) [\d]> "
644 · Use a command-line option. You can set the --prompt option on the
646 command line to mysql. For example:
647 shell> mysql --prompt="(\u@\h) [\d]> "
649 (user@host) [database]>
650 · Use an option file. You can set the prompt option in the [mysql]
652 group of any MySQL option file, such as /etc/my.cnf or the .my.cnf
653 file in your home directory. For example:
654 [mysql]
656 prompt=(\\u@\\h) [\\d]>\\_
657 In this example, note that the backslashes are doubled. If you set the
658 prompt using the prompt option in an option file, it is advisable to
659 double the backslashes when using the special prompt options. There is
660 some overlap in the set of allowable prompt options and the set of
661 special escape sequences that are recognized in option files. (These
662 sequences are listed in Section 3.2, “Using Option Files”.) The overlap
663 may cause you problems if you use single backslashes. For example, \s
664 is interpreted as a space rather than as the current seconds value. The
665 following example shows how to define a prompt within an option file to
666 include the current time in HH:MM:SS> format:
667 [mysql]
669 prompt="\\r:\\m:\\s> "
670 · Set the prompt interactively. You can change your prompt
672 interactively by using the prompt (or \R) command. For example:
673 mysql> prompt (\u@\h) [\d]>\_
675 PROMPT set to '(\u@\h) [\d]>\_'
676 (user@host) [database]>
677 (user@host) [database]> prompt
678 Returning to default PROMPT of mysql>
679 mysql>
680
682 mysql> help search_string
683 If you provide an argument to the help command, mysql uses it as a
685 search string to access server-side help from the contents of the MySQL
686 Reference Manual. The proper operation of this command requires that
687 the help tables in the mysql database be initialized with help topic
688 information (see the section called “SERVER-SIDE HELP”).
689 If there is no match for the search string, the search fails:
691 mysql> help me
693 Nothing found
694 Please try to run 'help contents' for a list of all accessible topics
695 Use help contents to see a list of the help categories:
697 mysql> help contents
699 You asked for help about help category: "Contents"
700 For more information, type 'help <item>', where <item> is one of the
701 following categories:
702 Account Management
703 Administration
704 Data Definition
705 Data Manipulation
706 Data Types
707 Functions
708 Functions and Modifiers for Use with GROUP BY
709 Geographic Features
710 Language Structure
711 Storage Engines
712 Stored Routines
713 Table Maintenance
714 Transactions
715 Triggers
716 If the search string matches multiple items, mysql shows a list of
718 matching topics:
719 mysql> help logs
721 Many help items for your request exist.
722 To make a more specific request, please type 'help <item>',
723 where <item> is one of the following topics:
724 SHOW
725 SHOW BINARY LOGS
726 SHOW ENGINE
727 SHOW LOGS
728 Use a topic as the search string to see the help entry for that topic:
730 mysql> help show binary logs
732 Name: 'SHOW BINARY LOGS'
733 Description:
734 Syntax:
735 SHOW BINARY LOGS
736 SHOW MASTER LOGS
737 Lists the binary log files on the server. This statement is used as
738 part of the procedure described in [purge-master-logs], that shows how
739 to determine which logs can be purged.
740 mysql> SHOW BINARY LOGS;
741 +---------------+-----------+
742 | Log_name | File_size |
743 +---------------+-----------+
744 | binlog.000015 | 724935 |
745 | binlog.000016 | 733481 |
746 +---------------+-----------+
747
749 The mysql client typically is used interactively, like this:
750 shell> mysql db_name
752 However, it is also possible to put your SQL statements in a file and
754 then tell mysql to read its input from that file. To do so, create a
755 text file text_file that contains the statements you wish to execute.
756 Then invoke mysql as shown here:
757 shell> mysql db_name < text_file
759 If you place a USE db_name statement as the first statement in the
761 file, it is unnecessary to specify the database name on the command
762 line:
763 shell> mysql < text_file
765 If you are already running mysql, you can execute an SQL script file
767 using the source command or \. command:
768 mysql> source file_name
770 mysql> \. file_name
771 Sometimes you may want your script to display progress information to
773 the user. For this you can insert statements like this:
774 SELECT '<info_to_display>' AS ' ';
776 The statement shown outputs <info_to_display>.
778 For more information about batch mode, see Section 5, “Using mysql in
780 Batch Mode”.
781
783 This section describes some techniques that can help you use mysql more
784 effectively.
785 Displaying Query Results Vertically
787 Some query results are much more readable when displayed vertically,
788 instead of in the usual horizontal table format. Queries can be
789 displayed vertically by terminating the query with \G instead of a
790 semicolon. For example, longer text values that include newlines often
791 are much easier to read with vertical output:
792 mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
794 *************************** 1. row ***************************
795 msg_nro: 3068
796 date: 2000-03-01 23:29:50
797 time_zone: +0200
798 mail_from: Monty
799 reply: monty@no.spam.com
800 mail_to: "Thimble Smith" <tim@no.spam.com>
801 sbj: UTF-8
802 txt: >>>>> "Thimble" == Thimble Smith writes:
803 Thimble> Hi. I think this is a good idea. Is anyone familiar
804 Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
805 Thimble> TODO list and see what happens.
806 Yes, please do that.
807 Regards,
808 Monty
809 file: inbox-jani-1
810 hash: 190402944
811 1 row in set (0.09 sec)
812 Using the --safe-updates Option
814 For beginners, a useful startup option is --safe-updates (or
815 --i-am-a-dummy, which has the same effect). It is helpful for cases
816 when you might have issued a DELETE FROM tbl_name statement but
817 forgotten the WHERE clause. Normally, such a statement deletes all rows
818 from the table. With --safe-updates, you can delete rows only by
819 specifying the key values that identify them. This helps prevent
820 accidents.
821 When you use the --safe-updates option, mysql issues the following
823 statement when it connects to the MySQL server:
824 SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000, SQL_MAX_JOIN_SIZE=1000000;
826 See Section 5.3, “SET Syntax”.
828 The SET statement has the following effects:
830 · You are not allowed to execute an UPDATE or DELETE statement unless
832 you specify a key constraint in the WHERE clause or provide a LIMIT
833 clause (or both). For example:
834 UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
836 UPDATE tbl_name SET not_key_column=val LIMIT 1;
837 · The server limits all large SELECT results to 1,000 rows unless the
839 statement includes a LIMIT clause.
840 · The server aborts multiple-table SELECT statements that probably
842 need to examine more than 1,000,000 row combinations.
843 To specify limits different from 1,000 and 1,000,000, you can override
846 the defaults by using the --select_limit and --max_join_size options:
847 shell> mysql --safe-updates --select_limit=500 --max_join_size=10000
849 Disabling mysql Auto-Reconnect
851 If the mysql client loses its connection to the server while sending a
852 statement, it immediately and automatically tries to reconnect once to
853 the server and send the statement again. However, even if mysql
854 succeeds in reconnecting, your first connection has ended and all your
855 previous session objects and settings are lost: temporary tables, the
856 autocommit mode, and user-defined and session variables. Also, any
857 current transaction rolls back. This behavior may be dangerous for you,
858 as in the following example where the server was shut down and
859 restarted between the first and second statements without you knowing
860 it:
861 mysql> SET @a=1;
863 Query OK, 0 rows affected (0.05 sec)
864 mysql> INSERT INTO t VALUES(@a);
865 ERROR 2006: MySQL server has gone away
866 No connection. Trying to reconnect...
867 Connection id: 1
868 Current database: test
869 Query OK, 1 row affected (1.30 sec)
870 mysql> SELECT * FROM t;
871 +------+
872 | a |
873 +------+
874 | NULL |
875 +------+
876 1 row in set (0.05 sec)
877 The @a user variable has been lost with the connection, and after the
879 reconnection it is undefined. If it is important to have mysql
880 terminate with an error if the connection has been lost, you can start
881 the mysql client with the --skip-reconnect option.
882 For more information about auto-reconnect and its effect on state
884 information when a reconnection occurs, see Section 2.13, “Controlling
885 Automatic Reconnect Behavior”.
886
888 Copyright 1997-2007 MySQL AB
889 This documentation is NOT distributed under a GPL license. Use of this
891 documentation is subject to the following terms: You may create a
892 printed copy of this documentation solely for your own personal use.
893 Conversion to other formats is allowed as long as the actual content is
894 not altered or edited in any way. You shall not publish or distribute
895 this documentation in any form or on any media, except if you
896 distribute the documentation in a manner similar to how MySQL
897 disseminates it (that is, electronically for download on a Web site
898 with the software) or on a CD-ROM or similar medium, provided however
899 that the documentation is disseminated together with the software on
900 the same medium. Any other use, such as any dissemination of printed
901 copies or use of this documentation, in whole or in part, in another
902 publication, requires the prior written consent from an authorized
903 representative of MySQL AB. MySQL AB reserves any and all rights to
904 this documentation not expressly granted above.
905 Please email <docs@mysql.com> for more information.
907
909 1. Bug#25946
910 http://bugs.mysql.com/25946
911
913 For more information, please refer to the MySQL Reference Manual, which
914 may already be installed locally and which is also available online at
915 http://dev.mysql.com/doc/.
916
918 MySQL AB (http://www.mysql.com/). This software comes with no
919 warranty.
920MySQL 5.0 07/04/2007 MYSQL(1)