MySQL – Developer F

本文要解决什么问题：当远程服务器无法开放MySQL端口或者无权限操作远程服务器的安全组时，我们应该如何在本地连接到远程的MySQL并进行操作

本文基础要求：远程服务器至少开放了SSH端口（通常默认情况下端口号为22），如果未开放22端口，则本文所述方法并不可用。

本文方法总结：利用SSH自带的端口映射功能，建立一个tunnel，将远程服务器上的MySQL端口映射到本地的端口上，从而使得本地客户端可以直接连接

当SSH已经准备就绪，打开Terminal，输入如下指令


ssh -L [local port]:[database host]:[remote port] [username]@[remote host]

ssh -L [本地端口]:[数据库所在地址]:[数据库远程端口] [远程服务器用户名]@[远程服务器地址]

*如果你不用同时登陆远程服务器进行操作，可以加上-N指令

以上指令唯一需要解释的就是数据库所在地址，如果远程服务器本身就是MySQL服务器，那么可以直接填127.0.0.1, 而如果远程服务器本身还是一个跳板，则应该输入在远程服务器内可以访问到MySQL的地址。

示例如下：

ssh -N -L 3310:127.0.0.1:3306 root@MyRemote

以上的命令会将远程服务器监听3306端口的MySQL服务，映射到本地的3310端口上，之后可以直接使用MySQL客户端在命令行里登录，或者使用pma等数据库操作工具

mysql -u root -h 127.0.0.1 -P 3310 -p

最近的项目连续遇到了对用户排名的需求，一个要求不用并列，一个要求同分并列，因为用户体量的考虑，拒绝了实时更新（即每个用户查询排名时获取排名），改为定时更新，在网上查了大量的MySQL排名算法之后，发现大部分的文章是以查询时获取来实现，而非定时更新所有人的排名，所以经过一番研究之后自己写了一个算法，欢迎斧正

首先我们假设有一张user表，其中有四个字段，score，rank，score_rank，join_time分别代表用户的分数，排名，获取排名时的分数，及用户加入时间

首先是不考虑并列的情况

SET @r = 0
UPDATE user
SET `rank` = @r := (@r + 1), `score_rank` = `score`
WHERE `score` != 0
ORDER BY `score` DESC, `join_time` DESC // 因为没有并列，所以增加一个排序

然后是考虑并列的情况

SET @r = 0;
SET @l = 0;
SET @s = 1;
UPDATE `user` SET `rank` = (
CASE
WHEN `score` = @l THEN @r := @r + (@s := @s + 1) - @s
ELSE @r := (@r + @s) + (@s := 1) - 1 END), `score_rank` = @l := (`score`)
WHERE `score` != 0
ORDER BY `score` DESC

这里唯一需要注意的事情就是MySQL中的变量问题，变量只能在两个地方赋值，SET或SELECT时，其余的地方只允许在变量赋值给其他字段时才允许被修改，所以就是在这里我们开始取巧，来解释一下并列时的思想：

r,l,s分别是当前排名，当前排名的分数，和当前同分的人数

当某一行的数据与上一行的分数相同时，即 score = @l，那么这时应该给rank赋值为当前的排名，但同时应该更新同分人数，所以我们这样来实现：@r := @r + (@s := @s + 1) – @s，这样s实现了自增，但对返回结果r没有影响

当某一行的数据与上一行的分数不同时，则应该给rank赋值r+1,但同时我们希望给l赋值当前的分数，并把同分人数s归为1，则选择这样实现：@r := (@r + @s) + (@s := 1) – 1

关于速度

这个算法在我这边的模拟测试中，三万条的数据排序用时0.4秒，所以肯定不能作为实时处理，计划是每天定时更新一回，操作时间也算是可以接受了。不知道其他算法的时间是会是多少，欢迎提供下对比。

标签： MySQL

使用SSH来连接远程服务器的MySQL

Mysql更新排名的取巧实现

关于速度