doukuiqian9911 2012-09-24 15:24
浏览 227
已采纳

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

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

  • 写回答

4条回答 默认 最新

  • dpw5865 2012-09-24 15:32
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

悬赏问题

  • ¥15 请教某软件缓存Ts文件破解合并mp4的方法
  • ¥15 求小游戏炸弹人中关于敌人的C++代码
  • ¥15 拿到这个服务器最高权限有偿
  • ¥50 来个抓app跳转支付宝转链接的
  • ¥15 remotes安装提示没有description文件
  • ¥15 AttributeError: 'NoneType' object has no attribute 'drop_duplicates'报错
  • ¥15 以下代码,运行结果报错
  • ¥15 Vivado仿真数据出错
  • ¥15 银河麒麟不支持vant等前端框架怎么办?
  • ¥15 vue3的子组件在父页面调用不显示不生效问题