MySQL中存储了序列化的字段，根据需求判断该条记录是否满足？

Question

额，大家好，首先感谢大家抽出时间看我的问题，可能描述不是很详细
我来举个例子吧，比如会员设置了自己的爱好，爱好是复选框，值分别是1,2,3···分别代表足球、篮球等等
PHP接收到的是数组，序列化之后再存到数据库，那用户在前台搜索同样爱好的会员，比如足球，怎么把数据表中有相同爱好的小伙伴查询出来呢？
我当时面对这个需求的时候，就两个选项，所以查询A爱好的时候，把A的值序列化，然后用like去查询，但总感觉这样做不太妥当，两个还好弄，如果N多个呢

Answer 1

If you have query requirements, it is not suitable to store this thing in serialized form. Create another association table. In your example, it is a hobby table. It needs to include at least two fields: user ID and hobby. Search When you go to this table to search, then deduplicate the user ID to get the result you want, and then use this result to go to the user table to perform a select...in query

Answer 2

There is already a problem with your design. This correspondence requires three tables.
The first one用户表(uid), uid as related field
The second one兴趣表(hobby), hobby_idas related field
The third one用户兴趣表(uid_hobby) The two fields uid, hobby_id can be related Get up. Which hobbies does a certain user have?
OK, your requirement is 用户在前台搜索同样爱好的会员
The ID of football is 1
so the following statement.
select uid from uid_hobby where hobby_id = 1 You can query all members who like football (including yourself)
Finally, I suggest like Except for the left prefix, no other indexing will be used, full table scan + matching, extremely efficient Low.

Answer 3

mysql find_in_set