PHPのテクメモ

少し前に設計・開発期間が当初の予定より大幅に短くなってしまった案件があった。しかし諸事情で納期は絶対ということもあり、何とか形にしたけれど、当然バグがあるし、月日がたてばボロが出てくる。バグはその場で直していけばよいのだが、データ量が増えてウェブアクセスが重くなったりする部分はそもそも根本の問題なので簡単には直すことが出来ない（設計出来てないわけで・・・）。

現在その問題を解決するにあたり、SQL文を練りなおしている。当時は考える時間がまったくなかったし（とにかくプログラムを書き進めないと絶対間に合わないという状況だった）、データベースをデータ格納庫としか思っていなかったので、結果としてデータがたまってくると複雑な処理をしているところが遅くなってきているのだ。まぁ、こういう問題を経験したからこそ、複雑なシステムを組む際は速度面を考慮してデータを作成しているのだが・・・。

で、ちょっと複雑（専門家にとってみればまだまだ短いのだが）なSQL文を3時間ほどかけて作り、うまく動いて喜んでいたところ、「あ、ちょっとVIEWにしてみよ」っと思ったけどこれが出来ない。MySQLはバージョン5からVIEWが使えるようになったのだが、実はVIEWをほとんど使っていなかった・・・。PostgreSQLなんかは覚えたてのころ（バージョン6とか7だったかな）からVIEWが使えていたわけで、最初からそういう機能があったのでよく使っていたけど、MySQLみたいに途中から追加された機能って勢いがないと踏み込めなくって・・・と言い訳してみる。で、VIEWが作れなくて調べたらマニュアルに記述。

ビューのFROM句でサブクエリを使用することはできません。この制限はいずれ取り除かれる予定です。

「あらまー」。もしくは「ふーん」。一言で終わってしまった。前置き長すぎた。

教えてもらったことをメモ。

とりあえず「単なるデータの格納庫」として、データベースを使っている。MySQLはバージョン5からVIEWが使えるようになった。作り方や使い方はわかっているが「実際のところVIEWって何」と根本的なことが気になった。MySQLのVIEWについて人に教えてもらった。

MySQLのVIEWは大きく2タイプに分かれる（作成時に指定でき、未指定の場合は自動選択される）。

• MERGE
  SQLを記述する際にVIEWを呼び出すと、VIEWを定義したSQL文が、当該VIEWを記述した部分に割り当てられて実行される。つまりSQL文に仮の名前をつけておくような感じ。
• TEMPTABLE
  TEMPTABLEを作成して、そこにVIEWで定義したSQL文の結果を展開し、それがテーブルとして扱われる。

TEMPTABLEもイメージとしてはわかっていたのだが、よく理解していなかったので調べてみた。当たり前だがマニュアルに書いてあった。

MySQL バージョン 3.23 以降では、テーブルの作成時に TEMPORARY キーワードを指定することができます。テンポラリテーブルは現在の接続の間のみ有効で、接続が閉じると自動で削除されます。そのため、異なる 2 つの接続が同じテンポラリテーブル名を使用できます。この場合、それぞれの接続のテンポラリテーブル間でコンフリクトが発生したり、同名の既存のテーブルとの間でコンフクリトが発生したりすることはありません（既存のテーブルはテンポラリテーブルが削除されるまで表示されません）。

現在の接続の間のみ有効ね。ということはpconnectすれば、結構な時間使えるということになるのかな。って・・・pconnectもイメージでしかわかっていない。これも調べておくか。

なにはともあれ、VIEWを使う1番のメリットはSQL文の可読性をあげることのようだ。またTEMPTABLEの場合は、場合によっては速くなることもあれば遅くなることもあるようだ。

機能のメモをしていて調べてみると、いろんな意見があった。記述忘れもあったのでメモしてみる。utf8_general_ciとutf8_unicode_ciの違いの続き。

まずutf8_general_ciのほうが高速と書いてあるサイトもある。正確かどうかというのは「何を持って正確とするか」という定義に依存するだろうから議論しないけれど・・・、どっちが速いんだろう。好みの動作という点ではutf_general_ciなんだけど。

utf8_unicode_ciでもう一つ。「は」「ぱ」「ば」「ハ」「パ」「バ」なんてのも全部同一視するらしい。ゆるい。こういうのを同一視しようと思ったらそれなりに大変なような気がするから、こっちのほうが速度が遅いような気もするな。

