JOIN句を含むSQLも楽に扱える！PHPからMySQLを効率よく使う方法

PHPからMySQLに対してJOIN句を含むSQLを実行すると必ずと言っていいほど失敗していた、という経験のある方は多いようです。実は、私もコツをつかむまでは苦労しました。今回は、PHPとMySQLを用いたシステムで、JOIN句を含む複雑なSQLを実行するために気を付けたいポイントについて紹介します。

1 SQLのデバックを侮ってはいけない
2 PHPのPDOを利用してデバックを効率化
3 PHPによるSQLを効率良くデバックする方法
- 3.1 SQLに埋め込む文字列の扱いの注意点
- 3.2 SQLはヒアドキュメントに記述する
4 PHPから実行するSQLの注意点
- 4.1 SQLのjoin句は時間がかかる
- 4.2 SQLのチューニングの基本
5 まとめ

SQLのデバックを侮ってはいけない

PHPに限らずプログラミングに慣れてくると、文字列を作ったり、加工することが簡単だと思えてきます。プログラミングを学び始めた方の中には、これなら、PHPのプログラムでMySQL用のSQL合成は、簡単だと思う方もいるでしょう。

しかし、そうやって合成したSQLをMySQLに送ると、最初は必ずと言っていいほどエラーが発生します。しかも、プログラムの中で合成したSQLのため、どこでエラーになったか解りません。SQLのデバックは想像以上に大変です。こうして、多くの方が、無駄な時間を費やしてしまいます。

SQLを、短い文字列の組み合わせだと侮ってはいけません。些細なミスでエラーが発生し、しかも、エラーの原因を調べるのは、意外に時間がかかります。PHPからMySQLに対してSQLを実行するシステムを作るのなら、エラーになりにくい書き方をマスターしましょう

PHPのPDOを利用してデバックを効率化

PHPでMySQLを利用する場合、特に指定が無ければPDOを利用します。なお、PDOとは、PHP Data Objects拡張モジュールの略で、PHPの中からデータベースにアクセスするためのインターフェイスを提供します。次から、PDOを使うことで、join句を使う複雑なSQLのデバックが楽にできることを解説します。

PHPのPDOでMySQLにアクセスする方法

多くのデータベース管理ソフトに対応しているPDOは、MySQLにも対応しているので、接続に必要な情報を指定するだけで、SQLを利用できます。また、スクリプトを終了した時点で自動的にデータベースと切り離してくれるので、接続解除の処理も不要です。

通常、PHPのPDOからMySQLに接続するには、次のように記述します。

<?php

/*
 * 同じサーバー(localhost)のMySQLにある、データベースtestdbに接続する例
 * アカウントとパスワードは、それぞれ$dbaccountと$dbpasswdに格納されています
 */

$pdo = new PDO('mysql:host=localhost;dbname=testdb', $dbaccount, $dbpasswd );

?>

<?php

* 同じサーバー(localhost)のMySQLにある、データベースtestdbに接続する例

* アカウントとパスワードは、それぞれ$dbaccountと$dbpasswdに格納されています

$pdo = new PDO('mysql:host=localhost;dbname=testdb', $dbaccount, $dbpasswd );

プリペアドステートメントを活用する

SQLは、簡単な文字列の組み合わせですが、join句を使ったり、サブクエリーと組み合わせるなど、複雑になればなるほどデバックが難しくなります。これは、SQLが、書かれた順番通りに実行される訳ではなく、MySQLからのエラーメッセージが解り難いといった問題があるためです。

そのため、あらかじめ動作することを確認したSQLの一部を変数に置き換えできる、プリペアドステートメントを活用してください。また、プリペアドステートメントには、データベースから不正にデータを抜き出したり、破壊する攻撃に対しても有効です。

プリペアドステートメントの例
/*
 * プリペアドステートメントにより、flg=1となるSQLで、PHPからMySQLのデータを取得する例
 */
$sql = "select id, name from list1 where flg = :flg";
$sth = $pdo->prepare($sql);
$sth->bindValue(':flg', 1, PDO::PARAM_INT);
$sth->execute();

