MySQLのGenerated Columnsを使う必要があり、動作が気になったので検証してみました。
Generated Columns(生成カラム)とは
Values of a generated column are computed from an expression included in the column definition. [翻訳] 生成された列の値は、列定義に含まれている式から計算されます。
つまり、Generated Columnsの値には、そのカラムを定義したときに定義した関数や式の計算結果の値が入るということ。
MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.8 CREATE TABLE and Generated Columns
なるほど、もう少し手を動かして掴んでみましょう。
作業
MySQLに接続するまで
試すには、Generated Columnsが追加されたバージョン 5.7.6 以上のMySQLをインストールします。
Dockerでサクッとやる。
docker pull mysql
これで最新のMySQLのイメージを取得。
docker run --name mysql -e MYSQL_ROOT_PASSWORD=mysql -d -p 3306:3306 mysql
これでMySQLを起動。
rootのパスワードはmysql
次は、MySQLのコンテナに入る。
docker exec -it `CONTAINER ID` /bin/bash
MySQLに接続する。 パスワードはさっき書いたものを入力する。
mysql -u root -p
データベースを作成する
以下のコマンドでデータベースを作成する。
create database gc_db;
テーブルを作成する
以下のコマンドでテーブルを作成する。
create table gc_test(id int unsigned auto_increment not null primary key, json_data json);
カラムは id , json_data のみでひとまず作成する。
データを追加する
以下のコマンドでデータを追加する。
insert into gc_test(json_data) values ('{"demo": {"id": 1}}'), ('{"demo": {"id": 2}}'), ('{"demo": {"id": 3}}');
これでJSONデータを含むテーブルができました。
mysql> select * from gc_test; +----+---------------------+ | id | json_data | +----+---------------------+ | 1 | {"demo": {"id": 1}} | | 2 | {"demo": {"id": 2}} | | 3 | {"demo": {"id": 3}} | +----+---------------------+
次は、Generated Columnsを定義します。
Generated Columnsを定義する
今回、Generated Columnsを定義するとき、 json_data カラムの demo オブジェクト中の id を取り出すように定義します。
次のコマンドでテーブルにGenerated Columnsを追加します。
alter table gc_test add column demo_id varchar(255) GENERATED ALWAYS AS (json_extract(`json_data`, '$.demo.id')) STORED;
STORED というキーワードを書いているが、他にもVIRTUALが存在します。
デフォルトでは、VIRTUALが選択されます。
このキーワードで、生成された値をストレージに格納するかを決めることができます。
VIRTUAL
VIRTUAL の場合は、ストレージには保存されず、BEFOREトリガーの後に行が読まれたときに評価されます。
VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage.
引用 MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.8 CREATE TABLE and Generated Columns
行が読み込まれたときに評価だと、Generated Columnsの値で検索を絞り込むときにパフォーマンスは悪いのかな？ そこらへんはデータを大量に用意して後日試してみたい。
STORED
STOREDの場合は、行がINSERTまたはUPDATEされたときに評価されて値が格納されます。
STORED: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.
引用 MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.8 CREATE TABLE and Generated Columns
Generated Columnsを定義した後はこんな感じになります。
mysql> select * from gc_test; +----+---------------------+---------+ | id | json_data | demo_id | +----+---------------------+---------+ | 1 | {"demo": {"id": 1}} | 1 | | 2 | {"demo": {"id": 2}} | 2 | | 3 | {"demo": {"id": 3}} | 3 | +----+---------------------+---------+
json_dataのdemoオブジェクトの中のidが、demo_id として取り出されてカラムに格納されているのが分かりますね。
検索のパフォーマンスがどうなるのかがモヤモヤしてきたので、 そこらへんはドキュメント読むなり実際に試してみて計測したほうが良さそうだな。
以上です。
