[MySQL] How to store array appropriately?

Question

Actual Phenomenon

1. Sometimes it is appropriate to store the content of a field in an array, such as point_types = [1, 2, 3]

2. It would be troublesome to split it into multiple fields

3. What I am doing now is

   1. point_types: VARCHAR(100) (think of it as a JSON string)

   2. Save: json.dumps(point_types_py) --> point_types

   3. Get: json.loads(point_types)

Expected Phenomenon

1. Practice 3, I always feel that I am making steel using the original method

2. Is there a better way

Context

• Linux

• MySQL 5.6

Answer 1

There is no need for query. It is ok to save json. If you need to query, just create a table

Answer 2

Generally, there are two situations:
1. It is just for display without complicated queries. It is stored in a text field and separated by commas. JSON feels a bit overkill.
2. Statistical analysis needs to be done. For example, the question TAG on SF requires a lot of classification statistics. A more reasonable solution is to create a new correspondence table between questions and TAGs.