プリペアドステートメントの例

* プリペアドステートメントにより、flg=1となるSQLで、PHPからMySQLのデータを取得する例

$sql = "select id, name from list1 where flg = :flg";

$sth = $pdo->prepare($sql);

$sth->bindValue(':flg', 1, PDO::PARAM_INT);

$sth->execute();

PDOを使うことでSQLのデバックも容易に

PDOでは、エラーが発生するとPDOExceptionをスローするので、catchブロックでエラーメッセージを表示させることが可能です。PHPからMySQLにアクセスしたエラーを確認するには、サーバーのログファイルをチェックするなど、かなり面倒なことになります。その点、エラーやワーニングが発生した際の時に詳しいメッセージを表示できれば、デバックを楽に行えます。

次に、catchブロックでPDOExceptionでスローされたメッセージを表示する例を紹介します。なお、エラーを表示した後は、die()でPHPを終了させ、スローされたメッセージをチェックできるようにしておきましょう。

<?php
/*
 * PDOの処理中に発生したMySQLの例外は、PDOExceptionでスローされるので、
 * PDOによる接続をSQLの実行時の例外を try catch で受け取り、
 * 例外時のメッセージを表示し、die()で処理を終了する例
 */
try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', $dbaccount, $dbpasswd );
    print_r( $pdo->query('select id, name from list1') );
} catch (PDOException $e) {
    print "エラー!: " . $e->getMessage() . "<br/>";
    die();
}
?>

<?php

* PDOの処理中に発生したMySQLの例外は、PDOExceptionでスローされるので、

* PDOによる接続をSQLの実行時の例外を try catch で受け取り、

* 例外時のメッセージを表示し、die()で処理を終了する例

try {

$pdo = new PDO('mysql:host=localhost;dbname=testdb', $dbaccount, $dbpasswd );

print_r( $pdo->query('select id, name from list1') );

} catch (PDOException $e) {

print "エラー!: " . $e->getMessage() . "<br/>";

die();

}

PHPによるSQLを効率良くデバックする方法

先ほど、PHPからMySQLを利用する場合、PDOを利用するとデバックが楽に行えると解説しましたが、それだけではありません。次から、PHPからMySQLを利用する場合、不用意にミスしないSQLの書き方や、PHPの機能を活用したデバックの方法について解説します。

SQLに埋め込む文字列の扱いの注意点

SQLは、使える文字に制限があり、特に列名で使える文字の制限が厳しく、数字や特殊文字が使われているとエラーになります。データベースを作成する場合、そのような列名を避けるのが正しい使い方ですが、中にはわざとそのような文字を使っているケースもあります。

そのため、PHPからMySQLを利用する場合、用意したSQLのテーブル名やフィールド名をバッククォートでくくる、といった処置でエラーを防ぎましょう。

-- バッククォートを使ったSQLの例
select a.`1001`, a.`1002` b.`2002`
 from `1_table` as a inner join `2_table` as b
 where a.`1002` = b.`2001`;

-- バッククォートを使ったSQLの例

select a.`1001`, a.`1002` b.`2002`

from `1_table` as a inner join `2_table` as b

where a.`1002` = b.`2001`;

SQLはヒアドキュメントに記述する

SQL程度の短い文字列なら、プログラムを使って簡単に合成できるものの、デバックやプログラムの可読性を重視すれば、完成したSQLをそのまま記述すべきです。そこで、使われるのが、ヒアドキュメントです。

先ほど説明したPDOでは、SQLに変数を埋め込むことで、後からそれを指定できます。そのため、PDOはヒアドキュメントで書いたSQLと相性がよく、join句が扱われるような複雑なSQLでも、改行を入れて見やすく書けるので、よく使われる方法です。

<?php
// PHPのヒアドキュメントで書いたSQLの例
$sql = <<<EOF
select a.`1001`, a.`1002` b.`2002`
 from `1_table` as a inner join `2_table` as b
 where a.`1002` = b.`2001`;
