【MySQL】Field ‘カラム名’ doesn’t have default value というエラーが出てきたので調べた。ついでにSQLモードについてもまとめてみた。

MySQLにデータをINSERTしようとした時、

Field 'カラム名' doesn't have default value

というエラーが。これはSQLモードに”STRICT_TRANS_TABLES”が追加されているため、NOT NULLなフィールドにNULL(値の指定無)なINSERT・UPDATEを実行しようとするとこうなります。

mysql> SHOW GLOBAL VARIABLES LIKE 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

設定の確認は上記のとおり、やっぱりSTRICT_TRANS_TABLESが設定されています。
これを解除するには以下のようにしてやります。

mysql> SET GLOBAL sql_mode=NO_ENGINE_SUBSTITUTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)

もしくは「my.cnf」の設定を

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
 ↓
sql_mode=NO_ENGINE_SUBSTITUTION

のように変更して再起動します。MySQL5.6からsql_modeの設定が「STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION」になったらしいので気付かんかったとです。

っていうか、sql_modeってなんなの?どんなことができるの?ってことで調べてみました。

ALLOW_INVALID_DATES

DATE型とDATETIME型のフィールドに対して、月は1〜12、日は1〜31のみのチェックを行うようにします。
例えば通常は’2014-02-31’のような日付は無効として’0000-00-00’に変換されますが、ALLOW_INVALID_DATESを有効にすると’2014-02-31’も許容されます。
TIMESTAMP型は常に有効な日付を扱うためALLOW_INVALID_DATESの対象にはなりません。

mysql> CREATE TABLE test (field DATE);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES('2014-02-01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES('2014-14-01');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test VALUES('2014-02-31');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM test;
+------------+
| field      |
+------------+
| 2014-02-01 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)

