维护排名表的最佳实践？

Question

使用 MySQL 和 PHP，我有一个简单的排名表，其中包含 2 列 user 和 score。

每周一次，我重新运行排名脚本，它会计算每个用户的分数。

• 许多用户有新分数，
• 有些没有
• ，有时有新用户添加到表中，

使用 MySQL 解决此问题的最佳方法是什么？如果我需要添加新行，update 是否有效？ insert 是否会覆盖现有行，以便我可以再次插入整个表？最好删除该表并从头开始重写吗？

**Sample Data**

User01   2500
User02   3000
User03    100

**New Data to be Added**

User01   2700
User02   4000
User04   1000 // new account

除了成千上万的用户......

Answer 1

REPLACE ... INTO 语法将在需要时插入新行并删除+插入现有行：

# deletes existing and inserts new row:
REPLACE INTO users SET score = 2000, username = 'User01';
# deletes existing and inserts new row
REPLACE INTO users SET score = 3000, username = 'User02';
# inserts new row
REPLACE INTO users SET score = 4000, username = 'User04';  

http://dev.mysql.com/doc/refman/5.0/en/replace.html

我最喜欢的方法是使用 ON DUPLICATE KEY 语法 - 主要是因为我一直认为删除一行然后重新插入它会比只更新现有行更“昂贵”。假设 username 字段是 UNIQUE 或 PRIMARY KEY 索引，您可以执行以下操作

# Updates existing User01 row
INSERT INTO users SET score = 2000, username = 'User01' ON DUPLICATE KEY UPDATE score = 2000;
# Updates existing User02 row
INSERT INTO users SET score = 3000, username = 'User02' ON DUPLICATE KEY UPDATE score = 2000;
# Inserts new User04 row
INSERT INTO users SET score = 4000, username = 'User04' ON DUPLICATE KEY UPDATE score = 2000;

： mysql.com/doc/refman/5.0/en/insert-on-duplicate.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate .html

Answer 2

INSERT 不会覆盖现有行，而是会产生错误（例如，如果您将用户列标记为唯一并尝试添加已存在的用户）。同样，UPDATE 不会插入新行，只会更改现有行。

如果大多数用户需要更新并且存在应该删除的用户，即不再包含在新的分数表中，则删除并重写整个表似乎是可接受的解决方案。

否则，我会更新表：

可移植的解决方案是检查表中是否已包含键（即用户）（通过 SELECT），然后对不存在的键使用 INSERT 或对已存在的键使用 UPDATE。当然，您也可以尝试 INSERT 并检查错误代码，如果出现约束违规错误，则返回到 UPDATE。

在 MySQL 上，您还可以使用以下语法，该语法允许您仅使用单个查询进行操作：

INSERT INTO ... ON DUPLICATE KEY UPDATE score = ...

但是，这不可移植，并且不能与其他 RDBMS 一起使用，只能与 MySQL 一起使用。

Answer 3

正如您提到的两种方式

更新或插入，

我认为插入比更新更好。对于插入，您添加一个查询。至于更新，您必须为每个用户运行 2 个查询。首先选择用户，然后根据userId更新。

所以最快的是插入，你可以随时使用 sql 查询求和。