从列的数组中按单个元素检索id - Retrive the id as per single element from an array of a coloumn

Retrive the id as per single element from an array of a coloumn

本文关键字：单个元素检索 id 数组 | 更新日期: 2024-03-26

我有一个这样的表。我想通过从"misc"列中选择"20"的"level_id"来检索product_id。大家知道如何在mySQL中做到这一点吗？

product_id    misc
----------    ------
1            {"level_id":20,"group_id":"2132","product_name":"bg","category_id":6}
2            {"level_id":20,"group_id":"2132","product_name":"bg","category_id":6} 
3            {"level_id":25,"group_id":"2132","product_name":"bg","category_id":6} 
4            {"level_id":28,"group_id":"2132","product_name":"bg","category_id":6}
5            {"level_id":28,"group_id":"2132","product_name":"bg","category_id":6} 
6            {"level_id":20,"group_id":"2132","product_name":"bg","category_id":6}

请帮帮我。。。实际上它是一个json数组。

您可以在mysql查询中使用正则表达式来获取level_id=20的记录。未经测试。请检查。

select * from table_name where misc REGEXP '(.*'"level_id'":'"20'".*)'

如果我理解您的更正，您将JSON存储在MySQL中。MySQL目前没有本地JSON支持（对于生产）（请参阅此处：https://dba.stackexchange.com/questions/50382/do-sql-server-or-mysql-contain-json-supporting-functions)。要么你的任务是编写自己的JSON解析器，要么你应该在MySQL（PHP、Java等）之外完成这项工作，要么你必须保存不同的数据。