代码之家 › 专栏 › 技术社区 › TerryMatula

mysql-使用逗号分隔值列表创建联接

mysql php

5

TerryMatula · 技术社区 · 15 年前

我有一张表,上面有商人姓名的字段,还有他们提供服务的字段。服务字段是与另一个服务表相关的整数的逗号分隔列表,其中包含服务ID和服务名称字段。

I'm trying to create a single query that joins those two, so I can have a list of Merchants, along with the Services Names. My solution so far has been to do a second loop within my initial 'foreach' loop, but that can mean 5 or 6 db calls for each Merchant name.

After some StackOverflow-ing (google-ing), I noticed that using a comma separated field is probably not the best way to go.

Anyone have either a way to do the join, or thoughts on how the db structure could be set up better? Many thanks in advance!

2 回复 | 直到 15 年前

1

5

Marcus Adams 15 年前

Merchant
MerchantId   Name
          1   Adams Consulting

Merchant_Services
MerchantId    Service
         1    SEO
         1    Brand Consulting

您实际上可以得到一个逗号分隔的列表:

SELECT m.*, GROUP_CONCAT(ms.Service) AS Services
FROM Merchant m
LEFT JOIN Merchant_Serivces ms
ON ms.MerchantId = m.MerchantId
GROUP BY m.MerchantId
ORDER BY m.Name, ms.Service

结果:

MerchantID  Name              Services
----------  ----------------  --------------------
         1  Adams Consulting  Brand Consulting,SEO

2

6

OMG Ponies 15 年前

短期解决方案是使用 FIND_IN_SET function 加入商户和服务表:

SELECT *
  FROM MERCHANT m
  JOIN SERVICES s ON FIND_IN_SET(s.service_id, m.services) > 0

The long term solution is to correct your tables - never allow columns to contain comma separated lists of referential ID/etc values.