MySQL pipe separated value vs mapping table

I've a case where 1 record there can be multiple rows possible of master table.

e.g.

sports master table
Cycling
Rafting
Shooting
Football
Cricket

Now I've person table

Person
id
first_name
last_name

Can I have sports id as foreign key in Person table as pipe separated field or can I create separate table which will act as mapping table

e.开发者_开发百科g.

sports_persons_mapping
id
person_id
sports_id

Come to the situation to select from 2 options.

Pipe separated field in person table or mapping table sports_persons_mapping?

In search which is effective or which is more effective and why?

Use the mappting table as you call it. Avoid using columns with comma-separated or pipe-separated values as this goes against good database design practices and make your life impossible when you have to start looking for data, inserting new data, etc. Your queries become overly complicated, difficult to write and your query performance is guaranteed to be less than optimal.