MySQL道普請便り

第60回SQLモードについて[その1]

第18回 MySQL5.7のデフォルトのSQLモードを確認してみるで、5.6までと5.7までのSQLモードのデフォルトがどのように変更されたのかについて説明させていただきました。しかし、第18回で紹介したSQLモード以外にも、設定することができるSQLモードがMySQLには沢山あります。

ということで、何回かに分けてMySQLのSQLモードについて紹介していきます。

SQLモードの使い方

ここでは、SQLモードの確認方法と設定方法について簡単に説明していきます。

現在のSQLモードを確認する

第18回でも軽く紹介しましたが、以下のコマンドでglobalの設定が確認できます。

$ mysql -uroot
mysql > SELECT @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

また、接続しているセッションでの変更を行った際には、以下のようにsessionのsql_modeを確認しましょう。

mysql> SELECT @@session.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

今回はまだ何も変更を加えていないため、同じものが出力されていることがわかります。

SQLモードを設定する

SQLモードの設定方法ですが、その他の設定と同様にmy.cnfを設定する方法と、サーバ起動時にコマンドで--sql-modeオプションを指定する方法と、繋いでいるクライアントで設定する方法があります。

my.cnfを利用する際には、第31回 MySQLのオプションファイル my.cnfの豆知識[その1]で説明されているとおり、後から読み込まれたファイルの設定で上書きされてしまいます。なので、もしSQLモードが思った通りに設定できない場合は、後から読み込みが行われるファイルで上書きがされていないか確認してみてください。

接続しているクライアントで設定をしたい場合には、以下のようにSETステートメントで設定をしましょう。接続しているセッションだけで設定を変更したい場合はSET SESSIONを使って、全体で変更したい場合はSET GLOBALを使いましょう。

mysql> SET SESSION sql_mode='';
mysql> SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)


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

mysql> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

SET GLOBALステートメントを利用して設定した場合の注意点としては、5.7とそれ以前では設定した値を保存する方法がないため、サーバを再起動した時に設定が失われてしまうことに注意をしましょう。もしずっと適用したい場合には、忘れずにmy.cnfの有効な場所に記述しておきましょう。

MySQL5.7のデフォルトで設定されているSQLモードについて

MySQL5.7からONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION7個のSQLモードがデフォルトで設定されました。これらのSQLモードに関しては、第18回で説明を行いましたので改めて説明はしません。

NO_AUTO_VALUE_ON_ZERO

このモードは、AUTO_INCREMENTなカラムを持っているテーブルに対して影響がある場合があります。

通常AUTO_INCREMENTなカラムにNULLや0を挿入すると、自動的に次のシーケンス番号を生成して置き換えます。しかし、このモードが有効になっていると、0はシーケンス番号に置き換えられることはありません。そもそもAUTO_INCREMENTなカラムに0を入れるというのもどうかと思いますが、運用上必要になることもあるかと思います。

ただし、このモードを使った時にリストアには注意が必要です。うっかり移行先のサーバでこのモードを設定し忘れると、0が挿入されたデータに次のシーケンス番号が振られて、データに差異が生まれてしまうので注意をしましょう。

このモードの挙動を確認するために、AUTO_INCREMENTなidを持った簡単なusersテーブルを作成してみます。

CREATE TABLE users(
        id INT NOT NULL AUTO_INCREMENT,
        name TEXT NOT NULL,
        PRIMARY KEY (id));

作成したテーブルにNULLと0を入れた時にどうなるか確認してみましょう。

mysql> INSERT INTO users (id, name) VALUES  (NULL, 'suzuki'), (0, 'kimura');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * from users;
+----+--------+
| id | name   |
+----+--------+
|  1 | suzuki |
|  2 | kimura |
+----+--------+
2 rows in set (0.01 sec)

NULLと0を指定した場合にシーケンシャルな番号が割り振られたのがわかると思います。ここで、NO_AUTO_VALUE_ON_ZEROを有効にしてみます。

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

そして、同様に0とNULLを挿入して結果を確認してみましょう。

mysql> INSERT INTO users (id, name) VALUES  (NULL, 'sato'), (0, 'tanaka');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * from users;
+----+--------+
| id | name   |
+----+--------+
|  0 | tanaka |
|  1 | suzuki |
|  2 | kimura |
|  3 | sato   |
+----+--------+
4 rows in set (0.00 sec)

先ほどと挿入した結果が変わり、idがシーケンシャルな番号ではなく、指定をした0が入っていることがわかると思います。

NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS

これらのSQLモードは、MySQLの固有のカラムオプションやインデックスオプション、テーブルのオプションなどをSHOW CREATE TABLEで表示させないようにするSQLモードです。これらの設定は、mysqldumpでスキーマなどをバックアップしたときにも影響があるので注意しましょう。

使い所としては、たとえばMySQLから他のRDBMSにデータを移し替える場合に、MySQL固有のEngineの設定などが不要な場合に利用されます。

先ほど作成したusersテーブルで、NO_TABLE_OPTIONSを例に取り説明します。

mysql> SHOW CREATE TABLE users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

この時点では、ENGINE=InnoDB~~~といったMySQL固有の情報が出力されていることがわかります。続いて、NO_TABLE_OPTIONSを有効にした状態でSHOW CREATE TABLEを実行してみましょう。

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

mysql> SHOW CREATE TABLE users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
)
1 row in set (0.00 sec)

以上のように、ストレージエンジンの設定などが消えていることがわかります。

まとめ

今回はMySQLのSQLモードについて紹介してみました。まだまだ紹介できていないオプションがたくさんあるので、これからも紹介していく予定です。

おすすめ記事

記事・ニュース一覧