为何不使用GROUP_CONCAT? [英] Reasons not to use GROUP_CONCAT?

问题描述

我刚刚发现了这个非常有用的MySQL函数GROUP_CONCAT.它对我来说是如此有用且过于简化，以至于我实际上不敢使用它.主要是因为自从我开始进行网络编程以来已经有一段时间了，而且我从未在任何地方见过它.以下是一个很棒的用法示例

I just discovered this amazingly useful MySQL function GROUP_CONCAT. It appears so useful and over-simplifying for me that I'm actually afraid of using it. Mainly because it's been quite some time since I started in web-programming and I've never seen it anywhere. A sample of awesome usage would be the following

表clients每个客户端具有唯一ID的客户端(您不说...).
表currencies具有3列client_id，currency和amount.

Table clients holds clients ( you don't say... ) one row per client with unique IDs.
Table currencies has 3 columns client_id, currency and amount.

现在，如果我想从clients表及其余额中获取用户15的name，并使用旧"数组覆盖方法，则必须使用以下SQL

Now if I wanted to get user 15's name from the clients table and his balances, with the "old" method of array overwriting I would have to do use the following SQL

SELECT id, name, currency, amount 
FROM clients LEFT JOIN currencies ON clients.id = client_id 
WHERE clients.id = 15

然后在php中，我将不得不遍历结果集并进行数组覆盖(我确实不是特别喜欢，尤其是在大型结果集中)

Then in php I would have to loop through the result set and do an array overwrite ( which I'm really not a big fan of, especially in massive result sets ) like

$result = array();
foreach($stmt->fetchAll() as $row){
    $result[$row['id']]['name'] = $row['name'];
    $result[$row['id']]['currencies'][$row['currency']] = $row['amount'];
}

不过，借助新发现的功能，我可以使用它

However with the newly discovered function I can use this

SELECT id, name, GROUP_CONCAT(currency) as currencies GROUP_CONCAT(amount) as amounts 
FROM clients LEFT JOIN currencies ON clients.id = client_id 
WHERE clients.id = 15
GROUP BY clients.id

然后在应用程序级别上，事情是如此的棒极了

Then on application level things are so awesome and pretty

$results = $stmt->fetchAll();
foreach($results as $k => $v){
    $results[$k]['currencies'] = array_combine(explode(',', $v['currencies']), explode(',', $v['amounts']));
}

我想问的问题是，在性能上使用此功能是否有任何弊端或根本不存在任何弊端，因为对我而言，它看起来就像是纯粹的令人敬畏，这使我认为人们一定有理由不这样做经常使用它.

The question I would like to ask is are there any drawbacks to using this function in performance or anything at all, because to me it just looks like pure awesomeness, which makes me think that there must be a reason for people not to be using it quite often.

最后，我想问的是，除了数组覆盖以外，还有哪些其他选项可以从MySQL结果集中获得多维数组，因为如果我选择15列，那么编写它会非常麻烦野兽..

I want to ask, eventually, what are the other options besides array overwriting to end up with a multidimensional array from a MySQL result set, because if I'm selecting 15 columns it's a really big pain in the neck to write that beast..

推荐答案

• 通常使用GROUP_CONCAT()调用分组逻辑并创建临时表，这通常会对性能产生很大的负面影响.有时，您可以添加正确的索引来避免在group-by查询中使用临时表，但并非在每种情况下都如此.

  • Using GROUP_CONCAT() usually invokes the group-by logic and creates temporary tables, which are usually a big negative for performance. Sometimes you can add the right index to avoid the temp table in a group-by query, but not in every case.

    正如@MarcB所指出的，组连接的字符串的默认长度限制很短，许多人被截断的列表弄糊涂了.您可以使用 group_concat_max_len 来增加限制

    As @MarcB points out, the default length limit of a group-concatenated string is pretty short, and many people have been confused by truncated lists. You can increase the limit with group_concat_max_len.

    在PHP中将字符串分解为数组不是免费的.仅仅因为您可以在PHP中的一个函数调用中做到这一点，并不意味着它是性能最佳的.我尚未对差异进行基准测试，但我怀疑您也有.

    Exploding a string into an array in PHP does not come for free. Just because you can do it in one function call in PHP doesn't mean it's the best for performance. I haven't benchmarked the difference, but I doubt you have either.

    GROUP_CONCAT()是一种MySQLism.其他SQL产品未广泛支持它.在某些情况下(例如SQLite)，它们具有GROUP_CONCAT()函数，但它与MySQL中的工作原理并不完全相同，因此如果必须支持多个RDBMS后端，这可能会导致令人困惑的错误.当然，如果您不必担心移植，这不是问题.

    GROUP_CONCAT() is a MySQLism. It is not supported widely by other SQL products. In some cases (e.g. SQLite), they have a GROUP_CONCAT() function, but it doesn't work exactly the same as in MySQL, so this can lead to confusing bugs if you have to support multiple RDBMS back-ends. Of course, if you don't need to worry about porting, this is not an issue.

    如果要从currencies表中获取多个列，则需要多个GROUP_CONCAT()表达式.列表是否保证顺序相同?也就是说，一个列表中的第三个字段是否与下一个列表中的第三个字段相对应?答案是否定的-除非您在GROUP_CONCAT()内使用ORDER BY子句指定顺序，否则不会.

    If you want to fetch multiple columns from your currencies table, then you need multiple GROUP_CONCAT() expressions. Are the lists guaranteed to be in the same order? That is, does the third field in one list correspond to the third field in the next list? The answer is no -- not unless you specify the order with an ORDER BY clause inside the GROUP_CONCAT().

    我通常会喜欢您的第一种代码格式，使用常规结果集，然后遍历结果，保存到由客户ID索引的新数组中，然后将货币附加到数组中.这是一个简单的解决方案，使SQL简单易行，并且易于优化，如果要提取多个列，效果更好.

    I usually favor your first code format, use a conventional result set, and loop over the results, saving to a new array indexed by client id, appending the currencies to an array. This is a straightforward solution, keeps the SQL simple and easier to optimize, and works better if you have multiple columns to fetch.

    我并不是要说GROUP_CONCAT()不好！在许多情况下，它确实很有用.但是，试图使任何一种一刀切"的规则使用(或避免使用)任何功能或语言功能都是很简单的.

    I'm not trying to say GROUP_CONCAT() is bad! It's really useful in many cases. But trying to make any one-size-fits-all rule to use (or to avoid) any function or language feature is simplistic.

    这篇关于为何不使用GROUP_CONCAT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！