1MYSQL(1) MariaDB Database System MYSQL(1)
2
6 mysql - the MariaDB 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 Typing Control-C causes mysql to attempt to kill the current statement.
38 If this cannot be done, or Control-C is typed again before the
39 statement is killed, mysql exits.
40 You can execute SQL statements in a script file (batch file) like this:
42 shell> mysql db_name < script.sql > output.tab
44
46 mysql supports the following options, which can be specified on the
47 command line or in the [mysql], [client], [client-server] or [client-
48 mariadb] option file groups. mysql also supports the options for
49 processing option files.
50 · --help, -?, -I
52 Display a help message and exit.
54 · --abort-source-on-error
56 Abort 'source filename' operations in case of errors.
58 · --auto-rehash
60 Enable automatic rehashing. This option is on by default, which
62 enables database, table, and column name completion. Use
63 --disable-auto-rehash, --no-auto-rehash, or --skip-auto-rehash to
64 disable rehashing. That causes mysql to start faster, but you must
65 issue the rehash command if you want to use name completion.
66 To complete a name, enter the first part and press Tab. If the name
68 is unambiguous, mysql completes it. Otherwise, you can press Tab
69 again to see the possible names that begin with what you have typed
70 so far. Completion does not occur if there is no default database.
71 · --auto-vertical-output
73 Automatically switch to vertical output mode if the result is wider
75 than the terminal width.
76 · --batch, -B
78 Print results using tab as the column separator, with each row on a
80 new line. With this option, mysql does not use the history file.
81 Batch mode results in nontabular output format and escaping of
83 special characters. Escaping may be disabled by using raw mode; see
84 the description for the --raw option.
85 · --binary-mode
87 By default, ASCII '\0' is disallowed and '\r\n' is translated to
89 '\n'. This switch turns off both features, and also turns off
90 parsing of all client commands except \C and DELIMITER, in non-
91 interactive mode (for input piped to mysql or loaded using the
92 'source' command). This is necessary when processing output from
93 mysqlbinlog that may contain blobs.
94 · --character-sets-dir=path
96 The directory where character sets are installed.
98 · --column-names
100 Write column names in results.
102 · --column-type-info, -m
104 Display result set metadata.
106 · --comments, -c
108 Whether to preserve comments in statements sent to the server. The
110 default is --skip-comments (discard comments), enable with
111 --comments (preserve comments).
112 · --compress, -C
114 Compress all information sent between the client and the server if
116 both support compression.
117 · --connect-timeout=seconds
119 Set the number of seconds before connection timeout. (Default value
121 is 0.)
122 · --database=db_name, -D db_name
124 The database to use.
126 · --debug[=debug_options], -# [debug_options]
128 Write a debugging log. A typical debug_options string is
130 ´d:t:o,file_name´. The default is ´d:t:o,/tmp/mysql.trace´.
131 · --debug-check
133 Print some debugging information when the program exits.
135 · --debug-info, -T
137 Prints debugging information and memory and CPU usage statistics
139 when the program exits.
140 · --default-auth=name
142 Default authentication client-side plugin to use.
144 · --default-character-set=charset_name
146 Use charset_name as the default character set for the client and
148 connection.
149 A common issue that can occur when the operating system uses utf8
151 or another multi-byte character set is that output from the mysql
152 client is formatted incorrectly, due to the fact that the MariaDB
153 client uses the latin1 character set by default. You can usually
154 fix such issues by using this option to force the client to use the
155 system character set instead.
156 · --defaults-extra-file=filename
158 Set filename as the file to read default options from after the
160 global defaults files has been read. Must be given as first
161 option.
162 · --defaults-file=filename
164 Set filename as the file to read default options from, override
166 global defaults files. Must be given as first option.
167 · --defaults-group-suffix=suffix
169 In addition to the groups named on the command line, read groups
171 that have the given suffix.
172 · --delimiter=str
174 Set the statement delimiter. The default is the semicolon character
176 (“;”).
177 · --disable-named-commands
179 Disable named commands. Use the \* form only, or use named commands
181 only at the beginning of a line ending with a semicolon (“;”).
182 mysql starts with this option enabled by default. However, even
183 with this option, long-format commands still work from the first
184 line. See the section called “MYSQL COMMANDS”.
185 · --execute=statement, -e statement
187 Execute the statement and quit. Disables --force and history file.
189 The default output format is like that produced with --batch.
190 · --force, -f
192 Continue even if an SQL error occurs. Sets --abort-source-on-error
194 to 0.
195 · --host=host_name, -h host_name
197 Connect to the MariaDB server on the given host.
199 · --html, -H
201 Produce HTML output.
203 · --ignore-spaces, -i
205 Ignore spaces after function names. Allows one to have spaces
207 (including tab characters and new line characters) between function
208 name and '('. The drawback is that this causes built in functions
209 to become reserved words.
210 · --init-command=str
212 SQL Command to execute when connecting to the MariaDB server. Will
214 automatically be re-executed when reconnecting.
215 · --line-numbers
217 Write line numbers for errors. Disable this with
219 --skip-line-numbers.
220 · --local-infile[={0|1}]
222 Enable or disable LOCAL capability for LOAD DATA INFILE. With no
224 value, the option enables LOCAL. The option may be given as
225 --local-infile=0 or --local-infile=1 to explicitly disable or
226 enable LOCAL. Enabling LOCAL has no effect if the server does not
227 also support it.
228 · --max-allowed-packet=num
230 Set the maximum packet length to send to or receive from the
232 server. (Default value is 16MB, largest 1GB.)
233 · --max-join-size=num
235 Set the automatic limit for rows in a join when using
237 --safe-updates. (Default value is 1,000,000.)
238 · --named-commands, -G
240 Enable named mysql commands. Long-format commands are allowed, not
242 just short-format commands. For example, quit and \q both are
243 recognized. Use --skip-named-commands to disable named commands.
244 See the section called “MYSQL COMMANDS”. Disabled by default.
245 ·· --net-buffer-length=size
247 Set the buffer size for TCP/IP and socket communication. (Default
249 value is 16KB.)
250 · --no-auto-rehash, -A
252 This has the same effect as --skip-auto-rehash. See the description
254 for --auto-rehash.
255 · --no-beep, -b
257 Do not beep when errors occur.
259 · --no-defaults
261 Do not read default options from any option file. This must be
263 given as the first argument.
264 · --one-database, -o
266 Ignore statements except those those that occur while the default
268 database is the one named on the command line. This filtering is
269 limited, and based only on USE statements. This is useful for
270 skipping updates to other databases in the binary log.
271 · --pager[=command]
273 Use the given command for paging query output. If the command is
275 omitted, the default pager is the value of your PAGER environment
276 variable. Valid pagers are less, more, cat [> filename], and so
277 forth. This option works only on Unix and only in interactive mode.
278 To disable paging, use --skip-pager. the section called “MYSQL
279 COMMANDS”, discusses output paging further.
280 · --password[=password], -p[password]
282 The password to use when connecting to the server. If you use the
284 short option form (-p), you cannot have a space between the option
285 and the password. If you omit the password value following the
286 --password or -p option on the command line, mysql prompts for one.
287 Specifying a password on the command line should be considered
289 insecure. You can use an option file to avoid giving the password
290 on the command line.
291 · --pipe, -W
293 On Windows, connect to the server via a named pipe. This option
295 applies only if the server supports named-pipe connections.
296 · --plugin-dir=dir_name
298 Directory for client-side plugins.
300 · --port=port_num, -P port_num
302 The TCP/IP port number to use for the connection or 0 for default
304 to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services,
305 built-in default (3306).
306 · --print-defaults
308 Print the program argument list and exit. This must be given as the
310 first argument.
311 · --progress-reports
313 Get progress reports for long running commands (such as ALTER
315 TABLE). (Defaults to on; use --skip-progress-reports to disable.)
316 · --prompt=format_str
318 Set the prompt to the specified format. The special sequences that
320 the prompt can contain are described in the section called “MYSQL
321 COMMANDS”.
322 · --protocol={TCP|SOCKET|PIPE|MEMORY}
324 The connection protocol to use for connecting to the server. It is
326 useful when the other connection parameters normally would cause a
327 protocol to be used other than the one you want.
328 · --quick, -q
330 Do not cache each query result, print each row as it is received.
332 This may slow down the server if the output is suspended. With this
333 option, mysql does not use the history file.
334 · --raw, -r
336 For tabular output, the “boxing” around columns enables one column
338 value to be distinguished from another. For nontabular output (such
339 as is produced in batch mode or when the --batch or --silent option
340 is given), special characters are escaped in the output so they can
341 be identified easily. Newline, tab, NUL, and backslash are written
342 as \n, \t, \0, and \\. The --raw option disables this character
343 escaping.
344 The following example demonstrates tabular versus nontabular output
346 and the use of raw mode to disable escaping:
347 % mysql
349 mysql> SELECT CHAR(92);
350 +----------+
351 | CHAR(92) |
352 +----------+
353 | \ |
354 +----------+
355 % mysql -s
356 mysql> SELECT CHAR(92);
357 CHAR(92)
358 \\
359 % mysql -s -r
360 mysql> SELECT CHAR(92);
361 CHAR(92)
362 \
363 · --reconnect
365 If the connection to the server is lost, automatically try to
367 reconnect. A single reconnect attempt is made each time the
368 connection is lost. Enabled by default, to disable use
369 --skip-reconnect or --disable-reconnect.
370 · --safe-updates, --i-am-a-dummy, -U
372 Allow only those UPDATE and DELETE statements that specify which
374 rows to modify by using key values. If you have set this option in
375 an option file, you can override it by using --safe-updates on the
376 command line. See the section called “MYSQL TIPS”, for more
377 information about this option.
378 · --secure-auth
380 Do not send passwords to the server in old (pre-4.1.1) format. This
382 prevents connections except for servers that use the newer password
383 format.
384 · --select-limit=limit
386 Set automatic limit for SELECT when using --safe-updates. (Default
388 value is 1,000.)
389 · --server-arg=name
391 Send name as a parameter to the embedded server.
393 · --show-warnings
395 Cause warnings to be shown after each statement if there are any.
397 This option applies to interactive and batch mode.
398 · --sigint-ignore
400 Ignore SIGINT signals (typically the result of typing Control-C).
402 · --silent, -s
404 Silent mode. Produce less output. This option can be given multiple
406 times to produce less and less output.
407 This option results in nontabular output format and escaping of
409 special characters. Escaping may be disabled by using raw mode; see
410 the description for the --raw option.
411 · --skip-auto-rehash
413 Disable automatic rehashing. Synonym for --disable-auto-rehash.
415 · --skip-column-names, -N
417 Do not write column names in results.
419 · --skip-line-numbers, -L
421 Do not write line numbers for errors. Useful when you want to
423 compare result files that include error messages.
424 · --socket=path, -S path
426 For connections to localhost, the Unix socket file to use, or, on
428 Windows, the name of the named pipe to use.
429 · --ssl
431 Enable SSL for connection (automatically enabled with other flags).
433 Disable with --skip-ssl.
434 · --ssl-ca=name
436 CA file in PEM format (check OpenSSL docs, implies --ssl).
438 · --ssl-capath=name
440 CA directory (check OpenSSL docs, implies --ssl).
442 · --ssl-cert=name
444 X509 cert in PEM format (check OpenSSL docs, implies --ssl).
446 · --ssl-cipher=name
448 SSL cipher to use (check OpenSSL docs, implies --ssl).
450 · --ssl-key=name
452 X509 key in PEM format (check OpenSSL docs, implies --ssl).
454 · --ssl-crl=name
456 Certificate revocation list (check OpenSSL docs, implies --ssl).
458 · --ssl-crlpath=name
460 Certificate revocation list path (check OpenSSL docs, implies
462 --ssl).
463 · --ssl-verify-server-cert
465 Verify server's "Common Name" in its cert against hostname used
467 when connecting. This option is disabled by default.
468 · --table, -t
470 Display output in table format. This is the default for interactive
472 use, but can be used to produce table output in batch mode.
473 · --tee=file_name
475 Append a copy of output to the given file. This option works only
477 in interactive mode. the section called “MYSQL COMMANDS”,
478 discusses tee files further.
479 · --unbuffered, -n
481 Flush the buffer after each query.
483 · --user=user_name, -u user_name
485 The MariaDB user name to use when connecting to the server.
487 · --verbose, -v
489 Verbose mode. Produce more output about what the program does. This
491 option can be given multiple times to produce more and more output.
492 (For example, -v -v -v produces table output format even in batch
493 mode.)
494 · --version, -V
496 Display version information and exit.
498 · --vertical, -E
500 Print query output rows vertically (one line per column value).
502 Without this option, you can specify vertical output for individual
503 statements by terminating them with \G.
504 · --wait, -w
506 If the connection cannot be established, wait and retry instead of
508 aborting.
509 · --xml, -X
511 Produce XML output. The output when --xml is used with mysql
513 matches that of mysqldump --xml. See mysqldump(1) for details.
514 The XML output also uses an XML namespace, as shown here:
516 shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE ´version%´"
518 <?xml version="1.0"?>
519 <resultset statement="SHOW VARIABLES LIKE ´version%´" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
520 <row>
521 <field name="Variable_name">version</field>
522 <field name="Value">5.0.40-debug</field>
523 </row>
524 <row>
525 <field name="Variable_name">version_comment</field>
526 <field name="Value">Source distribution</field>
527 </row>
528 <row>
529 <field name="Variable_name">version_compile_machine</field>
530 <field name="Value">i686</field>
531 </row>
532 <row>
533 <field name="Variable_name">version_compile_os</field>
534 <field name="Value">suse-linux-gnu</field>
535 </row>
536 </resultset>
537 You can also set the following variables by using --var_name=value.
539 · connect_timeout
541 The number of seconds before connection timeout. (Default value
543 is 0.)
544 · max_allowed_packet
546 The maximum packet length to send to or receive from the
548 server. (Default value is 16MB.)
549 · max_join_size
551 The automatic limit for rows in a join when using
553 --safe-updates. (Default value is 1,000,000.)
554 · net_buffer_length
556 The buffer size for TCP/IP and socket communication. (Default
558 value is 16KB.)
559 · select_limit
561 The automatic limit for SELECT statements when using
563 --safe-updates. (Default value is 1,000.)
564 On Unix, the mysql client writes a record of executed statements to
566 a history file. By default, this file is named .mysql_history and
567 is created in your home directory. To specify a different file, set
568 the value of the MYSQL_HISTFILE environment variable.
569 The .mysql_history should be protected with a restrictive access
571 mode because sensitive information might be written to it, such as
572 the text of SQL statements that contain passwords.
573 If you do not want to maintain a history file, first remove
575 .mysql_history if it exists, and then use either of the following
576 techniques:
577 · Set the MYSQL_HISTFILE variable to /dev/null. To cause this
579 setting to take effect each time you log in, put the setting in
580 one of your shell´s startup files.
581 · Create .mysql_history as a symbolic link to /dev/null:
583 shell> ln -s /dev/null $HOME/.mysql_history
585 You need do this only once.
587
589 mysql sends each SQL statement that you issue to the server to be
590 executed. There is also a set of commands that mysql itself interprets.
591 For a list of these commands, type help or \h at the mysql> prompt:
592 mysql> help
594 List of all MySQL commands:
595 Note that all text commands must be first on line and end with ´;´
596 ? (\?) Synonym for `help´.
597 clear (\c) Clear command.
598 connect (\r) Reconnect to the server. Optional arguments are db and host.
599 delimiter (\d) Set statement delimiter.
600 edit (\e) Edit command with $EDITOR.
601 ego (\G) Send command to mysql server, display result vertically.
602 exit (\q) Exit mysql. Same as quit.
603 go (\g) Send command to mysql server.
604 help (\h) Display this help.
605 nopager (\n) Disable pager, print to stdout.
606 notee (\t) Don´t write into outfile.
607 pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
608 print (\p) Print current command.
609 prompt (\R) Change your mysql prompt.
610 quit (\q) Quit mysql.
611 rehash (\#) Rebuild completion hash.
612 source (\.) Execute an SQL script file. Takes a file name as an argument.
613 status (\s) Get status information from the server.
614 system (\!) Execute a system shell command.
615 tee (\T) Set outfile [to_outfile]. Append everything into given
616 outfile.
617 use (\u) Use another database. Takes database name as argument.
618 charset (\C) Switch to another charset. Might be needed for processing
619 binlog with multi-byte charsets.
620 warnings (\W) Show warnings after every statement.
621 nowarning (\w) Don´t show warnings after every statement.
622 For server side help, type ´help contents´
623 Each command has both a long and short form. The long form is not case
625 sensitive; the short form is. The long form can be followed by an
626 optional semicolon terminator, but the short form should not.
627 The use of short-form commands within multi-line /* ... */ comments is
629 not supported.
630 · help [arg], \h [arg], \? [arg], ? [arg]
632 Display a help message listing the available mysql commands.
634 If you provide an argument to the help command, mysql uses it as a
636 search string to access server-side help. For more information, see
637 the section called “MYSQL SERVER-SIDE HELP”.
638 · charset charset_name, \C charset_name
640 Change the default character set and issue a SET NAMES statement.
642 This enables the character set to remain synchronized on the client
643 and server if mysql is run with auto-reconnect enabled (which is
644 not recommended), because the specified character set is used for
645 reconnects.
646 · clear, \c
648 Clear the current input. Use this if you change your mind about
650 executing the statement that you are entering.
651 · connect [db_name host_name]], \r [db_name host_name]]
653 Reconnect to the server. The optional database name and host name
655 arguments may be given to specify the default database or the host
656 where the server is running. If omitted, the current values are
657 used.
658 · delimiter str, \d str
660 Change the string that mysql interprets as the separator between
662 SQL statements. The default is the semicolon character (“;”).
663 The delimiter can be specified as an unquoted or quoted argument.
665 Quoting can be done with either single quote (´) or douple quote
666 (") characters. To include a quote within a quoted string, either
667 quote the string with the other quote character or escape the quote
668 with a backslash (“\”) character. Backslash should be avoided
669 outside of quoted strings because it is the escape character for
670 MariaDB. For an unquoted argument, the delmiter is read up to the
671 first space or end of line. For a quoted argument, the delimiter is
672 read up to the matching quote on the line.
673 When the delimiter recognized by mysql is set to something other
675 than the default of “;”, instances of that character are sent to
676 the server without interpretation. However, the server itself still
677 interprets “;” as a statement delimiter and processes statements
678 accordingly. This behavior on the server side comes into play for
679 multiple-statement execution, and for parsing the body of stored
680 procedures and functions, triggers, and events.
681 · edit, \e
683 Edit the current input statement. mysql checks the values of the
685 EDITOR and VISUAL environment variables to determine which editor
686 to use. The default editor is vi if neither variable is set.
687 The edit command works only in Unix.
689 · ego, \G
691 Send the current statement to the server to be executed and display
693 the result using vertical format.
694 · exit, \q
696 Exit mysql.
698 · go, \g
700 Send the current statement to the server to be executed.
702 · nopager, \n
704 Disable output paging. See the description for pager.
706 The nopager command works only in Unix.
708 · notee, \t
710 Disable output copying to the tee file. See the description for
712 tee.
713 · nowarning, \w
715 Enable display of warnings after each statement.
717 · pager [command], \P [command]
719 Enable output paging. By using the --pager option when you invoke
721 mysql, it is possible to browse or search query results in
722 interactive mode with Unix programs such as less, more, or any
723 other similar program. If you specify no value for the option,
724 mysql checks the value of the PAGER environment variable and sets
725 the pager to that. Pager functionality works only in interactive
726 mode.
727 Output paging can be enabled interactively with the pager command
729 and disabled with nopager. The command takes an optional argument;
730 if given, the paging program is set to that. With no argument, the
731 pager is set to the pager that was set on the command line, or
732 stdout if no pager was specified.
733 Output paging works only in Unix because it uses the popen()
735 function, which does not exist on Windows. For Windows, the tee
736 option can be used instead to save query output, although it is not
737 as convenient as pager for browsing output in some situations.
738 · print, \p
740 Print the current input statement without executing it.
742 · prompt [str], \R [str]
744 Reconfigure the mysql prompt to the given string. The special
746 character sequences that can be used in the prompt are described
747 later in this section.
748 If you specify the prompt command with no argument, mysql resets
750 the prompt to the default of mysql>.
751 · quit, \q
753 Exit mysql.
755 · rehash, \#
757 Rebuild the completion hash that enables database, table, and
759 column name completion while you are entering statements. (See the
760 description for the --auto-rehash option.)
761 · source file_name, \. file_name
763 Read the named file and executes the statements contained therein.
765 On Windows, you can specify path name separators as / or \\.
766 · status, \s
768 Provide status information about the connection and the server you
770 are using. If you are running in --safe-updates mode, status also
771 prints the values for the mysql variables that affect your queries.
772 · system command, \! command
774 Execute the given command using your default command interpreter.
776 The system command works only in Unix.
778 · tee [file_name], \T [file_name]
780 By using the --tee option when you invoke mysql, you can log
782 statements and their output. All the data displayed on the screen
783 is appended into a given file. This can be very useful for
784 debugging purposes also. mysql flushes results to the file after
785 each statement, just before it prints its next prompt. Tee
786 functionality works only in interactive mode.
787 You can enable this feature interactively with the tee command.
789 Without a parameter, the previous file is used. The tee file can be
790 disabled with the notee command. Executing tee again re-enables
791 logging.
792 · use db_name, \u db_name
794 Use db_name as the default database.
796 · warnings, \W
798 Enable display of warnings after each statement (if there are any).
800 Here are a few tips about the pager command:
802 · You can use it to write to a file and the results go only to the
804 file:
805 mysql> pager cat > /tmp/log.txt
807 You can also pass any options for the program that you want to use
809 as your pager:
810 mysql> pager less -n -i -S
812 · In the preceding example, note the -S option. You may find it very
814 useful for browsing wide query results. Sometimes a very wide
815 result set is difficult to read on the screen. The -S option to
816 less can make the result set much more readable because you can
817 scroll it horizontally using the left-arrow and right-arrow keys.
818 You can also use -S interactively within less to switch the
819 horizontal-browse mode on and off. For more information, read the
820 less manual page:
821 shell> man less
823 · The -F and -X options may be used with less to cause it to exit if
825 output fits on one screen, which is convenient when no scrolling is
826 necessary:
827 mysql> pager less -n -i -S -F -X
829 · You can specify very complex pager commands for handling query
831 output:
832 mysql> pager cat | tee /dr1/tmp/res.txt \
834 | tee /dr2/tmp/res2.txt | less -n -i -S
835 In this example, the command would send query results to two files
837 in two different directories on two different file systems mounted
838 on /dr1 and /dr2, yet still display the results onscreen via less.
839 You can also combine the tee and pager functions. Have a tee file
841 enabled and pager set to less, and you are able to browse the results
842 using the less program and still have everything appended into a file
843 the same time. The difference between the Unix tee used with the pager
844 command and the mysql built-in tee command is that the built-in tee
845 works even if you do not have the Unix tee available. The built-in tee
846 also logs everything that is printed on the screen, whereas the Unix
847 tee used with pager does not log quite that much. Additionally, tee
848 file logging can be turned on and off interactively from within mysql.
849 This is useful when you want to log some queries to a file, but not
850 others.
851 The prompt command reconfigures the default mysql> prompt. The string
853 for defining the prompt can contain the following special sequences.
854 ┌───────┬────────────────────────────┐
856 │Option │ Description │
857 ├───────┼────────────────────────────┤
858 │\c │ A counter that increments │
859 │ │ for each statement you │
860 │ │ issue │
861 ├───────┼────────────────────────────┤
862 │\D │ The full current date │
863 ├───────┼────────────────────────────┤
864 │\d │ The default database │
865 ├───────┼────────────────────────────┤
866 │\h │ The server host │
867 ├───────┼────────────────────────────┤
868 │\l │ The current delimiter (new │
869 │ │ in 5.1.12) │
870 ├───────┼────────────────────────────┤
871 │\m │ Minutes of the current │
872 │ │ time │
873 ├───────┼────────────────────────────┤
874 │\n │ A newline character │
875 ├───────┼────────────────────────────┤
876 │\O │ The current month in │
877 │ │ three-letter format (Jan, │
878 │ │ Feb, ...) │
879 ├───────┼────────────────────────────┤
880 │\o │ The current month in │
881 │ │ numeric format │
882 ├───────┼────────────────────────────┤
883 │\P │ am/pm │
884 ├───────┼────────────────────────────┤
885 │\p │ The current TCP/IP port or │
886 │ │ socket file │
887 ├───────┼────────────────────────────┤
888 │\R │ The current time, in │
889 │ │ 24-hour military time │
890 │ │ (0–23) │
891 ├───────┼────────────────────────────┤
892 │\r │ The current time, standard │
893 │ │ 12-hour time (1–12) │
894 ├───────┼────────────────────────────┤
895 │\S │ Semicolon │
896 ├───────┼────────────────────────────┤
897 │\s │ Seconds of the current │
898 │ │ time │
899 ├───────┼────────────────────────────┤
900 │\t │ A tab character │
901 ├───────┼────────────────────────────┤
902 │\U │ │
903 │ │ Your full │
904 │ │ user_name@host_name │
905 │ │ account name │
906 ├───────┼────────────────────────────┤
907 │\u │ Your user name │
908 ├───────┼────────────────────────────┤
909 │\v │ The server version │
910 ├───────┼────────────────────────────┤
911 │\w │ The current day of the │
912 │ │ week in three-letter │
913 │ │ format (Mon, Tue, ...) │
914 ├───────┼────────────────────────────┤
915 │\Y │ The current year, four │
916 │ │ digits │
917 ├───────┼────────────────────────────┤
918 │\y │ The current year, two │
919 │ │ digits │
920 ├───────┼────────────────────────────┤
921 │\_ │ A space │
922 ├───────┼────────────────────────────┤
923 │\ │ A space (a space follows │
924 │ │ the backslash) │
925 ├───────┼────────────────────────────┤
926 │\´ │ Single quote │
927 ├───────┼────────────────────────────┤
928 │\" │ Double quote │
929 ├───────┼────────────────────────────┤
930 │\\ │ A literal “\” backslash │
931 │ │ character │
932 ├───────┼────────────────────────────┤
933 │\x │ │
934 │ │ x, for any “x” not │
935 │ │ listed above │
936 └───────┴────────────────────────────┘
937 You can set the prompt in several ways:
939 · Use an environment variable. You can set the MYSQL_PS1 environment
941 variable to a prompt string. For example:
942 shell> export MYSQL_PS1="(\u@\h) [\d]> "
944 · Use a command-line option. You can set the --prompt option on the
946 command line to mysql. For example:
947 shell> mysql --prompt="(\u@\h) [\d]> "
949 (user@host) [database]>
950 · Use an option file. You can set the prompt option in the [mysql]
952 group of any MariaDB option file, such as /etc/my.cnf or the
953 .my.cnf file in your home directory. For example:
954 [mysql]
956 prompt=(\\u@\\h) [\\d]>\\_
957 In this example, note that the backslashes are doubled. If you set
959 the prompt using the prompt option in an option file, it is
960 advisable to double the backslashes when using the special prompt
961 options. There is some overlap in the set of allowable prompt
962 options and the set of special escape sequences that are recognized
963 in option files. The overlap may cause you problems if you use
964 single backslashes. For example, \s is interpreted as a space
965 rather than as the current seconds value. The following example
966 shows how to define a prompt within an option file to include the
967 current time in HH:MM:SS> format:
968 [mysql]
970 prompt="\\r:\\m:\\s> "
971 · Set the prompt interactively. You can change your prompt
973 interactively by using the prompt (or \R) command. For example:
974 mysql> prompt (\u@\h) [\d]>\_
976 PROMPT set to ´(\u@\h) [\d]>\_´
977 (user@host) [database]>
978 (user@host) [database]> prompt
979 Returning to default PROMPT of mysql>
980 mysql>
981
983 mysql> help search_string
984 If you provide an argument to the help command, mysql uses it as a
986 search string to access server-side help. The proper operation of this
987 command requires that the help tables in the mysql database be
988 initialized with help topic information.
989 If there is no match for the search string, the search fails:
991 mysql> help me
993 Nothing found
994 Please try to run ´help contents´ for a list of all accessible topics
995 Use help contents to see a list of the help categories:
997 mysql> help contents
999 You asked for help about help category: "Contents"
1000 For more information, type ´help <item>´, where <item> is one of the
1001 following categories:
1002 Account Management
1003 Administration
1004 Data Definition
1005 Data Manipulation
1006 Data Types
1007 Functions
1008 Functions and Modifiers for Use with GROUP BY
1009 Geographic Features
1010 Language Structure
1011 Plugins
1012 Storage Engines
1013 Stored Routines
1014 Table Maintenance
1015 Transactions
1016 Triggers
1017 If the search string matches multiple items, mysql shows a list of
1019 matching topics:
1020 mysql> help logs
1022 Many help items for your request exist.
1023 To make a more specific request, please type ´help <item>´,
1024 where <item> is one of the following topics:
1025 SHOW
1026 SHOW BINARY LOGS
1027 SHOW ENGINE
1028 SHOW LOGS
1029 Use a topic as the search string to see the help entry for that topic:
1031 mysql> help show binary logs
1033 Name: ´SHOW BINARY LOGS´
1034 Description:
1035 Syntax:
1036 SHOW BINARY LOGS
1037 SHOW MASTER LOGS
1038 Lists the binary log files on the server. This statement is used as
1039 part of the procedure described in [purge-binary-logs], that shows how
1040 to determine which logs can be purged.
1041 mysql> SHOW BINARY LOGS;
1042 +---------------+-----------+
1043 | Log_name | File_size |
1044 +---------------+-----------+
1045 | binlog.000015 | 724935 |
1046 | binlog.000016 | 733481 |
1047 +---------------+-----------+
1048
1050 The mysql client typically is used interactively, like this:
1051 shell> mysql db_name
1053 However, it is also possible to put your SQL statements in a file and
1055 then tell mysql to read its input from that file. To do so, create a
1056 text file text_file that contains the statements you wish to execute.
1057 Then invoke mysql as shown here:
1058 shell> mysql db_name < text_file
1060 If you place a USE db_name statement as the first statement in the
1062 file, it is unnecessary to specify the database name on the command
1063 line:
1064 shell> mysql < text_file
1066 If you are already running mysql, you can execute an SQL script file
1068 using the source command or \. command:
1069 mysql> source file_name
1071 mysql> \. file_name
1072 Sometimes you may want your script to display progress information to
1074 the user. For this you can insert statements like this:
1075 SELECT ´<info_to_display>´ AS ´ ´;
1077 The statement shown outputs <info_to_display>.
1079 You can also invoke mysql with the --verbose option, which causes each
1081 statement to be displayed before the result that it produces.
1082 mysql ignores Unicode byte order mark (BOM) characters at the beginning
1084 of input files. Presence of a BOM does not cause mysql to change its
1085 default character set. To do that, invoke mysql with an option such as
1086 --default-character-set=utf8.
1087
1089 This section describes some techniques that can help you use mysql more
1090 effectively.
1091 Displaying Query Results Vertically
1093 Some query results are much more readable when displayed vertically,
1094 instead of in the usual horizontal table format. Queries can be
1095 displayed vertically by terminating the query with \G instead of a
1096 semicolon. For example, longer text values that include newlines often
1097 are much easier to read with vertical output:
1098 mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
1100 *************************** 1. row ***************************
1101 msg_nro: 3068
1102 date: 2000-03-01 23:29:50
1103 time_zone: +0200
1104 mail_from: Monty
1105 reply: monty@no.spam.com
1106 mail_to: "Thimble Smith" <tim@no.spam.com>
1107 sbj: UTF-8
1108 txt: >>>>> "Thimble" == Thimble Smith writes:
1109 Thimble> Hi. I think this is a good idea. Is anyone familiar
1110 Thimble> with UTF-8 or Unicode? Otherwise, I´ll put this on my
1111 Thimble> TODO list and see what happens.
1112 Yes, please do that.
1113 Regards,
1114 Monty
1115 file: inbox-jani-1
1116 hash: 190402944
1117 1 row in set (0.09 sec)
1118 Using the --safe-updates Option
1120 For beginners, a useful startup option is --safe-updates (or
1121 --i-am-a-dummy, which has the same effect). It is helpful for cases
1122 when you might have issued a DELETE FROM tbl_name statement but
1123 forgotten the WHERE clause. Normally, such a statement deletes all rows
1124 from the table. With --safe-updates, you can delete rows only by
1125 specifying the key values that identify them. This helps prevent
1126 accidents.
1127 When you use the --safe-updates option, mysql issues the following
1129 statement when it connects to the MariaDB server:
1130 SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;
1132 The SET statement has the following effects:
1134 · You are not allowed to execute an UPDATE or DELETE statement unless
1136 you specify a key constraint in the WHERE clause or provide a LIMIT
1137 clause (or both). For example:
1138 UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
1140 UPDATE tbl_name SET not_key_column=val LIMIT 1;
1141 · The server limits all large SELECT results to 1,000 rows unless the
1143 statement includes a LIMIT clause.
1144 · The server aborts multiple-table SELECT statements that probably
1146 need to examine more than 1,000,000 row combinations.
1147 To specify limits different from 1,000 and 1,000,000, you can override
1149 the defaults by using the --select-limit and --max-join-size options:
1150 shell> mysql --safe-updates --select-limit=500 --max-join-size=10000
1152 Disabling mysql Auto-Reconnect
1154 If the mysql client loses its connection to the server while sending a
1155 statement, it immediately and automatically tries to reconnect once to
1156 the server and send the statement again. However, even if mysql
1157 succeeds in reconnecting, your first connection has ended and all your
1158 previous session objects and settings are lost: temporary tables, the
1159 autocommit mode, and user-defined and session variables. Also, any
1160 current transaction rolls back. This behavior may be dangerous for you,
1161 as in the following example where the server was shut down and
1162 restarted between the first and second statements without you knowing
1163 it:
1164 mysql> SET @a=1;
1166 Query OK, 0 rows affected (0.05 sec)
1167 mysql> INSERT INTO t VALUES(@a);
1168 ERROR 2006: MySQL server has gone away
1169 No connection. Trying to reconnect...
1170 Connection id: 1
1171 Current database: test
1172 Query OK, 1 row affected (1.30 sec)
1173 mysql> SELECT * FROM t;
1174 +------+
1175 | a |
1176 +------+
1177 | NULL |
1178 +------+
1179 1 row in set (0.05 sec)
1180 The @a user variable has been lost with the connection, and after the
1182 reconnection it is undefined. If it is important to have mysql
1183 terminate with an error if the connection has been lost, you can start
1184 the mysql client with the --skip-reconnect option.
1185
1187 Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc.,
1188 2010-2015 MariaDB Foundation
1189 This documentation is free software; you can redistribute it and/or
1191 modify it only under the terms of the GNU General Public License as
1192 published by the Free Software Foundation; version 2 of the License.
1193 This documentation is distributed in the hope that it will be useful,
1195 but WITHOUT ANY WARRANTY; without even the implied warranty of
1196 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1197 General Public License for more details.
1198 You should have received a copy of the GNU General Public License along
1200 with the program; if not, write to the Free Software Foundation, Inc.,
1201 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
1202 http://www.gnu.org/licenses/.
1203
1206 1. Bug#25946
1207 http://bugs.mysql.com/bug.php?id=25946
1208
1210 For more information, please refer to the MariaDB Knowledge Base,
1211 available online at https://mariadb.com/kb/
1212
1214 MariaDB Foundation (http://www.mariadb.org/).
1215MariaDB 10.3 9 May 2017 MYSQL(1)