如何在SQL字段中处理数组？

Question

I have a feed that comes from the State of Florida in a CSV that I need to load daily into MySQL. It is a listing of all homes for sale in my area. One field has a list of codes, separated by commas. Here's one such sample:

C02,C11,U01,U02,D02,D32,D45,D67

These codes all mean something (pool, fenced in area, etc) and I have the meanings in a separate table. My question is, how should I handle loading these? Should I put them in their own field as they are in the CSV? Should I create a separate table that holds them?

If I do leave them as they are in a field (called feature_codes), how could I get the descriptions out of a table that has the descriptions? That table is simply feature_code, feature_code_description. I don't know how to break them apart in my first query to do the join to bring the description in.

Thank you

Answer 1

As a general rule, csv data should never stored in a field, especially if you actually need to consider individual bits of the csv data, instead of just the csv string as a whole.

You SHOULD normalize the design and split each of those sub "fields" into their own table.

That being said, MySQL does have find_in_set() which allows you sort-of search those csv strings and treat each as its own distinct datum. It's not particularly efficient to use this, but it does put a bandaid on the design.