json

u5rb5r59  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(281)

我有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更新字符串?
例如,我想删除id=2的\u number=3字符串
结果:

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

谢谢!

6uxekuva

6uxekuva1#

我测试了这个,它工作了:

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;

我已经关注了一段时间关于mysql和json的堆栈溢出问题,我不得不说,在我所看到的几乎所有情况下,如果按照规范化规则以传统方式设计表会更好。sql查询将更易于编写和调试,运行速度更快,数据库将更高效地存储数据。

相关问题