新鮮

頑張らないために頑張る

MySQL で SQL 文を再利用する(プリペアード・ステートメント)

データベース系の資格といえば ORACLE MASTER が有名ですが、実は MySQL にも ORACLE MASTER 相当の資格があります。

  • (1Z0-882) Oracle Certified Professional, MySQL 5.6 Developer
  • (1Z0-883) Oracle Certified Professional, MySQL 5.6 Database Administrator

諸般の事情で MySQL 5.6 Developer をとらなければならなくなったので学習メモを残しておきたいと思います。資格の勉強をする方や MySQL を利用する方の参考になればと思います。

今回は「プリペアード・ステートメント」です。

プリペアード・ステートメントとは

同じSQL文だったり似たようなSQL文だったりを何度も実行する必要があるとき、つどつど全文を打つのはかったるいので、MySQL では SQL を再利用するしくみが用意されています。この仕組みをプリペアード・ステートメントとよびます。

具体的に試してみます。

mysql> prepare p1 from 'select 1+1';
mysql> execute p1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
mysql> execute p1; /* 簡単に同じSQLをうてる */
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

変数を与えることもできます。

mysql> create table t (a int);
/* ? でプレースホルダをつくって */
mysql> prepare t1 from 'insert into t(a) values(?)';

mysql> set @hoge=1;            /* 変数をセット */
mysql> execute t1 using @hoge; /* using で変数を使う */
mysql> select * from t;
+------+
| a    |
+------+
|    1 |
+------+

SQL インジェクション対策にも使えるようです。

注意点 1 再接続すると消える

プリペアード・ステートメントはセッション単位で管理することから、再接続すると失われてしまいます。

mysql> prepare x from 'select 1+1';
mysql> execute x;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
mysql> exit
# mysql -u root -p
Enter password:
mysql> execute x;
ERROR 1243 (HY000): Unknown prepared statement handler (x) given to EXECUTE

注意点 2 タイポすると消える

同じ名前でプリペアード・ステートメントを作成すると上書きとなりますが、タイポすると昔のものも消えます。

mysql> create table t(a int);
mysql> prepare x from 'insert into t(a) values(1)';
mysql> execute x;
Query OK, 1 row affected (0.01 sec)

/* カラム a ではなく、カラム b とタイポして上書きすると */
mysql> prepare x from 'insert into t(b) values(1)';
ERROR 1054 (42S22): Unknown column 'b' in 'field list'

/* 上書き前のものも消えてしまう・・・ */
mysql> execute x;
ERROR 1243 (HY000): Unknown prepared statement handler (x) given to EXECUTE

注意点 3 すべてのSQLで使えるわけではない

全てのSQL構文で使えるわけではなく使えないSQL文もあります。代表的なところをまとめます。

object create alter drop
table OK OK OK
view OK NG OK

マイナーどころだと、

OK?
call OK
load data infile NG

プリペアード・ステートメントが利用できる詳細範囲は マニュアル 参照のこと。

試験用チェックリスト

  • MySQL では SQL 文を再利用する仕組みとしてプリペアード・ステートメントがある
  • 構文は、prepare hoge from '[sql-text]'
  • 変数(プレースフォルダ)を使うこともできる
  • [注意] 再接続すると定義したプリペアード・ステートメントは失われる
  • [注意] 上書き時にタイポすると上書き前のプリペアード・ステートメントが消える
  • [注意] ALTER VIEW や LOAD DATA INFILE は使えない