检查mysql中的json编码数组中是否存在值 [英] check if a value exists in json encode array in mysql

问题描述

我正在使用Mysql触发器来对数据库进行操作.在进行任何操作之前，我想检查配置表中的json_encode字符串中是否存在一个值，然后继续运行触发器. 我的配置表由php脚本处理，如下所示:

I am working on a Mysql trigger to do something on my database. Before any thing, I want to check if a value exists in a json_encode string in my config table and after that proceed to run the trigger. my config table is handled by php scripts and looks like this:

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

在php中，我们可以使用: 如果是in_array(json_decode(config_value))和...

in php we can use: if in_array(json_decode(config_value)) and ...

但是我的问题是Mysql语法不支持in_array和json_decode

but my problem is in Mysql syntax which doesn't support in_array and json_decode

如何检查Mysql触发器中配置值"中是否存在我的值?

How can I check if my value exists in 'config value' in Mysql trigger?

推荐答案

如何解决此问题

如果要在mysql中存储JSON，请确保升级到mysql 5.7，然后可以使用

How to solve this problem

If you are storing JSON in mysql, make sure that you upgrade to mysql 5.7, then you can make use of the range of JSON functions available. In your particular case, you can do

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

你绝对应该做的

您的数据有问题.如果您发现自己一直在搜索JSON字段，那实际上意味着您的表应该被规范化.

What you Definitely ought to be doing

You have a problem in your data. If you find that you are always searching a JSON field, what that really means is that your table should be normalized.

更新:第2部分，标题已按照@Sammitch的建议更改

update: section 2, title changed as suggested by @Sammitch

这篇关于检查mysql中的json编码数组中是否存在值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！