当前位置:首页 > 开发 > 数据库 > 正文

Mysql多对多查询、列合并

发表于: 2011-04-11   作者:denger   来源:转载   浏览次数:
摘要: 表信息 资源表 synsource                             &nb
表信息
  • 资源表 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:
select s1.sourid, s1.sourcename , t.product_name from synsource s1 
  left join tab_r_sourmach c 
    on s1.sourid=c.sourid
      left join tab_product_detail t 
        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
select s1.*, GROUP_CONCAT(t.product_name SEPARATOR ' ') as product_name
  from tab_synsource s1 
    left join tab_r_sourmach c
      on s1.sourid=c.sourid
        left join tab_product_detail t 
              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)两表关联看看效率如何:
select c.sourid
from tab_r_sourmach c left join tab_product_detail t 
on  c.product_id = t.product_id  
group  by c.sourid limit 500;

以上语句用时:(5.65 sec)

再加上GROUP_CONCAT试试:
select c.sourid, GROUP_CONCAT(t.product_name SEPARATOR ' ') as products
from tab_r_sourmach c left join tab_product_detail t 
on  c.product_id = t.product_id  
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 进行连接测试:
select * from synsource s, (
  select c.sourid,
      GROUP_CONCAT(t.product_name SEPARATOR ' ') 
      from tab_r_sourmach c left join tab_product_detail t 
      on 
      c.product_id = t.product_id 
      group  by c.sourid  limit 1000
    ) as b
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 即可。

Mysql多对多查询、列合并

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
最近用到数据的的字符连接 || 在此做个笔记 2个字段用||连接起来,如果要加分隔符号。如下面 字段A|
上周写了一篇GridView多行多列合并单元格(完整代码和例子),实现的方法有很多不足之处,比如只能
create or replace function njutec_evaluation(paramid in varchar2,paramtype in varchar2) retur
这周工作时曾遇到一个问题。在一个MYSQL的表里做类似下面这一个很简单查询的时候耗时接近1秒钟的时
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UserAdd.aspx.cs" Inherits="Admin_U
源数据: 要转换成 解决方案: SELECT [1] AS c1,[2] AS c2,[3] FROM ( SELECT ROW_NUMBER() OVER(OR
关键代码请参考http://www.devexpress.com/Support/Center/p/K18333.aspx 最新DEMO 下载 The curren
1.MySQL多实例介绍 1.1.什么是MySQL多实例 MySQL多实例就是在一台机器上开启多个不同的服务端口(如
css多行多列的新闻模式 1. 最简单的两列 <style> ul { width:250px; list-style:none; } li {
css多行多列的新闻模式 1. 最简单的两列 <style> ul { width:250px; list-style:none; } li {
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号