MySQL 更新セットとの違い

問題の説明

最近、奇妙な問い合わせを受けました。更新ステートメントはエラーなく実行されたのですが、データが更新されませんでした。問題のある具体的なステートメントは次のようなものでした。

test.stu を更新し、cname = '0'、math = 90、his = 80、id = 100 に設定します。

原因分析

直感的に、この更新ステートメントの構文には問題があります。複数のデータ列を更新するための通常の構文では、次の形式のようにカンマを使用する必要があります。

test.stu を更新し、cname = '0'、math = 90、his = 80、id = 100 に設定します。

とを直接使用した場合の最初の反応は、実際には構文エラーが報告され、正常に実行できないように見えることです。次に、Tencent Cloud Database MySQL に基づいて、実際に簡単なシナリオを構築し、この問題を再現してみます。

SQL ステートメントは次のとおりです。

テーブル `stu` を作成します (
  `id` int(11) NULLではない、
  `sname` varchar(16) NOT NULL,
  `cname` varchar(8) デフォルト NULL,
  `math` int(11) NOT NULL,
  `eng` int(11) デフォルト NULL,
  `his` int(11) デフォルト NULL,
  主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

stu に値 (100,'sam','0',90,88,83) を挿入します。
stu に値 (101,'jhon','1',97,82,81) を挿入します。
stu に値 (102,'mary','2',87,89,92) を挿入します。
stu に値 (103,'adam','2',87,89,92) を挿入します。

次に、通常の更新ステートメントと、およびを使用した更新ステートメントを試して、実際の実行結果を確認します。

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> test.stu を更新し、cname = '0'、math = 90、his = 80、id = 100 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
一致した行: 1 変更: 0 警告: 0

mysql> stu から * を選択します。
+-----+-------+-------+------+------+------+
| id | sname | cname | math | eng | his |
+-----+-------+-------+------+------+------+
| 100 | サム | 0 | 90 | 88 | 83 |
| 101 | ジョン | 1 | 97 | 82 | 81 |
| 102 | メアリー | 2 | 87 | 89 | 92 |
| 103 | アダム | 2 | 87 | 89 | 92 |
+-----+-------+-------+------+------+------+
セット内の 4 行 (0.00 秒)

mysql> test.stu を更新し、cname = '0'、math = 90、his = 80、id = 100 に設定します。
クエリは正常、1 行が影響を受けました (0.01 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> stu から * を選択します。
+-----+-------+-------+------+------+------+
| id | sname | cname | math | eng | his |
+-----+-------+-------+------+------+------+
| 100 | サム | 0 | 90 | 88 | 80 |
| 101 | ジョン | 1 | 97 | 82 | 81 |
| 102 | メアリー | 2 | 87 | 89 | 92 |
| 103 | アダム | 2 | 87 | 89 | 92 |
+-----+-------+-------+------+------+------+
セット内の 4 行 (0.00 秒)

mysql> ロールバック;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

マイSQL>

どちらのステートメントもエラーを報告せず、更新ステートメントは特定の行と一致しますが (一致した行数: 1)、データは変更されません (変更: 0)。標準構文の更新ステートメントは、通常どおりデータを変更します。

これは、MySQL が and の使用を文法的に間違っているとは見なしていないことを示しています。つまり、MySQL はこのステートメントを別の方法で「解釈」します。最も簡単に考えられるのは、設定時に MySQL が英語の意味での「and」ではなく論理演算子として「and」を解釈するかどうかです。さらに、cname の値は元々 0 であり、これは bool データを処理するときのデータベースの動作と一致しています (False と True の代わりに 0 と 1 を使用します)。

検証は非常に簡単です。異なる cname 値でデータを更新するだけです。

mysql> stu から * を選択します。
+-----+-------+-------+------+------+------+
| id | sname | cname | math | eng | his |
+-----+-------+-------+------+------+------+
| 100 | サム | 0 | 90 | 88 | 83 |
| 101 | ジョン | 1 | 97 | 82 | 81 |
| 102 | メアリー | 2 | 87 | 89 | 92 |
| 103 | アダム | 2 | 87 | 89 | 92 |
+-----+-------+-------+------+------+------+
セット内の 4 行 (0.00 秒)

mysql> begin;update test.stu を設定し、cname = '0'、math = 90、his = 80、id = 101 を設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

mysql> stu から * を選択します。
+-----+-------+-------+------+------+------+
| id | sname | cname | math | eng | his |
+-----+-------+-------+------+------+------+
| 100 | サム | 0 | 90 | 88 | 83 |
| 101 | ジョン | 0 | 97 | 82 | 81 |
| 102 | メアリー | 2 | 87 | 89 | 92 |
| 103 | アダム | 2 | 87 | 89 | 92 |
+-----+-------+-------+------+------+------+
セット内の 4 行 (0.00 秒)

mysql> ロールバック;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

結果から、MySQL が cname の値を 0 に変更していることがわかります。これは、それが実際に論理演算子として扱われていることを意味します。このステートメントを注意深く分析すると、MySQL が次のように処理していることがわかります。

cname = ('0'、math = 90、his = 80) を設定します。

math と his の値は、where 条件によってフィルタリングされた行によって決定されます。上記のテストシナリオでは、次の論理的判断が行われます。

'0' と 97 = 90 と 81 = 80

PS: 文字データ 0 であっても False として扱われることに注意してください。

解決

現在、sql_mode やその他のパラメータを使用して「and」を使用したこのタイプの更新ステートメントを防ぐことはできないため、このタイプの問題は比較的目立たない状態になっています。開発中にこの問題を回避するには、カプセル化されたフレームワークを使用するか、コードまたは SQL レビューを強化することをお勧めします。

PS: Tencent Cloud Database MySQL にも同様の問題が発生するため、注意してください。

上記は、MySQL アップデートセットとの違いの詳細内容です。MySQL アップデートセットとの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

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