mysql> SET SESSION sql_mode=ALLOW_INVALID_DATES;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES('2014-02-01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES('2014-14-01');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test VALUES('2014-02-31');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+------------+
| field      |
+------------+
| 2014-02-01 |
| 0000-00-00 |
| 0000-00-00 |
| 2014-02-01 |
| 0000-00-00 |
| 2014-02-31 |
+------------+
6 rows in set (0.00 sec)

ANSI_QUOTES

「”(ダブルクォーテーション)」が文字列引用符ではなく識別子引用符として扱われる様になります。

mysql> SELECT "abc";
+-----+
| abc |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode=ANSI_QUOTES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "abc";
ERROR 1054 (42S22): Unknown column 'abc' in 'field list'

ERROR_FOR_DIVISION_BY_ZERO

INSERT/UPDATEで0除算をすると警告を出すようになります。

mysql> drop tables test;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test (field INT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES(1/0);
Query OK, 1 row affected (0.02 sec)

mysql> SET SESSION sql_mode=ERROR_FOR_DIVISION_BY_ZERO;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES(1/0);
Query OK, 1 row affected, 1 warning (0.00 sec)

これをエラーにするにはSTRICT_ALL_TABLES・STRICT_TRANS_TABLESのいずれかと併用する必要があります。

HIGH_NOT_PRECEDENCE

NOT演算子の優先順位を古いMySQLのバージョン(4以前)に合わせる。

mysql> SELECT NOT 1 = 2;
+-----------+
| NOT 1 = 2 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode=HIGH_NOT_PRECEDENCE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOT 1 = 2;
+-----------+
| NOT 1 = 2 |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

これは新しいMySQLのNOT演算子の優先順位が NOT(1 = 2) であるのに対し、古いMySQLは NOT(1) = 2 となっているためです。
HIGH_NOT_PRECEDENCEを有効にすると NOT(1) = 2 として解釈されます。

IGNORE_SPACE

関数名と「(」の間にスペースをいくつでも配置することができるようになります。これによりすべての関数名が予約後として扱われるようになりますので、注意が必要です。

mysql> SELECT NOW ();
ERROR 1630 (42000): FUNCTION test.NOW does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
mysql> SET SESSION sql_mode=IGNORE_SPACE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOW ();
+---------------------+
| NOW ()              |
+---------------------+
| 2014-03-17 13:51:39 |
+---------------------+
1 row in set (0.00 sec)

NO_AUTO_CREATE_USER

パスワードを指定していないと、GRANT文が自動的に新規ユーザーを作成しないようにします。

mysql> SELECT user,host FROM mysql.user;
+------+-----------------------+
| user | host                  |
+------+-----------------------+
| root | 127.0.0.1             |
| root | localhost             |
| root | localhost.localdomain |
+------+-----------------------+
3 rows in set (0.00 sec)

mysql> CREATE USER "test1"@"localhost" IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT ON *.* TO "test2"@"localhost" IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT ON *.* TO "test3"@"localhost";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user,host FROM mysql.user;
+-------+-----------------------+
| user  | host                  |
+-------+-----------------------+
| root  | 127.0.0.1             |
| root  | localhost             |
| test1 | localhost             |
| test2 | localhost             |
| test3 | localhost             |
| root  | localhost.localdomain |
+-------+-----------------------+
6 rows in set (0.00 sec)

mysql> SET SESSION sql_mode=NO_AUTO_CREATE_USER;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER "test4"@"localhost" IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT ON *.* TO "test5"@"localhost" IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT ON *.* TO "test6"@"localhost";
ERROR 1133 (42000): Can't find any matching row in the user table

mysql> SELECT user,host FROM mysql.user;
+-------+-----------------------+
| user  | host                  |
+-------+-----------------------+
| root  | 127.0.0.1             |
| root  | localhost             |
| test1 | localhost             |
| test2 | localhost             |
| test3 | localhost             |
| test4 | localhost             |
| test5 | localhost             |
| root  | localhost.localdomain |
+-------+-----------------------+
8 rows in set (0.00 sec)

NO_AUTO_VALUE_ON_ZERO

auto_increment属性のカラムは通常、0もしくはNULLが設定が設定された時が自動採番のトリガーとなりますが、NO_AUTO_VALUE_ON_ZEROが有効な場合はNULLのみが自動採番のトリガーとなります。

mysql> CREATE TABLE test (field INT AUTO_INCREMENT PRIMARY KEY) ;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test value(0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test value(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+-------+
| field |
+-------+
|     1 |
|     2 |
+-------+
2 rows in set (0.00 sec)

mysql> SET SESSION sql_mode=NO_AUTO_VALUE_ON_ZERO;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test value(0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test value(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+-------+
| field |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)

NO_BACKSLASH_ESCAPES

NO_BACKSLASH_ESCAPESを有効にすると文字列内の’¥(バックスラッシュ)’がエスケープ文字としてではなく、通常の文字として扱われます。

mysql> SELECT '\\';
+---+
| \ |
+---+
| \ |
+---+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode=NO_BACKSLASH_ESCAPES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '\\';
+----+
| \\ |
+----+
| \\ |
+----+
1 row in set (0.00 sec)

NO_DIR_IN_CREATE

MyISAMのテーブル作成時のINDEX DIRECTORY(MYIファイルの保存場所指定)とDATA DIRECTORY(MYDファイルの保存場所指定)の命令をすべて無視します。
これはレプリケーション時にスレーブサーバーに対象のディレクトリがない場合や、ダンプしたデータをディレクトリ構造の違うサーバーにインポートするときに役に立ちます。

NO_ENGINE_SUBSTITUTION

NO_ENGINE_SUBSTITUTIONが無効の場合は、CREATE TABLE実行時にストレージエンジンが無効なものが指定されていれば、デフォルトのストレージエンジンに自動置換されます。

mysql> CREATE TABLE test1 (field INT) ENGINE test;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1286 | Unknown table engine 'test'                   |
| Warning | 1266 | Using storage engine MyISAM for table 'test1' |
+---------+------+-----------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: test1
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 1970324836974591
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-03-17 19:28:22
    Update_time: 2014-03-17 19:28:22
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

上記は存在しないストレージエンジン「test」を指定したところ、デフォルトのストレージエンジン「MyISAM」に変更されてしまった例になります。

NO_ENGINE_SUBSTITUTIONを有効にすることで、デフォルトストレージエンジンの自動置換を防ぎ、エラーを出力するようになります。

mysql> SET SESSION sql_mode=NO_ENGINE_SUBSTITUTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test2 (field INT) ENGINE test;
ERROR 1286 (42000): Unknown table engine 'test'

NO_FIELD_OPTIONS

MySQL独自のフィールドオプションをSHOW CREATE TABLEの結果に出力しないようにします。

mysql> CREATE TABLE test (field INT AUTO_INCREMENT PRIMARY KEY) ;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `field` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`field`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode=NO_FIELD_OPTIONS;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `field` int(11) NOT NULL,
  PRIMARY KEY (`field`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec) 

NO_KEY_OPTIONS

MySQL独自のインデックスオプションをSHOW CREATE TABLEの結果に出力しないようにします。

mysql> CREATE TABLE test (field INT, KEY idx (field) USING HASH);
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `field` int(11) DEFAULT NULL,
  KEY `idx` (`field`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode=NO_KEY_OPTIONS;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `field` int(11) DEFAULT NULL,
  KEY `idx` (`field`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

NO_TABLE_OPTIONS

MySQL独自のテーブルオプションをSHOW CREATE TABLEの結果に出力しないようにします。

mysql> CREATE TABLE test (field INT);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `field` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode=NO_TABLE_OPTIONS;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `field` int(11) DEFAULT NULL
)
1 row in set (0.00 sec)

NO_UNSIGNED_SUBTRACTION

整数の減算時にオペランドの値が符号無(UNSIGNED)でも、結果は符号付(SIGNED)として出力されるようになります。

mysql> SELECT CAST(1 AS UNSIGNED) - 2;
+-------------------------+
| CAST(1 AS UNSIGNED) - 2 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode=NO_UNSIGNED_SUBTRACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(1 AS UNSIGNED) - 2;
+-------------------------+
| CAST(1 AS UNSIGNED) - 2 |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)

NO_ZERO_DATE

‘0000-00-00’な日付の場合は警告を出すようにします。

mysql> CREATE TABLE test (field DATE);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES('2014-00-01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES('0000-00-00');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+------------+
| field      |
+------------+
| 0000-00-00 |
| 2014-00-01 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)

mysql> SET SESSION sql_mode=NO_ZERO_DATE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES(0);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test VALUES('2014-00-01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES('0000-00-00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM test;
+------------+
| field      |
+------------+
| 0000-00-00 |
| 2014-00-01 |
| 0000-00-00 |
| 0000-00-00 |
| 2014-00-01 |
| 0000-00-00 |
+------------+
6 rows in set (0.00 sec)

警告は表示されるようになりましたが、値自体は入ります。
警告をエラーにするにはSTRICT_ALL_TABLES等との併用が必要です。

NO_ZERO_IN_DATE

日時型のフィールドで年月に0があった場合に警告を出すようにします。

mysql> CREATE TABLE test (field DATE);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES('2014-00-01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES('2014-03-01');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+------------+
| field      |
+------------+
| 0000-00-00 |
| 2014-00-01 |
| 2014-03-01 |
+------------+
3 rows in set (0.00 sec)

mysql> SET SESSION sql_mode=NO_ZERO_IN_DATE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES(0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES('2014-00-01');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test VALUES('2014-03-01');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+------------+
| field      |
+------------+
| 0000-00-00 |
| 2014-00-01 |
| 2014-03-01 |
| 0000-00-00 |
| 0000-00-00 |
| 2014-03-01 |
+------------+
6 rows in set (0.00 sec)

月に0を指定したものは警告が表示され、’0000-00-00’に丸め込まれました。
こちらもエラーにする場合はSTRICT_ALL_TABLES等との併用が必要です。

ONLY_FULL_GROUP_BY

SELECT文でGROUP BYで集約されないカラムが指定されている時エラーになります。

mysql> CREATE TABLE test (name VARCHAR(10), age INT, post INT);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name, AVG(age) FROM test GROUP BY post;
Empty set (0.00 sec)

mysql> SET SESSION sql_mode=ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name, AVG(age) FROM test GROUP BY post;
ERROR 1055 (42000): 'test.test.name' isn't in GROUP BY
mysql> SELECT post, AVG(age) FROM test GROUP BY post;
Empty set (0.00 sec)

PIPES_AS_CONCAT

「||」 は、「OR」のシノニムではなく、文字列連結演算子になります。

mysql> SELECT 'aaa' || 'bbb';
+----------------+
| 'aaa' || 'bbb' |
+----------------+
|              0 |
+----------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SET SESSION sql_mode=PIPES_AS_CONCAT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'aaa' || 'bbb';
+----------------+
| 'aaa' || 'bbb' |
+----------------+
| aaabbb         |
+----------------+
1 row in set (0.00 sec)

REAL_AS_FLOAT

「REAL」は、「DOUBLE」のシノニムではなく、「FLOAT」のシノニムとなります。

mysql> CREATE TABLE test (field1 REAL);
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION sql_mode=REAL_AS_FLOAT;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE test ADD field2 REAL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE "test" (
  "field1" double DEFAULT NULL,
  "field2" float DEFAULT NULL
)
1 row in set (0.00 sec)

STRICT_ALL_TABLES

すべてのテーブルに対して、無効または不明な値をインサートしようとした時に警告ではなくエラーとなります。

mysql> CREATE TABLE test_innodb (field INT NOT NULL) ENGINE InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE test_myisam (field INT NOT NULL) ENGINE MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test_innodb VALUES('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test_myisam VALUES('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SET SESSION sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test_innodb VALUES('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'field' at row 1
mysql> INSERT INTO test_myisam VALUES('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'field' at row 1
mysql> INSERT INTO test_innodb VALUES(0),(1),('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'field' at row 3
mysql> INSERT INTO test_myisam VALUES(0),(1),('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'field' at row 3

STRICT_TRANS_TABLES

トランザクションのテーブルに対して、無効または不明な値をインサート仕様とした時に警告ではなくエラーとなります。非トランザクションテーブルの場合は値が1行ステートメントの場合、または複数行ステートメントの最初の行である場合にエラーとなります。

mysql> CREATE TABLE test_innodb (field INT NOT NULL) ENGINE InnoDB;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE test_myisam (field INT NOT NULL) ENGINE MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test_innodb VALUES('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO test_myisam VALUES('abc');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SET SESSION sql_mode=STRICT_TRANS_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test_innodb VALUES('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'field' at row 1
mysql> INSERT INTO test_myisam VALUES('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'field' at row 1
mysql> INSERT INTO test_innodb VALUES(0),(1),('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'field' at row 3
mysql> INSERT INTO test_myisam VALUES(0),(1),('abc');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

InnoDBの場合は複数行の中で一行でも無効な値があればエラーとなります。
MyISAMの場合は最初の行が有効な値でしたので、エラーとはなりませんでした。

エイリアス

sql_modeにはいくつかのエイリアスがあり、ひとつの設定を行うことで、複数の設定を一度に設定できます。

ANSI

mysql> SET SESSION sql_mode=ANSI;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------+
| Variable_name | Value                                                       |
+---------------+-------------------------------------------------------------+
| sql_mode      | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+---------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

DB2

mysql> SET SESSION sql_mode=DB2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                         |
+---------------+-----------------------------------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,DB2,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS |
+---------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MAXDB

mysql> SET SESSION sql_mode=MAXDB;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+---------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                               |
+---------------+---------------------------------------------------------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER |
+---------------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MSSQL

mysql> SET SESSION sql_mode=MSSQL;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                           |
+---------------+-------------------------------------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS |
+---------------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MYSQL323

mysql> SET SESSION sql_mode=MYSQL323;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| sql_mode      | MYSQL323,HIGH_NOT_PRECEDENCE |
+---------------+------------------------------+
1 row in set (0.00 sec)

MYSQL40

mysql> SET SESSION sql_mode=MYSQL40;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| sql_mode      | MYSQL40,HIGH_NOT_PRECEDENCE |
+---------------+-----------------------------+
1 row in set (0.00 sec)

ORACLE

mysql> SET SESSION sql_mode=ORACLE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                |
+---------------+----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER |
+---------------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

POSTGRESQL

mysql> SET SESSION sql_mode=POSTGRESQL;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                |
+---------------+------------------------------------------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,POSTGRESQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS |
+---------------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

TRADITIONAL

mysql> SET SESSION sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                         |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

まとめ

MySQL5.1のリファレンスマニュアルを参考にまとめてみました。もしかしたら解釈が間違っているところがあるかも。

とりあえずひと通り自分で動かしてみることで、MySQLのデフォルトの動作とか勉強になったのでよかった。

コメントを残す