技術共有

MySQL sql_safe_updates パラメータ

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

sql_safe_updates は、MySQL サーバーが KEY または LIMIT 句を使用しない UPDATE または DELETE ステートメントでの更新または削除操作を許可するかどうかを制御する MySQL のシステム変数です。この変数が ON に設定されている場合、MySQL は、WHERE 句またはLIMIT 句は、影響を受ける行数を制限します。

この目的は、SQL ステートメントの不注意または誤った書き込みによる大量のデータの誤った損失や変更を防ぐことです。

sql_safe_updates を設定する方法

sql_safe_updates はいくつかの方法で設定できます。

グローバルレベル:

MySQL 構成ファイル (オペレーティング システムや MySQL のバージョンに応じて、my.cnf や my.ini など) を変更することで、この変数を永続的に設定できます。ただし、構成ファイルで sql_safe_updates を直接設定することは、すべての MySQL バージョンでサポートされているわけではない場合や、別の構成 (プラグインや他のシステム変数などを使用するなど) が必要な場合があることに注意してください。
より一般的なアプローチは、MySQL の SET GLOBAL ステートメントを使用して実行時に設定することですが、これは新しい接続にのみ影響します。例えば：

SET GLOBAL sql_safe_updates = 1;

  • 1

ただし、グローバル変数を直接設定するには管理者権限が必要な場合があり、この変更は既存のセッションには影響しないことに注意してください。

セッションレベル:

MySQL セッションで次の SQL ステートメントを実行することで、sql_safe_updates を設定できます。

SET SESSION sql_safe_updates = 1;

或者登录时加上--safe-updates 

mysql -uroot -p --safe-updates 

  • 1
  • 2
  • 3
  • 4
  • 5

これは現在のセッションの後続のアクションに影響しますが、他のセッションやグローバル設定には影響しません。

予防

  • sql_safe_updates が有効になっている場合、KEY または LIMIT を指定せずに UPDATE または DELETE ステートメントを実行しようとすると、MySQL は操作を拒否し、エラーを返します。
(root@localhost)[superdb]> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept  |          0 | PRIMARY  |            1 | deptno      | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.13 sec)

(root@localhost)[superdb]> update dept set loc='sz';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

  • すべての MySQL デプロイメントで sql_safe_updates がデフォルトで有効になるわけではありません。通常、データベース管理者または開発者が特定のセキュリティ要件に基づいて構成します。

  • 場合によっては、特定の一括更新または削除操作を実行するために、sql_safe_updates を一時的に無効にする必要がある場合があります。この場合、セッション レベルで sql_safe_updates = 0 を設定できますが、SQL ステートメントが安全であり、誤って大量のデータに影響を与えないように注意してください。

要約すると、sql_safe_updates は、不注意やエラーによるデータ損失の防止に役立つ便利なセキュリティ機能です。ただし、開発者やデータベース管理者は、SQL ステートメントのセキュリティと正確性を確保するために、より注意を払う必要もあります。

公式説明

1 に設定すると、MySQL は WHERE 句または LIMIT 句でキーを使用しない UPDATE または DELETE ステートメントを中止します。(具体的には、UPDATE ステートメントにはキーまたは LIMIT 句、あるいはその両方を使用する WHERE 句が必要です。DELETE ステートメントには両方が必要です。) これにより、キーが適切に使用されず、大量の行を変更または削除する可能性のある UPDATE または DELETE ステートメントをキャッチできるようになります。デフォルト値は 0 です。

sql_safe_updates が 1 に設定されている場合。

  • update ステートメントが正常に実行されるには、次の条件のいずれかを満たしている必要があります。
    • update ステートメントでは where が使用され、where 条件にはインデックス列が存在する必要があります。
    • update ステートメントは、limit を使用します。
    • update ステートメントでは、where とlimit を同時に使用します。現時点では、where 条件にインデックス列を含める必要はありません。
(root@localhost)[superdb]> update dept set loc='sz' limit 1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost)[superdb]> select * from dept;
+--------+------------+---------+
| deptno | dname      | loc     |
+--------+------------+---------+
|     10 | ACCOUNTING | sz      |
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> update dept set loc='NEW YORK' limit 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost)[superdb]> update dept set loc='NEW YORK' where deptno=10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

(root@localhost)[superdb]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> update dept set loc='NEW YORK' where deptno=10 limit 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

(root@localhost)[superdb]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • delete ステートメントが正常に実行されるには、次の条件を満たす必要があります。
    • delete ステートメントは、where 条件でもインデックス列を使用します。
    • delete ステートメントは、インデックス列と where 条件の制限の両方を使用します。
    • delete ステートメントでは、where と limit を同時に使用します。このとき、where 条件にはインデックス列が必要ありません。
(root@localhost)[superdb]> insert into dept values(50,'sz','hk');
Query OK, 1 row affected (0.01 sec)

-- 同时使用 where 和 limit，此时 where 条件中可以有索引列
(root@localhost)[superdb]> delete from dept where deptno=50 limit 1;
Query OK, 1 row affected (0.00 sec)

(root@localhost)[superdb]> insert into dept values(50,'sz','hk');
Query OK, 1 row affected (0.00 sec)

-- 仅使用 where条件中是索引列
(root@localhost)[superdb]> delete from dept where deptno=50;
Query OK, 1 row affected (0.01 sec)

(root@localhost)[superdb]> insert into dept values(50,'sz','hk');
Query OK, 1 row affected (0.00 sec)

-- dname不是索引列，因此无法删除操作
(root@localhost)[superdb]> delete from dept where dname='sz';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

-- 同时使用 where 和 limit，此时 where 条件中没有索引列
(root@localhost)[superdb]> delete from dept where dname='sz' limit 1;
Query OK, 1 row affected (0.05 sec)

(root@localhost)[superdb]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept  |          0 | PRIMARY  |            1 | deptno      | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.13 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43

where 条件によってインデックス列が取得されるが、オプティマイザーが最終的にインデックスではなくテーブル全体をスキャンして選択する場合、force Index([index_name]) を使用して、全体がロックされる可能性を回避するためにどのインデックスを使用するかをオプティマイザーに指示できます。テーブルによって引き起こされる隠れた危険。

個人プロフィール

彼は 30 年以上テクノロジーの研究に専念しており、Java、linux、javascript、php、css などのさまざまな言語に堪能であり、オープンソース分野で多くの貢献を行っています。将来の参考のために技術開発におけるいくつかの問題を共有する開発者ドキュメント ステーション。

私の連絡先情報

郵便