MySQLテナントの厳密モードにおけるデータ型変換動作

MySQLテナントでsql_modeパラメータにより厳密モード（STRICT_TRANS_TABLESまたはSTRICT_ALL_TABLESを設定することで有効化）が有効になると、DMLステートメント（INSERT、UPDATE、REPLACE、DELETEなど）およびSELECTステートメントを実行する際にデータ型変換が失敗した場合、システムは異なる処理方法を採用します。

主な違い

厳密モードがONの場合の動作

DMLステートメント

sql_mode に厳密モードが含まれている場合、DMLステートメントはデータ型変換に失敗すると即座に実行を終了します。

動作特性：

• ステートメントの実行に失敗し、明確なエラーメッセージを返します
• ステートメント全体で行われた変更はロールバックされます
• データは部分的に更新されず、またターゲット列の型に適合しないデータが挿入されることもありません

例：

INSERT INTO t (int_col) VALUES ('abc');
-- 文字列 'abc' は整数に変換できないため、失敗エラーが報告されます

UPDATE t SET date_col = '2024-02-30';
-- '2024-02-30' は無効な日付（2月に30日はないため）、失敗エラーが報告されます

INSERT INTO t (varchar10_col) VALUES ('This string is way too long');
-- 文字列の長さがVARCHAR(10) の定義を超えるため、失敗エラーが報告されます

原理： DML操作は永続化データを直接変更します。厳密モードはデータの正確性と整合性を保証します。誤った型変換を許可すると、データ損傷や業務ロジックの違反を引き起こす可能性があるため、OceanBaseデータベースは危険な変換が発生した場合には、直ちに失敗してロールバックすることを選択しています。

SELECTステートメント

sql_mode に厳密モードが含まれている場合、SELECT ステートメントはデータ型変換に失敗すると、DMLよりも寛容な処理を行います。

動作特性：

• ステートメントの実行は、型変換の失敗によって全体が中断されることはありません
• 変換に失敗した値は NULL またはデフォルト値と見なされます
• 警告メッセージが生成され、SHOW WARNINGS; で確認できます
• クエリは実行を続け、結果セットを返します

例：

SELECT * FROM t WHERE int_col = 'abc';
-- 'abc' は整数に変換できないため、警告が発生し、0またはNULLと見なされる可能性があります

SELECT DATE_ADD('2024-02-30', INTERVAL 1 DAY);
-- 無効な日付で警告が発生し、関数はNULLを返します

SELECT CAST('abc' AS UNSIGNED);
-- 文字列を符号なし整数に変換しようとして警告が発生し、0が返されます

原理： SELECT はクエリ操作であり、永続化データを変更しません。その主な目的は、ユーザーが確認できるように結果をできるだけ返すことです。厳密モードは SELECT において他の側面（例えば ONLY_FULL_GROUP_BY）に重点を置いており、型変換の失敗に対しては柔軟性と一部の結果の可用性を重視します。

厳密モードがOFFの場合の動作

DMLステートメント

非厳密モードでは、DMLステートメントは型変換に失敗しても実行を続けます。

動作特性：

• ステートメントの実行に成功し、警告メッセージが出力されます
• 変換に失敗したデータは、対応する型の0値で置き換えられます
• データはテーブルに書き込まれます

例：

CREATE TABLE test(a INT, b INT);
INSERT INTO test VALUES('abc', 'abc');
SELECT * FROM test;

実行結果：

+------+------+
| a    | b    |
+------+------+
|    0 |    0 |
+------+------+
1 row in set (0.039 sec)

SELECTステートメント

非厳密モードの SELECT ステートメントの動作は、厳密モードがONの場合と似ており、警告が発生しても実行を続けます。

重要な注意事項

STRICT_TRANS_TABLES vs STRICT_ALL_TABLES

MySQLでは、これら2つのモードはDML動作において型変換失敗時の処理が一致しています（どちらもエラーを返します）。主な違いは、非トランザクションテーブル（例えばMyISAM）への対応にあります。OceanBaseの基盤となるトランザクションエンジンは、異なるsql_mode下でも差異がないため、OceanBaseのMySQLテナントにおいては、STRICT_TRANS_TABLESとSTRICT_ALL_TABLESは型変換失敗時の挙動において通常差異がありません。

暗黙的変換ルール

変換失敗がなくても、暗黙的変換は精度損失や意味の変化を引き起こす可能性があります。厳密モードはすべての暗黙的変換を阻止するわけではなく、変換不可能であるか、またはデータが完全に失われる/無効になるようなケース（例えば文字列から数値への変換失敗、無効な日付、超長文字列の挿入など）を主に阻止します。

明示的変換

CAST()関数を使用して明示的変換を行う場合、変換に失敗すると、どのステートメントタイプやどのsql_modeにおいてもNULLを返し、警告が生成されます。これはSQL標準の動作です。

その他のモード

厳密モードは通常、ERROR_FOR_DIVISION_BY_ZEROなどの他のモードと一緒に設定されます。これらのモードは、ゼロ除算や無効な日付など特定のエラーの処理に影響を与えます。

故障診断

• エラー（ERROR）vs 警告（WARNING）： クライアントがERRORを返す場合、DMLステートメントは完全に失敗したことを意味します。結果セットを返しながら警告が表示される場合は、SELECTステートメント内で変換問題が発生したことを意味します。

• sql_modeの確認： SELECT @@sql_mode;を使用して、厳密モードが有効になっているかどうかを確認します。

• 実行計画とログの確認： 複雑な状況においては、OceanBaseの実行計画出力（EXPLAIN）がより詳細な手がかりを提供します。