mysql - MySQL 触发器 - 5.7.23-0ubuntu0.16.04.1 - 奇怪的行为并非每次都有效
问题描述
我已经使用 tornado 框架在 python 3 上开发了一个应用程序,该框架连接到一个巨大的 MySQL 数据库,里面有数百万行。
这个数据库是实时同步的,所以数据被插入、删除和更新,所以创建了3个触发器插入、更新和删除。
但是触发器的行为很奇怪,有时它们工作,有时它们不工作,就像当他们决定他们获取数据并做他们必须做的事情时,其他时候他们只是忽略它。
MySQL版本是这个5.7.23-0ubuntu0.16.04.1
我还插入了数据库中的 3 个触发器,可能是我的触发器中的逻辑错误:
插入
BEGIN
IF (SELECT count(*) FROM USERS_STATS WHERE LOGIN = NEW.LOGIN) = 0 THEN
INSERT INTO USERS_STATS (LOGIN) values (NEW.LOGIN);
END IF;
UPDATE USERS_STATS SET
TOTAL_DEPOSIT = TOTAL_DEPOSIT + IF(NEW.CMD = 6 AND NEW.COMMENT NOT LIKE '%internal%' AND NEW.COMMENT NOT LIKE '%Tr %' AND NEW.COMMENT NOT LIKE '%agent%' AND NEW.COMMENT NOT LIKE '%rebate%' AND NEW.COMMENT NOT LIKE '%MAM:CASH%' AND NEW.PROFIT > 0, NEW.PROFIT, 0),
TOTAL_WITHDRAW = TOTAL_WITHDRAW + IF(NEW.CMD = 6 AND NEW.COMMENT NOT LIKE '%internal%' AND NEW.COMMENT NOT LIKE '%Tr %' AND NEW.COMMENT NOT LIKE '%agent%' AND NEW.COMMENT NOT LIKE '%rebate%' AND NEW.COMMENT NOT LIKE '%MAM:CASH%' AND NEW.PROFIT < 0, NEW.PROFIT, 0),
TOTAL_VOLUME = TOTAL_VOLUME + IF(NEW.CMD IN(0, 1) AND NEW.CLOSE_TIME > '1970-01-01', NEW.VOLUME, 0),
TOTAL_REBATE = TOTAL_REBATE + IF((NEW.COMMENT LIKE '%agent%' OR NEW.COMMENT LIKE '%rebate%' OR NEW.COMMENT LIKE '%MAM:CASH%'), ROUND(NEW.PROFIT, 2), 0)
WHERE LOGIN = NEW.LOGIN;
END
更新
BEGIN
DECLARE old_is_deposit integer;
DECLARE new_is_deposit integer;
DECLARE old_is_withdraw integer;
DECLARE new_is_withdraw integer;
DECLARE old_is_rebate integer;
DECLARE new_is_rebate integer;
SET old_is_deposit := OLD.CMD = 6 AND OLD.COMMENT NOT LIKE '%internal%' AND OLD.COMMENT NOT LIKE '%Tr %' AND OLD.COMMENT NOT LIKE '%agent%' AND OLD.COMMENT NOT LIKE '%rebate%' AND OLD.COMMENT NOT LIKE '%MAM:CASH%' AND OLD.PROFIT > 0;
SET new_is_deposit := NEW.CMD = 6 AND NEW.COMMENT NOT LIKE '%internal%' AND NEW.COMMENT NOT LIKE '%Tr %' AND NEW.COMMENT NOT LIKE '%agent%' AND NEW.COMMENT NOT LIKE '%rebate%' AND NEW.COMMENT NOT LIKE '%MAM:CASH%' AND NEW.PROFIT > 0;
SET old_is_withdraw := OLD.CMD = 6 AND OLD.COMMENT NOT LIKE '%internal%' AND OLD.COMMENT NOT LIKE '%Tr %' AND OLD.COMMENT NOT LIKE '%agent%' AND OLD.COMMENT NOT LIKE '%rebate%' AND OLD.COMMENT NOT LIKE '%MAM:CASH%' AND OLD.PROFIT < 0;
SET new_is_withdraw := NEW.CMD = 6 AND NEW.COMMENT NOT LIKE '%internal%' AND NEW.COMMENT NOT LIKE '%Tr %' AND NEW.COMMENT NOT LIKE '%agent%' AND NEW.COMMENT NOT LIKE '%rebate%' AND NEW.COMMENT NOT LIKE '%MAM:CASH%' AND NEW.PROFIT < 0;
SET old_is_rebate := OLD.COMMENT LIKE '%agent%' OR OLD.COMMENT LIKE '%rebate%' OR OLD.COMMENT LIKE '%MAM:CASH%';
SET new_is_rebate := NEW.COMMENT LIKE '%agent%' OR NEW.COMMENT LIKE '%rebate%' OR NEW.COMMENT LIKE '%MAM:CASH%';
IF (SELECT count(*) FROM USERS_STATS WHERE LOGIN = NEW.LOGIN) = 0 THEN
INSERT INTO USERS_STATS (LOGIN) values (NEW.LOGIN);
END IF;
IF (old_is_deposit = 1 AND new_is_deposit = 1 AND OLD.PROFIT != NEW.PROFIT) THEN
UPDATE USERS_STATS SET
TOTAL_DEPOSIT = TOTAL_DEPOSIT + (NEW.PROFIT - OLD.PROFIT)
WHERE LOGIN = OLD.LOGIN;
ELSEIF (old_is_withdraw = 1 AND new_is_withdraw = 1 AND OLD.PROFIT != NEW.PROFIT) THEN
UPDATE USERS_STATS SET
TOTAL_WITHDRAW = TOTAL_WITHDRAW + (NEW.PROFIT - OLD.PROFIT)
WHERE LOGIN = OLD.LOGIN;
ELSEIF (old_is_deposit = 1 AND new_is_withdraw = 1) THEN
UPDATE USERS_STATS SET
TOTAL_DEPOSIT = TOTAL_DEPOSIT - OLD.PROFIT,
TOTAL_WITHDRAW = TOTAL_WITHDRAW + NEW.PROFIT
WHERE LOGIN = OLD.LOGIN;
ELSEIF (old_is_withdraw = 1 AND new_is_deposit = 1) THEN
UPDATE USERS_STATS SET
TOTAL_DEPOSIT = TOTAL_DEPOSIT + NEW.PROFIT,
TOTAL_WITHDRAW = TOTAL_WITHDRAW - OLD.PROFIT
WHERE LOGIN = OLD.LOGIN;
ELSEIF (old_is_deposit = 0 AND new_is_deposit = 1) THEN
UPDATE USERS_STATS SET
TOTAL_DEPOSIT = TOTAL_DEPOSIT + NEW.PROFIT
WHERE LOGIN = OLD.LOGIN;
ELSEIF (old_is_deposit = 1 AND new_is_deposit = 0) THEN
UPDATE USERS_STATS SET
TOTAL_DEPOSIT = TOTAL_DEPOSIT - OLD.PROFIT
WHERE LOGIN = OLD.LOGIN;
ELSEIF (old_is_withdraw = 0 AND new_is_withdraw = 1) THEN
UPDATE USERS_STATS SET
TOTAL_WITHDRAW = TOTAL_WITHDRAW + NEW.PROFIT
WHERE LOGIN = OLD.LOGIN;
ELSEIF (old_is_withdraw = 1 AND new_is_withdraw = 0) THEN
UPDATE USERS_STATS SET
TOTAL_WITHDRAW = TOTAL_WITHDRAW - OLD.PROFIT
WHERE LOGIN = OLD.LOGIN;
END IF;
IF (OLD.VOLUME != NEW.VOLUME) THEN
UPDATE USERS_STATS SET
TOTAL_VOLUME = TOTAL_VOLUME + (NEW.VOLUME - OLD.VOLUME)
WHERE LOGIN = OLD.LOGIN;
END IF;
IF (old_is_rebate = 1 AND new_is_rebate = 1 AND OLD.PROFIT != NEW.PROFIT) THEN
UPDATE USERS_STATS SET
TOTAL_REBATE = TOTAL_REBATE + (ROUND(NEW.PROFIT, 2) - ROUND(OLD.PROFIT, 2))
WHERE LOGIN = OLD.LOGIN;
ELSEIF (old_is_rebate = 0 AND new_is_rebate = 1) THEN
UPDATE USERS_STATS SET
TOTAL_REBATE = TOTAL_REBATE + ROUND(NEW.PROFIT, 2)
WHERE LOGIN = OLD.LOGIN;
ELSEIF (old_is_rebate = 1 AND new_is_rebate = 0) THEN
UPDATE USERS_STATS SET
TOTAL_REBATE = TOTAL_REBATE - ROUND(OLD.PROFIT, 2)
WHERE LOGIN = OLD.LOGIN;
END IF;
END
删除
BEGIN
IF (SELECT count(*) FROM USERS_STATS WHERE LOGIN = OLD.LOGIN) = 0 THEN
INSERT INTO USERS_STATS (LOGIN) values (OLD.LOGIN);
END IF;
UPDATE USERS_STATS SET
TOTAL_DEPOSIT = TOTAL_DEPOSIT - IF(OLD.CMD = 6 AND OLD.COMMENT NOT LIKE '%internal%' AND OLD.COMMENT NOT LIKE '%Tr %' AND OLD.COMMENT NOT LIKE '%agent%' AND OLD.COMMENT NOT LIKE '%rebate%' AND OLD.COMMENT NOT LIKE '%MAM:CASH%' AND OLD.PROFIT > 0, OLD.PROFIT, 0),
TOTAL_WITHDRAW = TOTAL_WITHDRAW - IF(OLD.CMD = 6 AND OLD.COMMENT NOT LIKE '%internal%' AND OLD.COMMENT NOT LIKE '%Tr %' AND OLD.COMMENT NOT LIKE '%agent%' AND OLD.COMMENT NOT LIKE '%rebate%' AND OLD.COMMENT NOT LIKE '%MAM:CASH%' AND OLD.PROFIT < 0, OLD.PROFIT, 0),
TOTAL_VOLUME = TOTAL_VOLUME - IF(OLD.CMD IN(0, 1) AND OLD.CLOSE_TIME > '1970-01-01', OLD.VOLUME, 0),
TOTAL_REBATE = TOTAL_REBATE - IF((OLD.COMMENT LIKE '%agent%' OR OLD.COMMENT LIKE '%rebate%' OR OLD.COMMENT LIKE '%MAM:CASH%'), ROUND(OLD.PROFIT, 2), 0)
WHERE LOGIN = OLD.LOGIN;
END
需要帮助为什么我的触发器无法正常工作。提前谢谢你。
解决方案
所以最后我找到了一种不同的逻辑,在我的情况下无法摆脱死锁,所以我能够在触发器执行的一个时间间隔内更新所有必要的数据,而不是使用 cronjob 脚本进行上述触发器。
推荐阅读
- python - Django + Nginx 无法提供媒体文件
- android - 如何以排序方式检索数据
- python - 导出到带有目录的 Jupyter 笔记本的 HTML 不嵌入绘图
- php - 如何从 PayPal 购买返回并显示成功或失败消息?
- latex - 减小条形图中“坐标附近的节点”中的字体大小#tikzpicture #pgfplots
- sql - 为 SQL 创建域约束
- android - 单击 EditText 使设备崩溃
- node.js - 除非全局安装,否则节点包似乎无法在本地工作
- linux - 控制台上的 SSH 谷歌云权限被拒绝(公钥)与 google-cloud-sdk 文件错误
- sql - 加入 ID 和 TIMESTAMP 列时,为什么我会在 LEFT JOIN 中获得额外的行?