MySQL游戏排名统计

Question

如果标题不太容易理解，我很抱歉，但我真的不知道还能怎么写。

无论如何，我有一个包含 3 列的数据库:索引、名称和值。

索引很好，索引，它会自动递增。 名称是一个随机字符串，值是一个可以在不同行中重复的随机数字。

我想要的是按照索引 1、2、3、4 等组织数据库，具体取决于 Value 列的值，从最高到最低。

这意味着我希望表格的第一行成为值列中具有最高值的行，依此类推。即使我在 Value 列上添加带有随机值的新行，我也希望它能够自动组织，并在必要时切换索引。这可以以任何方式实现吗？或者我可以以某种方式做到这一点吗？还是不可能？

Answer 1

create schema so_gibberish; -- creates database
use so_gibberish;   -- use it

create table TopScores
(
    id int auto_increment primary key,  -- whatever this is, never really defined, playerId?
    theName varchar(50) not null,
    theValue bigint not null,
    key (theValue)
);

-- truncate table TopScores;

-- create a stored procedure to insert random scores
-- creates 100 at a time when you call it

DELIMITER $$ 
drop procedure if exists insertRandomScores$$

create procedure insertRandomScores()
BEGIN
    set @i=1;
    WHILE @i<=100 DO
        insert TopScores (theName,theValue) values ('xxx',1);
        SELECT @lid:=LAST_INSERT_ID();
        UPDATE TopScores SET theName=concat(
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
            substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1)
            ), theValue=floor(rand()*384738)
        WHERE id=@lid;
        set @i=@i+1;
    END WHILE;
END;
$$
DELIMITER ;

call insertRandomScores();  -- inserts 100 random names,scores.  Call it 10 times for 1000
select count(*) from TopScores;
-- 1000 rows

-- the above random routine was lifted from a routine to generate better random values with seeding
-- not for performance

-- Instantaneous return all players ordered by rank, showing rank in column 1:
set @rownum=0;
select @rownum:=@rownum+1 'rank',id,theName,theValue from TopScores order by theValue desc;

-- Instantaneous return Top20 players ordered by rank, showing rank in column 1:
set @rownum=0;
select @rownum:=@rownum+1 'rank',id,theName,theValue from TopScores order by theValue desc limit 20;

+------+-----+----------+----------+
| rank | id  | theName  | theValue |
+------+-----+----------+----------+
|    1 | 781 | BUEYKGJJ |   384685 |
|    2 | 758 | FTHGJVZZ |   384223 |
|    3 | 999 | 389IL122 |   383682 |
|    4 | 622 | NRM7J9NN |   383255 |
|    5 | 538 | 5EJB53EE |   382572 |
|    6 | 226 | BQTD6A44 |   382501 |
|    7 | 334 | YRLCI5XX |   382397 |
|    8 | 404 | UTK09NN  |   381152 |
|    9 | 885 | X6RQIP88 |   380678 |
|   10 | 292 | 71JZTMQQ |   380662 |
|   11 | 844 | FYF8ZXQQ |   380630 |
|   12 | 730 | 9O2KJYHH |   380361 |
|   13 | 770 | IHNWCL55 |   380287 |
|   14 | 861 | SL6GXH00 |   379624 |
|   15 | 399 | KE8CBRR  |   379612 |
|   16 | 597 | 8XGKB4RR |   379537 |
|   17 | 131 | 05U7C88  |   378626 |
|   18 | 865 | T30SY44  |   378503 |
|   19 | 747 | MBPC7F44 |   378181 |
|   20 | 289 | FUODJHJJ |   377817 |
+------+-----+----------+----------+

获取单个用户排名

-- The main difficulty is that the OP wants the rank in column 1, regardless of even the
-- soon to be mentioned 'getting just one user' aspect. 
-- Order by is simple without any of this.

-- But later if you just want to do a search on player named "Catfish" and want his rank
-- for any particular statistic, it must have some sense of a rank # based on all users.
-- It is therefore necessary to run thru all the players even tho you want one. We run thru all players
-- to get rankings and house them in a report table if you will. So we create a report table.
-- In a complicated statistical model, a row could exist for every user with statistics on that row
-- for all the categories. Here we are saving just one statistic, theValue

-- Create a report table once or whenever. Periodically update it. 
-- Note on EA Battlefield the update occurs after each game, (some games last an hour). 
-- Stackoverflow updates its tag statistics near the end of the day.

-- so create, insert, update this table below whenever you want (cron, mysql create event, etc)
-- every minute if you want

-- Ok, so we are looking for just player 'Catfish'

update TopScores set theName='Catfish' where id=500;

-- note below the index on theName so the id above is irrelevant. no stacked deck here.

select count(*) from TopScores where theName='catfish'; -- sanity check

-- drop table playerRanks;
create table playerRanks
(   rankId int not null,
    id int not null, -- playerId ?
    theName varchar(50) not null,
    theValue int not null,
    key (theName)   -- this will be snappy
);

-- jam the data in there
set @rownum=0;
insert into playerRanks (rankId,id,theName,theValue)
select @rownum:=@rownum+1 'rank',id,theName,theValue from TopScores order by theValue desc;


-- Query OK, 1000 rows affected (0.14 sec)

-- now get Catfish's rank:

select * from playerRanks where theName='catfish';
+--------+-----+---------+----------+
| rankId | id  | theName | theValue |
+--------+-----+---------+----------+
|    196 | 500 | Catfish |   310864 |
+--------+-----+---------+----------+

-- your results may vary

使用事件进行自动化

Creating an Event to run every five minutes for automation of this:

SET GLOBAL event_scheduler = ON; -- enable the scheduler.
SELECT @@event_scheduler;  -- see if it is ON

CREATE EVENT evtUpdateStatistics
    ON SCHEDULE
      EVERY 300 SECOND  -- run once every 5 minutes
    DO
      truncate table playerRanks;
      set @rownum=0;
      insert into playerRanks (rankId,id,theName,theValue)
      select @rownum:=@rownum+1 'rank',id,theName,theValue from TopScores order by theValue desc;

其他需要考虑或修改的事情:

• 在获取上述数据和事件时锁定玩家排名表以更新它。这将是一个快速锁定和释放，保证当用户获取数据时该表具有值，而不是因为事件已截断它而为空。
• 主要对玩家排名进行更新，而不是截断和插入。只需使用 0 值将新用户塞入系统一次。此后就是更新。
• 显示进程列表； -- 事件事件调度程序显示为 User=event_scheduler
• 设置全局 event_scheduler = ON; -- 启用调度程序。
• 如果存在则删除事件 evtUpdateStatistics； -- 删除一个