mysql - 使用 mySql 查询更新字符串 JSON

Question

我有这个 mySql(版本 5.7.14)字段。

id  shop_name   json_string

 1   shop_1     [{"your_number": "2", "player_id": "6789" }, {"your_number": "3", "player_id": "9877" }, {"your_number": "4",   "player_id": "132456" }]  

 2   shop_2     [{"your_number": "2", "player_id": "6789" }, {"your_number": "3", "player_id": "9877" }, {"your_number": "4",   "player_id": "132456" }]  

如何根据 id 和 JSON your_number 更新字符串？

例如，我想删除 your_number = 3 字符串(其中 id = 2)

结果:

id  shop_name   json_string
 2   shop_2     [{"your_number": "2", "player_id": "6789" }, {"your_number": "4",   "player_id": "132456" }]  

谢谢!

Answer 1

我测试了这个并且它有效:

UPDATE Shops
SET json_string = JSON_REMOVE(
  json_string, 
  SUBSTRING_INDEX(
    JSON_UNQUOTE(JSON_SEARCH(json_string, 'one', '3', null, '$[*].your_number')), '.', 1)
)
WHERE id = 2;

我必须评论说，这并不会让 JSON 看起来对您的应用程序来说是一个好主意。

如果您需要操作 JSON 文档，将数据库存储为一组规范化的表会更容易。

CREATE TABLE Shops (
  id INT PRIMARY KEY,
  shop_name VARCHAR(10)
);

CREATE TABLE ShopPlayers (
  shop_id INT NOT NULL,
  your_number INT NOT NULL,
  player_id INT NOT NULL,
  PRIMARY KEY (shop_id, your_number)
);

现在您可以使用更简单的 SQL 删除玩家:

DELETE FROM ShopPlayers WHERE shop_id = 2 AND your_number = 3;

我在 Stack Overflow 上关注有关 mysql 和 json 的问题已经有一段时间了，我不得不说，在我见过的几乎所有情况下，如果表以传统方式设计会更好。规范化规则。 SQL 查询将更容易编写和调试，它们将运行得更快，并且数据库将更有效地存储数据。