Rustのsqlx crateとMySQL 8.0を使うときに IN (?, ?, ...) を回避する
はじめに
この記事ではRustのsqlx crateとMySQL 8.0で、IN (?, ?, ...)が含まれるクエリを避けるワークアラウンドについて書きます。サンプルコードのソースコードは以下のリポジトリに置いてあります。
動機： 実行時にプレースホルダを構築することを避けたい
SELECTクエリを実行するときにINを使って特定のID（複数個）のみを含むレコードを取得したいという状況があります。このとき、IDのリストは実行時に生成され任意の長さを持っているとします。
この状況の時、例えば以下のようなコードを書くことになりますが、クエリ中のINの後のプレースホルダは、1つの?に対してスカラー値が1つしか入れることができないため、リストの長さの分だけプレースホルダを書く必要があります。リストの長さが実行時に決まるのであれば、それに応じてクエリを実行時に組み立てる必要があります。
let ids: Vec<i64> = get_some_ids(); // 任意の個数のIDを返す
let rows = sqlx::query_as::<(i64,)>("SELECT id FROM table WHERE id IN (?, ?)")
// ^^^^^^
// ここは `ids.len()`に応じた長さにしたいけどどうすれば？
.bind(ids[0])
.bind(ids[1]) // `bind`も`ids.len()`の数だけ呼び出す必要がある
.fetch_all(&pool)
.await?;
StackOverflowの以下の記事のようにformat!でクエリを構築する方法も考えられますが、個人的にコードの見通しがよくないように感じるので他の方法を使います。
解法： JSON_TABLEを用いてテーブルを結合する
MySQLの関数JSON_TABLEを使いIDのリストをテーブルとして読み込み、INを使う代わりにテーブルをJOINします。
この方法は以下のブログ記事を参考にして思いつきました。
設定
以下のスキーマのテーブルitemsに適当な個数のレコードが挿入されているとします。（テーブルの作成とレコードの挿入はサンプルコードの関数setup_items_tableで実行されます。）
CREATE TABLE items (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
price DECIMAL(8, 2)
)
IN (?, ?, ...)を使うコード
まずはIN (?, ?, ...)を使うコードを見てみましょう。以下のクエリを実行して複数のレコードを取得します。
SELECT id, name, price FROM items WHERE id IN (?, ?, ?, ?)
プレースホルダには変数idsの要素を入れます。ここで、idsの長さは4です。そのため?を4つ書き、bindを4回呼ぶ必要があります。
JSON_TABLEを使うコード
先ほどのクエリをJSON_TABLEを使って書き直します。JSON_TABLEは２つの引数を受け取ります。第一引数がJSONのデータになります。変数idsをJSONに変換して渡すためプレースホルダはJSON_TABLEの第一引数のみになります。
JSON_TABLEで構築したテーブルに含まれるidを持つレコードだけを取り出すために、WHERE以下でINの代わりに等号で条件を指定します。
詳細な解説に移りましょう。FROMでJSON_TABLEを使っている行に注目します。
JSON_TABLE(?, '$[*]' COLUMNS( id BIGINT PATH '$' ERROR ON ERROR )) AS ids
MySQLのマニュアルを確認すると、JSON_TABLEの第一引数はJSONデータを返す式です。今回はプレースホルダを使って文字列として渡します。プレースホルダにはbindでJSONを渡します。（今回は数値の配列を渡すためDebugプリントでJSONのフォーマットにすることができます。）
ここで渡されるJSONは[10, 20, 381, 35]になります。
第二引数はpath COLUMNS (column_list)の形になっていて、pathはJSONパスの文字列です。column_listにはカラムとJSONデータ中の対応を記述します。
なお、最後の[AS] aliasを省略することはできません。JSON_TABLEで作成するテーブルには必ず名前を付けなければなりません。
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
JSONパス
JSONパスはJSONドキュメント内の要素を指し示すための構文です。定義は以下のページに記述されています。
今回のサンプルコードでは$[*]という形で使われています。$は渡されたJSONドキュメント全体を意味します。そして[*]は配列の要素全体を意味します。ここで渡されるJSONドキュメントは数値の配列です。従って、$[*]で配列の要素をイテレートします。配列の各要素はCOLUMNSで使われるJSONドキュメントとして扱われます。
JSON_TABLE(?, '$[*]' COLUMNS( id BIGINT PATH '$' ERROR ON ERROR )) AS ids
カラムの定義
JSON_TABLEでのカラムの定義は4つのタイプがありますが、今回は以下のタイプを使います。
name type PATH string_path [on_empty] [on_error]
まず、nameはカラムの名前、typeはカラムの型です。そして、string_pathはJSONパスになります。
今回のサンプルコードではstring_pathの部分は$になっていました。JSON_TABLEに渡されるJSONドキュメントは[10, 20, 381, 35]でした。このJSONから$[*]で配列の各要素が取り出され、各レコードに渡されます。カラムidはパス$の値を使うので、配列の各要素が一つ一つのレコードになることになります。
[on_empty]と[on_error]についてはMySQLのマニュアルを参照してください。
さいごに
JSON_TABLEを使うことでプレースホルダを含むクエリを実行時に構築する必要はなくなりましたが、一方でJSON_TABLEの使い方が分かりづらいというデメリットがあります。
また、JSON_TABLEがサポートされるのはMySQL 8.0以降のため、5.7以前では使えないテクニックになります。
今回のサンプルコードでは小さな例しか実装していないため、INとJSON_TABLEでどちらが高速なのかは分かりません。プロダクションに使う場合はパフォーマンスに関して別途検証が必要です。
Discussion