MySQL/PREPAREでPreparedStatementsを作成する

• Prev
• Next
• MySQL

MySQLのPREPAREでPreparedStatementsを作成する方法 †

JavaのPreparedStatementや、PHPの$dbh->prepare(...)などと同じような動作をMySQLを使用してSQL文で実現する方法を以下に記します。

関連記事 †

• PostgreSQLのPREPAREでPreparedStatementsを作成する方法
• MySQLでPREPAREしたクエリーを削除する方法・DEALLOCATE
• PostgreSQLでPREPAREしたクエリーを削除する方法・DEALLOCATE

動作確認環境 †

以下の環境で動作確認を行いました。

• OS
  CentOS6 x86_64
• MySQL
  MySQL 5.1.66

MySQLでPREPAREを使ってみる †

PREPAREを使うとSQLの一時的な関数(のようなもの)を作成することができます。 以下にいくつかのPREPARE使用例を記します。 尚、このPREPAREで指定した設定内容は接続が切れると無くなります。

select now(); を PREPAREしてみる †

mysqlコマンドで接続し以下のように入力します。 select now();をshow_nowという名前で登録します。 execute show_now;でPREPAREで指定したSQLが実行されているのが確認できます。

mysql> PREPARE show_now FROM
    -> 'SELECT now()';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE show_now;
+---------------------+
| now()               |
+---------------------+
| 2013-01-23 22:42:55 |
+---------------------+
1 row in set (0.00 sec)

上記で操作したSQLを添付しておきます。

prepare01.sql

以下のようにすれば端末から実行できます。

$ mysql -u root -p < prepare01.sql
Enter password:
now()
2013-01-23 22:45:37

PREPAREで引数を指定する例 †

以下にPREPAREクエリーで引数を指定する方法を以下に記します。
(以下の記述はデータベース名dbが存在することを前提に記述しています。)

1. データベースdb1を作成しdb1を使用する

   mysql> create database db1;
   Query OK, 1 row affected (0.00 sec)
   
   mysql> use db1;
   Database changed

2. テーブルの作成

   mysql> create table foo (a int, b text);
   Query OK, 0 rows affected (0.03 sec)

3. データのインサート

   mysql> insert into foo values(1,'sakura');
   Query OK, 1 row affected (0.00 sec)

4. データの確認

   mysql> select * from foo;
   +------+--------+
   | a    | b      |
   +------+--------+
   |    1 | sakura |
   +------+--------+
   1 row in set (0.00 sec)

5. PREPAREをつかってins_sqlを定義する

   mysql> prepare ins_sql from 'insert into foo values(?,?)';
   Query OK, 0 rows affected (0.00 sec)
   Statement prepared

6. PREPAREをつかってupd_sqlを定義する

   mysql> prepare upd_sql from 'update foo set b=? where a=?';
   Query OK, 0 rows affected (0.00 sec)
   Statement prepared

7. setで@a, @bに値を設定

   mysql> set @a=2 ;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> set @b='ajisai';
   Query OK, 0 rows affected (0.00 sec)

8. execute クエリー名(ins_sql) using 値...で@a,@bの値をインサート

   mysql> execute ins_sql using @a, @b;
   Query OK, 1 row affected (0.00 sec)

9. ins_sqlで追加されたデータを確認

   mysql> select * from foo;
   +------+--------+
   | a    | b      |
   +------+--------+
   |    1 | sakura |
   |    2 | ajisai |
   +------+--------+
   2 rows in set (0.00 sec)

10. setで@c,@dの値を設定

    mysql> set @c='AJISAI';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set @d=2;
    Query OK, 0 rows affected (0.00 sec)

11. execute クエリー名(upd_sql)でajisai(小文字)をAJISAI(大文字)にアップデートする

    mysql> execute upd_sql using @c, @d;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

12. アップデートした値の確認

    mysql> select * from foo;
    +------+--------+
    | a    | b      |
    +------+--------+
    |    1 | sakura |
    |    2 | AJISAI |
    +------+--------+
    2 rows in set (0.00 sec)

上記で操作したSQLを添付しておきます。

prepare02.sql

以下のようにすれば端末から実行できます。

$ mysql -u root -p < prepare02.sql
Enter password:
a       b
1       sakura
a       b
1       sakura
2       ajisai
a       b
1       sakura
2       AJISAI