2
3
4
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
19 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
26 Using mysql is very easy. Invoke it from the prompt of your command
27 interpreter as follows:
28
29 shell> mysql db_name
30
31 Or:
32
33 shell> mysql --user=user_name --password=your_password db_name
34
35 Then type an SQL statement, end it with ‘;’, \g, or \G and press Enter.
36
37 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
42 You can execute SQL statements in a script file (batch file) like this:
43
44 shell> mysql db_name < script.sql > output.tab
45
47 mysql supports the following options:
48
49 · --help, -?
50
51 Display a help message and exit.
52
53 · --auto-rehash
54
55 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
61 · --batch, -B
62
63 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
66 · --character-sets-dir=path
67
68 The directory where character sets are installed. See Section 8.1,
69 “The Character Set Used for Data and Sorting”.
70
71 · --column-names
72
73 Write column names in results.
74
75 · --compress, -C
76
77 Compress all information sent between the client and the server if
78 both support compression.
79
80 · --database=db_name, -D db_name
81
82 The database to use. This is useful primarily in an option file.
83
84 · --debug[=debug_options], -# [debug_options]
85
86 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
89 · --debug-info, -T
90
91 Print some debugging information when the program exits.
92
93 · --default-character-set=charset_name
94
95 Use charset_name as the default character set. See Section 8.1, “The
96 Character Set Used for Data and Sorting”.
97
98 · --delimiter=str
99
100 Set the statement delimiter. The default is the semicolon character
101 (‘;’).
102
103 · --execute=statement, -e statement
104
105 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
109 · --force, -f
110
111 Continue even if an SQL error occurs.
112
113 · --host=host_name, -h host_name
114
115 Connect to the MySQL server on the given host.
116
117 · --html, -H
118
119 Produce HTML output.
120
121 · --ignore-spaces, -i
122
123 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
127 · --line-numbers
128
129 Write line numbers for errors. Disable this with
130 --skip-line-numbers.
131
132 · --local-infile[={0|1}]
133
134 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
140 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
145 · --named-commands, -G
146
147 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
152 · --no-auto-rehash, -A
153
154 Deprecated form of -skip-auto-rehash. See the description for
155 --auto-rehash.
156
157 · --no-beep, -b
158
159 Do not beep when errors occur.
160
161 · --no-named-commands, -g
162
163 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
169 · --no-pager
170
171 Deprecated form of --skip-pager. See the --pager option.
172
173 · --no-tee
174
175 Do not copy output to a file. the section called “MYSQL COMMANDS”,
176 discusses tee files further.
177
178 · --one-database, -o
179
180 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
184 · --pager[=command]
185
186 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
193 · --password[=password], -p[password]
194
195 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
201 Specifying a password on the command line should be considered
202 insecure. See Section 6.6, “Keeping Your Password Secure”.
203
204 · --port=port_num, -P port_num
205
206 The TCP/IP port number to use for the connection.
207
208 · --prompt=format_str
209
210 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
214 · --protocol={TCP|SOCKET|PIPE|MEMORY}
215
216 The connection protocol to use.
217
218 · --quick, -q
219
220 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
224 · --raw, -r
225
226 Write column values without escape conversion. Often used with the
227 --batch option.
228
229 · --reconnect
230
231 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
236 · --safe-updates, --i-am-a-dummy, -U
237
238 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
244 · --secure-auth
245
246 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
250 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
254 · --show-warnings
255
256 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
260 · --sigint-ignore
261
262 Ignore SIGINT signals (typically the result of typing Control-C).
263
264 · --silent, -s
265
266 Silent mode. Produce less output. This option can be given multiple
267 times to produce less and less output.
268
269 · --skip-column-names, -N
270
271 Do not write column names in results.
272
273 · --skip-line-numbers, -L
274
275 Do not write line numbers for errors. Useful when you want to
276 compare result files that include error messages.
277
278 · --socket=path, -S path
279
280 For connections to localhost, the Unix socket file to use, or, on
281 Windows, the name of the named pipe to use.
282
283 · --ssl*
284
285 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
289 · --table, -t
290
291 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
294 · --tee=file_name
295
296 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
300 · --unbuffered, -n
301
302 Flush the buffer after each query.
303
304 · --user=user_name, -u user_name
305
306 The MySQL username to use when connecting to the server.
307
308 · --verbose, -v
309
310 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
315 · --version, -V
316
317 Display version information and exit.
318
319 · --vertical, -E
320
321 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
325 · --wait, -w
326
327 If the connection cannot be established, wait and retry instead of
328 aborting.
329
330 · --xml, -X
331
332 Produce XML output.
333
334 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
339 <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
344 Beginning with MySQL 5.0.40, the XML output also uses an XML namespace,
345 as shown here:
346
347 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
368 (See [1]Bug#25946.)
369
370
371 You can also set the following variables by using --var_name=value
372 syntax:
373
374 · connect_timeout
375
376 The number of seconds before connection timeout. (Default value is
377 0.)
378
379 · max_allowed_packet
380
381 The maximum packet length to send to or receive from the server.
382 (Default value is 16MB.)
383
384 · max_join_size
385
386 The automatic limit for rows in a join when using --safe-updates.
387 (Default value is 1,000,000.)
388
389 · net_buffer_length
390
391 The buffer size for TCP/IP and socket communication. (Default value
392 is 16KB.)
393
394 · select_limit
395
396 The automatic limit for SELECT statements when using --safe-updates.
397 (Default value is 1,000.)
398
399
400 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
404 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
409 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
413 · 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
417 · Create .mysql_history as a symbolic link to /dev/null:
418
419 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
427 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
459 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
463 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
468 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
474 In the delimiter command, you should avoid the use of the backslash
475 (‘\’) character because that is the escape character for MySQL.
476
477 The edit, nopager, pager, and system commands work only in Unix.
478
479 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
484 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
493 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
503 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
508 Here are a few tips about the pager command:
509
510 · You can use it to write to a file and the results go only to the
511 file:
512
513 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
517 mysql> pager less -n -i -S
518
519 · 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
527 shell> man less
528
529 · You can specify very complex pager commands for handling query
530 output:
531
532 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
538
539 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
551 The default mysql> prompt can be reconfigured. The string for defining
552 the prompt can contain the following special sequences:
553
554 ┌─────────────────────┬───────────────────────────────────────┐
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
633 ‘\’ followed by any other letter just becomes that letter.
634
635 If you specify the prompt command with no argument, mysql resets the
636 prompt to the default of mysql>.
637
638 You can set the prompt in several ways:
639
640 · Use an environment variable. You can set the MYSQL_PS1 environment
641 variable to a prompt string. For example:
642
643 shell> export MYSQL_PS1="(\u@\h) [\d]> "
644
645 · Use a command-line option. You can set the --prompt option on the
646 command line to mysql. For example:
647
648 shell> mysql --prompt="(\u@\h) [\d]> "
649 (user@host) [database]>
650
651 · 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
655 [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
668 [mysql]
669 prompt="\\r:\\m:\\s> "
670
671 · Set the prompt interactively. You can change your prompt
672 interactively by using the prompt (or \R) command. For example:
673
674 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
684 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
690 If there is no match for the search string, the search fails:
691
692 mysql> help me
693 Nothing found
694 Please try to run 'help contents' for a list of all accessible topics
695
696 Use help contents to see a list of the help categories:
697
698 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
717 If the search string matches multiple items, mysql shows a list of
718 matching topics:
719
720 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
729 Use a topic as the search string to see the help entry for that topic:
730
731 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
751 shell> mysql db_name
752
753 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
758 shell> mysql db_name < text_file
759
760 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
764 shell> mysql < text_file
765
766 If you are already running mysql, you can execute an SQL script file
767 using the source command or \. command:
768
769 mysql> source file_name
770 mysql> \. file_name
771
772 Sometimes you may want your script to display progress information to
773 the user. For this you can insert statements like this:
774
775 SELECT '<info_to_display>' AS ' ';
776
777 The statement shown outputs <info_to_display>.
778
779 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
786 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
793 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
813 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
822 When you use the --safe-updates option, mysql issues the following
823 statement when it connects to the MySQL server:
824
825 SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000, SQL_MAX_JOIN_SIZE=1000000;
826
827 See Section 5.3, “SET Syntax”.
828
829 The SET statement has the following effects:
830
831 · 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
835 UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
836 UPDATE tbl_name SET not_key_column=val LIMIT 1;
837
838 · The server limits all large SELECT results to 1,000 rows unless the
839 statement includes a LIMIT clause.
840
841 · The server aborts multiple-table SELECT statements that probably
842 need to examine more than 1,000,000 row combinations.
843
844
845 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
848 shell> mysql --safe-updates --select_limit=500 --max_join_size=10000
849
850 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
862 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
878 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
883 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
890 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
906 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.
920
921
922
923MySQL 5.0 07/04/2007 MYSQL(1)