The power of MySQL’s GROUP_CONCAT


The power of MySQL’s GROUP_CONCAT By Vadim Tkachenko and Michael Rikmas Copyright © 2006-2014 Percona LLC MySQL has useful extension to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group. When is GROUP_CONCAT useful? Usually while working with Support customers I recommend it when you have aggregation of many-to-many info. It makes the view simpler and more beautiful and it doesn’t need much effort to make it work. In the Percona eBook, "The power of MySQL’s GROUP_CONCAT," Vadim Tkachenko and Michael Rikmas provide real-world examples of GROUP_CONCAT in action. The power of MySQL’s GROUP_CONCAT 3 5 Chapter 1: GROUP_CONCAT useful GROUP BY extension Chapter 2: The power of MySQL's GROUP_CONCAT About the authors 12 Table of Contents Percona was founded in August 2006 by Peter Zaitsev and Vadim Tkachenko and now employs a global network of experts with a staff of more than 100 people. Our customer list is large and diverse, including Fortune 50 firms, popular websites, and small startups. We have over 1,800 customers and, although we do not reveal all of their names, chances are we're working with every large MySQL user you've heard about. To put Percona's MySQL expertise to work for you, please contact us. About Percona Skype: oncall.percona GTalk: oncall@percona.com AIM (AOL Instant Messenger): oncallpercona Telephone direct-to-engineer: +1-877-862-4316 or UK Toll Free: +44-800-088-5561 Telephone to live operator: +1-888-488-8556 Customer portal: https://customers.percona.com/ Is this an emergency? Get immediate assistance from Percona Support 24/7. Click here Copyright © 2006-2014 Percona LLC The power of MySQL’s GROUP_CONCAT Chapter 1: GROUP_CONCAT useful GROUP BY extension Chapter 1: GROUP_CONCAT useful GROUP BY extension MySQL has useful extension to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group. Where it can be useful? For example to get PHP array without looping inside PHP: Table: 3 By Vadim Tkachenko The power of MySQL’s GROUP_CONCAT Chapter 1: GROUP_CONCAT useful GROUP BY extension Handling in PHP: old way: with group_concat: This should work faster, as we remove loop from PHP to MySQL server side. Also it can be handy to use result concatenated string as part of IN statement: 4 Sure, the last example can be handled with one query with joins, but sometimes we need the temporary ids in clients code, for example to execute query on another server. One more thing: you may want to add ORDER BY NULL statement after GROUP_BY to avoid unnecessary sorting with filesort The power of MySQL’s GROUP_CONCAT Chapter 2: The power of MySQL's GROUP_CONCAT The power of MySQL's GROUP_CONCAT When is GROUP_CONCAT useful? Usually while working with Support customers I recommend it when you have aggregation of many-to-many info. It makes the view simpler and more beautiful and it doesn’t need much effort to make it work. Some simple examples: This is a test table: Without grouping info the only way you can check things is: 5 By Michael Rikmas The power of MySQL’s GROUP_CONCAT Chapter 2: The power of MySQL's GROUP_CONCAT But it looks much better and easier to read with GROUP_CONCAT: Easy? Let’s go to production usage and some “real” examples Assume you have 4 Support Engineers who were working with 6 Customers this week on 15 issues. As it usually happens: everyone (sure, except those who are on vacation :)) worked on everything with everybody. How you would represent it? 6 Here is my way: Create test tables: engineers (id, name, surname, URL) – list of engineers customers (id, company name, URL) – list of customers issues (id, customer_id, description) – list of issues assigned to customers workflow (id, engineer_id, issue_id) – list of actions: issues and engineers who worked on them The power of MySQL’s GROUP_CONCAT Chapter 2: The power of MySQL's GROUP_CONCAT 7 The power of MySQL’s GROUP_CONCAT Chapter 2: The power of MySQL's GROUP_CONCAT 8 Please procedeto the next page The power of MySQL’s GROUP_CONCAT Chapter 2: The power of MySQL's GROUP_CONCAT Examples: List of issues for each engineer (GROUP_CONCAT): 9 List of engineers for each customer (GROUP_CONCAT inside of GROUP_CONCAT): The power of MySQL’s GROUP_CONCAT Chapter 2: The power of MySQL's GROUP_CONCAT 10 PHP/HTML? Why not? It’s easy Source Code: The power of MySQL’s GROUP_CONCAT Chapter 2: The power of MySQL's GROUP_CONCAT Powered by TCPDF (www.tcpdf.org) 11 Visit the Percona library for more free MySQL eBook selections http://www.percona.com/resources/mysql-ebooks About the authors Copyright © 2006-2014 Percona LLC Vadim Tkachenko co-founded Percona in 2006 after four years in the High Performance Group within the official MySQL Support Team. He serves on Percona's executive team as CTO. He leads Percona's development group, which produces Percona Server, Percona XtraDB, and Percona XtraBackup. He is an expert in LAMP performance, especially optimizing MySQL and InnoDB internals to take full advantage of modern hardware using his multi-threaded programming background. Source code patches authored by Vadim have been incorporated by Oracle Corporation and its predecessors into the mainstream MySQL and InnoDB products. Vadim co-authored High Performance MySQL, 3rd Edition with Percona's Peter Zaitsev and Baron Schwartz. Previously he founded a web development company in his native Ukraine. He now lives in California with his wife and their two children. Michael Rikmas joined Percona in October 2007. As a Percona support engineer, he is involved in assuring Percona's 24x7 support coverage. Prior to joining the company, Michael worked as a consultant on Enterprise Resource Planning and Customer Relationship Management systems. His introduction to computers was in 2000, when he began his university studies. He now has an undergraduate degree in computer sciences, and in 2011 he earned an MBA. Michael was born in Chervonograd, a small city in western Ukraine. He was married on his 25th birthday. He loves his wife and son and tries to spend as much of his free time as possible with them. His hobbies are music and cars.
还剩11页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

f7ex

贡献于2014-10-19

下载需要 5 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf