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"] |
-------------------------------------------
in php we can use:
if in_array(json_decode(config_value)) and ...
but my problem is in Mysql syntax which doesn't support in_array and json_decode
How can I check if my value exists in 'config value' in Mysql trigger?
解决方案
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;
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.
update: section 2, title changed as suggested by @Sammitch