mysqlで検索結果に順位を付与する方法

mysqlで検索結果に順位を付与した結果を取得する方法です。

以下のようなテーブルで、id順にソートした結果に順位を付与します。

mysql> select * from tbl1;
+-----+
| id |
+-----+
| id0 |
| id1 |
| id2 |
| id3 |
| id4 |
| id5 |
| id6 |
| id7 |
| id8 |
| id9 |
+-----+

mysql> set @ord := 0;
mysql> select
-> @ord := @ord + 1 as ord
-> , id as id
-> from
-> tbl1
-> order by
-> id asc
-> ;
+------+-----+
| ord | id |
+------+-----+
| 1 | id0 |
| 2 | id1 |
| 3 | id2 |
| 4 | id3 |
| 5 | id4 |
| 6 | id5 |
| 7 | id6 |
| 8 | id7 |
| 9 | id8 |
| 10 | id9 |
+------+-----+

標準入力からのSQLのmysqlコマンド実行結果

mysqlの対話モードでは以下のように、実行時間が出力されます。

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-03-04 21:45:57 |
+---------------------+
1 row in set (0.00 sec)

mysqlコマンドの標準入力にSQLを与えると、tsvで実行結果が出力されます。

$ echo "select now();" | mysql
now()
2011-03-04 21:38:46

標準入力からのSQLを実行する場合でも対話モードと同じように実行時間が出力されるようにするには、-vvvオプションを指定します。

$ echo "select now();" | mysql -vvv
--------------
select now()
--------------

+---------------------+
| now() |
+---------------------+
| 2011-03-04 21:48:49 |
+---------------------+
1 row in set (0.00 sec)

Bye

対話モードで source でSQLファイルを読み込むという手もありますが。

mysqldump での時刻条件指定

mysqldump コマンドの -w オプションに now() で現在日時を指定すると、now() の現在日時が UTC になる場合があります。

■datetime型のカラムを持つテーブルを作成し、now()で現在日時のデータを登録

mysql> create table tbl (t datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tbl set t = now();
Query OK, 1 row affected (0.00 sec)

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-03-01 23:06:01 |
+---------------------+
1 row in set (0.00 sec)


■mysqldump で now() 以下のデータをダンプ

$ mysqldump -u tech test_nikeda tbl -w "t <= now()"
-- MySQL dump 10.11
--
-- Host: localhost Database: test_nikeda
-- ------------------------------------------------------
-- Server version 5.0.77-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `tbl`
--

DROP TABLE IF EXISTS `tbl`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `tbl` (
`t` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `tbl`
--
-- WHERE: t <= now()

LOCK TABLES `tbl` WRITE;
/*!40000 ALTER TABLE `tbl` DISABLE KEYS */;
/*!40000 ALTER TABLE `tbl` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2011-03-01 14:07:20


insert文が出力されていません。
mysqldump の最後に出力されている時刻はUTCの時刻なので、now() が 9時間前の時刻になっているのでしょう。

now() ではなく、直接時刻を設定すれば問題ありません。

$ mysqldump db tbl -w "t <= '2011-03-01 24:00:00'"
...
--
-- Dumping data for table `tbl`
--
-- WHERE: t <= '2011-03-01 24:00:00'

LOCK TABLES `tbl` WRITE;
/*!40000 ALTER TABLE `tbl` DISABLE KEYS */;
INSERT INTO `tbl` VALUES ('2011-03-01 23:05:55');
/*!40000 ALTER TABLE `tbl` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
...

mysqlのテーブル情報

mysqlのテーブルサイズなどを取得する方法です。
行数を一気に取得できるので便利です。

【SQL】
select
table_schema db
, table_name tbl
, concat(round(table_rows/1000, 2),'k') rows
, concat(round(data_length/(1024*1024), 2),'M') data
, concat(round(index_length/(1024*1024), 2),'M') idx
, concat(round((data_length+index_length)/(1024*1024), 2),'M') total_size
from
information_schema.TABLES
order by
db asc
, tbl asc
;

【実行結果】
db tbl rows data idx total_size
db1 tbl1 490.50k 100.63M 39.67M 140.30M
db1 tbl2 1215.99k 72.69M 76.75M 149.44M

mysql でプロセス情報を見る方法

mysql で処理中のプロセスをみるには、

> show processlist;

ですが、これだと SQL 文が中途半端にしか表示されず、例えばどのプロセスを kill してよいかわからないことがあります。

こんなときには、

> show full processlist;

です。
これなら、SQL 文全体が表示されるので、どのプログラムから実行された SQL 文かもわかって便利です。

php-5.2.0 + mysql-5.0.22

理由あって現在開発環境として使っているマシンの php-4 系は使えず、php-5 系が必要となったので、早速インストールしてみました。

php-5.2.0 をインストールしたのですが、configure の時点で libmysqlclient_r がみつからないというエラーがでました。
mysql はいつもと同様に文字コードの設定以外は行わないでインストールしたので、このライブラリが make されなかったようです。

libmysqlclient_r が作成されるためには、configure 時に --enable-thread-safe-client を指定しておく必要があります。

./congiure \
--enable-thread-safe-client \
--with-charset=utf8 \
--with-extra-charset=all

一方、php-5.2.0 は以下のようなオプションで configure を実行しました。

./configure.sh \
--prefix=/usr/local/ \
--with-apxs2=/usr/loacal/apache2/bin/apxs \
--with-enable-shared \
--with-mysql=/usr/local \
--enable-mbstring \
--enable-zend-multibyte

後は make、sudo make install でインストールすればＯＫです。

mysql のレプリケーションエラーが発生したら

mysql のスレーブサーバで、show slave status を実行して、Slave_IO_Running が No で、Slave_SQL_Running が Yes になっている場合の対処法です。

mysql> stop slave;
mysql> reset slave;
mysql> start slave;

スレーブサーバの状態を表示。
mysql> show slave status;

これで Slave_IO_Running、Slave_SQL_Running ともに Yes となればＯＫ。
No なら、「mysqlのレプリケーションが止まったら」の方法でスレーブサーバのデータを再構築しましょう。

mysqlサーバに接続できない場合には

grant 文でユーザの設定を正しく行っているのに mysql への接続でエラーがでる場合には、mysql サーバが接続元のホストからの接続を拒否している可能性があります。

mysql は、あるホストからの接続エラーが mysql_connect_errors で指定される回数以上あると、そのホストからの接続を拒否するようになります。
これが原因で mysql サーバに接続できなくなったときには、mysqladmin flush-hosts を実行すればＯＫです。

この前はこのコマンドを思い出せなくて、mysql サーバを再起動してしまいましたが、それでもＯＫです。

mysql でテーブルサイズの制限を変更する方法

mysql で大規模データを扱う際に、レコード数の最大値を変更する方法です。

設定を変更する前に、まずは現在の設定状況の確認から。

> show table status from {DB名};

Avg_row_length が１レコードの平均容量、Rows が最大レコード数なので、これらの値を変更します。

> alter table {テーブル名} AVG_ROW_LENGTH = {平均レコード容量};
> alter table {テーブル名} MAX_ROWS = {最大レコード数};