PHPのテクメモ

いつもよく使う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;

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

開発を始めて間もない頃に作ったサイトをリニューアル中。まだまだ駆け出しの頃だったので、プログラムもデータベースの構造もひどいものだ（だからサイトが重い）。そんなサイトでも月間数万ページビューあるのでリニューアルすることにしたのだ。

プログラムは全て新しく書き直すので、以前のプログラムはまったく気にならないけど、データベースの構造が大変。古いのをそのまま持ってくるのも重そうだし。新しく設計しなおしてデータをコンバートする過程で問題が起きた。テーブルに主キーを設定していなかったので、データの重複が起こっていた。そもそもプログラム側でも特に制御していなかったし、重複が起こっても当時のプログラムでは問題にならなかったのだが、今回はしっかりと主キーを設定したが故に、一筋縄では移行できなくなってしまったわけだ。

データの重複を削除する方法をウェブで探したら、ちゃんと見つかった。

CREATE TABLE temp as SELECT * FROM original GROUP BY column1, column2
DROP TABLE original
ALTER TABLE temp RENAME TO original

最初に、元のテーブルから全ての列をグループ化して、別名でテーブルを作る（この時点で重複行は一つにまとめられている）。次に元のテーブルを消して、最後にテーブル名を変更してそれで完了。ちょっとキツネにつままれたような感じだけどすごく簡単にできた。ほんとに大丈夫なのかどうかわからないんだけど、とりあえずこれでよしとした。

最近はあまり使うことはないのだが、以前はカラムを定義する際にdatetime型やdate型をよく使っていた（最近はint型にしてunix timestampでデータを入れるのが好き）。当然のことながら昔作ったプログラムをリニューアルするとき、違和感を覚えることがある。そんなときに便利な関数がunix_timestamp。例えばSELECT文で、以下のように使用する。

SELECT unix_timestamp(timestamp_type_column) FROM table

これでdatetime型やdate型の値をunix timestampにして返してくれる（ちなみにその逆の関数はfrom_unixtime）。SQL文を発行する際にその都度関数を呼び出してもいいだろうけど、それではオーバーヘッドがバカにならないこともある（もちろん相応の負荷があるサイトの場合だが）。なので、元のテーブルを、上記の関数やその他の関数を使って一旦吐き出し、新しいテーブルを作ってそこにデータを流し込むほうがいい。でも、型の変更が原因で諸々問題が出る可能性があることを理解して使わないといけないけれど。

最近は結構SQLiteを使うことが多い。PHP5という前提はあるけれど（もちろん条件がそろえばPHP4でも大丈夫なんだけど）。レンタルサーバを使う際に、MySQLの場合はデータベースの個数に制限がある場合がほとんどだけど、SQLiteなら何個データベースを作ってもOKだから気兼ねなく使える。

でもいまだに慣れないところもある。バージョン2系と3系の違いがあるのかもしれないけれど（いろいろ試したわけではないのでよくわからない）、バージョン2で連番生成させたいときの条件が難しかった。単にデータベースの操作ツールがsqliteManagerだったからわかりにくかったのかもしれないけれど。連番生成のための条件をメモしておく。

• 主キーであること
• データ型がINTEGER
• サイズを指定しない
• NULL許可

このカラム設定にしておいて、データのINSERT時に主キーをNULLで登録すればOK。

PHP4系だと、どうしてもSQLite2系を使わないといけない場合があって面倒だ。PHP5系ならSQLite3系で、管理ツールにFireFoxのプラグインの「SQLite Manager」を使って問題なくいろいろな操作ができるんだけど（しかし管理ツールの名前が同じってのは区別しにくい）。

データをランダムな順番で取得するには以下のorder句をつければいいということは前に書いた。

order by rand()

これで確かにランダムな順番でデータを吐き出してくれるのだが、SQL実行毎に順番が変わってしまう（当たり前）。ページング処理を実行したい場合は、1ページ目と2ページ目を表示する際に、順番に（例えば）10件ずつデータを取ってくるわけだが、そもそも順番がめちゃくちゃなので、1ページ目のデータが2ページ目でまた出現してしまう、といったことが起きてしまう。どうすればよいか。解決策がコレ。

order by rand(n)
nは適当な数値

詳しくは調べていないがnは乱数を発生させる際のseed値のようなもの。このseed値が同じであれば、並び順は常に同じになるのだ。なのでデータ取得の一番最初のときにこのseed値を初期化して、以降のページングの際はこのseed値を持ちまわってSQL文に仕込んでやればよいということになる。