标签 mysql 下的文章

有一个日志表，记录每日(date)不同类型(datatype) 数据值 格式如下

mysql> select * from userlog;

+-----+------------+----------+-----------+--------+
| uid | date       | datatype | datavalue | logger |
+-----+------------+----------+-----------+--------+
|   4 | 2020-03-03 |        1 |        83 |      2 |
|   4 | 2020-04-04 |        1 |        82 |      1 |
|   5 | 2020-03-03 |        1 |        83 |      2 |
|   6 | 2020-03-03 |        1 |        70 |      3 |
|   6 | 2020-04-04 |        5 |        23 |      3 |
|   6 | 2020-04-04 |        6 |        20 |      3 |
|   6 | 2020-04-04 |        7 |        37 |      3 |
|   6 | 2020-04-04 |       10 |        38 |      3 |
+-----+------------+----------+-----------+--------+
23 rows in set (0.00 sec)

我需要列出各项数据的最新状态时，并用了 group by 和 order by , 发现sql脚本并未如我期望地展示数据

mysql> SELECT * FROM userlog WHERE uid = 4 GROUP BY datatype ORDER BY date DESC;

+-----+------------+----------+-----------+--------+
| uid | date       | datatype | datavalue | logger |
+-----+------------+----------+-----------+--------+
|   4 | 2020-03-03 |        1 |        83 |      2 |
+-----+------------+----------+-----------+--------+
1 row in set (0.00 sec)

原因很好理解，处理的顺序是 先按自然顺序 group 了，才对结果 date 做 order DESC;

- 阅读剩余部分 -

某计费系统出了bug 出现了一批异常扣费记录，影响部分用户的余额。
需要找出异常的扣费记录不难，但不同用户的异常金额是不一样的。
因此无法简单用 'update set blance += patch_value ' 的方式划一校正。

方案一：研究SQL语法有没有办法 UPDATE 嵌套子查询，使每个用户发的 更新值不一样，发现这样写sql语句的逻辑繁琐语义风险高，抛弃！

方案二：用python或php脚本，先把异常扣费记录查出并根据用户统计，然后再逐个用户执行update。虽然可行，但补丁脚本怕被重复执行且执行后无事务可查不可逆，在业务层面并不理想，属于粗暴操作，也没有采用。

方案三：最终方案。计费记录表新增了一个对DELETE事件的触发器(trigger)，当删除记录时将扣费的金额归还到用户的余额。这样做的好处是修正的余额由mysql内部逻辑实现。而且将来其他情况下需要对异常记录撤销也能满足余额自动返还。不用人为用写程序干预。

实现步骤

0.首先备份原数据

此步骤非必须，只是作为保险在进行操作前对相关数据进行备份，万一出现严重错误仍可把备份表还原。

#复制表结构
CREATE TABLE `paylog_bak` LIKE `paylog`; 

#复制数据
INSERT INTO `paylog_bal` (SELECT * FROM `paylog`);

1. 创建 trigger

CREATE TRIGGER `refund` 
AFTER DELETE ON `paylog`FOR EACH ROW 
    UPDATE `customer` 
    SET `balance` = `balance` + OLD.`amount` 
    WHERE `id` = OLD.`cid`

sql解读：
创建一个名为 refund （退款） 的触发器，当 paylog 发生 删除事件时
将删除行的支付金额(amount)，增加到对应的客户表(customer)的余额(balance)

2. 删除异常的记录

此处sql略，根据异常数据的特征 DELETE FROM paylog WHERE .... 就行。执行后检查受影响的用户余额都有变化。

需求完美实现

最近有个lbs相关的项目，要求向用户位置推荐周边的(DB中的)已有地标。数据取自GPS的坐标系经纬度。是个典型求两点距离最优解的命题。
（需求接近于“滴滴拉屎”，自行脑洞一下）

备选的实现方向有：

  1. 使用地图服务商（例如百度地图、腾讯地图）提供的测距API计算 —— 缺点：不适合进行大量的计算，依赖第三方API业务稳定性有不可控,有网络请求延迟；
  2. 在后端脚本中进行计算 —— 缺点：每次查询需要取出数据库中全部地标数据，还需要自行实现排序结构化计算结果
  3. 在数据库中进行计算排序 —— 缺点：加重DB负担

衡量利弊后，似乎#3是比较理想的方案，于是找到了以下这个

《mysql 下 计算 两点 经纬度 之间的距离》
https://www.cnblogs.com/u0mo5/p/4260382.html

算法如下

round(6378.138*2*asin(sqrt(pow(sin( (`lat1`*pi()/180-`lat2`*pi()/180)/2),2)+cos(`lat1`*pi()/180)*cos(`lat2`*pi()/180)* pow(sin( (`lon1`*pi()/180-`lon2`*pi()/180)/2),2)))*1000)

验证的demo

#113.348407,22.968607 //lat, lon
#113.333603,22.973731 //地图距离1.6km

SELECT round(6378.138*2*asin(sqrt(pow(sin( (113.348407*pi()/180-113.333603*pi()/180)/2),2)+cos(113.348407*pi()/180)*cos(113.333603*pi()/180)* pow(sin( (22.968607*pi()/180-22.973731*pi()/180)/2),2)))*1000) as dist_m
dist_m
1663

经过验证计算结果和地图上直接测距一致, 单位 米

补充
初期的地标库比较小只有一百多条，所以直接用上面的sql便可以满足。若今后地标数据扩大，也会面临计算量大mysql计算负荷重的问题（需要计算后再排序，索引对提升速度无效）。届时进行的优化措施可以把坐标点按经纬度划分成若干间距的网格，仅在用户坐标的网格内（或加上周边一共9个网格内）进行检索，可以有效缩减计算量。
毕竟，如果我在北京王府井人有三急，我知道阿姆斯特丹有豪华六星厕所也没意义（笑）

  1. 1
  2. 2