戯言と技術 | MySQLのレスポンス結果をJSON形式で返してjqコマンドに渡す

MySQLのレスポンス結果をJSON形式で取得したいと考えいろいろ調べた結果をまとめていきます。

mysqlshコマンドを利用することでレスポンス結果をJSON形式で返却することができます。

mysqlshコマンドのインストール

以下のサイトからmysqlshコマンドのインストール方法があるので参照してください。 https://dev.mysql.com/doc/mysql-shell/8.0/ja/mysql-shell-install.html

MySQLをDockerでインストールし初期データの準備

docker run --name test-mysql -e MYSQL_ROOT_PASSWORD=password -d -p 3306:3306 mysql:latest
mysql -h 127.0.0.1  -u root -ppassword

Databaaseの作成とサンプルデータを作成

CREATE DATABASE test;
USE test;
CREATE TABLE data(
    id SERIAL PRIMARY KEY,
    title VARCHAR(256)
);
INSERT INTO data(title) VALUES('A1'), ('B2'), ('C3'), ('D4');

パスワードファイルの作成

vi ~/my.conf
=== 以下を貼り付け ===
[client]
user = root
password = password
====================

mysqlshコマンドを利用してJSONの結果を返却

mysqlshは、直接SQLを操作というよりコマンドライン上でSQLを指定して実行した結果を返す形なので mysql -eオプションを使うイメージが同じ感じになります。

mysqlshコマンドの基本サンプル

dataテーブルをSELECTするだけのサンプルです。

mysqlsh --defaults-extra-file=~/my.conf --host=127.0.0.1 --port=3306 --sql --execute='SELECT * FROM test.data;'
=== 実行結果 ===
id	title
1	A1
2	B2
3	C3
4	D4
===============

mysqlshコマンドでjson形式のレスポンス結果を得る

dataテーブルをSELECTした結果をjson形式で返却します。先ほどのコマンドに –result-format=jsonをつけるだけでできます。

mysqlsh --defaults-extra-file=~/my.conf --host=127.0.0.1 --port=3306 --result-format=json --sql --execute='SELECT * FROM test.data;'
=== 実行結果 ===
{
    "id": 1,
    "title": "A1"
}
{
    "id": 2,
    "title": "B2"
}
{
    "id": 3,
    "title": "C3"
}
{
    "id": 4,
    "title": "D4"
}
==============


### mysqlshコマンドでjson形式のレスポンス結果をjqコマンドを繋ぐ

出力結果からjqコマンドを使ってさらにtitleのみを一覧化するようなサンプルとなります。
パイプラインを繋いでスクリプトを組み合わせれば色々な応用が効くと思います。
```sh
mysqlsh --defaults-extra-file=~/my.conf --host=127.0.0.1 --port=3306 --result-format=json --sql --execute='SELECT * FROM test.data;'  | jq '.title'
=== 実行結果 ===
"A1"
"B2"
"C3"
"D4"
==============

mysqlshコマンドでファイルから実行

ファイルからの実行にも対応しているので利用の幅としては申し分ないです。

echo "SELECT * FROM test.data" > test.sql
mysqlsh --defaults-extra-file=~/my.conf --host=127.0.0.1 --port=3306 --result-format=json --sql --file=test.sql
=== 実行結果 ===
{
    "id": 1,
    "title": "A1"
}
{
    "id": 2,
    "title": "B2"
}
{
    "id": 3,
    "title": "C3"
}
{
    "id": 4,
    "title": "D4"
}
==============

まとめ

他のオプションは以下のサイトを参照
A MySQL Shell コマンドリファレンス

jsonレスポンを利用してjqコマンドとの応用が行える