php - Laravel 5.4 中使用多表连接处理数百万行的高效且事务安全的方法

Question

这不一定仅适用于 Laravel php 框架，因为我相信我的预期任务也可以在 MySQL 服务器本身上使用适当的 SQL 语句进行处理。

共有以下3张表:

users (>2 million rows)

• id
• name
• balance (decimal, 14, 2)
• package_id

packages (5 rows)

• id
• name
• value (decimal, 14, 2)
• percent (decimal, 5, 2) - stored as 0.10 instead of 10

transactions (log)

• id
• user_id
• description
• amount

上面的所有表格都分别链接到对象/模型用户、包和事务。它们包含时间戳列，并由 Laravel 自动插入/更新。

1. 应用程序计划根据软件包(分配给用户)乘以百分比字段加上原始余额值(每天午夜 12:00)。更清晰的表达是:

   users.balance = users.balance + (packages.value * packages.percent)

2. 同时，每个余额更新的事务日志都应插入到数据库中:

   user_id: [user's id]
   description: Updated user [user's name] with new balance
   amount: [amount added]

我成功地使用以下 SQL 语句实现了第一个操作:

UPDATE users 
INNER JOIN packages 
ON users.package_id = packages.id
SET users.balance = users.balance + (packages.value * packages.percent);

我似乎找不到任何解决方案来以最高效率和交易安全同时实现这两种操作。我可以检索用户和包并在应用程序中处理它们，然后执行更新和插入，但这将是一件显而易见的事情，因为它确实效率很低。

如果有人能帮助我解决问题，我将不胜感激!

Answer 1

我没有看到任何可能的方法来更新或插入 mysql 中的两个不同表。我知道用一个查询更新 2 个不同的表，但不知道在 2 个不同的表上插入和更新。我认为你可以使用触发器来实现你想要的

CREATE TRIGGER insert_into_logs_when_user_balance_update
AFTER UPDATE ON users 
FOR EACH ROW
  IF NEW.balance <> OLD.balance
  then
    insert into logs(user_id, description, amount)
    values(New.id, New.balance, New.balance)
  END IF

这将在 users.balance 更改/更新时更新日志表。

希望这有帮助。