{"id":3944,"date":"2019-10-23T15:01:31","date_gmt":"2019-10-23T07:01:31","guid":{"rendered":"https:\/\/damogame.cn\/wordpress\/?p=3944"},"modified":"2019-10-23T15:01:31","modified_gmt":"2019-10-23T07:01:31","slug":"%e4%bd%bf%e7%94%a8mysql%e3%80%81redis%e8%a7%a3%e5%86%b3%e6%8e%92%e8%a1%8c%e6%a6%9c%e9%97%ae%e9%a2%98","status":"publish","type":"post","link":"https:\/\/i007.cc\/wordpress\/archives\/3944","title":{"rendered":"\u4f7f\u7528MySQL\u3001Redis\u89e3\u51b3\u6392\u884c\u699c\u95ee\u9898"},"content":{"rendered":"
\u539f\u6587\u5730\u5740<\/a><\/div>\n
<\/div>\n
\n

\u95ee\u9898\u63cf\u8ff0<\/h1>\n

\u6392\u884c\u699c\u4e3b\u8981\u5206\u4e3a\u4e24\u79cd\uff0c\u4e00\u79cd\u662f\u5e76\u5217\u6392\u884c\u699c\uff08\u5b58\u5728\u76f8\u540c\u6392\u540d\u7684\u60c5\u51b5\uff09\uff0c\u4e00\u79cd\u662f\u4e25\u683c\u6392\u884c\u699c\uff08\u5206\u5148\u540e\u987a\u5e8f\uff0c\u4e0d\u5b58\u5728\u5e76\u5217\u540d\u6b21\uff09\u3002<\/p>\n

\u4e00\u822c\u6839\u636e\u4e0d\u540c\u7684\u4e1a\u52a1\u573a\u666f\uff0c\u9009\u7528\u4e0d\u540c\u7684\u6392\u884c\u699c\u3002\u4f8b\u5982\uff0c\u5bf9\u4e8e\u5b58\u5728\u5b9e\u7269\u5956\u52b1\u4e14\u524d\u4e00\u540d\u4e0e\u540e\u4e00\u540d\u7684\u5956\u54c1\u5dee\u8ddd\u5f88\u5927\u65f6\uff0c\u5f80\u5f80\u91c7\u7528\u4e25\u683c\u6392\u884c\u699c\uff0c\u800c\u5bf9\u4e8e\u53ea\u662f\u6fc0\u52b1\u7528\u6237\u7684\u573a\u666f\uff0c\u5219\u9009\u7528\u5e76\u5217\u6392\u884c\u699c\u3002<\/p>\n

\u4e0b\u9762\uff0c\u901a\u8fc7\u4e3e\u4e24\u4e2a\u4f8b\u5b50\uff0c\u4ecb\u7ecd\u4e00\u4e0b\u8fd9\u4e24\u79cd\u6392\u884c\u699c\u3002
\n\u5047\u8bbe\u5b58\u5728\u4e00\u5f20\u8868\u00a0tbl_score<\/code>\uff0c\u539f\u59cb\u6570\u636e\u5982\u56fe\uff1a<\/p>\n

\n
\n
<\/div>\n
<\/div>\n<\/div>\n
tbl_score \u8868<\/div>\n<\/div>\n

\u7528\u5230\u7684\u8868\u548c\u6570\u636e\uff1a<\/p>\n

CREATE TABLE `tbl_score` (\r\n  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '\u7528\u6237ID',\r\n  `score` int(11) DEFAULT NULL COMMENT '\u5f97\u5206',\r\n  `created_time` int(11) DEFAULT NULL COMMENT '\u5f97\u5206\u65f6\u95f4',\r\n  PRIMARY KEY (`id`)\r\n) COMMENT='\u5f97\u5206\u8868';\r\n\r\nINSERT INTO `tbl_score` (`id`, `score`, `created_time`)\r\nVALUES\r\n    (1, 90, 1534429870),\r\n    (2, 80, 1534429871),\r\n    (3, 82, 1534412273),\r\n    (4, 96, 1534429243),\r\n    (5, 100, 1534429133),\r\n    (6, 5, 1534429273),\r\n    (7, 8, 1534429823),\r\n    (8, 80, 1534429801);\r\n<\/code><\/pre>\n
\u65b9\u5f0f\u4e00\uff1a\u5e76\u5217\u6392\u884c\u699c<\/h2>\n
\u7ecf\u8fc7\u6392\u884c\u540e\u7684\u6392\u884c\u7ed3\u679c\u5982\u56fe\uff1a<\/p>\n
\n
\n
<\/div>\n
<\/div>\n<\/div>\n
\u5e76\u5217\u6392\u884c\u699c<\/div>\n<\/div>\n
\u53ef\u4ee5\u770b\u5230\uff0c\u76f8\u540c\u5f97\u5206\u60c5\u51b5\u4e0b\uff0c\u540d\u6b21\u76f8\u540c\u3002\u4e0b\u4e00\u4f4d\uff0c\u540d\u6b21\u4e3a\u5f53\u524d\u7528\u6237\u987a\u5e8f\u4f4d\u3002\u4f8b\u5982\uff0c\u4e24\u540d\u7b2c\u4e94\u540d\uff0c\u5219\u4e0b\u4e00\u4f4d\u4e3a\u7b2c\u4e03\u540d\uff0c<\/p>\n
\u65b9\u5f0f\u4e8c\uff1a\u4e25\u683c\u6392\u884c\u699c<\/h2>\n
\u4e25\u683c\u6392\u884c\u699c\uff0c\u901a\u8fc7\u591a\u7ea7\u6392\u884c\uff0c\u6392\u5217\u51fa\u6709\u5148\u540e\u6b21\u6570\u7684\u6392\u540d\u3002\u5728\u672c\u4f8b\u4e2d\uff0c\u5f53\u76f8\u540c\u5f97\u5206\u7684\u60c5\u51b5\u4e0b\uff0c\u6839\u636e\u65f6\u95f4\u8fdb\u884c\u4e8c\u6b21\u6392\u5e8f\uff0c\u6700\u5148\u5f97\u5206\u7684\u540d\u6b21\u8d8a\u9ad8\u3002
\n\u6392\u884c\u7ed3\u679c\u5982\u56fe\uff1a<\/p>\n
\n
\n
<\/div>\n
<\/div>\n<\/div>\n
\u4e25\u683c\u6392\u884c\u699c\uff0c\u5f97\u5206+\u65f6\u95f4<\/div>\n<\/div>\n
\n
\u5e76\u5217\u6392\u884c\u699c\u7684\u89e3\u51b3\u65b9\u6cd5<\/h1>\n
MySQL \u5b9e\u73b0<\/h2>\n
SELECT `id`, `score`,\r\n    IFNULL((SELECT COUNT(*) FROM `tbl_score` WHERE `score` > `t`.`score`), 0) + 1 AS `rank`  # \u8ba1\u7b97\u5e76\u5217\u6392\u540d\r\nFROM `tbl_score` t\r\nORDER BY rank ASC\r\n<\/code><\/pre>\n
\u901a\u8fc7\u00a0(SELECT COUNT(*) FROM `tbl_score` WHERE `score` > `t`.`score`)<\/code>\uff0c\u83b7\u53d6\u6392\u5e8f\u3002\u8ba1\u7b97\u539f\u7406\u662f\u6bd4\u8f83\u51fa\u6bd4\u5f53\u524d\u5206\u6570\u8981\u5927\u7684\u6761\u6570\uff0c\u7136\u540e\u52a01\uff0c\u83b7\u53d6\u5f53\u524d\u6392\u5e8f\u3002\u4f8b\u5982\uff0c\u6392\u540d\u7b2c\u4e00\u7684\u5f97\u5206\uff0c\u7b5b\u9009\u51fa\u6bd4\u5f53\u524d\u5927\u7684\u5f97\u5206\u884c\u6570\u4e3a0\uff0c\u7136\u540e\u52a01\uff0c\u5f97\u6392\u540d\u4e3a 1\u3002<\/p>\n
\u903b\u8f91\u5c42\u5b9e\u73b0\u6392\u884c<\/h2>\n
\/\/ data \u4e3a\u5df2\u6839\u636e score DESC \u6392\u5217\u7684\u6570\u636e<\/span>\r\n\r\nconst<\/span> ranked =<\/span> data.<\/span>map<\/span>(<\/span>function<\/span>(<\/span>item,<\/span> i<\/span>)<\/span> {<\/span>\r\n    if<\/span> (<\/span>i ><\/span> 0<\/span>)<\/span> {<\/span>\r\n        \/\/ \u83b7\u53d6\u4e0a\u4e00\u4e2a\u5143\u7d20<\/span>\r\n        var<\/span> prevItem =<\/span> data[<\/span>i -<\/span> 1<\/span>]<\/span>;<\/span>\r\n        if<\/span> (<\/span>prevItem.<\/span>score ==<\/span> item.<\/span>score)<\/span> {<\/span>\r\n            \/\/ \u5f97\u5206\u76f8\u540c\uff0c\u5219\u6392\u5e8f\u76f8\u540c<\/span>\r\n            item.<\/span>rank =<\/span> prevItem.<\/span>rank;<\/span>\r\n        }<\/span> else<\/span> {<\/span>\r\n            \/\/ \u5f97\u5206\u4e0d\u76f8\u540c\uff0c\u5219\u6392\u5e8f + 1<\/span>\r\n            item.<\/span>rank =<\/span> i +<\/span> 1<\/span>;<\/span>\r\n        }<\/span>\r\n    }<\/span> else<\/span> {<\/span>\r\n        \/\/ \u7b2c\u4e00\u4e2a\u6570\u636e\uff0c\u6392\u5e8f\u4e3a 1<\/span>\r\n        item.<\/span>rank =<\/span> 1<\/span>;<\/span>\r\n    }<\/span>\r\n\r\n    return<\/span> item;<\/span>\r\n}<\/span>)<\/span>;<\/span>\r\n<\/code><\/pre>\n
\n
\u7cbe\u786e\u6392\u884c\u699c\u7684\u89e3\u51b3\u65b9\u6cd5<\/h1>\n
MySQL \u5b9e\u73b0<\/h2>\n
\u6839\u636e\u591a\u7ea7\u6761\u4ef6\u8fdb\u884c\u6392\u5e8f\uff0c\u672c\u4f8b\u4e2d\u4ee5\u5f97\u5206\u3001\u65f6\u95f4\u4e3a\u6761\u4ef6\u8fdb\u884c\u6392\u5e8f\u3002\u4f18\u5148\u5bf9\u5f97\u5206\u6309\u7167\u964d\u5e8f\u8fdb\u884c\u6392\u5e8f\uff0c\u5176\u6b21\u662f\u4ee5\u65f6\u95f4\u5347\u5e8f\u8fdb\u884c\u6392\u5e8f\u3002\u5f97\u5206\u9ad8\u4e14\u83b7\u5f97\u5206\u6570\u8d8a\u65e9\u7684\u73a9\u5bb6\uff0c\u6392\u5e8f\u8d8a\u9760\u524d\u3002<\/p>\n
\u6700\u540e\uff0c\u8ba1\u7b97\u884c\u53f7\u5f97\u5230\u6392\u540d\u3002<\/p>\n
SELECT `id`,`score`, `created_time`, (@rowNum := @rowNum + 1) as rank\r\nFROM tbl_score, (SELECT @rowNum := 0) b\r\nORDER BY `score` DESC, `created_time` ASC\r\n<\/code><\/pre>\n
Redis \u5b9e\u73b0<\/h2>\n
\u4e3b\u8981\u5229\u7528Redis\u7684\u6709\u5e8f\u6570\u5217\u96c6\u5408\uff08zset<\/code>\uff09\u5b9e\u73b0\u3002<\/p>\n
\u5b9e\u73b0\u539f\u7406<\/h3>\n
\u6709\u5e8f\u96c6\u5408\u7531\u4e09\u90e8\u5206\u7ec4\u6210\uff0cKEY\uff08\u952e\uff09\u3001score\uff08\u6210\u5458\u7684\u5f97\u5206\uff09\u3001member\uff08\u6210\u5458\uff09\u3002\u6709\u5e8f\u96c6\u5408\u7684\u6bcf\u4e00\u9879\uff0c\u90fd\u662f\u4ee5\u952e\u503c\u5bf9\u7684\u5f62\u5f0f\u5b58\u50a8\uff0c\u6bcf\u4e00\u9879\u90fd\u6709\u4e00\u4e2a\u5206\u6570\u3002\u6709\u5e8f\u96c6\u5408\u4f1a\u6839\u636escore\u81ea\u52a8\u6392\u5e8f\u3002\u5229\u7528\u8fd9\u4e2a\u7279\u6027\uff0c\u6211\u4eec\u5c31\u53ef\u4ee5\u5b9e\u73b0\u6392\u884c\u699c\u4e86\u3002<\/p>\n
scoro \u662f\u6570\u5b57\u7c7b\u578b\uff0c\u53ef\u4ee5\u662f\u6574\u5f62\u4e5f\u53ef\u4ee5\u662f\u6d6e\u70b9\u578b\u3002\u6309\u7167\u6392\u884c\u699c\u591a\u7ea7\u6392\u5e8f\u7684\u8981\u6c42\uff0c\u76f8\u540c\u5206\u503c\u4e0b\u6309\u7167\u5148\u6765\u540e\u5230\u7684\u987a\u5e8f\u6392\u5e8f\uff08\u521b\u5efa\u65f6\u95f4\u8d8a\u65e9\uff0c\u6392\u5e8f\u8d8a\u9ad8\uff09\uff0c\u4f46\u662fRedis\u76f8\u540c\u5206\u503c\uff0c\u662f\u6309\u7167\u00a0member<\/code>\u00a0\u7684\u00a0ASCII<\/code>\u7801\u8fdb\u884c\u6392\u5e8f\u3002
\n\u5982\u679c\u76f4\u63a5\u5c06\u5f97\u5206\u4f5c\u4e3a\u6709\u5e8f\u96c6\u5408\u7684\u00a0score<\/code>\uff0c\u5f97\u4e0d\u5230\u6211\u4eec\u60f3\u8981\u7684\u6548\u679c\u3002<\/p>\n
\n
\n
<\/div>\n
<\/div>\n<\/div>\n
zset score \u6309\u7167ASCII\u7801\u6392\u5e8f<\/div>\n<\/div>\n
\u6240\u4ee5\uff0c\u9700\u8981\u5c06\u00a0score<\/code>\u8fdb\u884c\u6539\u9020\uff0c\u540c\u65f6\u8bb0\u5f55\u5f97\u5206\u4e0e\u65f6\u95f4\u4fe1\u606f\u3002\u5b9e\u73b0\u65b9\u5f0f\u6709\u4e24\u79cd\uff1a<\/p>\n
    \n
  1. score \u4e3a\u6574\u6570\u3002\u5f97\u5206 + \u65f6\u95f4\u5dee\u3002<\/li>\n
  2. score \u4e3a\u6d6e\u70b9\u6570\u3002\u6574\u6570\u90e8\u5206\u4f7f\u7528\u5f97\u5206\uff0c\u5c0f\u6570\u90e8\u5206\u4f7f\u7528\u65f6\u95f4\u5dee\u3002<\/li>\n<\/ol>\n
    \u5047\u8bbe\u5f97\u5206\u4e3a 10\uff0c \u5f97\u5206\u65f6\u95f4\u4e3a\u00a01534649521<\/code>\uff08Unix\u65f6\u95f4\u6233\u30022018\/8\/19 11:32:01\uff09, \u622a\u6b62\u65f6\u95f4\u4e3a\u00a01534694400<\/code>\uff08Unix\u65f6\u95f4\u6233\u30022018\/8\/20 0:0:0\u3002 \u82e5\u65e0\u622a\u6b62\u65f6\u95f4\uff0c\u53d6\u503c\u4e3a\u00a09999999999<\/code>\uff09\u3002
    \n\u65b9\u5f0f\u4e00\uff1a<\/p>\n
    10 + (1534694400 - 1534649521) = 44889\r\n<\/code><\/pre>\n
    \u65b9\u5f0f\u4e8c\uff1a<\/p>\n
    10.44879\r\n<\/code><\/pre>\n
    \u9009\u7b2c\u4e8c\u79cd\u65b9\u5f0f\u7684\u597d\u5904\u662f\uff0c\u5f53\u9700\u6c42\u4e0d\u4ec5\u9700\u8981\u6392\u5e8f\uff0c\u8fd8\u9700\u5c55\u793a\u5f97\u5206\u65f6\uff0c\u53ef\u4ee5\u5c06 score \u5f3a\u5236\u8f6c\u5316\u6210\u6574\u5f62\uff0c\u5373\u53ef\u83b7\u53d6\u5230\u5f97\u5206\u3002\u9700\u8981\u6ce8\u610f\u7684\u662f\uff0c\u5f97\u5206\u6700\u597d\u4e0d\u8981\u592a\u5927\uff0c\u00a0score<\/code>\u5f97\u5206\u5c3d\u91cf\u63a7\u5236\u572816\u4f4d\u4ee5\u4e0b\uff08\u6d6e\u70b9\u6570\u65f6\uff0c\u5c0f\u6570\u70b9\u524d\u540e\u4f4d\u6570\u548c\u4e0d\u8981\u8d85\u8fc716\u4f4d\uff0c\u6700\u597d15\u4f4d\uff09\u3002\u8d85\u8fc716\u4f4d\u540e\uff0cscore<\/code>\u503c\u5b58\u5165redis\uff0c\u4f1a\u53d1\u751f\u7cbe\u5ea6\u4e22\u5931\u3002<\/p>\n
    \n
    \n
    <\/div>\n
    <\/div>\n<\/div>\n
    \u8d85\u8fc716\u4f4d\uff0c\u7cbe\u5ea6\u53d1\u751f\u4e22\u5931<\/div>\n<\/div>\n
    \u83b7\u53d6\u6392\u884c\u699c<\/h3>\n
    ZREVRANGE test 0 -1 withscores\r\n<\/code><\/pre>\n
    \u5b9e\u73b0\u6b65\u9aa4<\/h3>\n
      \n
    1. \u6392\u884c\u699c<\/li>\n
    2. \u76f8\u540c\u5f97\u5206\uff0c\u6309\u7167\u5148\u6765\u540e\u5230\u7684\u987a\u5e8f<\/li>\n
    3. redis\u6709\u5e8f\u96c6\u5408<\/li>\n
    4. \u8ba1\u7b97score\u503c<\/li>\n
    5. \u5f97\u5230\u6392\u5e8f\u6392\u884c\u699c<\/li>\n<\/ol>\n
      \u603b\u7ed3<\/h1>\n
      \u672c\u6587\u4e3b\u8981\u5bf9\u5e76\u5217\u6392\u884c\u699c\u548c\u7cbe\u786e\u6392\u884c\u699c\u7684\u95ee\u9898\u8fdb\u884c\u4e86\u5206\u6790\uff0c\u91c7\u7528Redis \u6216MySQL\u7684\u65b9\u5f0f\u89e3\u51b3\u6392\u884c\u699c\u95ee\u9898\u3002\u5728\u4f7f\u7528Redis\u89e3\u51b3\u7cbe\u786e\u6392\u884c\u699c\u95ee\u9898\u65f6\uff0c\u9047\u5230\u4e86\u00a0score<\/code>\u00a0\u7cbe\u5ea6\u95ee\u9898\u3002\u5f00\u53d1\u8005\u9700\u8981\u7279\u522b\u6ce8\u610f\u6b64\u70b9\uff0c\u5bf9\u4e8e\u7279\u522b\u5927\u7684\u00a0score<\/code>\u65f6\uff0c\u9700\u8981\u7279\u522b\u5904\u7406\u3002\u4f8b\u5982\uff0c\u5c06\u5927\u6570\u503c\u8f6c\u6362\u6210\u5c0f\u6570\u503c\u3002<\/p>\n
      \u5173\u4e8e\u5e76\u5217\u6392\u884c\u699c\u5904\u7406\u95ee\u9898\uff0c\u5728\u5b9e\u8df5\u4e2d\u4ecd\u7136\u5b58\u5728\u4e00\u4e9b\u95ee\u9898\u3002\u4f8b\u5982\uff0c\u5bf9\u4e8e\u6392\u5e8f\u8ba1\u7b97\u8f83\u590d\u6742\u7684\u573a\u666f\u3002\u6211\u4eec\u4f1a\u4f7f\u7528\u5b9a\u65f6\u4efb\u52a1\u63d0\u524d\u8ba1\u7b97\u597d\u6392\u884c\u699c\uff0c\u7136\u540e\u5c06\u6570\u636e\u5199\u5165\u7f13\u5b58\u4e2d\u3002\u8fd9\u6837\uff0c\u7528\u6237\u5728\u62c9\u53d6\u6392\u884c\u699c\u65f6\u80fd\u5f97\u5230\u8f83\u5feb\u54cd\u5e94\u3002<\/p>\n
      \u4f46\u662f\u5f53\u65e2\u9700\u8981\u5c55\u793a\u6392\u540d\uff0c\u53c8\u9700\u8981\u5c55\u793a\u5f97\u5206\u4fe1\u606f\uff0c\u4e14\u9700\u8981\u4fdd\u8bc1\u83b7\u53d6\u7684\u6570\u7ec4\u662f\u6309\u5e8f\u65f6\uff0c\u4e3a\u4e86\u65b9\u4fbf\u67e5\u8be2\u65f6\uff0c\u8f83\u5feb\u83b7\u53d6\u6392\u540d\u3001\u5f97\u5206\u3002\u6211\u91c7\u7528\u7684\u65b9\u5f0f\u662f\uff0c\u5c06\u6392\u540d\u3001\u5f97\u5206\u5206\u522b\u5199\u5165\u4e24\u4e2a\u00a0zset<\/code>\u4e2d\u3002\u7406\u60f3\u72b6\u6001\u4e0b\uff0c\u671f\u671b\u5199\u4e00\u4e2a\u00a0zset<\/code>\uff0c\u5e76\u540c\u65f6\u50a8\u5b58\u6392\u540d\u3001\u5f97\u5206\u4fe1\u606f\uff0c\u4e14\u65b9\u4fbf\u53d6\u51fa\uff0c\u53c8\u4e0d\u4f1a\u4e22\u5931\u7cbe\u5ea6\u3002\u76ee\u524d\uff0c\u6ca1\u6709\u627e\u5230\u8fd9\u79cd\u89e3\u51b3\u529e\u6cd5\u3002<\/p>\n
      \u53c2\u8003\u6587\u7ae0<\/h1>\n