MySQL 数値型オーバーフローの処理方法

さて、質問させてください。MySQL で列を int(0) に設定すると何が起こりますか?

この問題を実証するために、まずテーブルを作成します

`na` が存在する場合はテーブルを削除します。
テーブル `na` を作成します (
n1 INT(0) NOT NULL デフォルト '0',
n2 INT(11) NOT NULL デフォルト '0'
);

次に、次のステートメントを使用して、naテーブルにデータを挿入します。

mysql> `na` に値(520,520),(5201314,5201314);を挿入します。
クエリは正常、2 行が影響を受けました (0.02 秒)
記録: 2 重複: 0 警告: 0

最後に、読み上げてみましょう

mysql> SELECT * FROM na;
+---------+---------+
| n1 | n2 |
+---------+---------+
| 520 | 520 |
| 5201314 | 5201314 |
+---------+---------+
セット内の 2 行 (0.00 秒)

そうですね、何も起こらないような気がします、問題がなければそうなのですが、何か問題があったら怖いです…笑

この章では、整数オーバーフローの問題について説明します。

MySQL 数値型オーバーフロー処理

MySQL が数値列に列のデータ型で許可されている範囲外の値を格納する場合、結果はその時点で有効な SQL モードによって異なります。

厳密な SQL モードが有効になっている場合、MySQL は SQL 標準に従って範囲外の値をエラーで拒否し、挿入は失敗します。
制限モードが有効になっていない場合、MySQL は値を列のデータ型範囲の上限と下限にクリップして保存します。
- 範囲外の値が整数列に割り当てられると、MySQL は列データ型の範囲の対応するエンドポイントを表す値を格納します。
- 浮動小数点または固定小数点の列に、指定された（またはデフォルトの）精度とスケールによって暗示される範囲を超える値が割り当てられると、MySQL は範囲の対応するエンドポイントを表す値を格納します。

これは簡単に理解できるはずですよね？

テーブルt1の構造が次のようになっていると仮定して例を見てみましょう。

テーブルt1を作成します（
i1 TINYINT、
i2 TINYINT 符号なし
);

厳密な SQL モードが有効になっている場合、範囲を超えるとエラーが発生します。

mysql> SET sql_mode = 'TRADITIONAL'; -- 最初に厳密モードを設定します。 mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
エラー 1264 (22003): 行 1 の列 'i1' の値が範囲外です
mysql> t1 から * を選択;
空のセット (0.00 秒)

厳密モードが無効になっている場合、値を挿入することはできますが、クリップされ、警告が発生します。

mysql> SET sql_mode = ''; -- すべてのモードを無効にします。 mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);
mysql> 警告を表示します。
+---------+------+----------------------------------------------------------+
| レベル | コード | メッセージ |
+---------+------+----------------------------------------------------------+
| 警告 | 1264 | 行 1 の列 'i1' の値が範囲外です |
| 警告 | 1264 | 行 1 の列 'i2' の値が範囲外です |
+---------+------+----------------------------------------------------------+
mysql> t1 から * を選択;
+------+------+
| i1 | i2 |
+------+------+
| 127 | 255 |
+------+------+

厳密な SQL モードが有効になっていない場合、ALTER TABLE、LOAD DATA INFILE、UPDATE、複数行の INSERT などのステートメントでは、プルーニングにより列割り当ての変換が発生し、警告が発生します。

厳密モードが有効になっている場合、これらのステートメントは単に失敗し、テーブルがトランザクションであるかどうかやその他の要因に応じて、一部またはすべての値が挿入または変更されません。

数値式の評価中にオーバーフローが発生すると、エラーが発生します。たとえば、最大の符号付き BIGINT 値は 9223372036854775807 であるため、次の式はエラーになります。

mysql> 9223372036854775807 + 1 を選択します。
エラー 1690 (22003): BIGINT 値が '(9223372036854775807 + 1)' の範囲外です

この場合、操作を成功させるには、値を符号なしに変換する必要があります。

mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+------------------------------------------+
| CAST(9223372036854775807 を UNSIGNED として) + 1 |
+------------------------------------------+
|9223372036854775808 |
+------------------------------------------+

一方、オーバーフローが発生するかどうかはオペランドの範囲によって異なります。したがって、前の式を処理する別の方法は、DECIMAL 値の範囲が整数の範囲よりも大きいため、正確な値の演算を使用することです。

mysql> 9223372036854775807.0 + 1 を選択します。
+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
|9223372036854775808.0 |
+---------------------------+

整数値間の減算では、いずれかの型が UNSIGNED の場合、デフォルトで符号なしの結果が生成されます。負の場合、エラーが発生します

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

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
エラー 1690 (22003): BIGINT UNSIGNED 値が '(cast(0 as unsigned) - 1)' の範囲外です

この場合、NO_UNSIGNED_SUBTRACTION SQL モードが有効になっていると、結果は負になります。

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 を UNSIGNED として) - 1 |
+-------------------------+
| -1 |
+-------------------------+

このような操作の結果が UNSIGNED 整数列の更新に使用される場合、結果は列タイプの最大値にクリップされるか、NO_UNSIGNED_SUBTRACTION が有効な場合は 0 にクリップされます。ただし、厳密な SQL モードが有効になっている場合はエラーが発生し、列は変更されません。

追記

すべてはルーチン、ルーチンです...基本的に SQL モードに関連しています...

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

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