第11回　MySQLの基礎を学ぶ

はじめに

みなさんこんにちは。技術系Q&Aサイトteratail開発チームの本橋佑介です。

MySQLは世界で広く使われているオープンソースのRDBMSです。今では多くのPaaSやSaaSでも利用でき、GoogleCloudSQLやAmazon Auroraなどといったクラウドコンピューティングサービスで独自実装されているRDBもMySQLを基礎とし、互換性を保ったものが利用されています。

このようにWebサービスを利用する上で非常に採用頻度が高いMySQLですが、利用するときに知っておくべき基礎的な機能について紹介していきたいと思います。

ストレージエンジン

MySQLの大きな特徴の一つとして、ストレージエンジンがあげられます。

ストレージエンジンとは、パーサによって最適化されたSQLクエリを実際に実行する機能部分です。MySQLではさまざまなテーブル型に対するSQL操作を処理するコンポーネントとしてストレージエンジンを提供しています。主に利用されていたストレージエンジンはInnoDBとMyISAMです。InnoDBはMySQL5.5からデフォルトのストレージエンジンとなっており、MyISAMと違いトランザクションが利用できます。

一昔前では頻繁な更新のあるテーブルはInnoDB、マスターデータなどの参照が主なデータを格納するテーブルや全文検索するテーブルはMyISAMを利用するのが主流でした。しかし、MySQL5.6ではInnoDBのパフォーマンスも改善され全文検索も可能になり、参照速度を重視するテーブルでもInnoDBを利用することが多くなっています。

参考：InnoDB以外のストレージエンジンを使うことはありますか？: https://teratail.com/questions/1035

データ型

MySQLでは数値型、日付型、文字列型、空間データ型がサポートされています。これからMySQL特有のデータ型について説明していきたいと思います。

SIGNED/UNSIGNED

数値型ではSIGNED, UNSIGNEDという指定により符号の有り無しの指定が可能です。たとえば TYNYINTの場合SIGNEDだと-128～127までの整数が格納できますが、UNSIGNEDの場合は0～255を対象とします。

MySQLは曖昧な型の利用をしているため、SIGNEDでも128以上の整数がinsertされます。その場合は以下のように一番近い入力できる値が挿入されます。

mysql> create table signed_type (signed_ti tinyint, unsigned_ti tinyint unsigned);

mysql> insert into signed_type values(200,200);
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> select * from signed_type;
+-----------+-------------+
| signed_ti | unsigned_ti |
+-----------+-------------+
|       127 |         200 |
+-----------+-------------+
1 rows in set (0.00 sec)

insert文でwarningが出ています。そこで show warnings;をすることでwarningの内容を参照することができます。

BOOLEAN/BOOL型

真偽値TRUE,FALSEを格納するために利用することの多いBOOLEAN型ですが、MySQLではTYNYINT(1)のシノニムとなっています。また、論理値であるTRUE,FALSEもそれぞれint型の1,0となります。BOOLEAN型と想定してカラムを作成したが0,1以外の値が入るため想定外の挙動をしバグの温床となることがありますので注意しましょう。

また、selectする際にもwhere column_name = trueではなくis trueと比較した場合、1以外の値も取得できてしまうため真偽値の取得をする際には= trueを利用しましょう。

日付型でのゼロの扱い

PostgreSQLでは「限りなく古い日付」を-infinityで処理しますが、MySQLではゼロ値で取り扱います。DATE型では0000-00-00、DATETIMEでは0000-00-00 00:00:00のように格納、参照することが可能です。しかし、PHPのdatetimeオブジェクトなどではこの値を正常な日付として取り扱いできないためプログラム側の条件式などで工夫して取り扱う必要があります。

大文字小文字の取り扱い

MySQLでは文字列比較で大文字と小文字を同一のものとして扱います。プログラム側で別のものとして取り扱う場合はここでも注意が必要です。バイナリ型を利用することでこの問題を回避することが可能です。

また、絵文字の比較、日本語のハハ パパ ババを同じ文字列としてしまうため、日本語を取り扱う場合はutf8mb4_unicode_ciを指定する必要があります。

全文検索

これまで全文検索を行うためにPostgreSQLを選択することが多かったですが、MySQL5.7からN-gram、MeCabの全文検索パーサがサポートされました。以下のように指定することが可能です。このパーサの追加により他のRDBと同じように全文検索を利用することが可能です。

mysql> CREATE FULLTEXT INDEX INDEX名 (対象カラム) WITH PARSER ngram ON ;

SQLモード

さまざまなMySQLの"癖"を回避するために厳密なSQLモードを指定することが可能です。多くの場合に予期せぬバグの原因となるMySQLの曖昧さを回避するためにsql_mode='TRADITIONAL'を指定することができます。

TRADITIONALでは、たとえばSIGNED/UNSIGNEDの項でtynyint型のカラムに200を挿入した際にwarningを発し127と格納されるのではなくerrorを返すようになります。

またSQLアンチパターンでたびたび取り上げられる曖昧なグループを許容しないONLY_FULL_GROUP_BYを利用することも多いです。

※　曖昧なグループとはGROUP BYにSELECT句で指定しないカラムを指定すること。

JSON型

MySQL5.7.8よりJSON型がサポートされました。まだPostgreSQLのような高度な関数や高速な捜査を可能にするインデックスは存在しませんが、スキーマレスなデータ型を保存することでより柔軟なデータの取り扱いが可能になりました。

mysql> CREATE TABLE json_type (json_data json);

MySQL5.7には、ここまでで紹介した幾つかの機能追加とともに多くの変更があります。パフォーマンスの大幅な向上のためにアップデートをすることも考慮に入れたいですが、その前にconfigの設定について一度調べておくことをおすすめします。

最後に

今回はMySQLを利用する上で忘れがち、見落としがちな基礎的な部分を紹介しました。teratailではMySQLに関する質問や回答も多く投稿されていますので、疑問点ができたらどんどん質問してみるとよいと思います。