SQLモードのSTRICT_TRANS_TABLESについて

SQLモードにデフォルトで設定されているSTRICT_TRANS_TABLESは、テーブルにINSERT文やUPDATE文でデータの登録、更新を行う際に指定した値が、データ型に対して不正なものになっている場合に、データの登録、更新は行わず、エラーとするものになります。

以下に例を示します。

以下のようなテーブルがあるとします。

mysql>, CREATE TABLE tbl01 (id CHAR(4) NOT NULL, num01 INT, txt01 VARCHAR(8), date01 DATETIME, PRIMARY KEY (id)) ENGINE InnoDB,

SQLモードにSTRICT_TRANS_TABLESが設定されている場合は、以下のようなデータ型に対して不正な値が指定されているINSERT文を実行するとエラーになります。

mysql>, INSERT INTO tbl01 (id, num01, txt01, date01) VALUES ('12345', 'a', '123456789', 'a'), ERROR 1406 (22001): Data too long for column 'id' at row 1

以下のようなSQLを実行して、デフォルトのSQLモードからSTRICT_TRANS_TABLESを外した状態にしてみます。

mysql>, SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION',

SQLモードにSTRICT_TRANS_TABLESが設定されていない場合は、先程のINSERT文を実行してもエラーにはならず、警告を発生してデータが登録されます。

mysql>, INSERT INTO tbl01 (id, num01, txt01, date01) VALUES ('12345', 'a', '123456789', 'a'), Query OK, 1 row affected, 4 warnings (0.08 sec)

発生した警告は、以下のようなSQLで確認できます。

mysql>, SHOW WARNINGS, +---------+------+----------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------+ | Warning | 1265 | Data truncated for column 'id' at row 1 | | Warning | 1366 | Incorrect integer value: 'a' for column 'num01' at row 1 | | Warning | 1265 | Data truncated for column 'txt01' at row 1 | | Warning | 1265 | Data truncated for column 'date01' at row 1 | +---------+------+----------------------------------------------------------+

登録されるデータは、以下のようになります。

mysql>, SELECT * FROM tbl01, +------+-------+----------+---------------------+ | id | num01 | txt01 | date01 | +------+-------+----------+---------------------+ | 1234 | 0 | 12345678 | 0000-00-00 00:00:00 | +------+-------+----------+---------------------+

• 文字列型に対しては、指定した値がサイズの長さに切り捨てられます。

• 数値型、日付型に対しては、0になります。

SQLモードのNO_ENGINE_SUBSTITUTIONについて

SQLモードにデフォルトで設定されているNO_ENGINE_SUBSTITUTIONは、CREATE TABLE文でテーブルの作成を行う際に指定したストレージエンジンが、無効なものになっている場合に、テーブルの作成は行わず、エラーとするものになります。

以下に例を示します。

SQLモードにNO_ENGINE_SUBSTITUTIONが設定されている場合は、以下のようなストレージエンジンに無効なものが指定されているCREATE TABLE文を実行するとエラーになります。

mysql>, CREATE TABLE tbl01 (id CHAR(4) NOT NULL, num01 INT, txt01 VARCHAR(8), date01 DATETIME, PRIMARY KEY (id)) ENGINE xxxxx, ERROR 1286 (42000): Unknown storage engine 'xxxxx'

以下のようなSQLを実行して、デフォルトのSQLモードからNO_ENGINE_SUBSTITUTIONを外した状態にしてみます。

mysql>, SET SESSION sql_mode = 'STRICT_TRANS_TABLES',

SQLモードにNO_ENGINE_SUBSTITUTIONが設定されていない場合は、先程のCREATE TABLE文を実行してもエラーにはならず、警告を発生してテーブルが作成されます。

mysql>, CREATE TABLE tbl01 (id CHAR(4) NOT NULL, num01 INT, txt01 VARCHAR(8), date01 DATETIME, PRIMARY KEY (id)) ENGINE xxxxx, Query OK, 0 rows affected, 2 warnings (0.71 sec)

発生した警告は、以下のようなSQLで確認できます。

mysql>, SHOW WARNINGS, +---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1286 | Unknown storage engine 'xxxxx' | | Warning | 1266 | Using storage engine InnoDB for table 'tbl01' | +---------+------+-----------------------------------------------+

作成されるテーブルは、以下のようになります。

mysql>, SHOW CREATE TABLE tbl01, +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tbl01 | CREATE TABLE `tbl01` ( `id` char(4) COLLATE utf8_bin NOT NULL, `num01` int(11) DEFAULT NULL, `txt01` varchar(8) COLLATE utf8_bin DEFAULT NULL, `date01` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

• ストレージエンジンには、デフォルトのInnoDBが使用されています。

SQLのテクニックに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

SQLのパフォーマンスに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

DB設計、SQLに関するおすすめの書籍

当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。