I want to update my raw with if-else condition. Here is my sql code;
update posts
set like =
if(json_contains(like,2,'$'),
json_remove(like,replace(json_search(like,'one',2),'"','')),
json_array_append(like,'$',2)
)
where id = 3
My table;
| id | like |
|---|---|
| 2 | [] |
| 3 | [2] |
After this sql code id= 3 should be like id = 2.
To sum up,: if json contain, remove from json; if json not contain, push data to array
The problem is that JSON_SEARCH() doesn't work with integers. You must use strings.
mysql> set @like = '["2"]';
mysql> select if(json_contains(@like,'["2"]','$'),
json_remove(@like,json_unquote(json_search(@like,'one','2'))),
json_array_append(@like,'$','2')) as `like`;
+------+
| like |
+------+
| [] |
+------+
mysql> set @like = '[]';
mysql> select if(json_contains(@like,'["2"]','$'),
json_remove(@like,json_unquote(json_search(@like,'one','2'))),
json_array_append(@like,'$','2')) as `like`;
+-------+
| like |
+-------+
| ["2"] |
+-------+
Using JSON in MySQL makes everything more difficult. I recommend instead you store your likes in a second table. Then just INSERT a row to add a like, or DELETE a row to remove a like.
SHOW CREATE TABLE. Maybe this the Answer:AUTO_INCREMENTvalues do not change without explicit action.