MySQLでlimitを使用したデータ件数カウント(SQL_CALC_FOUND_ROWS)

今回はMySQLのTipsです。

Webサイトの検索画面などで、
「５０件／１００００件中ヒットしました。」
という画面を作る場合にとても便利なMySQL関数です。

例：

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;

mysql> SELECT FOUND_ROWS();

これだけでＯＫ。

■１回目のＳＱＬでは、LIMITを含むSELECT文の先頭に
「SQL_CALC_FOUND_ROWS」というオプションをつけて実行します。

これでいつも通り、特定の条件で絞り込んだ結果の先頭ｎ件のデータが取得できます。

データ件数を取得する場合は、PHPならmysql_num_rows()でカウントできます。

■２回目は「SELECT FOUND_ROWS();」という関数をコールします。

これで１回目のSQLでLIMIT指定していない場合の全件数が取得できます。

通常は別途「SELECT count(*) FROM table_name」なんて再度実行する必要が
あるのですがその必要はありません。

よくプログラムでwhere句を変数にもっておいて、
「select a,b,c～」と「select count(*)～」の２種類のSQLにつけて
select文を２回実行するというプログラムを作っていました。

同じ条件のＳＱＬを実行するなんて無駄だな～。と思っていたのですが
MySQLくんは用意してくれています。すごくいい子です。

MySQLのマニュアル
http://dev.mysql.com/doc/refman/5.1/ja/information-functions.html

要するに同じ条件のＳＱＬを２回実行する必要がないため、
超高速です。

MySQLを使用した検索画面を作っている人は、パフォーマンス改善のためにも
とても有効な関数ですので検討してみてください。

MySQLでレプリケーション（前置き）

MySQLの便利な機能はいろいろあるのですが、その中でも特に便利だなと思うのがレプリケーションです。
この機能があるからこそMySQLがここまで支持されているのだと私は勝手に思っています。

もちろん他のＤＢでも同様の機能は存在しますし、ORACLE、SQLserverではもっと高機能なClusterもあります。ただし数千万円というレベルで予算が必要です。

確かに100%のデータを絶対に守らなくてはいけない場合には必要だとは思いますが、それが99%でよければレプリケーションで充分な場合がほとんどです。

Webのアプリケーションの場合、サーバ間のネットワークや、クライアントＰＣの状況など、もともとどんなに完璧なサーバを用意しても100%をカバーするシステムを構築することは不可能です。

もちろんお客様は100%の稼働率を求めてきます、ただし99%でよければのいかに予算を削減できるか、他の機能を増強できるか、しいてはＳＥ、プログラマーがやる気をもって仕事にとりくめるか、どちらがメリットになるか説得してみてください。

お客様の要望どうりに仕事をこなすのがＳＥではありません、お客様と開発会社の両方をハッピーにするのがＳＥの仕事です。

話しがいろいろそれましたが、次回以降に何回かに分けてMySQLのレプリケーションについて書いていこうと思っています。

MySQLでOracleのrownumやSQLServerのTopを実現する

グリッドのページ送りをしたいときや、大量データのテーブルなどで取得データの件数を制限したい場合ありますよね。

OracleではROWNUMで、SQLserverではTOPというキーワードがあります。

もちろんMySQLにもあります。それが「limit」。どちらかというとSQLserverのTOPに近い感じかな。

MySQLマニュアル

[1] 先頭から10件を取りたい場合。

select * from test
where aaa = '1'
   order by key01
   limit 10

[2] 最後から5件を取りたい場合。

select * from test
where aaa = '1'
   order by key01 desc
   limit 5

[3] 先頭から100件目～150件目を取りたい場合。

select * from test
where aaa = '1'
   order by key01
   limit 100, 150

引数が１つでも２つでも勝手に解釈してくれます。さらに応用でこんなこともできます。

データをランダムに10件取得する。

select * from test
where aaa = '1'
   order by rand()
   limit 10

MySQLの関数「 rand()」でランダムに割り当てて並び変えて先頭を取得でおしまい。

ほかのデータベースで同じ事を実現しようとすると、なかなか大変です。

やっぱり便利。MySQL。

MySQLでUTF-8の設定方法

Red Hat Enterprise Linux 4
mysql 5.0.27での設定

RPMでインストールした場合、デフォルトのキャラクターセットはlatin1となる、UTF-8にしたい場合は下のように設定ファイルに追加する必要がある。

/etc/my.cnf

[mysqld]
default-character-set=utf8
skip-character-set-client-handshake

「default-character-set=utf8」だけでいけるとおもったら、クライアントのキャラクターセットが「latin1」になってる。
そこで調べたら「skip-character-set-client-handshake」という設定が必要なんだとわかった。

「skip-character-set-client-handshake」はクライアントから接続したときにクライアント側の文字コードを「default-character-set」で指定されたものに自動設定するオプションなんだって。これは知らなかった。