Prepared statementはステートメントを一度定義して、それを
何回も違う引数で実行するものです。これはセキュリティを増し
なおかつ効率のよい方法で、アドホックなクエリのストリングに
置き換わるものです。典型的なprepared statementは以下のようです。

SELECT * FROM Country WHERE code = ?

”?”はいわゆる場所とりです。上のクエリを実行するときはこの場所
に値がいります。ではどうして、prepared statementを使うのでしょう。

アプリケーションでprepared statementを使用することで、
セキュリティや性能の理由で幾つもの利点をもたらします。

Prepared statementはSQL のロジックとデータを分離することで
セキュリティを増加します。ロジックとデータを分離することで、SQL
インジェクション攻撃を回避することができます。通常のクエリを
扱っている場合、ユーザから受け取ったデータを処理するには
注意が必要です。これはシングル・クオート、ダブル・クオート、
バックスラッシュなどの文字をエスケープする関数を使用すること
に関係します。こういったことはprepared
statementを使用する際には不必要です。
データを分離することでMySQLは自動的にこういった文字を考慮して
おり特別な関数を使用してこういう文字をエスケープする必要が
ありません。

prepared statementでの性能向上はいくつかの異なった機能によります。
まず最初に、クエリを一度しかパースしなくてよいことです。
最初にステートメントの用意をした際、MySQLはステートメント
をパースしてシンタクスをチックして、クエリの実行の用意を
します。同じクエリを何回も実行するのであれば、そのオーバヘッド
は2度目からありません。あらかじめ、パースしてあることで例えば
なんどもINSERTステートメントを使うような場合、スピードが増加します。

2つ目の性能向上は新しいバイナリーのプロトコルによります。
今までのプロトコルはネットワークを介して転送する前に、
全てをストリングに変換していました。クライアントはデータを
ストリングに変換し(大抵の場合もとのデータより大きい)、ネットワーク
(か他の方法で)サーバに転送します。サーバはストリングをもとの
正しいデータタイプに変換します。バイナリー・プロトコルであれば
このオーバーヘッドがありません。全てのタイプはそのままの
形で(バイナリー形式)で転送されます。そのためCPUの使用も
削減され、ネットワークの使用も押さえることができます。


mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select ID,NAME from City where ID > 1000 and ID < 1010; +------+---------------+ | ID | NAME | +------+---------------+ | 1001 | Depok | | 1002 | Citeureup | | 1003 | Pemalang | | 1004 | Klaten | | 1005 | Salatiga | | 1006 | Cibinong | | 1007 | Palangka Raya | | 1008 | Mojokerto | | 1009 | Purwakarta | +------+---------------+ 9 rows in set (0.00 sec) mysql> PREPARE p1 FROM "SELECT ID,Name FROM City WHERE ID > ? and ID < ?"; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> SET @atai1 = 1000;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @atai2 = 1010;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE p1 USING @atai1,@atai2;
+------+---------------+
| ID | Name |
+------+---------------+
| 1001 | Depok |
| 1002 | Citeureup |
| 1003 | Pemalang |
| 1004 | Klaten |
| 1005 | Salatiga |
| 1006 | Cibinong |
| 1007 | Palangka Raya |
| 1008 | Mojokerto |
| 1009 | Purwakarta |
+------+---------------+
9 rows in set (0.00 sec)

mysql>

mysql_prepared_statement

その他の例

prepare_1

補足
SQLステートメントの削除

DEALLOCATEステートメントは,登録したSQLステートメントを削除する。
以下のSQLステートメント「p1」を削除している。

mysql> DEALLOCATE PREPARE p1;
Query OK,0 rows affected (0.00 sec)

※ DROP PREPAREでもOKですし、セッションを切ればPREPAREもなくなります。

以下の例では、?を使わずにPREPAREを作成して最後に削除しています。

mysql> PREPARE P_Time from 'select now()';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute P_Time;
+———————+
| now() |
+———————+
| 2009-11-12 00:29:43 |
+———————+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE P_Time;
Query OK, 0 rows affected (0.00 sec)

mysql>

prepare_time

このようにプリペアド・ステートメントは,SQLステートメントを登録して実行する
機能を提供する。プリペアド・ステートメントは,事前にSQLステートメントを
登録しておくので,パラメータのみを受け渡すだけでデータベース処理が可能である。

参考サイト
[MySQLウォッチ]第11回 リリース迫る4.1

Comments are closed.

Post Navigation