mysqlコマンドクライアントに--safe-updatesというオプションがあります。このオプションをつけるとSafe Updateモードでクライアントを利用することができ、特定の条件の更新作業ができないように制御できるようになります。今回はこのオプションについて見ていきましょう。

なお、今回利用しているMySQLはバージョン8.0.35になります。

--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句（あるいは両方）を指定することでのみ、行を変更および削除することが可能です。この設定により、WHERE句が指定されていなかったり意図しない更新などを防ぐことが可能です。

また、後述するsql_select_limitやmax_join_sizeも設定されるため、クエリによっては表示が制限されるようになります。

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.

mysql> UPDATE t1 SET val = 'three' limit 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE t1 SET val = 'three' WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t1;
+----+-------+
| id | val   |
+----+-------+
|  1 | three |
|  2 | four  |
+----+-------+
2 rows in set (0.00 sec)

--safe-updatesの設定

--safe-updatesを付与して起動したクライアントは、セッションにsql_safe_updates=1，sql_select_limit=1000，max_join_size=1000000が設定されて起動します。

mysql> SELECT @@sql_safe_updates, @@sql_select_limit, @@max_join_size;
+--------------------+----------------------+----------------------+
| @@sql_safe_updates | @@sql_select_limit   | @@max_join_size      |
+--------------------+----------------------+----------------------+
|                  0 | 18446744073709551615 | 18446744073709551615 |
+--------------------+----------------------+----------------------+
1 row in set (0.00 sec)

mysql> SELECT @@sql_safe_updates, @@sql_select_limit, @@max_join_size;
+--------------------+--------------------+-----------------+
| @@sql_safe_updates | @@sql_select_limit | @@max_join_size |
+--------------------+--------------------+-----------------+
|                  1 |               1000 |         1000000 |
+--------------------+--------------------+-----------------+
1 row in set (0.00 sec)

sql_safe_updates

この変数が有効な場合、WHERE句でキーを使用しない場合やLIMIT句で行数の指定がない場合はUPDATE、DELETEステートメントがエラーになります。 デフォルトの設定ではOFFになっています。

sql_select_limit

SELECTステートメントで返される最大行数を指定します。デフォルトで2ˆ32−1または2ˆ64−1が設定されています。この設定を超えた行数が返されるSELECTステートメントを実行しようとすると、件数がsql_select_limitまで制限されて表示されます。LIMIT句を利用している場合は、LIMIT句がsql_select_limitの値まではLIMIT句の値で制限されて行を表示します。

たとえば以下では、全体で2行あるテーブルt1に対してsql_select_limitが1に制限されているため、1行しか行が表示されていません。

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の基となるテーブル（単一のテーブルの場合はそのテーブル）への最大アクセス行数を制限します。もし基テーブルにおいてmax_join_sizeより多くの行を読むこむ必要があると推定される場合はエラーが発生します。

ただし、この設定はMySQLのバージョンが8.0.31以前では少し挙動が違い、max_join_sizeを最大推定コストとして扱っていたため、予期しない結果になる可能性がありました。詳細についてはMySQL 8.0.32のリリースノートを確認してください。

この制限により以下のエラーが発生する場合は、MAX_JOIN_SIZEを変更するかSQL_BIG_SELECTS=1を設定することで、エラーを回避することができます。

SQL_BIG_SELECTS=1を設定すると、max_join_sizeは無視されます。

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_join_sizeで制限がある場合でも、EXPLAINで実行計画を確認することはできます。

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_select_limitなどの値が1000では足りない場合もあるかもしれません。そのような場合は以下のように--select-limitや--max-join-sizeを指定して起動することで、変更した値でSafe Updateモードを実行できます。

$ mysql -h 127.0.0.1 -P 3306 -u root -p --safe-updates --select-limit=2000

または、SET構文でsql_select_limitやmax_join_sizeを変更して利用してください。

I am a dummy

この--safe-updatesは--i-am-a-dummyというシノニムを持っています。このオプションをつけて実行することで、--safe-updatesと同様の効果を得ることができます。

まとめ

今回は、mysqlコマンドクライアントで実行するクエリの制限をする方法として、--safe-updatesというオプションを紹介しました。WHERE句のないクエリによる更新ができないため、事故防止として利用できたり、キーが適切に設定されておらず想定以上に大きいクエリの実行を防ぐことができます。もし有効な場面があれば利用してみてください。詳細については公式ドキュメントのセーフ更新モードの使用を確認してください。