SQL
SQL BLOB の容量を計算する方法
1OpenPNE のカスタマイズをしていて会員毎に画像容量の制限をする必要がでてきました。
quota を使用できれば簡単なのですが、OpenPNE は DB に バイナリデータとして画像を保存しているため使用できません。
そこで SQL で容量を計算する方法を考えました。
LENGTH 関数を使用して
SELECT LENGTH(blobカラム)
とすればバイト数を計算できます。
OpenPNE の場合は c_image というテーブルに画像データが格納されています。
そこで特定の会員の日記に使用した画像の容量を計算するのに下記のようにしてみました。
SELECT SUM(LENGTH(i.bin)) FROM c_image as i LEFT JOIN c_diary d ON(d.image_filename_1=i.filename OR d.image_filename_2=i.filename OR d.image_filename_3=i.filename) WHERE d.c_member_id=会員ID
得られる値は byte 数になります。
MySQL で NULL を一番最後にして昇順にソートする
42008.2.27 追記
コメントで教えていただきました下記方法で簡単にできました。
is null asc の指定と 通常の asc の指定をするのがポイントですね。
SELECT id, comment FROM table ORDER BY comment IS NULL ASC, comment ASC;
—–追記ここまで—–
MySQL で昇順にソートすると NULL は一番最初に来ます。
それを最後にできないかということで下記のようなSQL を考えてみました。
SELECT id, comment, CASE WHEN comment IS NULL THEN 10000 ELSE ASCII(LEFT(comment,1)) END AS dummy FROM table ORDER BY dummy ASC, COMMENT ASC
comment カラムを昇順でソートして、NULL の場合は一番最後に NULL がきます。
あまりスマートな方法ではないのですが、これで取り合えず NULL を最後にして昇順でソートするということができました。
ORACLE とかだと下記のように簡単にできますね。
ORDER BY comment ASC NULL LAST
NULLS FIRST
NULL 値を順序の最初にするソートを行なう
NULLS LAST
NULL 値を順序の最後にするソートを行なう
ランダムに抽出する SQL
4ランダムに10件 SELECT する SQL です。
ORDER BY RAND() でランダムにソートして LIMIT 句で取得したい件数だけ取得します。
SELECT * FROM table ORDER BY RAND() LIMIT 10;
ランダム関数は MySQL, PostgreSQL, SQLServer で使用できます。
ランダム関数は
MySQL, SQLServer は RAND()
PostgreSQL は RANDOM()
です。
ランキングを取得するSQL
3ランキングを取得するSQL です。
同じスコアの場合は同じ順位にするなどの必要があるため結構面倒ですが、
下記SQL でイッパツで取得できます。
実際に携帯ゲームのランキングを取得するのに使用しているSQLです。
ranking テーブル
CREATE TABLE ranking ( id bigint(20) NOT NULL auto_increment, name varchar(20) NOT NULL, score bigint(20) NOT NULL, );
SQL
SELECT r1.name, r1.score, (SELECT count(r2.score) FROM ranking as r2 WHERE r2.score>r1.score)+1 as rank FROM ranking as r1 ORDER BY r1.score DESC LIMIT 10;
rank というカラム名で順位が計算されます。
同じスコアの場合は順位は同じになります。
上記SQL はトップ10を取得していますが、実際にはLIMIT句を変更することにより
ページング処理などで下位のランキングを見れるようにしています。
副問い合わせで順位を計算しています。
MySQL ではバージョンが4.1 以降でないと副問い合わせが使用できないので注意してください。
※本運用ではインデックスを適宜作成しています。