phpの中での読みやすいSQLの書き方

Question

php＋MySQLでプログラムを書いているのですが、長いSQL文・間にif等が入るSQLを書くときにどう書いたら読みやすいかイマイチよくわかりません。
今は、下のように書いてるんですが、みなさんはどうされてますか？

あと、SQLは大文字・小文字どちらで書きますか？
テーブル名を大文字小文字を区別する設定になっているので、テーブル名やカラム名を大文字、それ以外を小文字で書いています。(読みやすいかと思って)

//条件が何もない時 下の書き方と統一したほうがいいか迷う
$sql = "select "
        ."     COL1 "
        ."     COL2 "
        ." from TABLE_NAME "
        ." where 1=1 "
        ." and COL1 = 1 "
        ." order by COL2 desc ";

//if等が間にあるとき 
//「$sql.=」を何回も書くのが面倒ですが、一部コメント化したりするときに楽。セミコロン気にしなくていい
$sql = "";
$sql .= " select ";
$sql .= "     COL1 ";
$sql .= "     ,COL2 ";
$sql .= " from TABLE_NAME ";
$sql .= " where 1=1 ";
$sql .= is_null($col1) ? " and COL1 is null " : " and COL1 is not null ";
if (is_null($col1) { //1行で収まらないとき
    $sql .= " and COL1 is null ";
} else {
    $sql .= " and COL1 is not null ";
}
$sql .= " order by COL2 asc ";

select COL1,COL2 from TABLE_NAME where 1=1 
--and COL1 is null
--and COL1 is not null
order by COL2 asc

select COL1,COL2 from TABLE_NAME where 1=1 and COL1 = 1 order by COL2 desc

Answer 1

自分は、クエリビルダーを用いて生成するため、生のクエリを直接書くことは少ないですが、
書く場合には、ヒアドキュメントで記述することが多いです。

下記の場合、パラメータを直接入れていますが、SQLインジェクションに繋がる危険性があるため、パラメータはバインドして実行するようにしたほうがいいかと思います。

$sql = <<<SQL
SELECT `COL1`, `COL2`
FROM `TABLE_NAME`
WHERE 1
AND `COL1` = '{$hoge}'
ORDER BY `COL2` DESC;
SQL;

if文が存在する場合

$andCondtions = array();
if (is_null($col1)
{
    $andConditions[] = " AND `COL1` IS NULL ";
} else {
    $andConditions[] = " AND `COL1` IS NOT NULL ";
}
$sql = <<<SQL
SELECT `COL1`, `COL2`
FROM `TABLE_NAME`
WHERE 1
SQL;
foreach ($andConditions as $condItem)
{
    $sql .= $condItem;
}
$sql .= " ORDER BY `COL2` DESC ";

Comments

[eabooobe_1048]

バッククォート！今回テーブル名カラム名に大文字を使うルールが決まっていたので見づらいと思ってselect等を小文字にしていたのですがバッククォートで囲めば小文字にしなくてよかったかもしれないです。次にまた同じような機会があったら使わせていただきます。

複数行はヒアドキュメント、一行なら代入と使い分けですね。
foerachでSQLの部品をくっつけるのスマートです！私の書き方で複数項目の検索クエリをかくとSQLの初めと終わりが離れすぎてしまうことが多くて…。
真似させていただきます！

Answer 2

生SQLを書く場合、最近はこれで統一しています。
SQL の一部をコメントアウトする場合は、
行頭に -- （SQLのコメント）を入れます。

$sql = "
SELECT
    hoge
FROM
    huga
WHERE
    a = 1
-- AND
--    b = 2
";

大文字小文字はお好みで。
外部からの入力値を SQL に埋め込みたい場合は SQL Injection の問題が出てきますので、とりあえずは prepared statement が楽です。

Comments

[eabooobe_1048]

SQL側をコメント化するというのは盲点でした。
文字数が少なくまとまって見えてキレイです。
全部の行に結合演算子ついてる？とか気にしなくていいのはとても楽ですね！
真似させていただきます！

Answer 3

質問者の書き方でも別に問題ない気がします。
自分ならsqlも含めて複数行にわたる文章は、
基本的にヒアドキュメントで書くことを推奨しています。

if (is_null($col1)) {
$col1 = "null";
} else {
$col1 = "not null";
}
$SQL = <<<SQL
select
COL1 ,COL2
from TABLE_NAME
where 1=1
and COL1 is {$col1}
order by COL2 asc
SQL;

ただ、この手法、SQL文を管理する、読みやすくするという視点では非常にいいのですが、
ヒアドキュメントのインテンドがしにくい、コード全体が読みにくいということで、
実際に記述する側の立場の開発者からは、不評だったりします。

SQL文の大文字小文字等の細かい書式は使ってるデータベースの公式マニュアルの
書式に合わせるのが普通だと思いますが、、正直統一されてればどっちでもいいかと。

Comments

[eabooobe_1048]

SQL書くときはtryとcatchの中で書くことが多くて、ヒアドキュメント使うとインデントが崩れる(？)感じがしてなかなか好きになれなかったりします。
hottaさんのご回答と似ていますね。hottaさんのご回答はタブ入れるとインデント崩れ(？)なく書ける・普通の代入と同じでわかりやすい、ヒアドキュメントだとスペース入れ忘れる心配がないのとechoして確かめるときに改行入ってて読みやすいですね。
参考になります！

一番身近なお手本を忘れていました。公式マニュアルもやっぱり構文が大文字ですね。
これからは書き方もきちんと見るようにします。今回のプロジェクトは仕方ないので統一感重視で次に活かしたいと思います。
ご回答ありがとうございました。

Answer 4

生のSQL文をPHPのスクリプトの中に直接埋め込むより、Prado SQLMap (http://www.pradosoft.com/demos/sqlmap/) のようなものを使う方が良いと思います。なぜなら、後日、SQL文の不具合に気付いたとき、同種の不具合が他にないか横展開して探す場合に、SQLMapのようにSQL文が別ファイルにまとめて記述されている方が、抜け漏れなく探しやすいからです。

そして、私もSQLはSELECTなどの予約語を大文字で書き、カラム名やテーブル名は小文字にしています。
20年ほど前に読んだOracleのコーディング規約で、そのように提案されていて、実際に使いやすく、困ったことがないからです。

Comments

[eabooobe_1048]

Prado SQLMapのURLのコード部分をすこし読んでみましたが、XMLにSQLを保存しておいて呼び出すようなイメージでしょうか？
もしそうなら、生のSQLを読み書きすることに慣れている上の人も説得しやすそうです！！
勉強してみます！

コーディング規約を探すというのは盲点でした。
書き方で迷った時はまずコーディング規約を探してみる方が迷わなくていいですね。
Oracleのコーディング規約がそうなっているなら従おうと思います。

Answer 5

ご質問の内容ですと私なら

if (is_null($col1)) {
$col1 = "null";
} else {
$col1 = "not null";
}
$sql = "";
$sql .= " select ";
$sql .= " COL1 ";
$sql .= " ,COL2 ";
$sql .= " from TABLE_NAME ";
$sql .= " where 1=1 ";
$sql .= " and COL1 is {$col1} ";
$sql .= " order by COL2 asc ";

こう書きますかねぇ。
こうすれば、コードは読みやすくなりますので。

実際にはエスケイプを行わなければならないので、DBアクセスは完全に抽象化したDataMapperを使っていますから、SQL文を書くことはほとんどありませんが…

Comments

[eabooobe_1048]

先にSQLの部品を作ってから結合すると、読んでる時に「この変数(SQL)の内容は？」って上に戻って確認する、みたいな作業が面倒かなと思って途中に挟んでたんですが、先に部品を作った方が読みやすいみたいですね。参考にさせていただきます！

DataMapperもSQLは書かずにDBを操作できるライブラリ？でしょうか。
こちらも習得が難しそうですが、勉強してみます！

Answer 6

回答というよりただの雑談になってしまいますが、私の場合はキャピタライズは逆ですね。
MySQLに由来するものは大文字、自分で定義したワードは小文字にしています。
理由は以下の通りです。

・phpは基本的に変数名を小文字スタートにするのがスタンダードなようなので、DBのカラム名もそれに統一している
・小文字より大文字の方が目に入りやすいので（個人的な感想です）、ORやANDなどがはっきり確認でき、書いた条件の把握が速い

長いSQLの書き方に関しては、最近はもっぱらCakePHPを使っている事と、それ以前もサニタイズ忘れ等を防ぐためにオレオレフレームワークで動的に生成していたので、生で書くことはほとんどなくなりました。
生で書いていた頃は、1つの変数に一気に代入せず、1行に収まる範囲の要素ごと（JOIN節やWHERE句など）に別々の変数に代入し、最後にまとめて連結させていました。

Comments

[eabooobe_1048]

やっぱり大文字小文字は逆でしたか…。
>ORやANDなどがはっきり確認でき、書いた条件の把握が速い
これが一番大事なことですね。
他のプロジェクトとDBを共有していてテーブル名・カラム名を大文字でつけるルールになっていたので逆にしていました。次からは小文字でつけさせてもらおうと思います。

CakePHPを勉強したことがないので少し調べてみたのですが、SQL自体は書かずに結果を取得したりDBの操作が出来るというイメージでしょうか。
「生のSQL」という言葉があるということはphp内にSQLを書くことは一般的ではないみたいですね。不勉強で恥ずかしいです。
習得が難しそうですが勉強してみます！

[terushu]

いえいえ、慣れの問題ですから、小文字のor, andを識別する方になれていたら、それはそれでご自身には問題ないのです。
ただ、納品するようなものとなると、大文字の方が一般的かな、とは思います。
もちろん、逆の方、全て小文字の方もおられます。ただ、全て大文字の方はほとんどお見かけしません。

CakePHPは「楽をするため」のフレームワークです。
MVCモデルを採用はしておりますが、MVC本来の目的はあまり満たしていません。
その辺が逆にプログラマーのセンスが問われるので、客先にコードをお見せするときに緊張するところでもあります。

生のSQLを書くこと自体は恥ずかしい事ではありません。
パフォーマンスを考えたら、生で書けるのが理想ですし、mysqlのクライアントから直接コマンドを叩けないと仕事になりません。
まずは生でSQLを書く知識、技術をしっかり習得なさるのがよろしいかと思います。
その上で楽をする、及びミスがあっても一応は安全なクエリを発行するためにSQLを動的に生成（フレームワーク側でキャッシュなどはされます）するようなものを利用するのがベストです。
なので、CakePHPもSQLそのものに理解がなく漠然とパラメータ配列を並べていると、パフォーマンスに大きな違いが出てくるケースもあります。

ただ、他の方がおっしゃっていたphpのプリペアードステートメント、?の位置とそこに指定されているはずの変数の対比がぱっと見分かりづらいですし、サニタイズ（エスケープ）しなくても良いところまで漫然と指定してしまったりするので、最初は自分で本当に必要な箇所をサニタイズしていくのが理解を深める役に立ちます。