MySQLのsql_modeにあるORACLEとは

これはJPOUG Advent Calendar 2016の16日目の記事です。

最初に言っておきますが、この記事はなんも役に立ちません！

では始めます。

MySQLのsql_modeにはいろいろとsqlの動作の設定ができるんですが、
ここにORACLEというのが選択できます。
たぶんOracle databaseっぽく動作するんでしょうけど、設定したらどうなるか確認しました。
MySQLのバージョンは5.6.34です。

設定

mysql > select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

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

sql_modeのデフォルトはNO_ENGINE_SUBSTITUTIONで
set globalでORACLEを設定します。で、sql_modeを確認すると、

mysql > select @@sql_mode;
+----------------------------------------------------------------------------------------------------------------------+
| @@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)

いっぱい表示されました。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード

マニュアルを見ると、いろんなsql_modeの組み合わせてOracle databaseの動作っぽくしてるみたいです。
他にもMSSQLやPOSTGRESQLなど選択できるようです。

動作の確認

sql_modeに追加された各モードの動作を確認してみます。

PIPES_AS_CONCAT

これは||を文字列結合に変更されます。従来ではORのシノニムです。

sql_mode:デフォルト

mysql> select 0 || 1 ;
+--------+
| 0 || 1 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

sql_mode:Oracle

mysql> select 0 || 1 ;
+--------+
| 0 || 1 |
+--------+
| 01     |
+--------+
1 row in set (0.00 sec)

これはOracleっぽくなりますね。ちなみにMySQLはSELECT文にFROM DUALつけなくでも実行できます。

ANSI_QUOTES

二重引用符を使用してリテラル文字列を引用できなくなります。

sql_mode:デフォルト

mysql> select "aaa" ;
+-----+
| aaa |
+-----+
| aaa |
+-----+
1 row in set (0.00 sec)

sql_mode:Oracle

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

IGNORE_SPACE

関数名と「(」文字の間にスペースを許可します。

sql_mode:デフォルト

mysql> select count (*) from ENGINES;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from ENGINES' at line 1

sql_mode:Oracle

mysql> select count (*) from ENGINES;
+-----------+
| count (*) |
+-----------+
|         9 |
+-----------+
1 row in set (0.00 sec)

ORACLE

これはsql_modeにOracleを設定したということ。

NO_KEY_OPTIONS

SHOW CREATE TABLE の出力で MySQL 固有のインデックスオプションを出力しません。このモードはポータビリティモードで mysqldump によって使用されます。

NO_TABLE_OPTIONS

SHOW CREATE TABLE の出力で MySQL 固有のテーブルオプション (ENGINE など) を出力しません。このモードはポータビリティモードで mysqldump によって使用されます。

NO_FIELD_OPTIONS

SHOW CREATE TABLE の出力に MySQL 固有のカラムオプションを出力しません。このモードはポータビリティモードで mysqldump によって使用されます。

この3つはまとめて紹介します。以下のようなテーブルを作成します。

CREATE TABLE t1( 
  id int primary key auto_increment,
  id2 varchar(10) character set 'utf8' collate 'utf8_bin' ,
  id3 int, 
  key id2_hash(id2) using hash,
  key id3_withcomment(id3) comment 'test'
);

SHOW CREATE TABLEで出力の違いを確認します。

sql_mode:デフォルト

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id2` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `id3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id2_hash` (`id2`) USING HASH,
  KEY `id3_withcomment` (`id3`) COMMENT 'test'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

sql_mode:Oracle

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `id2` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `id3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id2_hash` (`id2`),
  KEY `id3_withcomment` (`id3`)
)
1 row in set (0.00 sec)

NO_KEY_OPTIONSでキー（セカンダリインデックス）に付属する情報の隠す。(USING HASHやCOMMENT 'test'のところ)
NO_TABLE_OPTIONSでMySQL特有のテーブル構文を隠す。(ENGINE=InnoDB DEFAULT CHARSET=latin1のところ)
NO_FIELD_OPTIONSでMySQL特有のカラム構文を隠す。(AUTO_INCREMENTのところ)
CHARACTER SET utf8 COLLATE utf8_binこれも特有じゃないのかなと思いつつも。

NO_AUTO_CREATE_USER

認証情報が指定される場合を除き、ほかの方法で実行される場合は、GRANT ステートメントで新規ユーザーを自動的に作成しません。ステートメントは IDENTIFIED BY を使用した空以外のパスワードを指定するか、IDENTIFIED WITH を使用した認証プラグインを指定する必要があります。

これはMySQL5.7からsql_modeのデフォルト値に含まれていて、
OFFであればGrant文でユーザが作成されますが、ONであるとユーザを先に作成していないエラーになります。

sql_mode:デフォルト

mysql> GRANT SELECT ON *.* TO 'test'@'192.168.1.1';
Query OK, 0 rows affected (0.00 sec)

sql_mode:Oracle

mysql>  GRANT SELECT ON *.* TO 'test'@'192.168.1.1';
ERROR 1133 (42000): Can't find any matching row in the user table

以上、sql_modeをORACLEした際の説明でした。

結論はMySQLを使用するならsql_modeをORACLEにする理由はどこにもないです。
特にSHOW CREATE TABLEで大事な情報が隠れちゃうのはつらい。

明日は@discus_hamburgさんです！
確か去年も@discus_hamburgさんの前だった気がする。

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL