在操作 MySQL 5.7 中的 json 字段时，并发执行（查询、修改、重新赋值）时数据丢失的问题处理（使用修改 JSON 值的函数 JSON_ARRAY_APPEND）

1、json 字段中存储的是数组格式，值示例：[249, 247, 250, 244, 243, 255, 246, 245, 257, 248, 259, 252, 256]。如图1

2、现有的在队列作业中运行的代码实现如下，生成的 SQL 如下

$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $this->themeInstallationTask->id)->first();
$fromThemeInstallationIds = json_decode($themeSaasTask->from_theme_installation_ids, true);
$fromThemeInstallationIds[] = $this->themeInstallation->originalThemeInstallation->id;
$themeSaasTask->from_theme_installation_ids = json_encode($fromThemeInstallationIds);
$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
$themeSaasTask->save();

select * from `theme_saas_task` where json_contains(`theme_task_ids`, '393') limit 1

update `theme_saas_task` set `from_theme_installation_ids` = '[380]', `theme_saas_task`.`updated_at` = '2023-08-21 03:10:57' where `id` = 23

3、由于队列任务可以同时运行，在并发执行时，会导致需要添加至 json 字段中的值丢失。丢失了 [251, 253, 254, 258]。由于有大量的查询修改保存操作皆在同一时间点 2023-06-20 06:36:27 触发，进而导致了数据丢失。如图2

4、参考：12.17.4 修改 JSON 值的函数 JSON_ARRAY_APPEND  代码实现如下，生成的 SQL 如下 。如图3

$themeSaasTask->from_theme_installation_ids = DB::raw('JSON_ARRAY_APPEND(from_theme_installation_ids, "$", ' . $this->themeInstallation->originalThemeInstallation->id . ')');
$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
$themeSaasTask->save();

update
  `theme_saas_task`
set
  `from_theme_installation_ids` = JSON_ARRAY_APPEND(from_theme_installation_ids, "$", 403),
  `theme_saas_task`.`updated_at` = '2023-08-22 02:39:12'
where
  `id` = 30

5、除了往 json 数组中追加值以外，还存在往 json 数组中删除值的情况，现有的实现如下

$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $this->themeInstallationTask->id)->first();
if ($this->themeInstallation->type == ThemeInstallation::TYPE_UPDATE && $themeSaasTask) {
	$themeInstallationTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
	$themeInstallationTaskIds = array_values(array_diff($themeInstallationTaskIds, [$this->themeInstallationTask->id]));
	if (empty($themeInstallationTaskIds)) {
		ThemeSaasTask::where('id', $themeSaasTask->id)->delete();
	} else {
		$themeSaasTask->theme_task_ids = json_encode($themeInstallationTaskIds);
		$themeSaasTask->save();
	}
}

6、当在队列中并发运行时，字段 theme_task_ids 中总计有 17 个值，理论上来说，执行了 17 次以后，`id` = 22 应该会被删除掉。但是，实际上，字段 theme_task_ids 中还剩下 [380, 381, 382, 383, 385, 386, 389, 394]。执行的 SQL 如下： 如图4

update `theme_saas_task` set `theme_task_ids` = '[379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395]', `theme_saas_task`.`updated_at` = '2023-08-23 01:54:15' where `id` = 22;

/* 删除掉 379 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '379') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[380,381,382,383,384,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:39' where `id` = 22; 

/* 只需要删除掉 380，但是 379 又被添加 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '380') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[379,381,382,383,384,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:39' where `id` = 22;

/* 只需要删除掉 381，但是 379,380 又被添加 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '381') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[379,380,382,383,384,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:39' where `id` = 22;

/* 只需要删除掉 384，但是 380,381 又被添加 */
select * from `theme_saas_task` where json_contains(`theme_task_ids`, '384') limit 1;
update `theme_saas_task` set `theme_task_ids` = '[380,381,382,383,385,386,387,388,389,390,394]', `theme_saas_task`.`updated_at` = '2023-08-23 02:01:40' where `id` = 22;

7、参考：Mysql json 数组删除数据  。发现 JSON_SEARCH 的参数 search_str 暂不支持数字。如图5

SELECT
	JSON_SEARCH( theme_task_ids, 'all', '375' ) 
FROM
	theme_saas_task 
WHERE
	id = 21;

8、最终决定在插入到字段 theme_task_ids 中的值时，先转换为字符串。在队列作业中运行的代码调整实现如下

$themeInstallationTaskId = '' . $this->themeInstallationTask->id . '';
$themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $themeInstallationTaskId)->first();

$themeSaasTask->theme_task_ids = DB::raw('JSON_REMOVE(theme_task_ids,	JSON_UNQUOTE(JSON_SEARCH(theme_task_ids, "one", ' . $themeInstallationTaskId . ')))');
$themeSaasTask->save();

update
  `theme_saas_task`
set
  `theme_task_ids` = '[\"516\",\"517\",\"518\",\"519\",\"520\",\"521\",\"522\",\"523\",\"524\",\"525\",\"526\",\"527\",\"528\",\"529\",\"530\",\"531\",\"532\"]',
  `theme_saas_task`.`updated_at` = '2023-08-23 08:33:57'
