MySQLにはsql_modeという重要な設定変数が存在します。この設定は多くのケースで見過ごされがちですが、データベースの挙動を大きく左右する要素です。デフォルト設定では非標準的なデータ操作が許可されてしまうため、本番環境では必ず適切な厳格モードへの設定が推奨されます。
現在適用されているsql_modeの値は以下のクエリで確認可能です。
SHOW VARIABLES LIKE 'sql_mode';
sql_modeの主な活用目的
sql_modeを適切に設定することで、以下のようなメリットが得られます。
- データ検証の厳格度を調整し、データの正確性を担保
- 標準SQLへの準拠度を高め、他データベースシステムへの移行コストを削減
- 異なるデータベース間でのデータ移行時の互換性を確保
主要なsql_mode値の解説
| モード名 | 説明 |
|---|---|
| ONLY_FULL_GROUP_BY | GROUP BY句に含まれない非集約列をSELECTで参照するとエラーを発生させる |
| STRICT_TRANS_TABLES | トランザクション対応テーブルで無効な値の挿入時、即座にエラーを発生させて処理を中断 |
| NO_ZERO_IN_DATE | 月や日が0の日付を無効とみなす（厳格モード時はエラー、非厳格モード時は警告） |
| NO_ZERO_DATE | '0000-00-00'形式の日付を無効とする |
| ERROR_FOR_DIVISION_BY_ZERO | ゼロ除算時に警告ではなくエラーを発生させる |
| NO_ENGINE_SUBSTITUTION | 指定したストレージエンジンが無効な場合、代替エンジンへの自動置換を行わずエラーを返す |
代表的な動作モード
MySQL 5.0以降では主に3つのモードがサポートされています。
ANSIモード（寛容モード）
データ型や長さの制約に違反する場合でも、データの調整や切り捨てを行い、警告を出力しつつ処理を継続します。
SET @@sql_mode = ANSI;
CREATE TABLE user_accounts (
username VARCHAR(3),
user_key VARCHAR(3)
);
INSERT INTO user_accounts VALUES ('developer', 'auth_token');
-- 警告は出るが、データは切り捨てられて保存される
STRICT_TRANS_TABLESモード（厳格モード）
データの妥当性を厳密に検証し、制約違反があれば即座にエラーを発生させます。不正なデータの挿入を完全に防止できます。
SET @@sql_mode = STRICT_TRANS_TABLES;
CREATE TABLE product_info (
item_name VARCHAR(3),
item_code VARCHAR(3)
);
INSERT INTO product_info VALUES ('smartphone', 'SKU123');
-- エラーが発生し、挿入は拒否される
TRADITIONALモード（伝統的厳格モード）
STRICT_TRANS_TABLESと同様の厳格な検証を行います。トランザクション使用時は、エラー発生時にロールバックが実行されます。ただし、非トランザクション型ストレージエンジン（MyISAM等）では、エラー発生前の変更が取り消されない点に注意が必要です。
設定の適用方法
sql_modeはグローバルスコープとセッションスコープで個別に設定可能です。
-- 現在の設定を確認
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
-- 設定の変更
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE';
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
永続的な設定を行う場合は、設定ファイル（my.cnfまたはmy.ini）に以下のように記述します。
[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
本番環境では、ONLY_FULL_GROUP_BYを必要に応じて無効化し、上記の厳格設定の組み合わせを使用することが推奨されます。