In PHP 7.4, MySQL 5.7, insert a field of type json, successfully insert the analysis of the value string(4) “null” (related to json_encode(null))

1. In the MySQL table, the field type is JSON, and it is allowed to be null. as shown in Figure 1

Figure 1

CREATE TABLE `table` (
  `schema` json DEFAULT NULL COMMENT '主题的Schema，包括页面、主题级别、组件级别',
);

2. Since only is_null() is judged in the program, if it is not null, the default is json format. When its value is String(4) “NULL”, the program throws a 500 exception. Displayed as null, indicating that its value is string(4) “null”, which is not as expected. Displayed as (NULL), indicating that its value is NULL, in line with expectations. as shown in Figure 2

Figure 2

3. View the generated sql from the log and confirm that in the record in the change table, the value is assigned tonull. as shown in Figure 3

Figure 3

update
  `table`
set
  `schema` = 'null',
  `checksum` = '0821db9eefeab3d74bbbac6f58cd68e4c8f1b9e2',
  `table`.`updated_at` = '2023-04-21 03:41:48'
where
  `id` = 432070

4. Print the changes of the relevant variables at the SQL generation. The results are as follows. as shown in Figure 4

Figure 4

$schema1 = $themeAsset->schema;
$schema2 = $this->themeSettingMigration->migrateJsonTemplate($filename, $schema, $themeInstallationTask, $availableVersions);
$themeAsset->schema = json_encode($schema2);
Log::info(
	$themeAsset->id,
	[
		'schema1' => $schema1,
		'schema2' => $schema2,
		'schema3' => $themeAsset->schema,
	]
);

[2023-04-21 11:41:48] local.INFO: 432062 {
    "schema1": null,
    "schema2": null,
    "schema3": "null"
} 

5. This concludes that when json_encode(null) is returnednull, and eventually the SQL will be updated to the valuenull. Decide to adjust as follows, add judgment, when not null, only execute json_encode, otherwise return null

$schema = $this->themeSettingMigration->migrateJsonSettingsData($configSettingsDataSchema, $themeInstallationTask, $availableVersions);
$configSettingsData->schema = $schema ? json_encode($schema) : null;