[+/-]
This section describes some techniques that can help you use mysql more effectively.
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)
--safe-updates
Option
For beginners, a useful startup option is
--safe-updates
(or
--i-am-a-dummy
,
which has the same effect). This option was introduced in
MySQL 3.23.11. It is helpful for cases when you might have
issued a DELETE FROM
statement but
forgotten the tbl_name
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 MySQL server:
SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000;
See Section 5.1.4, “Session System Variables”.
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:
UPDATEtbl_name
SETnot_key_column
=val
WHEREkey_column
=val
; UPDATEtbl_name
SETnot_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
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.
For more information about auto-reconnect and its effect on state information when a reconnection occurs, see Section 17.6.11, “Controlling Automatic Reconnection Behavior”.
User Comments
Please be careful with '--safe-updates' mode. Though 'DELETE from table' is protected, but 'DROP Table' works withough any warning.
Add your own comment.