SQLの説明ではあまり触れられることのないSQLパラメータに関してですが、実は実務では利用されることの多い機能でもあります。
本記事では、SQLでのパラメータの使い方について、サンプルを交えながらご紹介していきたいと思います。
SQLのパラメータって何？
SQLで処理を実行する際、変数に値を嵌め込んでデータベースにデータを渡した上で処理を実行する「パラメータ化クエリ」という実行方法が存在します。
動的パラメータやバインド変数などとも呼ばれる手法ですが、MySQLでは一般的に「ユーザー定義変数」と呼ばれています。
SQLパラメータを利用する目的
SQLパラメータを利用する目的としては大きく「SQLインジェクション対策」と「パフォーマンス改善」が挙げられます。
SQLインジェクション対策
パラメータ化クエリは、セキュリティ対策として「SQLインジェクション対策」に頻繁に利用される手法です。
SQLインジェクションは想定しないSQL文を実行させることで、データベースを不正に操作する手法のことを指します。
パフォーマンス改善
実行計画をキャッシュとして保持するデータベースでは、同じSQL文の場合、前回実行した実行計画をそのまま利用することが可能です。
しかし、値が一部でも変わってしまうと、別のSQL文として認識されるため、再度SQLの実行計画を取得する必要があり、パフォーマンスが落ちてしまいます。
一方で、パラメータ化クエリを利用した場合には、ユーザー定義変数により実際の値は変更されるものの、SQL文自体は変わらないため、実行計画を取得するコストを省き、パフォーマンスの向上が期待出来ます。
SQLパラメータの記述方法
実際にSQLパラメータの記述方法をMySQLを参考にご紹介していきたいと思います。
ユーザー定義変数に値を格納する
ユーザー定義変数に値を格納するには「SET」を利用します。
SET 変数名 = "値";
変数名には「@test」のように「@」の後に好きな変数名を指定します。
ユーザー定義変数を確認する
格納されたユーザー定義変数の値を確認するには「SELECT」を利用します。
SELECT 変数名;
条件式で使用する
ユーザー定義変数に格納された変数は、同一セッション内であれば条件式の値として指定することも可能です。
SELECT カラム名(, カラム名, ...) FROM テーブル名 WHERE カラム名 = 変数名;
SELECT文の取得結果をユーザー定義変数に格納する
SELECT文で取得した結果をユーザー定義変数に格納するには「:=」を利用します。
注意しておきたいポイントとして、ユーザー定義変数に格納出来る値は1つだけです。
複数のデータが取得されるSQLでユーザー定義変数に格納しようとした場合、エラーとなってしまいます。
SELECT 変数名 := カラム名 FROM テーブル名 WHERE 条件式 LIMIT 1;
取得されるデータを確実に1つにするため、LIMIT句を指定しています。
SQLパラメータをサンプルで確認しよう
では実際にSQLパラメータをMySQLのサンプルで確認してみましょう。
今回サンプル用のテーブルとして「user」テーブルを下記のデータを格納した状態で作成してあります。
+----+-----------------+------+ | id | name | age | +----+-----------------+------+ | 1 | 山田太郎 | 30 | | 2 | 山田花子 | 25 | | 3 | 鈴木じろう | 20 | +----+-----------------+------+
ユーザー定義変数に格納するサンプル
ではまずユーザー定義変数に値を格納してみましょう。
SET @user_name = "山田太郎";
「user_name」という変数に「山田太郎」という値を格納しています。
実際にユーザー定義変数として格納出来ているか、下記コマンドで確認してみましょう。
SELECT @user_name;
実行した結果が下記の通りです。
+--------------+ | @user_name | +--------------+ | 山田太郎 | +--------------+
ここまでで、ユーザー定義変数に値を格納出来ていることが確認出来ました。
条件式で使用するサンプル
次に上記で設定したユーザー定義変数を条件式に利用したサンプルSQLを作成してみましょう。
SELECT * FROM user WHERE name = @user_name;
実行した結果が下記の通りです。
+----+--------------+------+ | id | name | age | +----+--------------+------+ | 1 | 山田太郎 | 30 | +----+--------------+------+
指定した値の条件で結果が抽出されていますね。
ユーザー定義変数に格納する
次はもう少し複雑に上記のサンプルSQLから取得した「id」カラムの値を別のユーザー定義変数に格納してみましょう。
SELECT @user_id:=id FROM user WHERE name = @user_name LIMIT 1;
実行した結果は下記のように表示されます。
+--------------+ | @user_id:=id | +--------------+ | 1 | +--------------+
ではこの取得したユーザー定義変数を使用して再度SQL文を組み立ててみましょう。
SELECT * FROM user WHERE id = @user_id;
実行した結果が下記の通りです。
+----+--------------+------+ | id | name | age | +----+--------------+------+ | 1 | 山田太郎 | 30 | +----+--------------+------+
きちんとユーザー定義変数に値が格納されており、条件式として使用出来ていることがご確認頂けます。
セッションが切れた場合
試しに「exit」と入力してMySQLを離脱してみましょう。
再度MySQLにログインして下記のコマンドを入力してください。
SELECT @user_name; SELECT @user_id;
実行してみるとどちらも設定した値が消えてしまっていることをご確認頂けます。
+------------------------+ | @user_name | +------------------------+ | 0x | +------------------------+
ユーザー定義変数は同一セッション内でのみ利用可能でしたね。
さいごに：SQLでパラメータの扱い方を覚えて一段階上のエンジニアを目指そう
本記事では、パラメータを使用したSQL文の使い方について、MySQLをサンプルとしてご紹介してきました。
今回は基本としてデータベース単体での操作方法についてご紹介してきましたが、プログラムと連携して使用される方法が一般的です。
まずはご利用のデータベースでパラメータ化クエリの操作方法を確認し、実際にデータベース処理を含んだシステム開発で応用してみてください。
以降の説明では、MySQLを利用したサンプルを解説していきます。
データベース毎に記述方法が異なりますので、ご利用のデータベースによって適宜読み替えて実施してみてください。