针对delete操作, 常常会慢的严重, 明明查询出来需要删除的记录才70多条, 执行一次都得1分多钟

需求, 下单未支付同样的信息的, 删除除了最大的一条记录的信息(这边假设单人下单不超过2次相同的下单信息的, 我实际的操作是多执行几次就够了)

• 原sql

delete from course_orders
where 
pid=1 
AND state=1
AND created_at<date_sub(now(),interval 7 DAY)
AND id IN (
    SELECT max_order_id FROM (
        SELECT use_uid,course_id,COUNT(1) AS num,MAX(order_id) AS max_order_id FROM `course_order_items`
        WHERE 
        pid=1 
        AND state=0
        AND created_at>'2020-01-01'
        GROUP BY use_uid,course_id
    ) a
    WHERE num>1
)

共 73 行受到影响

执行耗时   : 59.794 sec
传送时间   : 0.001 sec
总耗时      : 59.796 sec

• 优化后

delete co from course_orders co inner join (
    SELECT max_order_id FROM (
        SELECT use_uid,course_id,COUNT(1) AS num,MAX(order_id) AS max_order_id FROM `course_order_items`
        WHERE 
        pid=1 
        AND state=0
        AND created_at>'2020-01-01'
        GROUP BY use_uid,course_id
    ) a
    WHERE num>1
) bb on pid=1 
AND state=1
AND created_at<date_sub(now(),interval 7 DAY)
AND co.id=bb.max_order_id

执行结果
共 73 行受到影响

执行耗时   : 0.066 sec
传送时间   : 0.008 sec
总耗时      : 0.075 sec

正常的key查询哪些文档大家都能用, 但是如果key是 abc:de-fg 这样的值呢, 那么文档中那些办法无法实现查询了

尝试了好久, 终于找到了对应的解决办法

非法键值查询办法

比如demo为
json_data
{"abc:de-fg": "aa"}
那么取出这个值的办法为 json_data->'$."abc:de-fg"'
注意, 只能里面使用双引号的写法

JSON内的键值是否为null的判断方法

json_data
{"abc:de-fg": "aa","b":null}

// 判断里面的值等于null的判断方式
json_data->'$.b'=CAST('null' AS json)

业务场景
有一张表 project_users

表的数据列表
openid
------
[{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}]
[{"app_id": "1", "openid": "oromp5QBh9-a8VQlM99knmq2eM7k"}]
(NULL)

把表扁平化下来
SELECT 
uid
,openid
,openid->'$[*].app_id'
,json_search(openid->'$[*].app_id','all','2')
,openid->'$[0].openid'
,a.app_id
,a.json_openid
 FROM `project_users`,JSON_TABLE(openid, '$[*]' COLUMNS(app_id INT path '$.app_id',json_openid VARCHAR(50) path '$.openid')) a

-----

      uid  openid                                                                                                                  openid->'$[*].app_id'  json_search(openid->'$[*].app_id','all','2')  openid->'$[0].openid'           app_id  json_openid                   
---------  ----------------------------------------------------------------------------------------------------------------------  ---------------------  --------------------------------------------  ------------------------------  ------  ------------------------------
100001002  [{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}]  ["1", "2"]             "$[1]"                                        "oromp5ZEhkLz1BXELF-cxyqap-uw"       1  oromp5ZEhkLz1BXELF-cxyqap-uw  
100001002  [{"app_id": "1", "openid": "oromp5ZEhkLz1BXELF-cxyqap-uw"}, {"app_id": "2", "openid": "oKTQ41OqjJ848_SPe7wbTZO9RmOE"}]  ["1", "2"]             "$[1]"                                        "oromp5ZEhkLz1BXELF-cxyqap-uw"       2  oKTQ41OqjJ848_SPe7wbTZO9RmOE  
100001006  [{"app_id": "1", "openid": "oromp5QBh9-a8VQlM99knmq2eM7k"}]                                                             ["1"]                  (NULL)                                        "oromp5QBh9-a8VQlM99knmq2eM7k"       1  oromp5QBh9-a8VQlM99knmq2eM7k  

查询指定key

demo 数据包
data_cache
----------
{"courses:220-0": 24, "course_classes:1": 25701,"test":"666"}

如果我们查询test值或者移除非常简单
SELECT data_cache->'$.test',json_remove(data_cache,'$.test') FROM `daily_caches` WHERE id=32

result
data_cache->'$.test'  json_remove(data_cache,'$.test')                  
--------------------  --------------------------------------------------
"666"                 {"courses:220-0": 24, "course_classes:1": 25701}  


但是如果我们查询里面的 course_classes:1 key, 上面的方法就不行了, 需要下面的写法才行, 试了好久才试出来的

SELECT data_cache->'$."course_classes:1"',json_remove(data_cache,'$."course_classes:1"') FROM `daily_caches` WHERE id=32

result
data_cache->'$."course_classes:1"'  json_remove(data_cache,'$."course_classes:1"')  
----------------------------------  ------------------------------------------------
25701                               {"test": "666", "courses:220-0": 24}            

创建日志表

DROP TABLE IF EXISTS `table_change_log`;
CREATE TABLE `table_change_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(20) NOT NULL DEFAULT '' COMMENT '表名',
  `table_op` varchar(20) NOT NULL DEFAULT '' COMMENT '操作',
  `table_id` int(11) NOT NULL DEFAULT '0' COMMENT '表ID',
  `add_time` datetime NOT NULL COMMENT '添加时间',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='表操作记录';

创建触发器, 跟踪增删改

DROP TRIGGER IF EXISTS `tg_project_users_insert`;
CREATE TRIGGER `tg_project_users_insert` AFTER INSERT ON `project_users`
FOR EACH ROW INSERT INTO table_change_log (`table_name`, `table_op`, `table_id`, `add_time`) VALUES('project_users', 'insert', new.pro_id, NOW());

DROP TRIGGER IF EXISTS `tg_project_users_update`;
CREATE TRIGGER `tg_project_users_update` AFTER UPDATE ON `project_users`
FOR EACH ROW INSERT INTO table_change_log (`table_name`, `table_op`, `table_id`, `add_time`) VALUES('project_users', 'update', old.pro_id, NOW());

DROP TRIGGER IF EXISTS `tg_project_users_delete`;
CREATE TRIGGER `tg_project_users_delete` AFTER DELETE ON `project_users`
FOR EACH ROW INSERT INTO table_change_log (`table_name`, `table_op`, `table_id`, `add_time`) VALUES('project_users', 'delete', old.pro_id, NOW());