修改存储在表列中的JSON字符串的属性值

I have JSON string stored in my database column. I have to update that value in JSON string.

我将JSON字符串存储在数据库列中。我必须用JSON字符串更新那个值。

Here Is my table.

这是我的桌子。

I want to update the state value inside it.

我想要更新里面的状态值。

Example:

Name1 has State value KA so I want to update it to GJ.

Name1有状态值KA，所以我想把它更新到GJ。

What I have Tried So far?

UPDATE Customer
SET Detail = JSON_MODIFY(Detail , '$.Address.State', 'KA')
WHERE Name = 'name1';

Also Tried JSON_REPLACE is also not working.

同样尝试过JSON_REPLACE也不能工作。

But it shows the error:

FUNCTION Customer.JSON_MODIFY does not exist

功能的客户。JSON_MODIFY不存在

Note: I know one workaround to do this but I didn't Want to fetch that string and update it completely. I want to update the particular detail in string.

注意:我知道有一种方法可以做到这一点，但我不想获取那个字符串并对其进行完全更新。我想在字符串中更新特定的细节。

I have also created the SQL Fiddle.

我还创建了SQL Fiddle。

I am doing this on localhost. Below are the localhost detail.

我在本地主机上做这个。下面是localhost的详细信息。

Database server     
Server: localhost (localhost via TCP/IP)
Software: MySQL
MySQL Version :5.5.24

phpMyAdmin
Version information: 3.5.1, latest stable version: 4.7.3

4 个解决方案

#1

12.16 JSON Functions

12.16 JSON函数

...

…

Unless otherwise indicated, the JSON functions were added in MySQL 5.7.8.

除非另有说明，JSON函数是在MySQL 5.7.8中添加的。

...

…

Try:

试一试:

UPDATE `Customer`
SET `Detail` = JSON_REPLACE(`Detail`, '$.Address.State', 'GJ')
WHERE `Name` = 'name1';

See db-fiddle.

看到db-fiddle。

#2

As @wchiquito already pointed out, the JSON function were added in MySQL 5.7.8.

正如@ wchi奎多已经指出的，JSON函数是在MySQL 5.7.8中添加的。

If you are not able to upgrade your MySQL installation you will have to take the workaround you mentioned. Using regular expression to replace your value is also not going to work without being able to define custom mysql functions. (Also there is a lot that can go wrong if you do operations like this with regex...)

如果你不能升级你的MySQL安装，你将不得不采取你提到的解决办法。如果不能定义自定义的mysql函数，使用正则表达式替换值也不能正常工作。(如果你像regex一样做这样的操作，也会有很多出错的地方…)

So the only options I see you have, are:

所以我看到你唯一的选择是:

upgrade your installation (award to @wchiquito).

升级您的安装(奖励到@ wchi奎多)。
Fetch the column, parse it and update it. Just as you mentioned yourself as a workaround.

获取列，解析并更新它。就像你提到自己是一个变通的人一样。

That could look something like this:

它可以是这样的:

// fetch the details
$sth = $pdo->prepare('select `Detail` from `Customer` where `Name` = ?');
$sth->execute(['name1']);

$detail = json_decode($sth->fetchColumn(), true);

// modify the state
$detail['Address']['State'] = 'KA';

// update the details
$sth = $pdo->prepare('update `Customer` set `Detail` = ? where `Name` = ?');
$sth->execute([json_encode($detail), 'name1']);

But I recommend just upgrading your MySQL installation if possible.

但是如果可能的话，我建议升级你的MySQL安装。

#3

As you have an old MySQL, fetch the JSON string, decode it to an array, edit the array, re-encode it, and update your row:

当您有一个旧的MySQL时，获取JSON字符串，解码成一个数组，编辑数组，重新编码，并更新您的行:

// Fetch row
$json = $row['columnName'];
$array = json_decode($json, true); //true creates array and not stdClass
$array['valueToChange'] = 'some new value';
$json = json_encode($array);
// Perform update query

#4

You can:

您可以:

Use JSON_INSERT function to add the property to the object if it is not exist

使用JSON_INSERT函数将属性添加到不存在的对象中

Use JSON_REPLACE function substitutes the property only if it is exist

使用JSON_REPLACE函数只在属性存在时替换属性

Use JSON_SET function to add the property if it is not found then replace it.

使用JSON_SET函数添加属性(如果没有找到属性)，然后替换它。

Hope this helps!

希望这可以帮助!

#1