mysqlの整数型カラムにデータを入れるときの補正

あまり役に立つ情報ではない気もしますが、mysqlの整数カラムについてタイトルの内容が気になったため、調べて＆試してみました。

気になったのは、

1. 範囲外の数値を入れようとしたらどうなるか。
2. 小数点以下の値を持つ数値を入れようとしたらどうなるか。
   の2点。

結論から言えば、以下のような挙動でした。

1. sql_modeに何を指定しているかによって変わる。
   　(1)「厳密なSQLモード」で動作させている場合
   　　エラー扱いとなる。
   　(2)「厳密なSQLモード」で動作させていない場合
   　　・データ型の最大値超過なら、データ型の最大値を入れる。
   　　・データ型の最小値未満なら、データ型の最小値を入れる。
2. 小数点以下第1位を四捨五入した後の値を入れようとする。
   　四捨五入した後の値が1.のような値になった場合は、さらに1.の判定が入る。

実験に使ったのは下のようなテーブルです。

mysql> desc test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| col1  | tinyint(3) unsigned | YES  |     | NULL    |       |
| col2  | tinyint(4)          | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+

「厳密なSQLモード」ではない場合

mysql> select @@SESSION.sql_mode;
+------------------------+
| @@SESSION.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+

まずは下限値未満。警告が出ますが、テーブル上にレコードは作成されます。
以下だと、作成されるレコードの中身は(0,-128)。

mysql> insert into test values( -1, -129 );
Query OK, 1 row affected, 2 warnings (0.10 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'col1' at row 1 |
| Warning | 1264 | Out of range value for column 'col2' at row 1 |
+---------+------+-----------------------------------------------+
2 rows in set (0.00 sec)

次は上限値超過。下限値未満の場合と同様、警告が出るもののテーブル上にレコードは作成されます。
以下だと、作成されるレコードの中身は(255,127)。

mysql> insert into test values( 256, 128 );
Query OK, 1 row affected, 2 warnings (0.13 sec)

小数点以下は四捨五入されます。警告も何も出ません。

mysql> insert into test values( 1.49, -1.49 );
Query OK, 1 row affected (0.38 sec)

mysql> insert into test values( 1.50, -1.50 );
Query OK, 1 row affected (0.13 sec)

小数点以下を処理してからデータ型に対する評価がされるようで、範囲外っぽい値を入れようとしても特に問題視されないことも（笑）。

mysql> insert into test values( 255.4, 127.4 );
Query OK, 1 row affected (0.36 sec)

小数点以下を処理した結果がデータ型の範囲外になれば、もちろん、それなりの扱いを受けます。

mysql> insert into test values( 255.5, 127.5 );
Query OK, 1 row affected, 2 warnings (0.11 sec)

これまでのinsertによる結果はこちら。

mysql> select * from test;
+------+------+
| col1 | col2 |
+------+------+
|    0 | -128 |
|  255 |  127 |
|    1 |   -1 |
|    2 |   -2 |
|  255 |  127 |
|  255 |  127 |
+------+------+

「厳密なSQLモード」の場合

「厳密なSQLモード」ではない場合には警告で済んだクエリが、悉くエラーとなります。

mysql> set session sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
| STRICT_ALL_TABLES  |
+--------------------+
1 row in set (0.00 sec)

mysql> insert into test values( -1, -129 );
ERROR 1264 (22003): Out of range value for column 'col1' at row 1

mysql> insert into test values( 256, 128 );
ERROR 1264 (22003): Out of range value for column 'col1' at row 1

mysql> insert into test values( 255.5, 127.5 );
ERROR 1264 (22003): Out of range value for column 'col1' at row 1

参考情報

・厳密なSQLモードに関する説明
　https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html