MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

在开发网站的时候,数据库操作是必须,复杂的网站有时要涉及到几张表的同时操作。以下简单讲解一下多表查询和GROUP_CONCAT函数的使用。例如有三张表 jd_role:角色表,jd_role_privilege:角色权限id关联表,jd_privilege:权限表,三张表的结构如下:

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

1、要求查询每个角色拥有的权限:

使用left join左外连进行连表操作:

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

此时,就可以查询出每个角色拥有哪些权限,但是这样查询不是很符合实际开发,比如说在修改角色权限的时候,如何把一个角色的权限放入下拉选择框中,这需要把一个角色拥有的所有的权限转换成字符串或者数组,这时候可以使用GROUP_CONCAT函数:

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

2、以id分组,把权限名称打印成一行,用分号隔开:

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

3、以id分组,把权限名称打印成一行,去除重复字段:

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

4、以id分组,把权限名称打印成一行,用逗号隔开,根据权限id排序:

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

本质上就是用到了下面的样板句:

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

例如:

select id,group_concat(name) from aa group by id;  

// separator 提供自定义分割线,默认是逗号,
select id,group_concat(name separator ';') from aa group by id;  
SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;

// 去重复
select id,group_concat(distinct name) from aa group by id;  

// 排序
select id,group_concat(name order by name desc) from aa group by id;  

// 分割多字符串
SELECT  id, GROUP_CONCAT( DISTINCT CONCAT(aa.name,',',aa.sex)  SEPARATOR ';' ) FROM aa GROUP BY id;  

// 来个多表的, 能读懂就理解差不多了!(四表联查)
SELECT 
`table_products`.`id`, `table_products`.`title`, `table_products`.`tags`,
`table_ali_products`.`link`,
GROUP_CONCAT(DISTINCT CONCAT(table_product_images.`id`, ",,,", table_product_images.`src`) SEPARATOR ";;;") as images,
GROUP_CONCAT(DISTINCT CONCAT(table_product_variants.`id`, ",,,", table_product_variants.`sku`, ",,,", table_product_variants.`image_id`, ",,,", table_product_variants.`price`) SEPARATOR ";;;") as variants 

FROM table_products  

LEFT JOIN table_ali_products ON (table_products.ali_product_id = table_ali_products.id ) 
LEFT JOIN table_product_images ON (table_products.id = table_product_images.product_id ) 
LEFT JOIN table_product_variants ON (table_products.id = table_product_variants.product_id ) 

WHERE (  table_products.store_id= '1'  )   
GROUP BY table_products.id 


注意 GROUP_CONCAT() maximum length

你链表太多,查出的数据就越大,但是 GROUP_CONCAT 默认输出最大字节是 1024, 如果大于这个数字,多余的数据将会自动删除,势必数据将不完整,解决的方法就是,临时增加 group_concat_max_len 的值,例如:

SET SESSION group_concat_max_len = 1000000;
select group_concat(column) from table group by column;

 

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

 

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

 

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

 

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns
MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

 

 

本文:MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns