【CakePHP3.6】MySQL8との連携対応は可能なのか？

CakePHP3.6のフレームワークを利用して、最新のMySQL8を使いたい！という趣旨のもと使用方法を確認していきます。

イチオシ記事

【裏ワザ】Amazonで商品を安く買うための裏テクニック16（完全保存版）
Macが遅い&重い時の改善・対処方法とは？動作がおかしくなった場合の対処とメンテナンス方法（完全保存版）
MacのExcel（エクセル）が重い・クラッシュしてしまう時の対処方法
MacのWord（ワード）が重い・クラッシュしてしまう時の対処方法

MySQL8の変更点
CakePHP3.6の対応状況
今後の期待

MySQL8の変更点

まず、今回使ってみようと思うMySQL8の変更点です。
※変更点全てではありません。シンプルなシステムで使用する可能性のある変更点です。

ユーザー認証プラグインの変更

これまでのMySQLでは、「mysql_native_password（ネイティブ認証プラグイン）」がデフォルトで選択されていました。
この認証は、ネイティブのパスワードハッシュ化方式を利用して、mysql.userテーブルのpasswordカラムに格納されたパスワードと照合する認証を行います。
しかし、MySQL8からは、よりセキュアな「caching_sha2_password（キャッシュSHA-2認証プラグイン）」に変更になりました。
この認証は、SHA-256によりハッシュ化されたパスワードを使用して認証を行います。また、メモリー上にキャッシュすることにより認証時のハッシュ化処理が省略できより高速な接続を可能とします。

参考「native-authentication-plugin」について参考「caching_sha2_password」について

インストールしているMySQLの認証方式を確認するには、下記のSQLを実行します。

SQL

show variables like 'default_authentication_plugin'

CTE（Common Table Expressions：共通テーブル式）が追加

複雑な条件のSQLを書く場合、ネストが深くなったり同じクエリ文が続いたりすることがあります。
その時は共通な条件のクエリ文をViewに置き換えたりして対応していました。
この機能を使用することで、クエリ文の可読性を上げよりスマートなSQLを作成することが可能になります。

例えば、子供の親のデータを取得するSQLがあった場合、通常は下記のように作成します。

SQL

SELECT
  * 
FROM
  kaisou 
WHERE
  parent_id IN (
  	SELECT
  		parent_id 
	FROM
  		kaisou 
	WHERE
  		name = '親'
	)

これをCTEで作成すると

SQL

WITH RECURSIVE child_table(id, parent_id, name) AS ( 
    SELECT
        id
        , parent_id
        , name 
    FROM
        kaisou 
    WHERE
        name = '親' 
    UNION ALL 
    SELECT
        child.id
        , child.parent_id
        , child.name 
    FROM
        kaisou AS child
        , child_table 
    WHERE
        child_table.parent_id = child.parent_id
) 
SELECT
    * 
FROM
    child_table

簡単なSQLなのですが、「UNION ALL」の前のクエリ文で、親になるデータを選出します。
「UNION ALL」の後のクエリで再帰的な処理を行い、合致するデータを取得します。

参考 MySQL8 WITH句について

ウィンドウ関数の追加

参考 Window Function Descriptions

上記公式サイトの関数を見ると、下記のウィンドウ関数が用意されています。

関数	説明
CUME_DIST()	累積分布値
DENSE_RANK()	隙間のない、そのパーティション内の現在の行のランク
FIRST_VALUE()	ウィンドウ枠の1行目からの引数の値
LAG()	行内の引数の値は、パーティション内の現在の行を遅らせる
LAST_VALUE()	ウィンドウ枠の最後の行からの引数の値
LEAD()	パーティション内の現在の行を先頭にする行からの引数の値
NTH_VALUE()	ウィンドウ枠のN番目の行からの引数の値
NTILE()	そのパーティション内の現在の行のバケット番号
PERCENT_RANK()	パーセントランク値
RANK()	ギャップを持つ、そのパーティション内の現在の行のランク
ROW_NUMBER()	パーティション内の現在の行の数

今まで、PostgreSQLやOracleにあって、MySQLに無かった関数など追加されたことにより便利になります。

CakePHP3.6の対応状況

CakePHP3.6でMySQL8が使用できるか確認していきます。

ユーザー認証プラグインの変更

この機能を使うためには、MySQLの接続用の「X DevAPI」を使用する必要があります。

「X DevAPI」を使用せずCakePHP3の既存機能でアクセスすると下記のようなエラーになります。

インストール方法

●依存関係のモジュールインストール

bash(Debian)

apt-get install -y build-essential libprotobuf-dev libboost-dev openssl protobuf-compiler

●PHPのインストール

bash(Debian)

apt-get install -y php7.2-cli php7.2-dev php7.2-mysql php7.2-pdo php7.2-xml

●Boostのインストール

bash(Debian)

apt-get install -y libboost-dev

●X DevAPIのインストール

bash(Debian)

pecl install mysql_xdevapi

参考 php.net 参考 Mysql_xdevapi

CakePHP3で使用する場合は、自前の接続用クラスが必要になるのでCakePHPの恩恵はあまり受けられないかもしれません。

ただし、「mysql_xdevapi」ですが現在下記のエラーが発生してインストールができません。
※インストールができるようになった時に再度調査いたします。

bash(Debian)

mkdir: cannot create directory 'xmysqlnd/proto_gen/.libs': No such file or directory
Makefile:247: recipe for target 'xmysqlnd/proto_gen/mysqlx_connection.lo' failed
make: *** [xmysqlnd/proto_gen/mysqlx_connection.lo] Error 1
ERROR: `make' failed

その代り、認証方式を以前の「mysql_native_password」に変更するとMySQL5.7までの従来どおりの方法でアクセスすることが可能になります。