Mysql多对多查询、列合并

表信息

• 资源表 synsource                                                 199824       rows
• 产品表 tab_product_detail                                  108              rows
• 资源与产品多对多的关联表 tab_r_sourmach  1,113,866   rows

以上表均采用MyISAM引擎。

连接测试

因 为方便用户进行更好的资源的搜索，所以需要将资源数据全部建立索引至Lucene中，希望在Lucene中存储的Document为：

sourceName	fileName	subject	grade	products
英语学生用书第十一册	外研新标准.nwe	英语	小学	NP7000 NP6000 NP2300
英语学生用书第十二册	外研新标准2.nwe	英语	小学	NP2300

以上前四个字段是属于 synsource表中，而 products 属于 tab_product_detail 表，它们之间的关系由tab_r_sourmach进行中间关联。
一开始想到的是直接使用以下SQL：

Sql代码  

1. select s1.sourid, s1.sourcename , t.product_name from synsource s1   
2.   left join tab_r_sourmach c   
3.     on s1.sourid=c.sourid  
4.       left join tab_product_detail t   
5.         on c.product_id = t.product_id where s1.sourceid=1  

5 rows in set (0.45 sec)

sourid	sourcename	subjectname	product_name
1	小六上Module07	英语	ND520
1	小六上Module07	英语	NP560T
1	小六上Module07	英语	NP560+
1	小六上Module07	英语	NP360+

对于以上SQL的结果重复数据的合并处理考虑采用了 GROUP_CONCAT：

Sql代码  

1. select s1.*, GROUP_CONCAT(t.product_name SEPARATOR ' ') as product_name  
2.   from tab_synsource s1   
3.     left join tab_r_sourmach c  
4.       on s1.sourid=c.sourid  
5.         left join tab_product_detail t   
6.               on c.product_id = t.product_id where s1.sourceid=1  

1 row in set (0.31 sec)

sourid	sourcename	subjectname	product_name
1	小六上Module07	英语	ND520 NTV518 NP560T NP560+ NP360+

看上去似乎能够满足需求，但是以上只是针对单条数据进行查询，对于数据的批量索引建立的话肯定是直接 limit 500之类的操作。于是去掉以上where加上 limit 10执行之后，半天都执行不出来，估计它先会全部数据连接查询之后再去 limit。

于是先测试一下直接(tab_r_sourmach, tab_product_detail)两表关联看看效率如何：

Sql代码  

1. select c.sourid  
2. from tab_r_sourmach c left join tab_product_detail t   
3. on  c.product_id = t.product_id    
4. group  by c.sourid limit 500;  

以上语句用时：(5.65 sec)

再加上GROUP_CONCAT试试：

Sql代码  

1. select c.sourid, GROUP_CONCAT(t.product_name SEPARATOR ' ') as products  
2. from tab_r_sourmach c left join tab_product_detail t   
3. on  c.product_id = t.product_id    
4. group  by c.sourid limit 800;  

输出结果样例：

sourid	products
767	ND520 ND416 NTV518 NP560T NP560+ NP360+
760	ND550 NP600+ NP9588 NP600

800 rows in set (1.74 sec)， 经过反复测试都发现加上了GROUP_CONCAT 函数比没使用该函数快了五倍多，暂时还不知道为什么。
不过1秒左右对于后台索引建立来说还算能够接受，于是再将以上SQL与 synsource 进行连接测试：

Sql代码  

1. select * from synsource s, (  
2.   select c.sourid,  
3.       GROUP_CONCAT(t.product_name SEPARATOR ' ')   
4.       from tab_r_sourmach c left join tab_product_detail t   
5.       on   
6.       c.product_id = t.product_id   
7.       group  by c.sourid  limit 1000  
8.     ) as b  
9. where s.sourid = b.sourid;  

输出结果样例：

968	词汇学习-第三级K(参考剑桥少儿英语)	英语	NP600+ NP600
983	词汇学习-六年级上Unit7(参考湘教版)	英语	ND520 ND416 NTV518 NP560T NP560+ NP360+

1000 rows in set (2.02 sec)


不知道大家对这种情况如何处理的？以上的SQL虽然能够达到目的，不过还是存在不足。

忘说了，在使用MYSQL GROUP_CONCAT函数进行分组连接时，它对字符串的长度连接是有长度限止的，默认的情况的话我发布 products 太长的话会被截断，如果想一劳永逸的话直接在my.cnf 中加入 group_concat_max_len=99999 即可。