1MYSQL(1) MySQL Database System MYSQL(1)
2
6 mysql - the MySQL command-line client
7
9 mysql [options] db_name
10
12 mysql is a simple SQL shell with input line editing capabilities. It
13 supports interactive and noninteractive use. When used interactively,
14 query results are presented in an ASCII-table format. When used
15 noninteractively (for example, as a filter), the result is presented in
16 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 Note
27 Alternatively, MySQL Shell offers access to the X DevAPI. For
28 details, see MySQL Shell 8.0 (part of MySQL 8.0)[1].
29 Using mysql is very easy. Invoke it from the prompt of your command
31 interpreter as follows:
32 shell> mysql db_name
34 Or:
36 shell> mysql --user=user_name --password db_name
38 Enter password: your_password
39 Then type an SQL statement, end it with ;, \g, or \G and press Enter.
41 Typing Control+C interrupts the current statement if there is one, or
43 cancels any partial input line otherwise.
44 You can execute SQL statements in a script file (batch file) like this:
46 shell> mysql db_name < script.sql > output.tab
48 On Unix, the mysql client logs statements executed interactively to a
50 history file. See the section called “MYSQL CLIENT LOGGING”.
51
53 mysql supports the following options, which can be specified on the
54 command line or in the [mysql] and [client] groups of an option file.
55 For information about option files used by MySQL programs, see
56 Section 4.2.7, “Using Option Files”.
57 · --help, -?
59 Display a help message and exit.
61 · --auto-rehash
63 Enable automatic rehashing. This option is on by default, which
65 enables database, table, and column name completion. Use
66 --disable-auto-rehash to disable rehashing. That causes mysql to
67 start faster, but you must issue the rehash command or its \#
68 shortcut if you want to use name completion.
69 To complete a name, enter the first part and press Tab. If the name
71 is unambiguous, mysql completes it. Otherwise, you can press Tab
72 again to see the possible names that begin with what you have typed
73 so far. Completion does not occur if there is no default database.
74 Note
76 This feature requires a MySQL client that is compiled with the
77 readline library. Typically, the readline library is not
78 available on Windows.
79 · --auto-vertical-output
81 Cause result sets to be displayed vertically if they are too wide
83 for the current window, and using normal tabular format otherwise.
84 (This applies to statements terminated by ; or \G.)
85 · --batch, -B
87 Print results using tab as the column separator, with each row on a
89 new line. With this option, mysql does not use the history file.
90 Batch mode results in nontabular output format and escaping of
92 special characters. Escaping may be disabled by using raw mode; see
93 the description for the --raw option.
94 · --binary-as-hex
96 When this option is given, mysql displays binary data using
98 hexadecimal notation (0xvalue). This occurs whether the overall
99 output dislay format is tabular, vertical, HTML, or XML.
100 · --binary-mode
102 This option helps when processing mysqlbinlog output that may
104 contain BLOB values. By default, mysql translates \r\n in statement
105 strings to \n and interprets \0 as the statement terminator.
106 --binary-mode disables both features. It also disables all mysql
107 commands except charset and delimiter in non-interactive mode (for
108 input piped to mysql or loaded using the source command).
109 · --bind-address=ip_address
111 On a computer having multiple network interfaces, use this option
113 to select which interface to use for connecting to the MySQL
114 server.
115 · --character-sets-dir=dir_name
117 The directory where character sets are installed. See
119 Section 10.14, “Character Set Configuration”.
120 · --column-names
122 Write column names in results.
124 · --column-type-info
126 Display result set metadata.
128 · --comments, -c
130 Whether to strip or preserve comments in statements sent to the
132 server. The default is --skip-comments (strip comments), enable
133 with --comments (preserve comments).
134 Note
136 The mysql client always passes optimizer hints to the server,
137 regardless of whether this option is given.
138 Comment stripping is deprecated. This feature and the options
140 to control it will be removed in a future MySQL release.
141 · --compress, -C
143 Compress all information sent between the client and the server if
145 both support compression.
146 · --connect-expired-password
148 Indicate to the server that the client can handle sandbox mode if
150 the account used to connect has an expired password. This can be
151 useful for noninteractive invocations of mysql because normally the
152 server disconnects noninteractive clients that attempt to connect
153 using an account with an expired password. (See Section 6.3.9,
154 “Server Handling of Expired Passwords”.)
155 · --database=db_name, -D db_name
157 The database to use. This is useful primarily in an option file.
159 · --debug[=debug_options], -# [debug_options]
161 Write a debugging log. A typical debug_options string is
163 d:t:o,file_name. The default is d:t:o,/tmp/mysql.trace.
164 This option is available only if MySQL was built using WITH_DEBUG.
166 MySQL release binaries provided by Oracle are not built using this
167 option.
168 · --debug-check
170 Print some debugging information when the program exits.
172 · --debug-info, -T
174 Print debugging information and memory and CPU usage statistics
176 when the program exits.
177 · --default-auth=plugin
179 A hint about the client-side authentication plugin to use. See
181 Section 6.3.10, “Pluggable Authentication”.
182 · --default-character-set=charset_name
184 Use charset_name as the default character set for the client and
186 connection.
187 This option can be useful if the operating system uses one
189 character set and the mysql client by default uses another. In this
190 case, output may be formatted incorrectly. You can usually fix such
191 issues by using this option to force the client to use the system
192 character set instead.
193 For more information, see Section 10.4, “Connection Character Sets
195 and Collations”, and Section 10.14, “Character Set Configuration”.
196 · --defaults-extra-file=file_name
198 Read this option file after the global option file but (on Unix)
200 before the user option file. If the file does not exist or is
201 otherwise inaccessible, an error occurs. file_name is interpreted
202 relative to the current directory if given as a relative path name
203 rather than a full path name.
204 For additional information about this and other option-file
206 options, see Section 4.2.8, “Command-Line Options that Affect
207 Option-File Handling”.
208 · --defaults-file=file_name
210 Use only the given option file. If the file does not exist or is
212 otherwise inaccessible, an error occurs. file_name is interpreted
213 relative to the current directory if given as a relative path name
214 rather than a full path name.
215 Exception: Even with --defaults-file, client programs read
217 .mylogin.cnf.
218 For additional information about this and other option-file
220 options, see Section 4.2.8, “Command-Line Options that Affect
221 Option-File Handling”.
222 · --defaults-group-suffix=str
224 Read not only the usual option groups, but also groups with the
226 usual names and a suffix of str. For example, mysql normally reads
227 the [client] and [mysql] groups. If the
228 --defaults-group-suffix=_other option is given, mysql also reads
229 the [client_other] and [mysql_other] groups.
230 For additional information about this and other option-file
232 options, see Section 4.2.8, “Command-Line Options that Affect
233 Option-File Handling”.
234 · --delimiter=str
236 Set the statement delimiter. The default is the semicolon character
238 (;).
239 · --disable-named-commands
241 Disable named commands. Use the \* form only, or use named commands
243 only at the beginning of a line ending with a semicolon (;). mysql
244 starts with this option enabled by default. However, even with this
245 option, long-format commands still work from the first line. See
246 the section called “MYSQL CLIENT COMMANDS”.
247 · --enable-cleartext-plugin
249 Enable the mysql_clear_password cleartext authentication plugin.
251 (See Section 6.5.1.4, “Client-Side Cleartext Pluggable
252 Authentication”.)
253 · --execute=statement, -e statement
255 Execute the statement and quit. The default output format is like
257 that produced with --batch. See Section 4.2.5, “Using Options on
258 the Command Line”, for some examples. With this option, mysql does
259 not use the history file.
260 · --force, -f
262 Continue even if an SQL error occurs.
264 · --get-server-public-key
266 Request from the server the public key required for RSA key
268 pair-based password exchange. This option applies to clients that
269 that authenticate with the caching_sha2_password authentication
270 plugin. For that plugin, the server does not send the public key
271 unless requested. This option is ignored for accounts that do not
272 authenticate with that plugin. It is also ignored if RSA-based
273 password exchange is not used, as is the case when the client
274 connects to the server using a secure connection.
275 If --server-public-key-path=file_name is given and specifies a
277 valid public key file, it takes precedence over
278 --get-server-public-key.
279 For information about the caching_sha2_password plugin, see
281 Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
282 · --histignore
284 A list of one or more colon-separated patterns specifying
286 statements to ignore for logging purposes. These patterns are added
287 to the default pattern list ("*IDENTIFIED*:*PASSWORD*"). The value
288 specified for this option affects logging of statements written to
289 the history file, and to syslog if the --syslog option is given.
290 For more information, see the section called “MYSQL CLIENT
291 LOGGING”.
292 · --host=host_name, -h host_name
294 Connect to the MySQL server on the given host.
296 · --html, -H
298 Produce HTML output.
300 · --ignore-spaces, -i
302 Ignore spaces after function names. The effect of this is described
304 in the discussion for the IGNORE_SPACE SQL mode (see
305 Section 5.1.11, “Server SQL Modes”).
306 · --init-command=str
308 SQL statement to execute after connecting to the server. If
310 auto-reconnect is enabled, the statement is executed again after
311 reconnection occurs.
312 · --line-numbers
314 Write line numbers for errors. Disable this with
316 --skip-line-numbers.
317 · --local-infile[={0|1}]
319 Enable or disable LOCAL capability for LOAD DATA. For mysql, this
321 capability is disabled by default. With no value, the option
322 enables LOCAL. The option may be given as --local-infile=0 or
323 --local-infile=1 to explicitly disable or enable LOCAL. Enabling
324 local data loading also requires that the server permits it; see
325 Section 6.1.6, “Security Issues with LOAD DATA LOCAL”
326 · --login-path=name
328 Read options from the named login path in the .mylogin.cnf login
330 path file. A “login path” is an option group containing options
331 that specify which MySQL server to connect to and which account to
332 authenticate as. To create or modify a login path file, use the
333 mysql_config_editor utility. See mysql_config_editor(1).
334 For additional information about this and other option-file
336 options, see Section 4.2.8, “Command-Line Options that Affect
337 Option-File Handling”.
338 · --named-commands, -G
340 Enable named mysql commands. Long-format commands are permitted,
342 not just short-format commands. For example, quit and \q both are
343 recognized. Use --skip-named-commands to disable named commands.
344 See the section called “MYSQL CLIENT COMMANDS”.
345 · --no-auto-rehash, -A
347 This has the same effect as --skip-auto-rehash. See the description
349 for --auto-rehash.
350 · --no-beep, -b
352 Do not beep when errors occur.
354 · --no-defaults
356 Do not read any option files. If program startup fails due to
358 reading unknown options from an option file, --no-defaults can be
359 used to prevent them from being read.
360 The exception is that the .mylogin.cnf file, if it exists, is read
362 in all cases. This permits passwords to be specified in a safer way
363 than on the command line even when --no-defaults is used.
364 (.mylogin.cnf is created by the mysql_config_editor utility. See
365 mysql_config_editor(1).)
366 For additional information about this and other option-file
368 options, see Section 4.2.8, “Command-Line Options that Affect
369 Option-File Handling”.
370 · --one-database, -o
372 Ignore statements except those that occur while the default
374 database is the one named on the command line. This option is
375 rudimentary and should be used with care. Statement filtering is
376 based only on USE statements.
377 Initially, mysql executes statements in the input because
379 specifying a database db_name on the command line is equivalent to
380 inserting USE db_name at the beginning of the input. Then, for each
381 USE statement encountered, mysql accepts or rejects following
382 statements depending on whether the database named is the one on
383 the command line. The content of the statements is immaterial.
384 Suppose that mysql is invoked to process this set of statements:
386 DELETE FROM db2.t2;
388 USE db2;
389 DROP TABLE db1.t1;
390 CREATE TABLE db1.t1 (i INT);
391 USE db1;
392 INSERT INTO t1 (i) VALUES(1);
393 CREATE TABLE db2.t1 (j INT);
394 If the command line is mysql --force --one-database db1, mysql
396 handles the input as follows:
397 · The DELETE statement is executed because the default database
399 is db1, even though the statement names a table in a different
400 database.
401 · The DROP TABLE and CREATE TABLE statements are not executed
403 because the default database is not db1, even though the
404 statements name a table in db1.
405 · The INSERT and CREATE TABLE statements are executed because the
407 default database is db1, even though the CREATE TABLE statement
408 names a table in a different database.
409 · --pager[=command]
411 Use the given command for paging query output. If the command is
413 omitted, the default pager is the value of your PAGER environment
414 variable. Valid pagers are less, more, cat [> filename], and so
415 forth. This option works only on Unix and only in interactive mode.
416 To disable paging, use --skip-pager. the section called “MYSQL
417 CLIENT COMMANDS”, discusses output paging further.
418 · --password[=password], -p[password]
420 The password to use when connecting to the server. If you use the
422 short option form (-p), you cannot have a space between the option
423 and the password. If you omit the password value following the
424 --password or -p option on the command line, mysql prompts for one.
425 Specifying a password on the command line should be considered
427 insecure. See Section 6.1.2.1, “End-User Guidelines for Password
428 Security”. You can use an option file to avoid giving the password
429 on the command line.
430 · --pipe, -W
432 On Windows, connect to the server using a named pipe. This option
434 applies only if the server supports named-pipe connections.
435 · --plugin-dir=dir_name
437 The directory in which to look for plugins. Specify this option if
439 the --default-auth option is used to specify an authentication
440 plugin but mysql does not find it. See Section 6.3.10, “Pluggable
441 Authentication”.
442 · --port=port_num, -P port_num
444 The TCP/IP port number to use for the connection.
446 · --print-defaults
448 Print the program name and all options that it gets from option
450 files.
451 For additional information about this and other option-file
453 options, see Section 4.2.8, “Command-Line Options that Affect
454 Option-File Handling”.
455 · --prompt=format_str
457 Set the prompt to the specified format. The default is mysql>. The
459 special sequences that the prompt can contain are described in the
460 section called “MYSQL CLIENT COMMANDS”.
461 · --protocol={TCP|SOCKET|PIPE|MEMORY}
463 The connection protocol to use for connecting to the server. It is
465 useful when the other connection parameters normally would cause a
466 protocol to be used other than the one you want. For details on the
467 permissible values, see Section 4.2.2, “Connecting to the MySQL
468 Server”.
469 · --quick, -q
471 Do not cache each query result, print each row as it is received.
473 This may slow down the server if the output is suspended. With this
474 option, mysql does not use the history file.
475 · --raw, -r
477 For tabular output, the “boxing” around columns enables one column
479 value to be distinguished from another. For nontabular output (such
480 as is produced in batch mode or when the --batch or --silent option
481 is given), special characters are escaped in the output so they can
482 be identified easily. Newline, tab, NUL, and backslash are written
483 as \n, \t, \0, and \\. The --raw option disables this character
484 escaping.
485 The following example demonstrates tabular versus nontabular output
487 and the use of raw mode to disable escaping:
488 % mysql
490 mysql> SELECT CHAR(92);
491 +----------+
492 | CHAR(92) |
493 +----------+
494 | \ |
495 +----------+
496 % mysql -s
497 mysql> SELECT CHAR(92);
498 CHAR(92)
499 \\
500 % mysql -s -r
501 mysql> SELECT CHAR(92);
502 CHAR(92)
503 \
504 · --reconnect
506 If the connection to the server is lost, automatically try to
508 reconnect. A single reconnect attempt is made each time the
509 connection is lost. To suppress reconnection behavior, use
510 --skip-reconnect.
511 · --safe-updates, --i-am-a-dummy, -U
513 If this option is enabled, UPDATE and DELETE statements that do not
515 use a key in the WHERE clause or a LIMIT clause produce an error.
516 In addition, restrictions are placed on SELECT statements that
517 produce (or are estimated to produce) very large result sets. If
518 you have set this option in an option file, you can use
519 --skip-safe-updates on the command line to override it. For more
520 information about this option, see the section called “Using
521 Safe-Updates Mode (--safe-updates)”.
522 · --secure-auth
524 This option was removed in MySQL 8.0.3.
526 · --server-public-key-path=file_name
528 The path name to a file containing a client-side copy of the public
530 key required by the server for RSA key pair-based password
531 exchange. The file must be in PEM format. This option applies to
532 clients that authenticate with the sha256_password or
533 caching_sha2_password authentication plugin. This option is ignored
534 for accounts that do not authenticate with one of those plugins. It
535 is also ignored if RSA-based password exchange is not used, as is
536 the case when the client connects to the server using a secure
537 connection.
538 If --server-public-key-path=file_name is given and specifies a
540 valid public key file, it takes precedence over
541 --get-server-public-key.
542 This option is available only if MySQL was built using OpenSSL.
544 For information about the sha256_password and caching_sha2_password
546 plugins, see Section 6.5.1.2, “SHA-256 Pluggable Authentication”,
547 and Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
548 · --shared-memory-base-name=name
550 On Windows, the shared-memory name to use, for connections made
552 using shared memory to a local server. The default value is MYSQL.
553 The shared-memory name is case-sensitive.
554 The server must be started with the --shared-memory option to
556 enable shared-memory connections.
557 · --show-warnings
559 Cause warnings to be shown after each statement if there are any.
561 This option applies to interactive and batch mode.
562 · --sigint-ignore
564 Ignore SIGINT signals (typically the result of typing Control+C).
566 · --silent, -s
568 Silent mode. Produce less output. This option can be given multiple
570 times to produce less and less output.
571 This option results in nontabular output format and escaping of
573 special characters. Escaping may be disabled by using raw mode; see
574 the description for the --raw option.
575 · --skip-column-names, -N
577 Do not write column names in results.
579 · --skip-line-numbers, -L
581 Do not write line numbers for errors. Useful when you want to
583 compare result files that include error messages.
584 · --socket=path, -S path
586 For connections to localhost, the Unix socket file to use, or, on
588 Windows, the name of the named pipe to use.
589 · --ssl*
591 Options that begin with --ssl specify whether to connect to the
593 server using SSL and indicate where to find SSL keys and
594 certificates. See Section 6.4.2, “Command Options for Encrypted
595 Connections”.
596 · --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
598 mode on the client side. The --ssl-fips-mode option differs from
599 other --ssl-xxx options in that it is not used to establish
600 encrypted connections, but rather to affect which cryptographic
601 operations are permitted. See Section 6.6, “FIPS Support”.
602 These --ssl-fips-mode values are permitted:
604 · OFF: Disable FIPS mode.
606 · ON: Enable FIPS mode.
608 · STRICT: Enable “strict” FIPS mode.
610 Note
613 If the OpenSSL FIPS Object Module is not available, the only
614 permitted value for --ssl-fips-mode is OFF. In this case,
615 setting --ssl-fips-mode to ON or STRICT causes the client to
616 produce a warning at startup and to operate in non-FIPS mode.
617 · --syslog, -j
619 This option causes mysql to send interactive statements to the
621 system logging facility. On Unix, this is syslog; on Windows, it is
622 the Windows Event Log. The destination where logged messages appear
623 is system dependent. On Linux, the destination is often the
624 /var/log/messages file.
625 Here is a sample of output generated on Linux by using --syslog.
627 This output is formatted for readability; each logged message
628 actually takes a single line.
629 Mar 7 12:39:25 myhost MysqlClient[20824]:
631 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
632 DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
633 Mar 7 12:39:28 myhost MysqlClient[20824]:
634 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
635 DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'
636 For more information, see the section called “MYSQL CLIENT
638 LOGGING”.
639 · --table, -t
641 Display output in table format. This is the default for interactive
643 use, but can be used to produce table output in batch mode.
644 · --tee=file_name
646 Append a copy of output to the given file. This option works only
648 in interactive mode. the section called “MYSQL CLIENT COMMANDS”,
649 discusses tee files further.
650 · --tls-ciphersuites=ciphersuite_list
652 For client programs, specifies which TLSv1.3 ciphersuites the
654 client permits for encrypted connections. The value is a list of
655 one or more colon-separated ciphersuite names. The ciphersuites
656 that can be named for this option depend on the SSL library used to
657 compile MySQL. For details, see Section 6.4.6, “Encrypted
658 Connection Protocols and Ciphers”.
659 This option was added in MySQL 8.0.16.
661 · --tls-version=protocol_list
663 The protocols the client permits for encrypted connections. The
665 value is a list of one or more comma-separated protocol names. The
666 protocols that can be named for this option depend on the SSL
667 library used to compile MySQL. For details, see Section 6.4.6,
668 “Encrypted Connection Protocols and Ciphers”.
669 · --unbuffered, -n
671 Flush the buffer after each query.
673 · --user=user_name, -u user_name
675 The MySQL user name to use when connecting to the server.
677 · --verbose, -v
679 Verbose mode. Produce more output about what the program does. This
681 option can be given multiple times to produce more and more output.
682 (For example, -v -v -v produces table output format even in batch
683 mode.)
684 · --version, -V
686 Display version information and exit.
688 · --vertical, -E
690 Print query output rows vertically (one line per column value).
692 Without this option, you can specify vertical output for individual
693 statements by terminating them with \G.
694 · --wait, -w
696 If the connection cannot be established, wait and retry instead of
698 aborting.
699 · --xml, -X
701 Produce XML output.
703 <field name="column_name">NULL</field>
705 The output when --xml is used with mysql matches that of mysqldump
707 --xml. See mysqldump(1), for details.
708 The XML output also uses an XML namespace, as shown here:
710 shell> mysql --xml -uroot -e "SHOW VARIABLES LIKE 'version%'"
712 <?xml version="1.0"?>
713 <resultset statement="SHOW VARIABLES LIKE 'version%'" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
714 <row>
715 <field name="Variable_name">version</field>
716 <field name="Value">5.0.40-debug</field>
717 </row>
718 <row>
719 <field name="Variable_name">version_comment</field>
720 <field name="Value">Source distribution</field>
721 </row>
722 <row>
723 <field name="Variable_name">version_compile_machine</field>
724 <field name="Value">i686</field>
725 </row>
726 <row>
727 <field name="Variable_name">version_compile_os</field>
728 <field name="Value">suse-linux-gnu</field>
729 </row>
730 </resultset>
731 (See Bug #25946.)
733 You can also set the following variables by using --var_name=value.
735 · connect_timeout
737 The number of seconds before connection timeout. (Default value is
739 0.)
740 · max_allowed_packet
742 The maximum size of the buffer for client/server communication. The
744 default is 16MB, the maximum is 1GB.
745 · max_join_size
747 The automatic limit for rows in a join when using --safe-updates.
749 (Default value is 1,000,000.)
750 · net_buffer_length
752 The buffer size for TCP/IP and socket communication. (Default value
754 is 16KB.)
755 · select_limit
757 The automatic limit for SELECT statements when using
759 --safe-updates. (Default value is 1,000.)
760
762 mysql sends each SQL statement that you issue to the server to be
763 executed. There is also a set of commands that mysql itself interprets.
764 For a list of these commands, type help or \h at the mysql> prompt:
765 mysql> help
767 List of all MySQL commands:
768 Note that all text commands must be first on line and end with ';'
769 ? (\?) Synonym for `help'.
770 clear (\c) Clear the current input statement.
771 connect (\r) Reconnect to the server. Optional arguments are db and host.
772 delimiter (\d) Set statement delimiter.
773 edit (\e) Edit command with $EDITOR.
774 ego (\G) Send command to mysql server, display result vertically.
775 exit (\q) Exit mysql. Same as quit.
776 go (\g) Send command to mysql server.
777 help (\h) Display this help.
778 nopager (\n) Disable pager, print to stdout.
779 notee (\t) Don't write into outfile.
780 pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
781 print (\p) Print current command.
782 prompt (\R) Change your mysql prompt.
783 quit (\q) Quit mysql.
784 rehash (\#) Rebuild completion hash.
785 source (\.) Execute an SQL script file. Takes a file name as an argument.
786 status (\s) Get status information from the server.
787 system (\!) Execute a system shell command.
788 tee (\T) Set outfile [to_outfile]. Append everything into given
789 outfile.
790 use (\u) Use another database. Takes database name as argument.
791 charset (\C) Switch to another charset. Might be needed for processing
792 binlog with multi-byte charsets.
793 warnings (\W) Show warnings after every statement.
794 nowarning (\w) Don't show warnings after every statement.
795 resetconnection(\x) Clean session context.
796 For server side help, type 'help contents'
797 If mysql is invoked with the --binary-mode option, all mysql commands
799 are disabled except charset and delimiter in non-interactive mode (for
800 input piped to mysql or loaded using the source command).
801 Each command has both a long and short form. The long form is not
803 case-sensitive; the short form is. The long form can be followed by an
804 optional semicolon terminator, but the short form should not.
805 The use of short-form commands within multiple-line /* ... */ comments
807 is not supported.
808 · help [arg], \h [arg], \? [arg], ? [arg]
810 Display a help message listing the available mysql commands.
812 If you provide an argument to the help command, mysql uses it as a
814 search string to access server-side help from the contents of the
815 MySQL Reference Manual. For more information, see the section
816 called “MYSQL CLIENT SERVER-SIDE HELP”.
817 · charset charset_name, \C charset_name
819 Change the default character set and issue a SET NAMES statement.
821 This enables the character set to remain synchronized on the client
822 and server if mysql is run with auto-reconnect enabled (which is
823 not recommended), because the specified character set is used for
824 reconnects.
825 · clear, \c
827 Clear the current input. Use this if you change your mind about
829 executing the statement that you are entering.
830 · connect [db_name host_name]], \r [db_name host_name]]
832 Reconnect to the server. The optional database name and host name
834 arguments may be given to specify the default database or the host
835 where the server is running. If omitted, the current values are
836 used.
837 · delimiter str, \d str
839 Change the string that mysql interprets as the separator between
841 SQL statements. The default is the semicolon character (;).
842 The delimiter string can be specified as an unquoted or quoted
844 argument on the delimiter command line. Quoting can be done with
845 either single quote ('), double quote ("), or backtick (`)
846 characters. To include a quote within a quoted string, either quote
847 the string with a different quote character or escape the quote
848 with a backslash (\) character. Backslash should be avoided outside
849 of quoted strings because it is the escape character for MySQL. For
850 an unquoted argument, the delimiter is read up to the first space
851 or end of line. For a quoted argument, the delimiter is read up to
852 the matching quote on the line.
853 mysql interprets instances of the delimiter string as a statement
855 delimiter anywhere it occurs, except within quoted strings. Be
856 careful about defining a delimiter that might occur within other
857 words. For example, if you define the delimiter as X, you will be
858 unable to use the word INDEX in statements. mysql interprets this
859 as INDE followed by the delimiter X.
860 When the delimiter recognized by mysql is set to something other
862 than the default of ;, instances of that character are sent to the
863 server without interpretation. However, the server itself still
864 interprets ; as a statement delimiter and processes statements
865 accordingly. This behavior on the server side comes into play for
866 multiple-statement execution (see Section 28.7.23, “C API Multiple
867 Statement Execution Support”), and for parsing the body of stored
868 procedures and functions, triggers, and events (see Section 24.1,
869 “Defining Stored Programs”).
870 · edit, \e
872 Edit the current input statement. mysql checks the values of the
874 EDITOR and VISUAL environment variables to determine which editor
875 to use. The default editor is vi if neither variable is set.
876 The edit command works only in Unix.
878 · ego, \G
880 Send the current statement to the server to be executed and display
882 the result using vertical format.
883 · exit, \q
885 Exit mysql.
887 · go, \g
889 Send the current statement to the server to be executed.
891 · nopager, \n
893 Disable output paging. See the description for pager.
895 The nopager command works only in Unix.
897 · notee, \t
899 Disable output copying to the tee file. See the description for
901 tee.
902 · nowarning, \w
904 Disable display of warnings after each statement.
906 · pager [command], \P [command]
908 Enable output paging. By using the --pager option when you invoke
910 mysql, it is possible to browse or search query results in
911 interactive mode with Unix programs such as less, more, or any
912 other similar program. If you specify no value for the option,
913 mysql checks the value of the PAGER environment variable and sets
914 the pager to that. Pager functionality works only in interactive
915 mode.
916 Output paging can be enabled interactively with the pager command
918 and disabled with nopager. The command takes an optional argument;
919 if given, the paging program is set to that. With no argument, the
920 pager is set to the pager that was set on the command line, or
921 stdout if no pager was specified.
922 Output paging works only in Unix because it uses the popen()
924 function, which does not exist on Windows. For Windows, the tee
925 option can be used instead to save query output, although it is not
926 as convenient as pager for browsing output in some situations.
927 · print, \p
929 Print the current input statement without executing it.
931 · prompt [str], \R [str]
933 Reconfigure the mysql prompt to the given string. The special
935 character sequences that can be used in the prompt are described
936 later in this section.
937 If you specify the prompt command with no argument, mysql resets
939 the prompt to the default of mysql>.
940 · quit, \q
942 Exit mysql.
944 · rehash, \#
946 Rebuild the completion hash that enables database, table, and
948 column name completion while you are entering statements. (See the
949 description for the --auto-rehash option.)
950 · resetconnection, \x
952 Reset the connection to clear the session state.
954 Resetting a connection has effects similar to mysql_change_user()
956 or an auto-reconnect except that the connection is not closed and
957 reopened, and re-authentication is not done. See Section 28.7.7.3,
958 “mysql_change_user()”) and see Section 28.7.28, “C API Automatic
959 Reconnection Control”).
960 This example shows how resetconnection clears a value maintained in
962 the session state:
963 mysql> SELECT LAST_INSERT_ID(3);
965 +-------------------+
966 | LAST_INSERT_ID(3) |
967 +-------------------+
968 | 3 |
969 +-------------------+
970 mysql> SELECT LAST_INSERT_ID();
971 +------------------+
972 | LAST_INSERT_ID() |
973 +------------------+
974 | 3 |
975 +------------------+
976 mysql> resetconnection;
977 mysql> SELECT LAST_INSERT_ID();
978 +------------------+
979 | LAST_INSERT_ID() |
980 +------------------+
981 | 0 |
982 +------------------+
983 · source file_name, \. file_name
985 Read the named file and executes the statements contained therein.
987 On Windows, you can specify path name separators as / or \\.
988 Quote characters are taken as part of the file name itself. For
990 best results, the name should not include space characters.
991 · status, \s
993 Provide status information about the connection and the server you
995 are using. If you are running with --safe-updates enabled, status
996 also prints the values for the mysql variables that affect your
997 queries.
998 · system command, \! command
1000 Execute the given command using your default command interpreter.
1002 The system command works only in Unix.
1004 · tee [file_name], \T [file_name]
1006 By using the --tee option when you invoke mysql, you can log
1008 statements and their output. All the data displayed on the screen
1009 is appended into a given file. This can be very useful for
1010 debugging purposes also. mysql flushes results to the file after
1011 each statement, just before it prints its next prompt. Tee
1012 functionality works only in interactive mode.
1013 You can enable this feature interactively with the tee command.
1015 Without a parameter, the previous file is used. The tee file can be
1016 disabled with the notee command. Executing tee again re-enables
1017 logging.
1018 · use db_name, \u db_name
1020 Use db_name as the default database.
1022 · warnings, \W
1024 Enable display of warnings after each statement (if there are any).
1026 Here are a few tips about the pager command:
1028 · You can use it to write to a file and the results go only to the
1030 file:
1031 mysql> pager cat > /tmp/log.txt
1033 You can also pass any options for the program that you want to use
1035 as your pager:
1036 mysql> pager less -n -i -S
1038 · In the preceding example, note the -S option. You may find it very
1040 useful for browsing wide query results. Sometimes a very wide
1041 result set is difficult to read on the screen. The -S option to
1042 less can make the result set much more readable because you can
1043 scroll it horizontally using the left-arrow and right-arrow keys.
1044 You can also use -S interactively within less to switch the
1045 horizontal-browse mode on and off. For more information, read the
1046 less manual page:
1047 shell> man less
1049 · The -F and -X options may be used with less to cause it to exit if
1051 output fits on one screen, which is convenient when no scrolling is
1052 necessary:
1053 mysql> pager less -n -i -S -F -X
1055 · You can specify very complex pager commands for handling query
1057 output:
1058 mysql> pager cat | tee /dr1/tmp/res.txt \
1060 | tee /dr2/tmp/res2.txt | less -n -i -S
1061 In this example, the command would send query results to two files
1063 in two different directories on two different file systems mounted
1064 on /dr1 and /dr2, yet still display the results onscreen using
1065 less.
1066 You can also combine the tee and pager functions. Have a tee file
1068 enabled and pager set to less, and you are able to browse the results
1069 using the less program and still have everything appended into a file
1070 the same time. The difference between the Unix tee used with the pager
1071 command and the mysql built-in tee command is that the built-in tee
1072 works even if you do not have the Unix tee available. The built-in tee
1073 also logs everything that is printed on the screen, whereas the Unix
1074 tee used with pager does not log quite that much. Additionally, tee
1075 file logging can be turned on and off interactively from within mysql.
1076 This is useful when you want to log some queries to a file, but not
1077 others.
1078 The prompt command reconfigures the default mysql> prompt. The string
1080 for defining the prompt can contain the following special sequences.
1081.br
1083.br
1084.br
108572
1086 ┌───────────────────────────┬────────────────────────────┐
1087 │Option │ Description │
1088 ├───────────────────────────┼────────────────────────────┤
1089 │ │ The current connection │
1090 │ │ identifier │
1091 ├───────────────────────────┼────────────────────────────┤
1092 │ │ A counter that increments │
1093 │ │ for each statement you │
1094 │ │ issue │
1095 ├───────────────────────────┼────────────────────────────┤
1096 │ │ The full current date │
1097 ├───────────────────────────┼────────────────────────────┤
1098 │ │ The default database │
1099 ├───────────────────────────┼────────────────────────────┤
1100 │ │ The server host │
1101 ├───────────────────────────┼────────────────────────────┤
1102 │ │ The current delimiter │
1103 ├───────────────────────────┼────────────────────────────┤
1104 │ │ Minutes of the current │
1105 │ │ time │
1106 ├───────────────────────────┼────────────────────────────┤
1107 │ │ A newline character │
1108 ├───────────────────────────┼────────────────────────────┤
1109 │ │ The current month in │
1110 │ │ three-letter format (Jan, │
1111 │ │ Feb, ...) │
1112 ├───────────────────────────┼────────────────────────────┤
1113 │ │ The current month in │
1114 │ │ numeric format │
1115 ├───────────────────────────┼────────────────────────────┤
1116 │P │ am/pm │
1117 ├───────────────────────────┼────────────────────────────┤
1118 │ │ The current TCP/IP port or │
1119 │ │ socket file │
1120 ├───────────────────────────┼────────────────────────────┤
1121 │ │ The current time, in │
1122 │ │ 24-hour military time │
1123 │ │ (0–23) │
1124 ├───────────────────────────┼────────────────────────────┤
1125 │ │ The current time, standard │
1126 │ │ 12-hour time (1–12) │
1127 ├───────────────────────────┼────────────────────────────┤
1128 │ │ Semicolon │
1129 ├───────────────────────────┼────────────────────────────┤
1130 │ │ Seconds of the current │
1131 │ │ time │
1132 ├───────────────────────────┼────────────────────────────┤
1133 │ │ A tab character │
1134 ├───────────────────────────┼────────────────────────────┤
1135 │U │ │
1136 │ │ Your full │
1137 │ │ user_name@host_name │
1138 │ │ account name │
1139 ├───────────────────────────┼────────────────────────────┤
1140 │ │ Your user name │
1141 ├───────────────────────────┼────────────────────────────┤
1142 │ │ The server version │
1143 ├───────────────────────────┼────────────────────────────┤
1144 │ │ The current day of the │
1145 │ │ week in three-letter │
1146 │ │ format (Mon, Tue, ...) │
1147 ├───────────────────────────┼────────────────────────────┤
1148 │ │ The current year, four │
1149 │ │ digits │
1150 ├───────────────────────────┼────────────────────────────┤
1151 │y │ The current year, two │
1152 │ │ digits │
1153 ├───────────────────────────┼────────────────────────────┤
1154 │_ │ A space │
1155 ├───────────────────────────┼────────────────────────────┤
1156 │\ │ A space (a space follows │
1157 │ │ the backslash) │
1158 ├───────────────────────────┼────────────────────────────┤
1159 │´ │ Single quote │
1160 ├───────────────────────────┼────────────────────────────┤
1161 │ │ Double quote │
1162 ├───────────────────────────┼────────────────────────────┤
1163 │T}:T{ A literal backslash │ │
1164 │character │ │
1165 ├───────────────────────────┼────────────────────────────┤
1166 │\fIx │ │
1167 │ │ x, for any “x” not │
1168 │ │ listed above │
1169 └───────────────────────────┴────────────────────────────┘
1170 You can set the prompt in several ways:
1172 · Use an environment variable. You can set the MYSQL_PS1 environment
1174 variable to a prompt string. For example:
1175 shell> export MYSQL_PS1="(\u@\h) [\d]> "
1177 · Use a command-line option. You can set the --prompt option on the
1179 command line to mysql. For example:
1180 shell> mysql --prompt="(\u@\h) [\d]> "
1182 (user@host) [database]>
1183 · Use an option file. You can set the prompt option in the [mysql]
1185 group of any MySQL option file, such as /etc/my.cnf or the .my.cnf
1186 file in your home directory. For example:
1187 [mysql]
1189 prompt=(\\u@\\h) [\\d]>\\_
1190 In this example, note that the backslashes are doubled. If you set
1192 the prompt using the prompt option in an option file, it is
1193 advisable to double the backslashes when using the special prompt
1194 options. There is some overlap in the set of permissible prompt
1195 options and the set of special escape sequences that are recognized
1196 in option files. (The rules for escape sequences in option files
1197 are listed in Section 4.2.7, “Using Option Files”.) The overlap may
1198 cause you problems if you use single backslashes. For example, \s
1199 is interpreted as a space rather than as the current seconds value.
1200 The following example shows how to define a prompt within an option
1201 file to include the current time in HH:MM:SS> format:
1202 [mysql]
1204 prompt="\\r:\\m:\\s> "
1205 · Set the prompt interactively. You can change your prompt
1207 interactively by using the prompt (or \R) command. For example:
1208 mysql> prompt (\u@\h) [\d]>\_
1210 PROMPT set to '(\u@\h) [\d]>\_'
1211 (user@host) [database]>
1212 (user@host) [database]> prompt
1213 Returning to default PROMPT of mysql>
1214 mysql>
1215
1217 The mysql client can do these types of logging for statements executed
1218 interactively:
1219 · On Unix, mysql writes the statements to a history file. By default,
1221 this file is named .mysql_history in your home directory. To
1222 specify a different file, set the value of the MYSQL_HISTFILE
1223 environment variable.
1224 · On all platforms, if the --syslog option is given, mysql writes the
1226 statements to the system logging facility. On Unix, this is syslog;
1227 on Windows, it is the Windows Event Log. The destination where
1228 logged messages appear is system dependent. On Linux, the
1229 destination is often the /var/log/messages file.
1230 The following discussion describes characteristics that apply to all
1232 logging types and provides information specific to each logging type.
1233 · How Logging Occurs
1235 · Controlling the History File
1237 · syslog Logging Characteristics
1239 How Logging Occurs.PP For each enabled logging destination, statement
1240 logging occurs as follows:
1241 · Statements are logged only when executed interactively. Statements
1243 are noninteractive, for example, when read from a file or a pipe.
1244 It is also possible to suppress statement logging by using the
1245 --batch or --execute option.
1246 · Statements are ignored and not logged if they match any pattern in
1248 the “ignore” list. This list is described later.
1249 · mysql logs each nonignored, nonempty statement line individually.
1251 · If a nonignored statement spans multiple lines (not including the
1253 terminating delimiter), mysql concatenates the lines to form the
1254 complete statement, maps newlines to spaces, and logs the result,
1255 plus a delimiter.
1256 Consequently, an input statement that spans multiple lines can be
1258 logged twice. Consider this input:
1259 mysql> SELECT
1261 -> 'Today is'
1262 -> ,
1263 -> CURDATE()
1264 -> ;
1265 In this case, mysql logs the “SELECT”, “'Today is'”, “,”, “CURDATE()”,
1267 and “;” lines as it reads them. It also logs the complete statement,
1268 after mapping SELECT\n'Today is'\n,\nCURDATE() to SELECT 'Today is' ,
1269 CURDATE(), plus a delimiter. Thus, these lines appear in logged output:
1270 SELECT
1272 'Today is'
1273 ,
1274 CURDATE()
1275 ;
1276 SELECT 'Today is' , CURDATE();
1277 mysql ignores for logging purposes statements that match any pattern in
1279 the “ignore” list. By default, the pattern list is
1280 "*IDENTIFIED*:*PASSWORD*", to ignore statements that refer to
1281 passwords. Pattern matching is not case sensitive. Within patterns, two
1282 characters are special:
1283 · ? matches any single character.
1285 · * matches any sequence of zero or more characters.
1287 To specify additional patterns, use the --histignore option or set the
1289 MYSQL_HISTIGNORE environment variable. (If both are specified, the
1290 option value takes precedence.) The value should be a list of one or
1291 more colon-separated patterns, which are appended to the default
1292 pattern list.
1293 Patterns specified on the command line might need to be quoted or
1295 escaped to prevent your command interpreter from treating them
1296 specially. For example, to suppress logging for UPDATE and DELETE
1297 statements in addition to statements that refer to passwords, invoke
1298 mysql like this:
1299 shell> mysql --histignore="*UPDATE*:*DELETE*"
1301 Controlling the History File.PP The .mysql_history file should be
1303 protected with a restrictive access mode because sensitive information
1304 might be written to it, such as the text of SQL statements that contain
1305 passwords. See Section 6.1.2.1, “End-User Guidelines for Password
1306 Security”.
1307 If you do not want to maintain a history file, first remove
1309 .mysql_history if it exists. Then use either of the following
1310 techniques to prevent it from being created again:
1311 · Set the MYSQL_HISTFILE environment variable to /dev/null. To cause
1313 this setting to take effect each time you log in, put it in one of
1314 your shell's startup files.
1315 · Create .mysql_history as a symbolic link to /dev/null; this need be
1317 done only once:
1318 shell> ln -s /dev/null $HOME/.mysql_history
1320 syslog Logging Characteristics.PP If the --syslog option is given,
1321 mysql writes interactive statements to the system logging facility.
1322 Message logging has the following characteristics.
1323 Logging occurs at the “information” level. This corresponds to the
1325 LOG_INFO priority for syslog on Unix/Linux syslog capability and to
1326 EVENTLOG_INFORMATION_TYPE for the Windows Event Log. Consult your
1327 system documentation for configuration of your logging capability.
1328 Message size is limited to 1024 bytes.
1330 Messages consist of the identifier MysqlClient followed by these
1332 values:
1333 · SYSTEM_USER
1335 The system user name (login name) or -- if the user is unknown.
1337 · MYSQL_USER
1339 The MySQL user name (specified with the --user option) or -- if the
1341 user is unknown.
1342 · CONNECTION_ID:
1344 The client connection identifier. This is the same as the
1346 CONNECTION_ID() function value within the session.
1347 · DB_SERVER
1349 The server host or -- if the host is unknown.
1351 · DB
1353 The default database or -- if no database has been selected.
1355 · QUERY
1357 The text of the logged statement.
1359 Here is a sample of output generated on Linux by using --syslog. This
1361 output is formatted for readability; each logged message actually takes
1362 a single line.
1363 Mar 7 12:39:25 myhost MysqlClient[20824]:
1365 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
1366 DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
1367 Mar 7 12:39:28 myhost MysqlClient[20824]:
1368 SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
1369 DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'
1370
1372 mysql> help search_string
1373 If you provide an argument to the help command, mysql uses it as a
1375 search string to access server-side help from the contents of the MySQL
1376 Reference Manual. The proper operation of this command requires that
1377 the help tables in the mysql database be initialized with help topic
1378 information (see Section 5.1.14, “Server-Side Help Support”).
1379 If there is no match for the search string, the search fails:
1381 mysql> help me
1383 Nothing found
1384 Please try to run 'help contents' for a list of all accessible topics
1385 Use help contents to see a list of the help categories:
1387 mysql> help contents
1389 You asked for help about help category: "Contents"
1390 For more information, type 'help <item>', where <item> is one of the
1391 following categories:
1392 Account Management
1393 Administration
1394 Data Definition
1395 Data Manipulation
1396 Data Types
1397 Functions
1398 Functions and Modifiers for Use with GROUP BY
1399 Geographic Features
1400 Language Structure
1401 Plugins
1402 Storage Engines
1403 Stored Routines
1404 Table Maintenance
1405 Transactions
1406 Triggers
1407 If the search string matches multiple items, mysql shows a list of
1409 matching topics:
1410 mysql> help logs
1412 Many help items for your request exist.
1413 To make a more specific request, please type 'help <item>',
1414 where <item> is one of the following topics:
1415 SHOW
1416 SHOW BINARY LOGS
1417 SHOW ENGINE
1418 SHOW LOGS
1419 Use a topic as the search string to see the help entry for that topic:
1421 mysql> help show binary logs
1423 Name: 'SHOW BINARY LOGS'
1424 Description:
1425 Syntax:
1426 SHOW BINARY LOGS
1427 SHOW MASTER LOGS
1428 Lists the binary log files on the server. This statement is used as
1429 part of the procedure described in [purge-binary-logs], that shows how
1430 to determine which logs can be purged.
1431 mysql> SHOW BINARY LOGS;
1432 +---------------+-----------+-----------+
1433 | Log_name | File_size | Encrypted |
1434 +---------------+-----------+-----------+
1435 | binlog.000015 | 724935 | Yes |
1436 | binlog.000016 | 733481 | Yes |
1437 +---------------+-----------+-----------+
1438 The search string can contain the wildcard characters % and _. These
1440 have the same meaning as for pattern-matching operations performed with
1441 the LIKE operator. For example, HELP rep% returns a list of topics that
1442 begin with rep:
1443 mysql> HELP rep%
1445 Many help items for your request exist.
1446 To make a more specific request, please type 'help <item>',
1447 where <item> is one of the following
1448 topics:
1449 REPAIR TABLE
1450 REPEAT FUNCTION
1451 REPEAT LOOP
1452 REPLACE
1453 REPLACE FUNCTION
1454
1456 The mysql client typically is used interactively, like this:
1457 shell> mysql db_name
1459 However, it is also possible to put your SQL statements in a file and
1461 then tell mysql to read its input from that file. To do so, create a
1462 text file text_file that contains the statements you wish to execute.
1463 Then invoke mysql as shown here:
1464 shell> mysql db_name < text_file
1466 If you place a USE db_name statement as the first statement in the
1468 file, it is unnecessary to specify the database name on the command
1469 line:
1470 shell> mysql < text_file
1472 If you are already running mysql, you can execute an SQL script file
1474 using the source command or \. command:
1475 mysql> source file_name
1477 mysql> \. file_name
1478 Sometimes you may want your script to display progress information to
1480 the user. For this you can insert statements like this:
1481 SELECT '<info_to_display>' AS ' ';
1483 The statement shown outputs <info_to_display>.
1485 You can also invoke mysql with the --verbose option, which causes each
1487 statement to be displayed before the result that it produces.
1488 mysql ignores Unicode byte order mark (BOM) characters at the beginning
1490 of input files. Previously, it read them and sent them to the server,
1491 resulting in a syntax error. Presence of a BOM does not cause mysql to
1492 change its default character set. To do that, invoke mysql with an
1493 option such as --default-character-set=utf8.
1494 For more information about batch mode, see Section 3.5, “Using mysql in
1496 Batch Mode”.
1497
1499 This section describes some techniques that can help you use mysql more
1500 effectively.
1501 Input-Line Editing
1503 mysql supports input-line editing, which enables you to modify the
1504 current input line in place or recall previous input lines. For
1505 example, the left-arrow and right-arrow keys move horizontally within
1506 the current input line, and the up-arrow and down-arrow keys move up
1507 and down through the set of previously entered lines. Backspace
1508 deletes the character before the cursor and typing new characters
1509 enters them at the cursor position. To enter the line, press Enter.
1510 On Windows, the editing key sequences are the same as supported for
1512 command editing in console windows. On Unix, the key sequences depend
1513 on the input library used to build mysql (for example, the libedit or
1514 readline library).
1515 Documentation for the libedit and readline libraries is available
1517 online. To change the set of key sequences permitted by a given input
1518 library, define key bindings in the library startup file. This is a
1519 file in your home directory: .editrc for libedit and .inputrc for
1520 readline.
1521 For example, in libedit, Control+W deletes everything before the
1523 current cursor position and Control+U deletes the entire line. In
1524 readline, Control+W deletes the word before the cursor and Control+U
1525 deletes everything before the current cursor position. If mysql was
1526 built using libedit, a user who prefers the readline behavior for these
1527 two keys can put the following lines in the .editrc file (creating the
1528 file if necessary):
1529 bind "^W" ed-delete-prev-word
1531 bind "^U" vi-kill-line-prev
1532 To see the current set of key bindings, temporarily put a line that
1534 says only bind at the end of .editrc. mysql will show the bindings
1535 when it starts.
1536 Unicode Support on Windows
1538 Windows provides APIs based on UTF-16LE for reading from and writing to
1539 the console; the mysql client for Windows is able to use these APIs.
1540 The Windows installer creates an item in the MySQL menu named MySQL
1541 command line client - Unicode. This item invokes the mysql client with
1542 properties set to communicate through the console to the MySQL server
1543 using Unicode.
1544 To take advantage of this support manually, run mysql within a console
1546 that uses a compatible Unicode font and set the default character set
1547 to a Unicode character set that is supported for communication with the
1548 server:
1549 1. Open a console window.
1551 2. Go to the console window properties, select the font tab, and
1553 choose Lucida Console or some other compatible Unicode font. This
1554 is necessary because console windows start by default using a DOS
1555 raster font that is inadequate for Unicode.
1556 3. Execute mysql.exe with the --default-character-set=utf8 (or
1558 utf8mb4) option. This option is necessary because utf16le is one of
1559 the character sets that cannot be used as the client character set.
1560 See the section called “Impermissible Client Character Sets”.
1561 With those changes, mysql will use the Windows APIs to communicate with
1563 the console using UTF-16LE, and communicate with the server using
1564 UTF-8. (The menu item mentioned previously sets the font and character
1565 set as just described.)
1566 To avoid those steps each time you run mysql, you can create a shortcut
1568 that invokes mysql.exe. The shortcut should set the console font to
1569 Lucida Console or some other compatible Unicode font, and pass the
1570 --default-character-set=utf8 (or utf8mb4) option to mysql.exe.
1571 Alternatively, create a shortcut that only sets the console font, and
1573 set the character set in the [mysql] group of your my.ini file:
1574 [mysql]
1576 default-character-set=utf8
1577 Displaying Query Results Vertically
1579 Some query results are much more readable when displayed vertically,
1580 instead of in the usual horizontal table format. Queries can be
1581 displayed vertically by terminating the query with \G instead of a
1582 semicolon. For example, longer text values that include newlines often
1583 are much easier to read with vertical output:
1584 mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
1586 *************************** 1. row ***************************
1587 msg_nro: 3068
1588 date: 2000-03-01 23:29:50
1589 time_zone: +0200
1590 mail_from: Monty
1591 reply: monty@no.spam.com
1592 mail_to: "Thimble Smith" <tim@no.spam.com>
1593 sbj: UTF-8
1594 txt: >>>>> "Thimble" == Thimble Smith writes:
1595 Thimble> Hi. I think this is a good idea. Is anyone familiar
1596 Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
1597 Thimble> TODO list and see what happens.
1598 Yes, please do that.
1599 Regards,
1600 Monty
1601 file: inbox-jani-1
1602 hash: 190402944
1603 1 row in set (0.09 sec)
1604 Using Safe-Updates Mode (--safe-updates)
1606 For beginners, a useful startup option is --safe-updates (or
1607 --i-am-a-dummy, which has the same effect). Safe-updates mode is
1608 helpful for cases when you might have issued an UPDATE or DELETE
1609 statement but forgotten the WHERE clause indicating which rows to
1610 modify. Normally, such statements update or delete all rows in the
1611 table. With --safe-updates, you can modify rows only by specifying the
1612 key values that identify them, or a LIMIT clause, or both. This helps
1613 prevent accidents. Safe-updates mode also restricts SELECT statements
1614 that produce (or are estimated to produce) very large result sets.
1615 The --safe-updates option causes mysql to execute the following
1617 statement when it connects to the MySQL server, to set the session
1618 values of the sql_safe_updates, sql_select_limit, and max_join_size
1619 system variables:
1620 SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
1622 The SET statement affects statement processing as follows:
1624 · Enabling sql_safe_updates causes UPDATE and DELETE statements to
1626 produce an error if they do not specify a key constraint in the
1627 WHERE clause, or provide a LIMIT clause, or both. For example:
1628 UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
1630 UPDATE tbl_name SET not_key_column=val LIMIT 1;
1631 · Setting sql_select_limit to 1,000 causes the server to limit all
1633 SELECT result sets to 1,000 rows unless the statement includes a
1634 LIMIT clause.
1635 · Setting max_join_size to 1,000,000 causes multiple-table SELECT
1637 statements to produce an error if the server estimates it must
1638 examine more than 1,000,000 row combinations.
1639 To specify result set limits different from 1,000 and 1,000,000, you
1641 can override the defaults by using the --select_limit and
1642 --max_join_size options when you invoke mysql:
1643 mysql --safe-updates --select_limit=500 --max_join_size=10000
1645 It is possible for UPDATE and DELETE statements to produce an error in
1647 safe-updates mode even with a key specified in the WHERE clause, if the
1648 optimizer decides not to use the index on the key column:
1649 · Range access on the index cannot be used if memory usage exceeds
1651 that permitted by the range_optimizer_max_mem_size system variable.
1652 The optimizer then falls back to a table scan. See the section
1653 called “Limiting Memory Use for Range Optimization”.
1654 · If key comparisons require type conversion, the index may not be
1656 used (see Section 8.3.1, “How MySQL Uses Indexes”). Suppose that an
1657 indexed string column c1 is compared to a numeric value using WHERE
1658 c1 = 2222. For such comparisons, the string value is converted to a
1659 number and the operands are compared numerically (see Section 12.2,
1660 “Type Conversion in Expression Evaluation”), preventing use of the
1661 index. If safe-updates mode is enabled, an error occurs.
1662 As of MySQL 8.0.13, safe-updates mode also includes these behaviors:
1664 · EXPLAIN with UPDATE and DELETE statements does not produce
1666 safe-updates errors. This enables use of EXPLAIN plus SHOW WARNINGS
1667 to see why an index is not used, which can be helpful in cases such
1668 as when a range_optimizer_max_mem_size violation or type conversion
1669 occurs and the optimizer does not use an index even though a key
1670 column was specified in the WHERE clause.
1671 · When a safe-updates error occurs, the error message includes the
1673 first diagnostic that was produced, to provide information about
1674 the reason for failure. For example, the message may indicate that
1675 the range_optimizer_max_mem_size value was exceeded or type
1676 conversion occurred, either of which can preclude use of an index.
1677 · For multiple-table deletes and updates, an error is produced with
1679 safe updates enabled only if any target table uses a table scan.
1680 Disabling mysql Auto-Reconnect
1682 If the mysql client loses its connection to the server while sending a
1683 statement, it immediately and automatically tries to reconnect once to
1684 the server and send the statement again. However, even if mysql
1685 succeeds in reconnecting, your first connection has ended and all your
1686 previous session objects and settings are lost: temporary tables, the
1687 autocommit mode, and user-defined and session variables. Also, any
1688 current transaction rolls back. This behavior may be dangerous for you,
1689 as in the following example where the server was shut down and
1690 restarted between the first and second statements without you knowing
1691 it:
1692 mysql> SET @a=1;
1694 Query OK, 0 rows affected (0.05 sec)
1695 mysql> INSERT INTO t VALUES(@a);
1696 ERROR 2006: MySQL server has gone away
1697 No connection. Trying to reconnect...
1698 Connection id: 1
1699 Current database: test
1700 Query OK, 1 row affected (1.30 sec)
1701 mysql> SELECT * FROM t;
1702 +------+
1703 | a |
1704 +------+
1705 | NULL |
1706 +------+
1707 1 row in set (0.05 sec)
1708 The @a user variable has been lost with the connection, and after the
1710 reconnection it is undefined. If it is important to have mysql
1711 terminate with an error if the connection has been lost, you can start
1712 the mysql client with the --skip-reconnect option.
1713 For more information about auto-reconnect and its effect on state
1715 information when a reconnection occurs, see Section 28.7.28, “C API
1716 Automatic Reconnection Control”.
1717
1719 Copyright © 1997, 2019, Oracle and/or its affiliates. All rights
1720 reserved.
1721 This documentation is free software; you can redistribute it and/or
1723 modify it only under the terms of the GNU General Public License as
1724 published by the Free Software Foundation; version 2 of the License.
1725 This documentation is distributed in the hope that it will be useful,
1727 but WITHOUT ANY WARRANTY; without even the implied warranty of
1728 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1729 General Public License for more details.
1730 You should have received a copy of the GNU General Public License along
1732 with the program; if not, write to the Free Software Foundation, Inc.,
1733 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
1734 http://www.gnu.org/licenses/.
1735
1738 1. MySQL Shell 8.0 (part of MySQL 8.0)
1739 http://dev.mysql.com/doc/mysql-shell/8.0/en/
1740
1742 For more information, please refer to the MySQL Reference Manual, which
1743 may already be installed locally and which is also available online at
1744 http://dev.mysql.com/doc/.
1745
1747 Oracle Corporation (http://dev.mysql.com/).
1748MySQL 8.0 02/20/2019 MYSQL(1)