第18回 MySQL5.
ということで、
SQLモードの使い方
ここでは、
現在のSQLモードを確認する
第18回でも軽く紹介しましたが、
$ 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 | +-------------------------------------------------------------------------------------------------------------------------------------------+
また、
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.を設定する方法と、--sql-modeオプションを指定する方法と、
my.を利用する際には、
接続しているクライアントで設定をしたい場合には、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ステートメントを利用して設定した場合の注意点としては、my.の有効な場所に記述しておきましょう。
MySQL5.7のデフォルトで設定されているSQLモードについて
MySQL5.ONLY_, STRICT_, NO_, NO_, ERROR_, NO_, NO_7個のSQLモードがデフォルトで設定されました。これらのSQLモードに関しては、
NO_AUTO_VALUE_ON_ZERO
このモードは、AUTO_なカラムを持っているテーブルに対して影響がある場合があります。
通常AUTO_なカラムにNULLや0を挿入すると、AUTO_なカラムに0を入れるというのもどうかと思いますが、
ただし、
このモードの挙動を確認するために、AUTO_な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_を有効にしてみます。
mysql> SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO'; Query OK, 0 rows affected, 1 warning (0.00 sec)
そして、
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)
先ほどと挿入した結果が変わり、
NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS
これらのSQLモードは、SHOW CREATE TABLEで表示させないようにするSQLモードです。これらの設定は、mysqldumpでスキーマなどをバックアップしたときにも影響があるので注意しましょう。
使い所としては、
先ほど作成したusersテーブルで、NO_を例に取り説明します。
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_を有効にした状態で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モードについて紹介してみました。まだまだ紹介できていないオプションがたくさんあるので、