在MySQL中检查JSON编码的数组中是否存在某个值

Question

我正在处理一个Mysql触发器，用于在我的数据库中执行特定操作。在开始之前，我想检查在我的配置表中的json_encode字符串中是否存在某个值，并在检测完成后继续运行触发器。 我的配置表是由php脚本进行管理，如下所示：

-------------------------------------------
| config_name | config_value              |
-------------------------------------------
| target_id   | ["1","16","18","22","37"] |
-------------------------------------------

在PHP中，我们可以使用以下代码：

if (in_array(json_decode(config_value)) and ...)

但是我的问题在于MySQL语法不支持in_array和json_decode。

在MySQL触发器中，如何检查我的值是否存在于“config value”中？

- Amin. Sh.

Answer 1

如何解决这个问题

如果您正在将 JSON 存储在 MySQL 中，请确保升级到 MySQL 5.7，然后您可以利用可用的一系列JSON 函数。在您的特定情况下，您可以执行以下操作：

   SELECT * FROM my_table WHERE JSON_SEARCH(config_value,"one", "17") IS NOT NULL;

你绝对应该做的事情

你的数据存在问题。如果你发现自己总是在搜索一个JSON字段，那么这实际上意味着你的表应该进行规范化。

更新：根据 @Sammitch 的建议更改了第二部分的标题。

- e4c5

---

更像是“你绝对应该做的事情”。 - Sammitch

好的，@Sammitch，现在更改 :-) - e4c5

得到我的支持，以前从未听说过这个，并将在一些客户项目中使用它！干杯 :-) - Darren

感谢您的使用，Darren，谢谢您的投票。 - e4c5

Answer 2

好的，你拥有的是一个JSON_ARRAY()，要检查特定的id是否存在于JSON_ARRAY()中，只需放置一个条件即可，例如：JSON_CONTAINS(config_value,"1")=1，所以如果 "1" 存在于 JSON_ARRAY() 中，则返回 1，否则返回 0。

此外，在执行插入/更新查询时，你可以使用"..config_value=JSON_ARRAY(" . implode(',', $arr) . ") ..."而不必在 PHP 代码中执行 json_encode。

- Shikhar Tyagi

Answer 3

使用这个：

@Query(value = "select * from configs where json_contains(config_value, json_array(?1))))", nativeQuery = true)
List<Config> list(String config);

- GeekLei

Answer 4

感谢您的回复。但不幸的是，mysql版本是5.1，无法升级。实际上，我正在为voipswitch系统添加一些功能。因此，我的灵活性受到现有功能的限制。

我正在选择多个资费，并希望记录插入的通话（通过我的触发器在“calls”表中插入通话），这些通话属于选定的资费，在列表中进行一些分析。

此外，我尝试使用php序列化函数而不是json_encode将我的资费ID存储在配置表中。但是mysql也不支持反序列化。

- Amin. Sh.

---

你应该回复别人的评论，指出这是问题所在，而不是回答问题陈述。 - Kaushik

你可以尝试使用mysql函数INSTR或FIND_IN_SET来检查值。尝试玩弄这些函数，你可能会找到解决方案。你可以使用INSTR，首先检查,"17",的值，然后是"17",，最后是,"17"。看看这些，我添加了双引号，这样就可以精确搜索你要查找的内容。第一个意味着你要查找的数字在中间，第二个意味着在开头，第三个意味着在结尾。试着理解一下。 - Kaushik