MySQL-数据修复-线上课程分配的提醒消息错误-从json中提取字段更新其他字段

 SELECT * from user_message where message_type in (23,24) and param not like CONCAT("%",enterprise_id,"%");
+---------+----------------------+--------------+-------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+---------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-----------+
| id      | enterprise_id        | message_type | user_id           | content                                                                                                               | redirect_url                                 | is_read | param                                                                                                                                 | create_time | update_time | is_delete |
+---------+----------------------+--------------+-------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+---------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-----------+
| 2384658 | tianjinjingjikaifaqu |           23 | administrator02   | 新课程【文档课程（administrator01）】已经添加到您的必修课程中，点击开始学习                                           | /safetyapp/educate/view/courseDetail?id=101  |       0 | {"id":101,"suffix":"2022-03","containerId":"BaoXianFuWu","courseId":101,"userId":"administrator02","distributeAction":1}              |  1647423990 |  1647423990 |         0 |
| 2387069 | nanjingzongyangaoxin |           23 | 13205158918       | 新课程【隐患排查】已经添加到您的必修课程中，点击开始学习                                                              | /safetyapp/educate/view/courseDetail?id=5005 |       0 | {"id":5005,"suffix":"2022-03","containerId":"kunshanfulongshiyeqi","courseId":5005,"userId":"13205158918","distributeAction":1}       |  1647571245 |  1647571245 |         0 |
| 2387070 | nanjingzongyangaoxin |           23 | 13225121797       | 新课程【隐患排查】已经添加到您的必修课程中，点击开始学习                                                              | /safetyapp/educate/view/courseDetail?id=5005 |       0 | {"id":5005,"suffix":"2022-03","containerId":"kunshanfulongshiyeqi","courseId":5005,"userId":"13225121797","distributeAction":1}       |  1647571245 |  1647571245 |         0 |
| 2387071 | nanjingzongyangaoxin |           23 | 13328058578       | 新课程【隐患排查】已经添加到您的必修课程中，点击开始学习                                                              | /safetyapp/educate/view/courseDetail?id=5005 |       0 | {"id":5005,"suffix":"2022-03","containerId":"kunshanfulongshiyeqi","courseId":5005,"userId":"13328058578","distributeAction":1}       |  1647571245 |  1647571245 |         0 |
| 2399404 | nanjingjinqihuagongj |           23 | admin_sdwhp       | 新课程【安全教育】已经添加到您的必修课程中，点击开始学习                                                              | /safetyapp/educate/view/courseDetail?id=5082 |       0 | {"id":5082,"suffix":"2022-03","containerId":"shandongshengweixian","courseId":5082,"userId":"admin_sdwhp","distributeAction":1}       |  1648696944 |  1648696944 |         0 |
| 2403364 | jiangsudenahuaxueanq |           23 | admin_51          | 新课程【演示课程2】已经添加到您的必修课程中，点击开始学习                                                             | /safetyapp/educate/view/courseDetail?id=4915 |       0 | {"id":4915,"suffix":"2022-04","containerId":"AnQuanShengChanGong2","courseId":4915,"userId":"admin_51","distributeAction":1}          |  1648826306 |  1648826306 |         0 |
| 2403876 | jiangsudenahuaxueanq |           23 | admin_51          | 新课程【测试培训课程0402-2（岗位培训）】已经添加到您的必修课程中，点击开始学习                                        | /safetyapp/educate/view/courseDetail?id=5106 |       0 | {"id":5106,"suffix":"2022-04","containerId":"AnQuanShengChanGong2","courseId":5106,"userId":"admin_51","distributeAction":1}          |  1648864772 |  1648864772 |         0 |
+---------+----------------------+--------------+-------------------+-----------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+---------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-----------+
109 rows in set (1.35 sec)


update user_message set enterprise_id = JSON_UNQUOTE(json_extract(param,"$.containerId")),update_time = update_time where message_type in (23,24) and param not like CONCAT("%",enterprise_id,"%");
Query OK, 109 rows affected (3.04 sec)
Rows matched: 109  Changed: 109  Warnings: 0


mysql> SELECT JSON_UNQUOTE(json_extract(param,"$.containerId")) from user_message where message_type in (23,24) and param not like CONCAT("%",enterprise_id,"%");
Empty set (0.87 sec)


and param not like CONCAT("%",enterprise_id,"%") 应该也可以改成 enterprise_id <>  JSON_UNQUOTE(json_extract(param,"$.containerId"))