本番環境でのMySQLパラメータsql_safe_updatesの使用に関する詳細な説明

序文

アプリケーションのバグや DBA の誤操作が発生した場合、テーブル全体が更新される可能性があります (update delete)。 MySQL では、この操作を制限するために sql_safe_updates が提供されています。

sql_safe_updates を 1 に設定します。

設定後、更新および削除における where 条件のない SQL 実行が制限され、より厳格になります。既存のオンライン環境に悪影響を及ぼします。新しいシステムとアプリケーションを厳密に監査することで、テーブル全体の更新が行われないようにすることができます。

テーブルworking.test01を作成します(id INT NOT NULL AUTO_INCREMENT、NAME VARCHAR(20)、age INT、gmt_created DATETIME、PRIMARY KEY(id));

 test01(name,age,gmt_created) に値('xiaowang',2,now()) を挿入します。
 test01(name,age,gmt_created) に値 ('huahua',5,now()) を挿入します。 
 test01(name,age,gmt_created) に値('gougou',9,now()) を挿入します。 
 test01(name,age,gmt_created) に値 ('heihei',12,now()) を挿入します。 
 test01(name,age,gmt_created) に値('baibai',134,now()) を挿入します。 

# フィルターフィールドのインデックスは更新されません
test01 を更新し、name = 'xiaoxiao'、age = 2 に設定します。
エラー 1175 (HY000): セーフ更新モードを使用しており、KEY 列を使用する WHERE なしでテーブルを更新しようとしました。
# テーブル全体を更新します update test01 set name = 'xiaoxiao';
エラー 1175 (HY000): セーフ更新モードを使用しており、KEY 列を使用する WHERE なしでテーブルを更新しようとしました。
# 制限更新を追加 update test01 set name = 'xia' limit 1;
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

# 新しいインデックスを作成します。create index idx_age on test01(age);

test01 を更新し、name = 'xiaoxiao'、age = 2 に設定します。
クエリは正常、1 行が影響を受けました (0.01 秒)
一致した行: 1 変更された行: 1 警告: 0

test01 を更新し、name = 'hhh' を設定します。age = 9、limit 10;
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

テーブル test01 を変更し、インデックス idx_age を削除します。
test01(age,name) にインデックス idx_age_name を作成します。


test01 を更新し、age を 100 に設定し、name を 'hhh' に設定します。
エラー 1175 (HY000): セーフ更新モードを使用しており、KEY 列を使用する WHERE なしでテーブルを更新しようとしました。

test01 を更新し、age を 100 に設定し、name を 'hhh' に設定し、limit を 10 に設定します。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

したがって、更新時に、where条件がない場合、またはwhere条件がインデックスフィールドでない場合は、limitを使用する必要があります。where条件がある場合は、インデックスフィールド

最近、仕事で別の問題を発見しました。MySQL sql_safe_updates はサブクエリの更新をサポートしていません。

開発者が誤ってデータを更新する可能性があることを考慮して、オンラインデータベースの MySQL インスタンスでは、インデックスなしでの更新と削除を回避するために sql_safe_updates=1 を設定する必要があります。

その結果、ある日、開発者は次の SQL を正しく実行できないことに気付きました。

t1 を更新し、key1 が in の場合に col2=1 を設定します (key2='ABcD' の場合に t2 から col2 を選択)。

エラーは次のとおりです:

エラー 1175 (HY000): セーフ更新モードを使用しており、KEY 列を使用する WHERE なしでテーブルを更新しようとしました。

つまり、インデックスされていない where 条件を更新することはできません。調べてみたところ、確かにそれは不可能だということが分かりました。 key1 と key2 がそれぞれ t1 と t2 のインデックスであっても [主キーに変更することはできません]。説明: サブクエリの更新はサポートされていません。

グーグルで検索してみたところ、以前にも誰かがこの質問をしていたことがわかりました。。

http://stackoverflow.com/questions/24314830/query-not-getting-executed-if-supplied-a-nested-sub-query

最終解決策:

1) セッションレベルのパラメータを変更します。sql_safe_updates set sql_safe_updates=0; 、更新操作を実行します。ターミナルを終了します。

2) プログラム処理: 最初にselect col2 from t2 where key2='ABcD'データを取得し、次に結果をループして、 update t1 set col2=1 where key1=？を使用します。一括更新しました。プログラム処理を使用することをお勧めします。変数の一時的な変更は長期的な解決策ではありません。

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。

以下もご興味があるかもしれません: