mysql 一对多 join_mysql多表left join 1对多的解决办法

一个表left join多个表并导出csv本身是很简单的事。但是主表的一列有多个值，一开始用逗号分隔保存数据，这样做的好处是方便应用程序处理，但是对于mysql来说这是anti-pattern的做法。果然，在写left join的时候无法实现一个field保存多ID，并跟关联表匹配输出csv文件。(尝试了find_in_set 只能输入一行)

还是应该遵守RMDB的范式来设计DB，将多值的列，拆分出一个新表来保存。这样写left join时就方便一对一处理。最后，要输出多个值时，多值会存在多列，可以采用GROUP_CONCAT() ... group by将多列值合并为一行。

最终的sql如下

CREATE VIEW edc_v_customer AS SELECT A.`id`,

A.`email`,

A.`mobile`, A.`first_name`,A.`last_name`,

A.`manggis_id`,

A.`external_id`,

GROUP_CONCAT(ST.item_value) as site,

A.`nick_name`,

A.`chinese_name`,

S.`item_value` as sex,

A.`birthdate`,

A.`age`,

G.`item_value` as `age_group`,

I.`item_value` as income,

E.`item_value` as educate,

M.`item_value` as marial,

O.`item_value` as occupation,

C.`item_value` as country,

A.`update_time`,A.`status` FROM edc_customer AS A

LEFT JOIN edc_prop_country AS C ON A.`country_id`>0 and A.`country_id`=C.id

LEFT JOIN edc_customer_in_site AS CS ON (CS.customer_id=A.id)

LEFT JOIN edc_prop_site AS ST ON ST.id=CS.site_id

LEFT join `edc_prop_sex` AS S on A.`sex_id`>0 and S.id=A.`sex_id`

LEFT join `edc_prop_occupation` AS O on A.`occupation_id`>0 and O.id=A.`occupation_id`

LEFT join `edc_prop_marial` AS M on A.`marial_id`>0 and M.id=A.`marial_id`

LEFT join `edc_prop_income` AS I on A.`income_id`>0 and I.id=A.`income_id`

LEFT join `edc_prop_educate` AS E on A.`educate_id`>0 and E.id=A.`educate_id`

LEFT join `edc_prop_age_group` AS G on A.`age_group_id`>0 and G.id=A.`age_group_id`

GROUP BY A.id

; 另外，要记得加上index，如果没有index，表数据大的话会非常慢，甚至拒绝响应。 默认导出csv文件是没有header的，为了增加header，可以选择“Custom - display all possible options”，勾选“Put columns names in the first row”