Как обеспечить одинаковое обновление на 2 таблицы одновременно на MySQL

Question

Как обеспечить одинаковое обновление на 2 таблицы одновременно на MySQL

У меня есть проблема, которую я не могу решить. Я пытаюсь обновить 2 разные таблицы с одинаковыми данными, но всегда получаю несоответствие

У меня есть 2 таблицы: users а также stats

users состав:

id: int(25)
balance: decimal(18,6)

а также stats состав:

date: date
userid: int(25)
balance: decimal(18,6)

Я использую транзакции innodb для обновления таблиц следующим образом:

    mysql_query("BEGIN TRANSACTION");

$result1 = mysql_query("INSERT INTO users(id, balance)
VALUES ".$balance."ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance)");$result2 = mysql_query("INSERT INTO pubday (date, userid, balance)
VALUES ".$balance."ON DUPLICATE KEY UPDATE balance=balence+VALUES(balance)");if($result1 === false || $result2 === false) {
mysql_query("ROLLBACK");
}else{
mysql_query("COMMIT");
}

Я обновляю таблицы не менее 5 миллионов раз в день. Независимо от того, что я всегда получаю незначительное расхождение между балансом от users а также stats с помощью этих запросов:

SELECT sum(balance) from stats

а также

SELECT balance from users

Например, я получаю баланс = 302,001731 по статистике и баланс = 302,19010 по пользователям. Это должно быть то же самое.

У меня вопрос в чем здесь ошибка или что я делаю не так? Или как лучше подойти к этому вопросу? обновить 2 таблицы одновременно с одинаковыми данными.

0

data-integrity mysql normalization php sql

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Ответ прост: не иметь нескольких точек авторитета. Если два столбца должны быть одинаковыми, сделать их в одном столбце. Я бы сделал это удаление stats.balanceи вычисление статистики, когда вам это нужно, на лету через SQL.

В конце концов, специальные запросы являются одной из основных причин, по которым системы управления реляционными базами данных (RDBMS) были первоначально исследованы в 1970-х годах.

Между тем, похоже, что вам нужно какое-то «материализованное представление», чтобы вы могли отображать данные из прошлого. Если это так, рассмотрите возможность сохранения каждый транзакции на некоторое время, а затем вставлять в таблицу статистики через равные промежутки времени на основе расчетов SQL. Что-то вроде:

INSERT INTO stats (date, userid, balance)
SELECT CURDATE(), id, SUM(balance) FROM users GROUP BY 1, 2;

И, возможно, в зависимости от ваших потребностей, использовать UPSERT через ON DUPLICATE KEY UPDATE,

Очевидно, что SUM(balance) не является полной или правильной, но идея суммирования — это точка после обновления вашей схемы.

3