EOF;
$stmt = $pdo->query($sql);
?>

<?php

// PHPのヒアドキュメントで書いたSQLの例

$sql = <<<EOF

select a.`1001`, a.`1002` b.`2002`

from `1_table` as a inner join `2_table` as b

where a.`1002` = b.`2001`;

EOF;

$stmt = $pdo->query($sql);

PHPから実行するSQLの注意点

簡単なテストデータを使ったデバックでは問題なかったものの、本番に近いデータを使ったとたん、エラーが多発して動作しない、というケースはよくあります。このようなケースを避ける方法を紹介します。

SQLのjoin句は時間がかかる

最近は、サーバーの性能が上がっているので、MySQLでデータベースを検索したとしても、それなりの速度が出るようになりました。しかし、残念ながらMySQLのデータベースエンジンは、早い方ではありません。データが少ない時点では、全く問題にならないSQLでも、大量のデータを扱う本番環境では、処理が遅くなることもあります。

特に、join句をサブクエリーで多用すると、データの量によってはかなり遅くなることがあります。そのような場合は、PHPの書き方をいくら変更しても改善できません。この場合は、SQLのチューニングが必要です。

SQLのチューニングの基本

SQLのチューニングについては、それだけで1冊の本が書けてしまうほど、多くのスキルがあります。これは、SQLは、簡単な記述で複雑な処理を指示できる反面、内部の動作を細かく指定できないことが原因です。

ただし、大量のデータに対して演算させれば、それだけ多くの時間がかかることは、誰でも理解できるでしょう。join句やサブクエリーを使うようなSQLでも、それは同じです。中間データをイメージし、できるだけ少ないデータに絞ってから、演算させるSQLを作りましょう。

次に、これまでの解説を使った、PHPのPDOを利用し、MySQLからデータを取り出す処理の例を紹介します。

$sql = <<<EOF
select a.`1001`, a.`1002` b.`2002`
 from `1_table` as a
  inner join
    (select id, `2001`, `2002` from `2_table` where `2001' = :str ) as b
 where a.`1002` = b.`2001`;
EOF;
try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', $dbaccount, $dbpasswd );
    $sth = $pdo->prepare($sql);
    $sth->bindValue(':str', $keyword, PDO::PARAM_STR);
    $sth->execute();
  } catch (PDOException $e) {
    print "エラー!: " . $e->getMessage() . "<br/>";
    die();
}

$sql = <<<EOF

select a.`1001`, a.`1002` b.`2002`

from `1_table` as a

inner join

(select id, `2001`, `2002` from `2_table` where `2001' = :str ) as b

where a.`1002` = b.`2001`;

EOF;

try {

$pdo = new PDO('mysql:host=localhost;dbname=testdb', $dbaccount, $dbpasswd );

$sth = $pdo->prepare($sql);

$sth->bindValue(':str', $keyword, PDO::PARAM_STR);

$sth->execute();

} catch (PDOException $e) {

print "エラー!: " . $e->getMessage() . "<br/>";

die();

}

まとめ

PHPは、MySQLなどのデータベースと相性が良いことから、多くのWebシステム開発に使われています。しかし、初心者にとってSQLのデバックは難しく、PHPからjoin句を含むような複雑なSQLを使ってMySQLを利用する場合はなおさら難しくなります。

そのため、今回紹介したPDOを上手く活用し、PHPとMySQLを活用したシステムを効率的に開発するためのスキルを学びましょう。

ウェブカツは「年収1000万円」を目指すフリーランスITエンジニアを育成するオンライン最大級のプログラミングスクールとして、元医師やBIG4税理士法人会計士など高学歴高経歴な方から、中卒土方、40代主婦、海外在住者など様々な方が通われています。
現役ITエンジニアとして活躍し、富裕層でプロのファイナンシャルプランナーでもある顧問「かずきち」が教える【フリーランスITエンジニアになって10年で1億の資産を築く方法】など他のプログラミングスクールとは全く異なり、「転職をゴール」とはせずに「会社に依存せずに外で稼げる力」を身につけさせています。