んー。あとSET NAMESの設定とか、なんか、いろいろ考えたら、もうちょっとデータベースのことや文字コードのことをしっかり勉強しなきゃいけないなぁと痛感する今日この頃だ。こういうことを勉強してくれる立派な家来をみつけなくては（他力本願）。

基本的に今は文字コードを全てUTF-8で統一するようにしている。もともとPHPはシフトJISが得意でないし、いまさらEUC-JPを使う理由もない。自分のウェブサイトを構築する場合は、携帯サイトを除いて出力もUTF-8だ（ウェブサービスとの親和性もいいし）。必然的にデータベースもUTF-8でデータを格納しているわけなんだが。

MySQLでいつも迷う点がある。いわゆる照会順序ってやつ。SQL serverなんかでは照合順序って名前になってることもある。照会順序とは「言語種別」と「ロケール」に基づく文字列の比較方法の規則のこと（らしい）。文字列の比較だからWHERE句での比較はもちろんだし、ORDER句の並び替えにも影響するので、希望通りの結果を得たい場合はしっかり設定しておかないといけない。でも、これ、いろいろあるんだな。

文字列に対しての影響を及ぼすわけだから、数値型カラムなどは関係ない。varchar型やtext型で設定しなくてはいけない（つまりカラムごとにも設定できるっぽい）。それにデータベースのデフォルト照会順序も設定できるし、テーブルでも照会順序を設定できる。照会順序で設定できる値は大量にあるのだが、日本語環境でUTF-8を使っている場合に設定するであろう値は以下の3つだ。

• utf8_bin
• utf8_general_ci
• utf8_unicode_ci

最初のはbin。これはバイナリ（binary）レベルで検査してくれる。バイナリで見てくれるわけだから、絶対確実。間違いなく期待通りの答えを返してくれそうな感じ。

あとの2つは両方とも後ろに_ciとなっているが、これは「case insensitive」の略。つまり大文字小文字（upper caseとlower case）を区別しない（「sensitive：敏感」でない）という意味だ。だからバイナリレベルでのチェックではないことは明らか。これだけ見ればbinがいいような気がするけれど、マルチバイトもシングルバイトも関係なくてバイトオーダーで検査するので、ひょっとしたら希望通りの動作にならないことがあるかもしれない。

で_ciな二つだが、前者（general）は英数の全角半角の判断くらいはしてくれるらしい（大文字小文字は判断しない）のに対して、後者（unicode）は全角半角大文字小文字も判断しないらしい。例えば文字列比較で以下のような例を考える。

1. （半角大文字のA）=（全角大文字のA）
2. （半角大文字のA）=（半角小文字のa）

前者（general）だと1はfalse、2はtrueだが、後者（unicode）だた1もtrue、2もtrueになる。どんな動きをしてほしいかということは、用途によってそれぞれだから一概にどちらが正しいとは言えないけれど、ちょっとややこしい。でも、前者のほうがなんとなく正確っぽいので、より厳密なのかなと思う。後者はなんとなくあいまいっぽいので、処理が簡単そう。だから正確度ならgeneral、速度ならunicodeということになるだろうか。

用途によるんだけど、まぁ、どれを使っても、一般人の考えているとおりの動作はしてくれなさそうだ。がんばって使い分けるしかない。

いつもよく使うMySQL。特定カラムでソートするときのTipsというか備忘録。

本来は数値で入力しておくべきカラムなのだが、わがままなクライアントが時として文字列を入力したいということがあるので、とりあえずvarchar型を採用することにしたのが、そのカラムでソートする必要が出てきてしまった。

SELECT * FROM myTable ORDER BY myColumn DESC;

普通にこんな風に描いてみたけど問題が出た。たいていの場合、そこには二桁の数字が入るのだけれど、まれに三桁の数字が入ることがある。そのときに期待通りに並ばない。例えば90と100という”文字列”が入っていたときに、上記のSQLなら100のほうが上位になってほしいのに下位になってしまうのだ。文字列で判断しているわけだから最初の文字「9」と「1」で判断して、そういう結果になるのは当たり前なのだが・・・、100のほうが上位になってほしい。

そんなときに使用する関数（MySQLの）がCASTだ。名前のとおり型変換（型のキャスト）をしてくれる関数だ。上記のSQLを以下のように書き換えてうまく動作してくれた。

SELECT * FROM myTable ORDER BY CAST(myColumn as signed) DESC;

すっきり。でもこれ、行数が多いと時間かかりそう。