mysqlコマンドクライアントに--safe-updatesというオプションがあります。このオプションをつけるとSafe Updateモードでクライアントを利用することができ、特定の条件の更新作業ができないように制御できるようになります。今回はこのオプションについて見ていきましょう。
なお、今回利用しているMySQLはバージョン8.
--safe-updates
mysqlコマンドクライアントに--safe-updatesを付与して起動すると、Safe Updateモードとしてクライアントが起動されます。
$ mysql -h 127.0.0.1 -P 3306 -u root -p --safe-updates
このモードが有効になっていると、UPDATE文やDELETE文を実行するときに、WHERE句がない場合にエラーを返すようになります。Safe Updateモードが有効な場合は、行を識別するキーの値またはLIMIT句
また、後述するsql_
mysql> SELECT * FROM t1; +----+------+ | id | val | +----+------+ | 1 | one | | 2 | two | +----+------+ 2 rows in set (0.00 sec) mysql> UPDATE t1 SET val = 'three'; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
--safe-updatesの設定
--safe-updatesを付与して起動したクライアントは、セッションにsql_
,sql_
,max_
が設定されて起動します。
sql_safe_updates
この変数が有効な場合、WHERE句でキーを使用しない場合やLIMIT句で行数の指定がない場合はUPDATE、DELETEステートメントがエラーになります。 デフォルトの設定ではOFFになっています。
sql_select_limit
SELECTステートメントで返される最大行数を指定します。デフォルトで2ˆ32−1または2ˆ64−1が設定されています。この設定を超えた行数が返されるSELECTステートメントを実行しようとすると、件数がsql_
たとえば以下では、全体で2行あるテーブルt1に対してsql_
mysql> SET SESSION sql_select_limit = 1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT count(1) FROM t1; +----------+ | count(1) | +----------+ | 2 | +----------+ 1 row in set (0.05 sec) mysql> SELECT * FROM t1; +----+-------+ | id | val | +----+-------+ | 1 | three | +----+-------+ 1 row in set (0.00 sec)
max_join_size
この値はJOINの基となるテーブル
ただし、この設定はMySQLのバージョンが8.
この制限により以下のエラーが発生する場合は、MAX_SQL_
を設定することで、エラーを回避することができます。
SQL_
を設定すると、max_
mysql> set session max_join_size = 10; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM dummy; ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
また、max_
mysql> SELECT * FROM dummy WHERE id in (1,2,3,5,7,89); ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay mysql> EXPLAIN SELECT * FROM dummy WHERE id in (1,2,3,5,7,89); +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | dummy | NULL | ALL | PRIMARY | NULL | NULL | NULL | 99666 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)
設定の変更
Safe Updateモードで起動させたいが、sql_
$ mysql -h 127.0.0.1 -P 3306 -u root -p --safe-updates --select-limit=2000
または、SET構文でsql_
I am a dummy
この--safe-updatesは--i-am-a-dummyというシノニムを持っています。このオプションをつけて実行することで、--safe-updatesと同様の効果を得ることができます。
まとめ
今回は、mysqlコマンドクライアントで実行するクエリの制限をする方法として、--safe-updatesというオプションを紹介しました。WHERE句のないクエリによる更新ができないため、事故防止として利用できたり、キーが適切に設定されておらず想定以上に大きいクエリの実行を防ぐことができます。もし有効な場面があれば利用してみてください。詳細については公式ドキュメントのセーフ更新モードの使用を確認してください。