从db中检索值，该值以json编码格式存储 - Mysql

Question

Friends from the above image I have saved the values of category_id in json_encode format directly and for processing , I'm retrieving the values but i'm not getting the result as expected . My requirement is I need to retrieve the value of rss_url_id which belongs to category_id 1 , it should return rss_url_id 2 .

I use the query

SELECT rss_url_id FROM rss_url  WHERE `status` = 0 AND REPLACE( REPLACE(`category_id`,'[', '' ) , ']', '' ) LIKE '%1%'

But this query returns the both the result as in the first row it contains value 10 as i'm using like %1% it returns both the result, is there is any alternate query to return only the 2nd row . if i give the value as 1 .

Answer 1

You can try

SELECT rss_url_id FROM rss_url  WHERE `status` = 0 AND `category_id` LIKE '%"1"%'

Keep your category_id (to be searched) in double quotes