在Laravel 5.4中使用多个表连接处理数百万行的高效且交易安全的方法

Question

This does not necessarily apply only to the Laravel php framework as I believe my intended tasks can be processed on the MySQL server itself as well with appropriate SQL statements.

There are 3 tables as following:

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

All of the tables above are linked to the objects/models User, Package, and Transaction respectively. They contain timestamp columns and are inserted/updated automatically by Laravel.

1. The application is scheduled to update the column balance in table users based on the value of package (that is assigned to the user) multiplied by percent field plus the original balance value, every day at midnight 12:00am. A clearer expression would be:

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

2. At the same time, a transaction log for each balance update should be inserted to the database:

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

I have managed to achieve the first operation with the following SQL statements:

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

I can't seem to find any solution to achieve both operations at the same time with highest efficiency and transaction-safe. I could possibly retrieve both users and packages and process them in the application then do UPDATEs and INSERTs, but it'd be a no-brainer thing to do as it's really inefficient.

I'd appreciate if someone could help me with the solution!

Answer 1

I don't see any possible way to update or insert on two different tables in mysql. I know of updating 2 different tables with one query but not insert and update on 2 different tables. I think you can use triggers to achieve what you want

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

This will update logs tables when users.balance changes/update.

Hope this helps.