8.5.4.2. 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 MySQL server:
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000, SQL_MAX_JOIN_SIZE=1000000;
See Section 13.5.3, “SET Syntax”.
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
