Laravel 5.8更新MySQL JSON列强制转换为数组更改数据存储格式 [英] Laravel 5.8 update mysql json column cast as array changes data stored format

问题描述

我有一个表，其中的json列强制转换为数组.架构创建具有定义如下的列:

I have a table with a json column cast as array. The Schema creation has the column defined like this:

$table->json('row_ids');

在我的课上:

protected $casts = [
    'row_ids' => 'array',
];

我通过从另一个表中获取每个行id来生成此数组/列中的数据，如下所示:

I generate the data in this array / column by getting every row id from another table like this:

$id_array = DB::table($table->name)->pluck('api_id')->toArray();

TableChannelRow::create([
    'table_api_id' => $table->api_id,
    'channel_api_id' => $channel->api_id,
    'row_ids' => $id_array,
]);

当我dd收集记录时，我可以看到目标表中的列OK，其中一列包含预期的数组:

When I dd a collection record I can see the columns in the target table OK, with one of the columns containing an array as expected:

#attributes: array:4 [▼
  "api_id" => 2
  "table_api_id" => 1
  "channel_api_id" => 6
  "row_ids" => "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42,  ▶"
]

当我签入MySQLWorkbench时，数据如下所示:

When I check in MySQLWorkbench the data looks like this:

在另一个控制器中，我想像这样在此列的数组中添加或删除条目:

In another controller I want to add or remove entries from the array in this column like this:

$table_channel_row = TableChannelRow::where('table_api_id', '=', $rowId)
    ->where('channel_api_id', '=', $channelId)
    ->first();

$row_ids = $table_channel_row->row_ids;

if ($is_checked == 'yes') {

    // Add value to array if it does not already exist
    if (!in_array($row_id, $row_ids)) {
        array_push($row_ids, $row_id);
    }

} else {

    // Remove value from array
    $row_id_array[] = $row_id;
    $row_ids = array_diff($row_ids, $row_id_array);

}

$table_channel_row->update([
    'row_ids' => $row_ids,
]);

现在MySQLWorkbench中的数据如下:

为什么首先将其存储为类似于PHP数组，然后在更新时将其存储为json对象?

Why does it get stored looking like a PHP array in the first instance, then later on update it gets stored as a json object?

另外，删除的PHP代码可以正常工作，但是添加没有起作用，尽管它不会触发异常(您可以在第二个图像中看到第一个值被删除，但是当我在MySQL的对象中找不到该值时，触发代码以将其添加)

Additionally the remove PHP code is working, yet the add is not, though it does not trigger an exception (you can see the first value is removed in the second image, but I cannot find it in the object in MySQL when I trigger the code to add it)

我想念什么?谢谢！

推荐答案

之所以进行不同的保存，是因为

The reason why it's saving differently is because array_diff returns an associative array whereas your initial data is an indexed array. Take the following for example:

$ids = [1, 2, 3, 4, 5];
$ids2 = [1, 2, 3];

然后，如果执行array_diff($ids, $ids2)，它将返回以下内容:

Then if you perform an array_diff($ids, $ids2), it would return the following:

[
    3 => 4,
    4 => 5
]

因此，如果要保存为与初始格式相同的格式，则必须使用

So if you want to save as the same format as your initial one, you have to retrieve the values of the array using array_values:

$row_ids = array_values(array_diff($row_ids, $row_id_array));

这篇关于Laravel 5.8更新MySQL JSON列强制转换为数组更改数据存储格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！