MySQLで NoSQL
MySQL 5.7.12 から、MySQLでもNoSQLのように JSON形式による Document Store が扱えるようになりました。
今回は PHP の mysql_xdevapi の バージョン 8.0.2 (alpha) がリリースされたのでどういった機能があるか検証を行いました。
X DevAPI とは？
X DevAPI は MySQL の新しいプロトコル「X Protocol」を通じて MySQL を操作するライブラリです。
従来のSQLによる操作に加えて、CRUD 操作によるモダンな方法でプログラムフレンドリーな操作を提供しています。
参考
- MySQL :: X DevAPI User Guide
- MySQL :: MySQL 5.7 Reference Manual :: 19.7 X Plugin
- PECL :: Package :: mysql_xdevapi
X Plugin セットアップ
X DevAPI を使う場合、MySQLサーバ側で X Plugin を有効にする必要があります。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so'; Query OK, 0 rows affected (0.03 sec) mysql> show plugins; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ ...(省略)... | mysqlx | ACTIVE | DAEMON | mysqlx.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) |
参考
PHP と mysql-xdevapi のインストール
CentOS 7 の REMI レポジトリを有効にすると、mysql-xdevapi パッケージをインストールすることが出来ます。
EPELとREMIパッケージをインストール
|
1 2 3 |
$ sudo yum install -y http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-10.noarch.rpm $ sudo rpm --import https://rpms.remirepo.net/RPM-GPG-KEY-remi $ sudo yum install -y yum-utils https://rpms.remirepo.net/enterprise/remi-release-7.rpm |
PHP 7.1 と mysql-xdevapi をインストール
|
1 2 |
$ sudo yum install -y php71 php71-php-pecl-mysql-xdevapi $ sudo ln -s /usr/bin/php71 /usr/bin/php |
参考
- How to Enable EPEL Repository for RHEL/CentOS 7.x/6.x/5.x
- English : Repository Configuration – Remi’s RPM repository – Blog
従来のテーブル構造に対しての操作を行う
X DevAPI は Document Store のためだけの API ではなく、従来どおりのテーブルに対しても操作することができます。
例えば以下のような users テーブルがあった場合
|
1 2 3 4 5 6 7 8 |
mysql> desc users; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | name | varchar(255) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) |
PHPのコードでは以下のようにして操作できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
// MySQLへの接続 $session = mysql_xdevapi\getSession(mysqlx://root:password@localhost:33060); // スキーマ(データベース)を取得 $schema = $session->getSchema("xdev_test"); // テーブルを取得 $table = $schema->getTable("users"); // PHPのコードでデータを取得できる $result = $table->select('age', 'name') // 取得するカラム ->where('age >= :age') // バインドパラメーターも利用可能 ->orderby('age desc') ->limit(2) ->bind(['age' => 30]) // バインドした値を入れる ->execute() ->fetchAll(); /* 結果セットは配列で返ってくる $result => [ [ "age" => 45, "name" => "Ginger Sutton",], [ "age" => 41, "name" => "Evelyn Pena",], ] */ |
MySQL側では以下のSQLに変換されて実行されています。
|
1 |
SELECT `age`,`name` FROM `xdev_test`.`users` WHERE (`age` >= 30) ORDER BY `age` DESC LIMIT 2 |
PHPのWebフレームワークではデータベース操作をO/Rマッパーで行うことが多いと思いますが、XDevAPIを使えばMySQLのネイティブドライバで同様の仕組みが実現できています。
データの挿入や更新、削除もできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
// データの挿入 $table->insert("name", "age") ->values(["Franklin Little", 30], ["Ramiro Gray", 40], ["Ralph Duncan", 25]) ->execute(); // データの削除 $table->delete() ->where("age > :age") ->bind(["age" => 35]) ->execute(); // データの更新 $table->update() ->set('name', 'Beatrice Freeman') ->where('name = :name') ->bind(['name' => 'Franklin Little']) ->execute(); |
MySQL側では以下のSQLに変換されて実行されています。
|
1 2 3 4 5 6 |
# データの挿入 INSERT INTO `xdev_test`.`users` (`name`,`age`) VALUES ('Franklin Little',30),('Ramiro Gray',40),('Ralph Duncan',25) # データの削除 DELETE FROM `xdev_test`.`users` WHERE (`age` > 35) # データの更新 UPDATE `xdev_test`.`users` SET `name`='Beatrice Freeman' WHERE (`name` = 'Franklin Little') |
Document Store として使う
X DevAPI を使えば、NoSQLのように Document Store が使えるようになりますが、内部的には特別な領域にデータが保存されるわけでは無く、従来のテーブル構造に変換されて保存されます。
PHPから Schema を作成する
PHPコード
|
1 2 3 |
$session = mysql_xdevapi\getSession(mysqlx://root:password@localhost:33060); // Schema を作成 $session->createSchema('xdev_test_schema'); |
一般クエリーログでは以下のSQLに変換されて実行されています。
|
1 |
CREATE DATABASE `xdev_test_schema` |
確認すると、たしかに新しいデータベースが作成されています。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | xdev_test_schema | +--------------------+ 5 rows in set (0.00 sec) |
このことからMySQLでは「Schema = データベース」にマッピングされて保存されていることがわかります。
PHP から Collection を作成する
PHPコード
|
1 2 |
$schema = $session->getSchema('xdev_test_schema'); $schema->createCollection('test_collection'); |
MySQL では以下のSQLに変換されて実行されていました。
|
1 |
CREATE TABLE `xdev_test_schema`.`test_collection` (doc JSON,_id VARCHAR(32) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4 ENGINE=InnoDB |
新しく _id と doc を持っているテーブルが作成されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> show tables; +----------------------------+ | Tables_in_xdev_test_schema | +----------------------------+ | test_collection | +----------------------------+ 1 row in set (0.00 sec) mysql> desc test_collection; +-------+-------------+------+-----+---------+------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+------------------+ | doc | json | YES | | NULL | | | _id | varchar(32) | NO | PRI | NULL | STORED GENERATED | +-------+-------------+------+-----+---------+------------------+ 2 rows in set (0.00 sec) |
このことからMySQLでは「Collection = テーブル」にマッピングされて保存されているのがわかります。
コレクションへの追加
PHPのコード
|
1 2 |
$collection = $schema->getCollection('test_collection'); $collection->add(["name" => "Ralph Duncan", "age" => 25])->execute(); |
MySQL では以下のSQLに変換されて実行されていました。
|
1 |
INSERT INTO `xdev_test_schema`.`test_collection` (doc) VALUES ('{\"name\":\"Ralph Duncan\",\"age\":25,\"_id\":\"FE49AA04A6F82CFBFE832CACBD252B0F\"}') |
_id は ライブラリ内で UUID が生成されて割り当てられているようです。
データの set, unset
set で項目を追加
|
1 2 3 4 |
$collection->modify("name like :name") ->bind(["name" => "Craig Sullivan"]) ->set("job", "Programmer") ->execute(); |
実行されるSQL
|
1 |
UPDATE `xdev_test_schema`.`test_collection` SET doc=JSON_SET(doc,'$.job','Programmer') WHERE (JSON_UNQUOTE(JSON_EXTRACT(doc,'$.name')) LIKE 'Craig Sullivan') |
unset で項目を削除
|
1 2 3 4 |
$collection->modify("age like :age") ->bind(["age" => 25]) ->unset("name") ->execute(); |
実行されるSQL
|
1 |
UPDATE `xdev_test_schema`.`test_collection` SET doc=JSON_REMOVE(doc,'$.name') WHERE (JSON_UNQUOTE(JSON_EXTRACT(doc,'$.age')) LIKE 25) |
ALTER でカラムの追加や削除無しに項目変更ができるのは Document Store の強みの一つです。
トランザクションのサポート
NoSQL の多くはトランザクションをサポートしていませんが、MySQLでは Document Store でもトランザクションをサポートしています。
PHP のコード例
|
1 2 3 4 5 6 7 8 |
$session->startTransaction(); $collection = $schema->getCollection('test_collection'); try { $collection->add(["name" => "Craig Sullivan", "age" => 33])->execute(); $session->commit(); } catch(Exception $e) { $session->rollback(); } |
まとめ
- PHP の mysql_xdevapi パッケージはまだ alpha バージョンのため不安定です（検証中に segfault が発生することも）
- 従来のテーブル構造にも O/R Mapper を使わずに PHP のコードでデータにアクセスすることができます。
- Document Store も使えるが、内部的にはデータベースやテーブルにマッピングされて保存されています。
安定して使えるようになるにはまだ先のようですが、以下の用途で使う場合は検討を始めてもいいかもしれません。
- 一部のデータだけNoSQLのような使い方が必要な場合
- トランザクション処理を通常のテーブルも含めて行いたい場合
- 軽量なO/R Mapper が欲しい場合
