This section describes some techniques that can help you use
mysql more effectively.
Displaying Query Results Vertically
Some query results are much more readable when displayed
vertically, instead of in the usual horizontal table format.
Queries can be displayed vertically by terminating the query with
\G instead of a semicolon. For example, longer text values that
include newlines often are much easier to read with vertical
output:
mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
msg_nro: 3068
date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
reply: monty@no.spam.com
mail_to: "Thimble Smith" <tim@no.spam.com>
sbj: UTF-8
txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi. I think this is a good idea. Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I´ll put this on my
Thimble> TODO list and see what happens.
Yes, please do that.
Regards,
Monty
file: inbox-jani-1
hash: 190402944
1 row in set (0.09 sec)
Using the --safe-updates Option
For beginners, a useful startup option is --safe-updates (or
--i-am-a-dummy, which has the same effect). It is helpful for
cases when you might have issued a DELETE FROM tbl_name statement
but forgotten the WHERE clause. Normally, such a statement
deletes all rows from the table. With --safe-updates, you can
delete rows only by specifying the key values that identify them.
This helps prevent accidents.
When you use the --safe-updates option, mysql issues the
following statement when it connects to the MariaDB server:
SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;
The SET statement has the following effects:
• You are not allowed to execute an UPDATE or DELETE statement
unless you specify a key constraint in the WHERE clause or
provide a LIMIT clause (or both). For example:
UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
UPDATE tbl_name SET not_key_column=val LIMIT 1;
• The server limits all large SELECT results to 1,000 rows
unless the statement includes a LIMIT clause.
• The server aborts multiple-table SELECT statements that
probably need to examine more than 1,000,000 row
combinations.
To specify limits different from 1,000 and 1,000,000, you can
override the defaults by using the --select-limit and
--max-join-size options:
shell> mysql --safe-updates --select-limit=500 --max-join-size=10000
Disabling mysql Auto-Reconnect
If the mysql client loses its connection to the server while
sending a statement, it immediately and automatically tries to
reconnect once to the server and send the statement again.
However, even if mysql succeeds in reconnecting, your first
connection has ended and all your previous session objects and
settings are lost: temporary tables, the autocommit mode, and
user-defined and session variables. Also, any current transaction
rolls back. This behavior may be dangerous for you, as in the
following example where the server was shut down and restarted
between the first and second statements without you knowing it:
mysql> SET @a=1;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t VALUES(@a);
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: test
Query OK, 1 row affected (1.30 sec)
mysql> SELECT * FROM t;
+------+
| a |
+------+
| NULL |
+------+
1 row in set (0.05 sec)
The @a user variable has been lost with the connection, and after
the reconnection it is undefined. If it is important to have
mysql terminate with an error if the connection has been lost,
you can start the mysql client with the --skip-reconnect option.
