如何从表字段MySQL中的字符串中获取部分精确值

Question

I got table promos, with field store_id_list (VARCHAR). what i want to achieve here is i want this promo can be available into multiple store in 1 record, instead using multiple record, the store_id_list value is the list of store_id separated by comma (ex: 1,4,5,7,)

Now, i want to get record of table promo, where i have single store_id value, ex: store_id=5 how can i do that in MySQL? can i do that in single query? if using LIKE then more likely i can get 15 or 45 instead of 5.

Answer 1

My advice is to normalize your tables, and have a db-record per store/promo. But it can be done like this:

Make sure you have commas at the beginning and end of the column value, like this:

store_id_list : ,1,4,5,7,

And then query like this:

... where store_id_list like '%,5,%'