maco's life

主にエンジニアリングと読書について書いていきます。

mysqlでユーザ定義変数をつかう

SELECTで取ってきたレコードをソートして、ソートした結果に連番でidふるみたいなことできないかなーとおもってmysqlのドキュメント読んでたら変数を使えることを知りました。

MySQL :: MySQL 5.7 Reference Manual :: 9.4 User-Defined Variables

変数使えるなら連番ふれるじゃんとおもってさっそくやってみた。

やりたいこと

先程も書いた通り結果を小さい順にソートして、小さい方から1,2,3...と通しの番号をふる

テーブル

mysql> SHOW CREATE TABLE user_point\G;
*************************** 1. row ***************************
       Table: user_point
Create Table: CREATE TABLE `user_point` (
  `user_id` int(11) DEFAULT NULL,
  `point` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

userを判別するためのuser_idカラムと、userが所持しているポイントを管理するpointカラムをもったuser_pointテーブルで試します。

テーブルの中は

mysql> SELECT * FROM user_point;
+---------+-------+
| user_id | point |
+---------+-------+
|       1 |   500 |
|       2 |   400 |
|       3 |   300 |
|       4 |   200 |
|       5 |   100 |
+---------+-------+

このようになっています。

早速試してみる

mysql> SET @number = 0;
mysql> SELECT (@number := @number + 1) AS number, user_id ,point FROM user_point ORDER BY point asc;

^のSQLを流すと

mysql> SELECT (@number := @number + 1) AS number, user_id ,point FROM user_point ORDER BY point asc;
+--------+---------+-------+
| number | user_id | point |
+--------+---------+-------+
|      1 |       5 |   100 |
|      2 |       4 |   200 |
|      3 |       3 |   300 |
|      4 |       2 |   400 |
|      5 |       1 |   500 |
+--------+---------+-------+

こんな感じでポイントをソートして通しの番号を振ってくれます。 もう一回同じクエリを流すと

mysql> SELECT (@number := @number + 1) AS number, user_id ,point FROM user_point ORDER BY point asc;
+--------+---------+-------+
| number | user_id | point |
+--------+---------+-------+
|      6 |       5 |   100 |
|      7 |       4 |   200 |
|      8 |       3 |   300 |
|      9 |       2 |   400 |
|     10 |       1 |   500 |
+--------+---------+-------+
5 rows in set (0.00 sec)

前の値は保持したままインクリメントされました。 どうやらこの変数公式ドキュメントによると

User-defined variables are session-specific. A user variable defined by one client cannot be seen or used by other clients.

と書いてあり、セッションで固定で、自分がいじっている変数を他のユーザが見たり、編集したりすることはできないそうです。 なので一旦exitしてもう一度同じクエリを流すと@numberの値は何も入っていない状態になります。

学びがある