我如何附加 MYSQL JSON [英] How do I append MYSQL JSON

问题描述

当我运行 SELECT type FROM office;

+--------------------------------------------------------+
| type                                                   |
+--------------------------------------------------------+
| a:2:{i:0;s:16:"Shared Workspace";i:1;s:9:"Workshops";} |
+--------------------------------------------------------+

如何为其他类型的办公室添加额外的 JSON 值.基本上我希望它添加私人办公室"类型.因此，我的数据库中的值将如下所示:

How can I add an extra JSON value for another type of office. Basically I'd like it to add the type "Private Offices". So the value in my DB will then look like:

+-----------------------------------------------------------------------------------+
| type                                                                              
+-----------------------------------------------------------------------------------+
| a:3:{i:0;s:16:"Shared Workspace";i:1;s:9:"Workshops";i:2;s:15:"Private Offices";} |
+-----------------------------------------------------------------------------------+

推荐答案

那不是 JSON 格式.它看起来像 PHP 的 serialize() 函数的输出.

That's not JSON format. It looks like the output of PHP's serialize() function.

没有将值附加到序列化 PHP 数据的 SQL 函数.您应该将它提取到一个 PHP 应用程序中，将它反序列化()到一个 PHP 数组中，然后将数据添加到 PHP 数组中并将其更新回数据库.

There's no SQL function to append values to serialized PHP data. You should fetch it into a PHP application, unserialize() it into a PHP array, then add data to the PHP array and update it back into the database.

类似于以下内容(一些细节已被省略，例如您引用的特定行的 WHERE 条件).

Something like the following (some details have been omitted, like the WHERE conditions for the specific row you reference).

<?php

$pdo = new PDO(...);
$typeSerialized = $pdo->query("SELECT type FROM office WHERE ...")->fetchColumn();
$typeArray = unserialize($typeSerialized);
$typeArray[] = "Private Offices";
$typeSerialized = serialize($typeArray);
$stmt = $pdo->prepare("UPDATE office SET type = ? WHERE ...");
$stmt->execute([$typeSerialized]);

这篇关于我如何附加 MYSQL JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！