where
  `id` = 30;

"JSON_REMOVE(theme_task_ids, JSON_UNQUOTE(JSON_SEARCH(theme_task_ids, \"one\", 519)))";

9、但是在并发情况下，仍然存在一些值未被删除掉，总计 17 个，删除了 12 个，还剩下 5 个。如图6

10、由于这一段代码运行的频率很低，最终决定添加锁实现

        $themeSaasTask = ThemeSaasTask::whereJsonContains('theme_task_ids', $this->themeInstallationTask->id)->first();

        if ($this->themeInstallation->type == ThemeInstallation::TYPE_UPDATE && $themeSaasTask) {

            app(ThemeManager::class)->cleanTheme($this->themeInstallation, $this->themeInstallation->theme);

            DB::beginTransaction();
            $themeSaasTask = ThemeSaasTask::lockForUpdate()->find($themeSaasTask->id);
            $themeInstallationTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
            Log::info(
                '$themeInstallationTaskIds3-'.$this->themeInstallationTask->id,
                [$themeInstallationTaskIds]
            );
            $themeInstallationTaskIds = array_values(array_diff($themeInstallationTaskIds, [$this->themeInstallationTask->id]));
            if (empty($themeInstallationTaskIds)) {
                ThemeSaasTask::where('id', $themeSaasTask->id)->delete();
            } else {
                $themeSaasTask->theme_task_ids = json_encode($themeInstallationTaskIds);
                Log::info(
                    '$themeSaasTask->theme_task_ids3-'.$this->themeInstallationTask->id,
                    [$themeSaasTask->theme_task_ids]
                );
                $themeSaasTask->save();
            }
            DB::commit();
        }

11、最终确认此方案存在问题，最后剩下 [567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581]。期望为 空数组。通过日志记录发现，在查询时，查询出的数据不符合预期，减少了许多，分别打印出第 1 次与 第 17 次的日志。

[2023-08-24 10:10:06] local.INFO: $themeInstallationTaskIds3-567 [
    [
        567,
        568,
        569,
        570,
        571
    ]
] 
[2023-08-24 10:10:06] local.INFO: $themeSaasTask->theme_task_ids3-567 [
    "[568,569,570,571]"
] 

[2023-08-24 10:10:09] local.INFO: $themeInstallationTaskIds3-583 [
    [
        567,
        568,
        569,
        570,
        571,
        572,
        573,
        574,
        575,
        576,
        577,
        578,
        579,
        580,
        581,
        583
    ]
] 
[2023-08-24 10:10:09] local.INFO: $themeSaasTask->theme_task_ids3-583 [
    "[567,568,569,570,571,572,573,574,575,576,577,578,579,580,581]"
]

12、最终找到根源，根源在于同一条记录，在另一个地方在不断地向字段 theme_task_ids 添加值，添加完毕后，就立即入队列，然后在队列中在不断地从字段 theme_task_ids 中删除值。决定向字段 theme_task_ids 添加值完毕后，再统一批量入队列。

调整前的实现：

foreach ($list as $theme) {
	$themeTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
	$themeTaskIds[] = $themeInstallationTask->id;
	$themeSaasTask->theme_task_ids = json_encode($themeTaskIds);
	$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
	$themeSaasTask->save();
	// 推送任务到队列
	InstallThemeToDb::dispatch($themeInstallationTask)->onQueue('longtime');
}

调整后的实现：

$themeInstallationTasks = [];
foreach ($list as $theme) {
	$themeTaskIds = json_decode($themeSaasTask->theme_task_ids, true);
	$themeTaskIds[] = $themeInstallationTask->id;
	$themeSaasTask->theme_task_ids = json_encode($themeTaskIds);
	$themeSaasTask->updated_at = now()->utc()->toDateTimeString();
	$themeSaasTask->save();


	$themeInstallationTasks[] = $themeInstallationTask;
}

foreach ($themeInstallationTasks as $themeInstallationTask) {
	// 推送任务到队列
	InstallThemeToDb::dispatch($themeInstallationTask)->onQueue('longtime');
}

13、确认调整后，第 12 步骤的方案可行，最后剩下 []。期望为 空数组，符合预期。分别打印出第 1 次与 第 16 次、 第 17 次的日志。

[2023-08-24 13:42:24] local.INFO: $themeInstallationTaskIds6-635 [
    [
        635,
        636,
        637,
        638,
        639,
        640,
        641,
        642,
        643,
        644,
        645,
        646,
        647,
        648,
        649,
        650,
        651
    ]
] 
[2023-08-24 13:42:24] local.INFO: $themeSaasTask->theme_task_ids6-635 [
    "[636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651]"
]  

[2023-08-24 13:42:25] local.INFO: $themeInstallationTaskIds6-650 [
    [
        650,
        651
    ]
] 
[2023-08-24 13:42:25] local.INFO: $themeSaasTask->theme_task_ids6-650 [
    "[651]"
] 

[2023-08-24 13:42:26] local.INFO: $themeInstallationTaskIds6-651 [
    [
        651
    ]
]